A comprehensive list of over 30+ SQL interview questions along with their answers

date
May 10, 2024
slug
SQL-30+interview-questions
status
Published
tags
SQL
Interview
questions
DBMS
summary
Questions cover a wide range of topics from basic to advanced SQL concepts
type
Post

Basic SQL Questions

  1. What is SQL?
      • SQL stands for Structured Query Language, used to manage and manipulate relational databases.
  1. What are the different types of SQL statements?
      • DDL (Data Definition Language): CREATE, ALTER, DROP
      • DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
      • DCL (Data Control Language): GRANT, REVOKE
      • TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
  1. What is a primary key?
      • A primary key is a column (or a set of columns) that uniquely identifies each row in a table.
  1. What is a foreign key?
      • A foreign key is a column that creates a relationship between two tables by referring to the primary key of another table.
  1. What is a join? Explain its types.
      • A join is used to combine rows from two or more tables based on a related column.
        • INNER JOIN: Returns only matching rows.
        • LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and matching rows from the right table.
        • RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and matching rows from the left table.
        • FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in one of the tables.
        • CROSS JOIN: Returns the Cartesian product of both tables.
        • SELF JOIN: A table is joined with itself.
  1. What is normalization?
      • Normalization is the process of organizing data to minimize redundancy and improve data integrity.
  1. What are the different normal forms?
      • 1NF (First Normal Form): Ensures that each column contains atomic values, and each record is unique.
      • 2NF (Second Normal Form): Achieves 1NF and removes partial dependency.
      • 3NF (Third Normal Form): Achieves 2NF and removes transitive dependency.
      • BCNF (Boyce-Codd Normal Form): A stronger version of 3NF.
  1. What is a unique key?
      • A unique key ensures all values in a column are unique, similar to a primary key, but it can have null values.
  1. What is an index?
      • An index is a database object that improves the speed of data retrieval operations on a table.
  1. What is a view?
      • A view is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table.

Intermediate SQL Questions

  1. What is a stored procedure?
      • A stored procedure is a prepared SQL code that you can save and reuse. It can accept parameters and execute multiple SQL statements.
  1. What is a trigger?
      • A trigger is a set of instructions that automatically executes (or “fires”) in response to certain events on a particular table or view.
  1. Explain the difference between DELETE and TRUNCATE.
      • DELETE removes specified rows from a table and can be rolled back.
      • TRUNCATE removes all rows from a table, resets table identity, and cannot be rolled back.
  1. What is a cursor?
      • A cursor is a database object used to retrieve, manipulate, and navigate through a result set row by row.
  1. What are aggregate functions? Name a few.
      • Aggregate functions perform calculations on multiple rows and return a single value.
        • Examples: COUNT(), SUM(), AVG(), MIN(), MAX()
  1. What is a subquery?
      • A subquery is a query within another query. It is used to return data that will be used in the main query.
  1. What is the difference between WHERE and HAVING clause?
      • WHERE is used to filter rows before any groupings are made.
      • HAVING is used to filter groups after the GROUP BY clause.
  1. Explain the use of GROUP BY clause.
      • GROUP BY groups rows that have the same values in specified columns into summary rows, like “find the number of customers in each country.”
  1. What is a composite key?
      • A composite key is a primary key composed of multiple columns used to identify a unique row in a table.
  1. What is a union?
      • UNION combines the result sets of two or more SELECT statements (eliminates duplicates).

Advanced SQL Questions

  1. What is the difference between UNION and UNION ALL?
      • UNION removes duplicate records.
      • UNION ALL includes duplicate records.
  1. What is a Common Table Expression (CTE)?
      • A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
  1. Explain window functions in SQL.
      • Window functions perform calculations across a set of table rows related to the current row, allowing you to use row-based calculations without collapsing rows.
  1. What is the difference between RANK() and DENSE_RANK()?
      • RANK() assigns a rank to each row within a partition, leaving gaps for tied ranks.
      • DENSE_RANK() assigns ranks without gaps between tied ranks.
  1. Explain the use of the PARTITION BY clause.
      • PARTITION BY divides the result set into partitions and applies the window function to each partition.
  1. What is database sharding?
      • Sharding is a type of database partitioning that separates very large databases into smaller, faster, more easily managed parts called shards.
  1. What is a materialized view?
      • A materialized view is a database object that contains the results of a query. It is updated periodically based on the query definition.
  1. Explain ACID properties in the context of transactions.
      • ACID stands for Atomicity, Consistency, Isolation, Durability, ensuring reliable transaction processing in a database.
  1. What is the difference between OLTP and OLAP?
      • OLTP (Online Transaction Processing) is designed for managing transactional data.
      • OLAP (Online Analytical Processing) is designed for analysis and query of large volumes of data.
  1. What are the different types of indexes?
      • Unique Index, Non-Unique Index, Clustered Index, Non-Clustered Index, Composite Index, Bitmap Index

SQL Performance Tuning Questions

  1. What is SQL query optimization?
      • SQL query optimization involves improving the performance of SQL queries to reduce their execution time and resource consumption.
  1. How do you optimize SQL queries?
      • Indexing, Avoiding unnecessary columns in SELECT, Using joins instead of subqueries, Analyzing execution plans, Avoiding wildcard (%) at the beginning of a LIKE pattern, Using WHERE instead of HAVING
  1. What is an execution plan?
      • An execution plan is a visual representation of the steps the database takes to execute a query, used for query performance analysis.
  1. What is database indexing and why is it important?
      • Indexing is a technique to improve the speed of data retrieval operations on a database table at the cost of additional writes and storage space.
  1. What is the difference between a clustered and a non-clustered index?
      • Clustered index sorts and stores the data rows of the table based on the key values. Each table can have only one clustered index.
      • Non-clustered index creates a separate object within the table that points back to the original table rows after sorting.
  1. What is query caching?
      • Query caching stores the results of a query in memory, allowing subsequent executions of the same query to be retrieved quickly from the cache.
  1. Explain database partitioning.
      • Database partitioning involves dividing a database into smaller, more manageable pieces to improve performance and manageability.
  1. What is the use of the EXPLAIN statement?
      • EXPLAIN statement provides information about how MySQL executes statements, helping to analyze and optimize queries.
  1. How does indexing affect INSERT operations?
      • Indexing can slow down INSERT operations because the database has to update the index every time a row is inserted.
  1. What is denormalization?
      • Denormalization is the process of combining tables to reduce the number of joins and improve query performance, often at the expense of data redundancy.

Miscellaneous SQL Questions

  1. What is a data warehouse?
      • A data warehouse is a system used for reporting and data analysis, storing large volumes of historical data for querying and analysis.
  1. Explain the difference between data mining and data warehousing.
      • Data mining is the process of discovering patterns and knowledge from large amounts of data.
      • Data warehousing is the process of compiling and organizing data into one common database.
  1. What is ETL?
      • ETL stands for Extract, Transform, Load, which are the three processes used to move data from one database to another.
  1. What is a fact table?
      • A fact table stores quantitative data for analysis and is often at the center of a star schema or snowflake schema in a data warehouse.
  1. What is a dimension table?
      • A dimension table stores attributes, or dimensions, that describe the objects in a fact table.
  1. What is the difference between SQL and PL/SQL?
      • SQL is a standard language for accessing and manipulating databases.
      • PL/SQL (Procedural Language/SQL) is an extension of SQL used in Oracle databases to write full programs with loops, conditions, and variables.
  1. What is referential integrity?
      • Referential integrity ensures that relationships between tables remain consistent. When one table has a foreign key to another, referential integrity ensures that the key values always point to valid rows in the referenced table.
  1. What is a surrogate key?
      • A surrogate key is a unique identifier for an entity, often used as a primary key. It is not derived from application data.
  1. Explain the difference between DELETE and DROP.
      • DELETE removes rows from a table.
      • DROP removes the entire table or database schema from the database.
  1. What is a data lake?
      • A data lake is a storage repository that holds a vast amount of raw data in its native format until it is needed for analysis.

Advanced SQL Questions Continued

  1. What is the use of the COALESCE function?
      • COALESCE returns the first non-null value in a list of arguments.
  1. What is a recursive CTE?
      • A recursive CTE is a common table expression that references itself, allowing recursive queries.
  1. What is the difference between VARCHAR and CHAR?
      • VARCHAR stores variable-length strings, while CHAR stores fixed-length strings.
  1. What is the purpose of the SQL LIMIT clause?
      • LIMIT is used to specify the number of records to return.
  1. What are window functions and how do they differ from aggregate functions?
      • Window functions perform calculations across a set of table rows related to the current row without collapsing the rows into a single output row like aggregate functions.
  1. What is the difference between scalar and vector subqueries?
      • Scalar subqueries return a single value, while vector subqueries return multiple values.
  1. What is the use of the DISTINCT keyword in SQL?
      • DISTINCT is used to remove duplicate rows from the result set.
  1. What are the advantages of using stored procedures?
      • Stored procedures improve performance, ensure code reusability, enhance security, and reduce network traffic.
  1. Explain the difference between ROW_NUMBER(), RANK(), and DENSE_RANK().
      • ROW_NUMBER() assigns a unique sequential integer to rows within a partition.
      • RANK() assigns a rank with gaps for tied ranks.
      • DENSE_RANK() assigns ranks without gaps for tied ranks.
  1. What is the use of the CASE statement in SQL?
      • CASE is used to implement conditional logic in SQL queries.
If you have any questions, please contact me.