SQL interviews test your grasp of database fundamentals, joins, subqueries, indexing, window functions, and query optimization. This guide covers 100+ SQL interview questions and answers for freshers and experienced professionals, ranging from basic definitions to advanced scenario based problems like finding duplicates, calculating running totals, and writing window function queries.
SQL remains the backbone of every data driven role, from software developers to data analysts and data scientists. Whether you are appearing for your first technical interview or preparing for a senior data engineering role, a strong hold on SQL concepts can set you apart. This guide from Codegnan brings together 100+ SQL interview questions and answers, organized from the basics to advanced scenario based queries, so you can prepare in one place. If you want structured, mentor led training instead of self study, you can also explore Codegnan’s MySQL course or the broader Data Analytics program that covers SQL alongside Python and Excel.
SQL Basics
1. What is SQL?
SQL, or Structured Query Language, is a standard programming language used to create, manage, and manipulate relational databases. It allows users to insert, update, delete, and retrieve data, as well as define and control the structure of database objects like tables, views, and indexes.
2. What are the different types of SQL commands?
SQL commands are grouped into five categories:
- DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
- DML (Data Manipulation Language): INSERT, UPDATE, DELETE
- DQL (Data Query Language): SELECT
- DCL (Data Control Language): GRANT, REVOKE
- TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
3. What is the difference between SQL and MySQL?
SQL is a language used to interact with relational databases, while MySQL is a specific database management system that uses SQL to perform operations. In simple terms, SQL is the language, and MySQL is one of many software products (along with PostgreSQL, Oracle, and SQL Server) that implement that language.
4. What is a database?
A database is an organized collection of structured data that is stored electronically and can be easily accessed, managed, and updated. Databases allow multiple users and applications to store and retrieve information efficiently.
5. What is an RDBMS?
An RDBMS, or Relational Database Management System, is software that stores data in tables made up of rows and columns, with relationships defined between those tables using keys. Examples include MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.
6. What are primary and foreign keys?
A primary key uniquely identifies each record in a table and cannot contain NULL values. A foreign key is a column (or set of columns) in one table that references the primary key of another table, establishing a relationship between the two tables.
7. What is a unique key?
A unique key ensures that all values in a column are distinct, similar to a primary key, but unlike a primary key, a unique key can accept one NULL value and a table can have multiple unique keys.
8. What is a composite key?
A composite key is a primary key made up of two or more columns that together uniquely identify a row in a table, even though no single column among them is unique on its own.
9. What are constraints in SQL?
Constraints are rules applied to table columns to enforce data integrity. Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.
10. What is the difference between DELETE, TRUNCATE, and DROP?
DELETE removes specific rows based on a condition and can be rolled back. TRUNCATE removes all rows from a table quickly and resets identity columns, but cannot usually target specific rows. DROP removes the entire table structure along with its data permanently from the database.
11. What is the difference between CHAR and VARCHAR?
CHAR is a fixed length data type that always uses the defined storage space, padding shorter values with spaces. VARCHAR is a variable length data type that only uses as much storage as the actual data requires, making it more space efficient for varying text lengths.
12. What are SQL data types?
SQL data types define the kind of value a column can store. Common categories include numeric types (INT, FLOAT, DECIMAL), character types (CHAR, VARCHAR, TEXT), date and time types (DATE, DATETIME, TIMESTAMP), and boolean types.
SELECT, Filtering, and Aggregation
13. What is the SELECT statement?
The SELECT statement is used to retrieve data from one or more tables in a database. It can be combined with clauses like WHERE, GROUP BY, HAVING, and ORDER BY to filter, group, and sort the results.
14. What is the WHERE clause?
The WHERE clause filters rows based on a specified condition before any grouping takes place. Only rows that satisfy the condition are included in the result set.
15. What is the HAVING clause?
The HAVING clause filters groups of rows after aggregation has been performed, typically used alongside GROUP BY to filter based on aggregate function results like SUM or COUNT.
16. What is the GROUP BY clause?
The GROUP BY clause groups rows that share the same values in specified columns into summary rows, often used with aggregate functions to calculate totals, averages, or counts for each group.
17. What is the ORDER BY clause?
The ORDER BY clause sorts the result set by one or more columns, either in ascending (ASC) or descending (DESC) order. By default, results are sorted in ascending order.
18. What is DISTINCT in SQL?
DISTINCT is a keyword used with SELECT to remove duplicate rows from the result set, returning only unique combinations of the selected columns.
19. What are aggregate functions in SQL?
Aggregate functions perform calculations on a set of values and return a single summarized value. Common aggregate functions include COUNT(), SUM(), AVG(), MIN(), and MAX().
20. What is the COUNT() function?
COUNT() returns the number of rows that match a specified condition. It can count all rows, non-null values in a specific column, or distinct values when combined with DISTINCT.
21. What is the SUM() function?
SUM() calculates the total of all numeric values in a specified column, ignoring NULL values during the calculation.
22. What is the AVG() function?
AVG() calculates the average value of a numeric column by dividing the sum of all values by the count of non-null values.
23. What is the MIN() function?
MIN() returns the smallest value in a specified column across the rows being evaluated.
24. What is the MAX() function?
MAX() returns the largest value in a specified column across the rows being evaluated.
25. What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping and cannot use aggregate functions directly. HAVING filters groups after aggregation and is specifically designed to work with aggregate functions like SUM(), COUNT(), or AVG().
Joins in SQL
26. What are joins in SQL?
Joins combine rows from two or more tables based on a related column between them, allowing you to query data that is spread across multiple tables in a relational database.
27. What is an INNER JOIN?
An INNER JOIN returns only the rows that have matching values in both tables being joined. Rows without a match in either table are excluded from the result.
28. What is a LEFT JOIN?
A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.
29. What is a RIGHT JOIN?
A RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and the matching rows from the left table, filling in NULLs where there is no corresponding match on the left side.
30. What is a FULL OUTER JOIN?
A FULL OUTER JOIN returns all rows from both tables, matching them where possible, and filling in NULLs for columns where no match exists on either side.
31. What is a CROSS JOIN?
A CROSS JOIN returns the Cartesian product of two tables, combining every row from the first table with every row from the second table, without requiring a matching condition.
32. What is a SELF JOIN?
A SELF JOIN is a join where a table is joined with itself, typically used to compare rows within the same table, such as finding employees and their managers stored in the same table.
33. What is the difference between INNER JOIN and OUTER JOIN?
An INNER JOIN returns only matching rows from both tables, while an OUTER JOIN (LEFT, RIGHT, or FULL) returns matching rows plus unmatched rows from one or both tables, filling the gaps with NULL values.
Subqueries, CTEs, and Views
34. What is a subquery?
A subquery is a query nested inside another SQL query, often used within a SELECT, INSERT, UPDATE, or DELETE statement to return data that will be used by the main query.
35. What are correlated subqueries?
A correlated subquery is a subquery that references a column from the outer query, meaning it cannot run independently and is re-evaluated once for each row processed by the outer query.
36. What is a Common Table Expression (CTE)?
A Common Table Expression, defined using the WITH clause, is a temporary named result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement, making complex queries easier to read and maintain.
37. What are recursive CTEs?
A recursive CTE is a CTE that references itself to process hierarchical or recursive data structures, such as organizational charts or category trees, by repeatedly executing until a defined termination condition is met.
38. What are views in SQL?
A view is a virtual table based on the result of a stored SQL query. It does not store data itself but presents data from one or more underlying tables in a structured, reusable format.
39. What is the difference between a view and a table?
A table physically stores data on disk, while a view is a saved query definition that dynamically pulls data from underlying tables each time it is accessed. Views help simplify complex queries and can restrict access to specific columns or rows.
Indexing and Optimization
40. What are indexes in SQL?
An index is a database object that improves the speed of data retrieval operations on a table by creating a quick lookup structure, similar to an index in a book, at the cost of additional storage and slower write operations.
41. What is clustered indexing?
A clustered index determines the physical order in which data is stored in a table. Since data can only be physically sorted one way, a table can have only one clustered index.
42. What is non-clustered indexing?
A non-clustered index creates a separate structure from the actual data, containing pointers back to the original rows. A table can have multiple non-clustered indexes, unlike clustered indexes.
43. What is query optimization?
Query optimization is the process of improving SQL query performance by choosing efficient execution plans, reducing resource usage, and minimizing execution time, often involving indexing, rewriting queries, and analyzing execution plans.
44. What is normalization?
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity by dividing large tables into smaller related tables and defining relationships between them.
45. What are the normal forms in SQL?
The common normal forms are First Normal Form (1NF), which eliminates repeating groups; Second Normal Form (2NF), which removes partial dependency; Third Normal Form (3NF), which removes transitive dependency; and Boyce-Codd Normal Form (BCNF), a stricter version of 3NF.
46. What is denormalization?
Denormalization is the process of intentionally introducing redundancy into a database by combining tables, typically done to improve read performance in reporting or analytical systems at the cost of some data duplication.
Transactions and ACID Properties
47. What are transactions in SQL?
A transaction is a sequence of one or more SQL operations executed as a single logical unit of work, which either completes entirely or has no effect at all if any part fails.
48. What are ACID properties?
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably: atomicity guarantees all-or-nothing execution, consistency maintains valid data states, isolation prevents transactions from interfering with each other, and durability ensures committed changes persist even after a system failure.
49. What is COMMIT?
COMMIT is a command that permanently saves all changes made during the current transaction to the database, making those changes visible to other users and sessions.
50. What is ROLLBACK?
ROLLBACK undoes changes made during the current transaction, reverting the database to its state before the transaction began, typically used when an error occurs.
51. What is SAVEPOINT?
A SAVEPOINT sets a point within a transaction to which you can later roll back, without undoing the entire transaction, allowing for more granular control over partial rollbacks.
Stored Procedures, Functions, and Triggers
52. What is a stored procedure?
A stored procedure is a precompiled collection of SQL statements stored in the database that can be executed repeatedly with a single call, improving performance and code reusability.
53. What are SQL functions?
SQL functions are reusable blocks of code that perform a specific task and return a single value. They can be built in, like COUNT() or UPPER(), or user defined for custom logic.
54. What are user-defined functions (UDFs)?
User-defined functions are custom functions created by developers to perform specific calculations or logic that is not covered by built in SQL functions, and can be reused across multiple queries.
55. What are triggers in SQL?
A trigger is a set of instructions that automatically executes in response to specific events on a table, such as an INSERT, UPDATE, or DELETE operation, often used to enforce business rules or maintain audit logs.
56. What are cursors?
A cursor is a database object used to retrieve and process rows from a result set one at a time, typically used in stored procedures when row by row processing is required instead of set based operations.
57. What is a sequence?
A sequence is a database object that generates a series of unique numeric values, commonly used to automatically populate primary key columns.
Window Functions
58. What are window functions?
Window functions perform calculations across a set of rows related to the current row, without collapsing the result into a single output row like aggregate functions do. They are defined using the OVER() clause.
59. What is ROW_NUMBER()?
ROW_NUMBER() assigns a unique sequential number to each row within a partition of a result set, starting at 1 for each partition, with no ties allowed even for identical values.
60. What is RANK()?
RANK() assigns a rank to each row within a partition, with rows having equal values receiving the same rank, and a gap left in the ranking sequence after ties.
61. What is DENSE_RANK()?
DENSE_RANK() is similar to RANK(), but it does not leave gaps in the ranking sequence after tied values, assigning consecutive rank numbers.
62. What is NTILE()?
NTILE() divides the rows within a partition into a specified number of roughly equal groups and assigns a group number to each row, useful for creating percentiles or quartiles.
63. What is LEAD()?
LEAD() allows you to access data from a subsequent row in the same result set without using a self join, useful for comparing a row’s value to the value in the next row.
64. What is LAG()?
LAG() allows you to access data from a previous row in the same result set, commonly used to compare current and prior values, such as calculating month over month change.
65. What is the OVER() clause?
The OVER() clause defines the window, or set of rows, that a window function operates on. It can include PARTITION BY to divide rows into groups and ORDER BY to define the sequence within each group.
SQL Operators
66. What are SQL operators?
SQL operators are symbols or keywords used to perform operations on data, including arithmetic operators, comparison operators, logical operators, and set operators.
67. What are comparison operators in SQL?
Comparison operators compare two values and return a boolean result. Common comparison operators include equals (=), not equals (<> or !=), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=).
68. What are logical operators in SQL?
Logical operators combine multiple conditions in a WHERE or HAVING clause. The main logical operators are AND, OR, and NOT.
69. What is the LIKE operator?
The LIKE operator is used to search for a specified pattern in a column, typically using wildcard characters such as % for multiple characters and _ for a single character.
70. What is the IN operator?
The IN operator allows you to specify multiple values in a WHERE clause, checking whether a column’s value matches any value in a given list, functioning as shorthand for multiple OR conditions.
71. What is BETWEEN in SQL?
The BETWEEN operator selects values within a given range, inclusive of the boundary values, and is commonly used with numeric or date columns.
72. What is EXISTS in SQL?
The EXISTS operator checks whether a subquery returns any rows, returning TRUE if at least one row is found, and is often used to test for the existence of related records.
Set Operations and Conditional Logic
73. What is UNION?
UNION combines the result sets of two or more SELECT statements into a single result set, automatically removing duplicate rows, as long as each query has the same number of columns with compatible data types.
74. What is UNION ALL?
UNION ALL combines the result sets of multiple SELECT statements like UNION, but it keeps all duplicate rows instead of removing them, which typically makes it faster than UNION.
75. What is INTERSECT?
INTERSECT returns only the rows that appear in the result sets of both SELECT statements being compared, effectively finding the common records between two queries.
76. What is EXCEPT (MINUS)?
EXCEPT, known as MINUS in some databases like Oracle, returns the rows from the first SELECT statement that do not appear in the result set of the second SELECT statement.
77. What is the CASE statement?
The CASE statement adds conditional logic to a SQL query, allowing you to return different values based on specified conditions, similar to an if-else statement in programming languages.
78. What is COALESCE()?
COALESCE() returns the first non-null value from a list of expressions, commonly used to substitute a default value when a column might contain NULL.
79. What is NULLIF()?
NULLIF() compares two expressions and returns NULL if they are equal, or the first expression if they are not equal, often used to avoid division by zero errors.
80. What is IFNULL() or ISNULL()?
IFNULL() (in MySQL) and ISNULL() (in SQL Server) return a specified replacement value when the given expression evaluates to NULL, otherwise returning the original value.
Handling NULL Values
81. What is NULL in SQL?
NULL represents a missing, unknown, or inapplicable value in a database. It is not the same as zero or an empty string, and any comparison with NULL using standard operators returns unknown rather than true or false.
82. How do you handle NULL values?
NULL values can be handled using functions like COALESCE(), IFNULL(), or ISNULL() to substitute default values, or by using IS NULL and IS NOT NULL in WHERE clauses to filter rows based on the presence or absence of NULL.
83. What is the difference between NULL and an empty string?
NULL represents the complete absence of a value, while an empty string (”) is an actual value that happens to contain zero characters. A column with NULL takes no storage for the value itself, whereas an empty string is a defined, empty piece of data.
SQL Security
84. What is SQL injection?
SQL injection is a security vulnerability where an attacker inserts malicious SQL code into an input field, which is then executed by the database, potentially allowing unauthorized access to or manipulation of data.
85. How can SQL injection be prevented?
SQL injection can be prevented by using parameterized queries and prepared statements, validating and sanitizing all user inputs, applying the principle of least privilege to database accounts, and avoiding dynamic SQL built directly from user input.
86. What are prepared statements?
Prepared statements are precompiled SQL statements that separate the SQL logic from the data being passed into it, treating user input strictly as data rather than executable code, which significantly reduces the risk of SQL injection.
Schemas, Synonyms, and Temporary Structures
87. What is a schema in SQL?
A schema is a logical container that organizes database objects like tables, views, and procedures, helping to group related objects together and manage access permissions within a database.
88. What is a synonym in SQL?
A synonym is an alternative name given to a database object, such as a table or view, allowing users to reference that object using a simpler or more meaningful name, often across different schemas or databases.
89. What are temporary tables?
Temporary tables are tables that exist only for the duration of a session or transaction, used to store intermediate results during complex query processing without affecting the permanent database schema.
90. What are materialized views?
A materialized view is a database object that stores the physical result of a query, unlike a regular view which is virtual. Materialized views need to be refreshed periodically but offer faster read performance for complex aggregations.
Scenario Based and Coding Questions
91. How do you find duplicate records?
Duplicate records can be found by grouping rows based on the columns that should be unique and using HAVING COUNT(*) > 1 to filter groups that appear more than once.
SELECT email, COUNT(*)
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;
92. How do you delete duplicate records?
One common approach is to use a CTE with ROW_NUMBER() to identify duplicate rows and then delete all rows except the first occurrence of each duplicate group.
WITH ranked AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM employees
)
DELETE FROM employees
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
93. How do you retrieve the second highest salary?
The second highest salary can be retrieved using a subquery with LIMIT and OFFSET, or by using DENSE_RANK().
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
94. How do you retrieve the Nth highest salary?
The Nth highest salary can be found using DENSE_RANK() within a subquery and filtering on the desired rank.
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 3;
95. How do you find employees without managers?
Employees without managers can be found by checking for NULL values in the manager ID column.
SELECT * FROM employees
WHERE manager_id IS NULL;
96. How do you find duplicate emails?
Duplicate emails can be identified using GROUP BY on the email column combined with a HAVING clause to filter groups with more than one occurrence.
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
97. How do you remove duplicate rows?
Duplicate rows across all columns can be removed by selecting DISTINCT values into a new table, or by using ROW_NUMBER() partitioned across every column to identify and delete extra copies.
CREATE TABLE users_clean AS
SELECT DISTINCT * FROM users;
98. How do you find missing values in a sequence?
Missing values in a numeric sequence can be found by generating a series of expected numbers and using a LEFT JOIN or NOT IN clause to identify which expected values do not appear in the actual table.
SELECT n AS missing_id
FROM (SELECT generate_series(1, 100) AS n) AS seq
LEFT JOIN orders o ON seq.n = o.order_id
WHERE o.order_id IS NULL;
99. How do you calculate a running total?
A running total can be calculated using the SUM() window function combined with ORDER BY inside the OVER() clause.
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
100. How do you optimize slow SQL queries?
Slow SQL queries can be optimized by adding appropriate indexes on frequently filtered or joined columns, avoiding SELECT * and retrieving only needed columns, analyzing the query execution plan, rewriting correlated subqueries as joins where possible, and reducing the use of functions on indexed columns within WHERE clauses.
101. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() in SQL?
ROW_NUMBER() assigns a unique number to every row regardless of ties, always producing distinct sequential values. RANK() assigns the same rank to tied values but skips the next rank number, leaving gaps. DENSE_RANK() also assigns the same rank to tied values but does not skip any rank numbers, keeping the sequence consecutive. These are useful for tasks like finding the Nth highest value or ranking results within groups, a pattern frequently asked in interviews alongside other window function questions.
102. What are the differences between OLTP and OLAP databases, and when would you use each?
OLTP (Online Transaction Processing) databases are designed for handling a large volume of short, real time transactions such as inserts, updates, and deletes, and are optimized for speed and data integrity in applications like banking systems or e-commerce platforms. OLAP (Online Analytical Processing) databases are designed for complex read heavy queries and analysis over large historical datasets, often used in business intelligence and reporting systems. You would use OLTP for day to day operational applications where transactions need to be fast and reliable, and OLAP for data warehousing scenarios where the goal is deep analysis, trend spotting, and reporting across large volumes of aggregated data.
Final Thoughts
SQL interview questions rarely stay limited to theory. Interviewers usually mix conceptual questions with hands on coding problems to see how you think through real data scenarios, so practicing actual queries on a sample database is just as important as memorizing definitions. Work through the scenario based questions above on your own database instance, try writing alternate solutions using joins, subqueries, and window functions, and revisit the topics on indexing and query optimization since these often separate freshers from experienced candidates in interviews.
If you are looking to build these skills from the ground up with hands on projects and mentor guidance, Codegnan’s MySQL course and Python course are a good place to start, and you can explore all available programs on the Codegnan Online Academy page.
