20+ SQL Interview Questions

Arth Jani

6 min read

If you’re preparing for an upcoming SQL interview, you’re in the right place! Whether you’re a fresher aiming for your first job in data analytics or an experienced developer looking to level up, having a strong command of SQL is essential. SQL (Structured Query Language) remains one of the most in-demand skills in the tech industry, with applications ranging from data manipulation and analysis to complex database design.

In this blog, we’ve compiled 20+ essential SQL interview questions that cover everything from the basics to advanced concepts. You’ll find a mix of theoretical questions to test your knowledge and practical code examples to sharpen your skills. By the end of this guide, you’ll be well-equipped to handle the most commonly asked questions and confidently showcase your SQL expertise.

Let’s dive into these SQL questions and answers to help you prepare, practice, and perform your best!

1. What is SQL?

SQL stands for Structured Query Language. It is a standardized programming language specifically designed for managing and manipulating relational databases. SQL allows users to query, insert, update, and delete data, as well as define and modify the structure of database objects (e.g., tables, views). SQL commands can be categorized into:

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

2. What is a Primary Key?

A Primary Key is a unique identifier for a table. It ensures that each record in a table is unique. The primary key column must contain unique values and cannot be NULL. There can only be one primary key per table, but it can consist of multiple columns (composite key).

3. What is a Foreign Key?

A Foreign Key is a column (or a set of columns) in a table that is used to establish a link between the data in two tables. It acts as a reference to the primary key in another table, ensuring referential integrity. Foreign keys help prevent actions that would destroy links between tables.

4. Difference between WHERE and HAVING clauses?

WHERE: Used to filter rows before any grouping or aggregation is performed. It is applied to individual rows in a table.
HAVING: Used to filter records after aggregation (when using functions like COUNT, SUM, AVG). It is applied to groups created by the GROUP BY clause.

5. What is the difference between INNER JOIN and LEFT JOIN ?

INNER JOIN: Returns only the rows that have matching values in both tables.
LEFT JOIN: Returns all rows from the left table, and matching rows from the right table. If there is no match, the result is NULL for columns from the right table.

6. What is a NULL value in SQL?

NULL represents missing, undefined, or unknown data. It is not equivalent to zero or an empty string. NULL values require special handling because they are not equal to anything, including themselves.

7. What is the difference between UNION and UNION ALL ?

UNION: Combines the result sets of two or more SELECT statements and removes duplicate records.
UNION ALL: Combines the result sets and includes duplicates.

8. How to fetch the top 3 highest salaries?

This query filters employees with a salary greater than 50,000 (WHERE clause) and then groups them by department. It only shows departments with more than 5 employees (HAVING clause).

In the INNER JOIN, only employees who have orders are returned. In the LEFT JOIN, all employees are returned, along with their orders (if any). If an employee has no orders, the OrderID will be NULL.

The first query removes duplicate names, while the second query retains all names, including duplicates.

Intermediate SQL Interview Questions

Using ORDER BY sorts the salaries in descending order, and LIMIT 3 fetches the top 3 highest.

9. Explain the concept of INDEX in SQL.

An index is a database object that improves the speed of data retrieval operations. It is created on one or more columns of a table. However, indexes also add overhead for data modification operations (INSERT, UPDATE, DELETE) because the index itself must be updated.

This creates an index on the Name column of the Employees table.

10. What is a VIEW in SQL?

A VIEW is a virtual table that is based on a query. It does not store data itself but provides a way to simplify complex queries and ensure data abstraction.

You can now query ActiveEmployees like a regular table:

11. How do you perform a subquery in SQL?

A subquery is a query nested within another SQL query. It can be used in the SELECT, FROM, WHERE, or HAVING clauses. Subqueries are useful for performing operations that require data from multiple tables or complex conditions.

Here, the subquery (SELECT AVG(Salary) FROM Employees) calculates the average salary. The main query selects the names of employees who earn more than this average.

12. What is a TRIGGER in SQL?

A TRIGGER is a set of SQL statements that automatically execute when a specified event occurs on a table (e.g., INSERT, UPDATE, or DELETE). Triggers help maintain data integrity and enforce business rules.

This trigger logs salary changes to the SalaryAudit table whenever the Salary column in the Employees table is updated.

13. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE: Removes specific rows based on a condition. It can be rolled back if used within a transaction.
TRUNCATE: Removes all rows from a table without logging individual row deletions. It cannot be rolled back.
DROP: Deletes the entire table structure and data.

14. What are Window Functions in SQL?

Window Functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not collapse the rows. Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE().

This query assigns a rank to each employee based on their salary, with the highest salary ranked first.

15. Explain CROSS JOIN with an example.

A CROSS JOIN produces the Cartesian product of two tables, meaning it returns every combination of rows from both tables. If Table A has m rows and Table B has n rows, the result set will have m * n rows.

This query returns all possible combinations of employees and departments.

16. How to get the second highest salary from a table?

You can use a subquery or window function to find the second highest salary.

Using Subquery:

Using Window Function:

17. What is a CTE (Common Table Expression)?

A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve code readability and are useful for breaking down complex queries.

Here, SalesCTE is a temporary result set that calculates total sales for each product.

18. Explain Normalization and its types.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The main types are:

1NF (First Normal Form): Ensures each column contains atomic (indivisible) values.
2NF (Second Normal Form): Meets 1NF and all non-key columns are fully dependent on the primary key.
3NF (Third Normal Form): Meets 2NF and there are no transitive dependencies (non-key columns do not depend on other non-key columns).

19. Write a query to find duplicate records in a table.

To find duplicate records, use the GROUP BY and HAVING clauses.

This query groups employees by name and returns only those with a count greater than 1, indicating duplicates.

20. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

RANK() assigns a rank to each row within a partition. Rows with the same value receive the same rank, but the next rank is skipped (e.g., 1, 2, 2, 4).
DENSE_RANK() assigns ranks without skipping any numbers (e.g., 1, 2, 2, 3).
ROW_NUMBER() assigns a unique number to each row, regardless of duplicates.

Advanced SQL Interview Questions

21. How can you optimize a query for better performance?

Query optimization can be achieved through several techniques:

Use Indexes: Create indexes on frequently used columns in WHERE, JOIN, or ORDER BY clauses.
Avoid SELECT *: Specify only the required columns to reduce data retrieval.
Use Joins Efficiently: Prefer using indexed columns in joins and avoid cross joins unless necessary.
Use Proper Data Types: Choose appropriate data types for columns to reduce storage and improve performance.
Analyze and Use Execution Plans: Review query execution plans to understand bottlenecks and optimize them.

22. Explain EXISTS and NOT EXISTS with an example.

EXISTS checks if a subquery returns any rows. If it does, the condition is true. NOT EXISTS checks if a subquery returns no rows.

The first query returns customers who have placed orders, while the second query returns customers who have not placed any orders.