Top 30 SQL Interview Questions and Answers for All Experience Levels

Prepare for your SQL interview with these 30 essential questions covering basic, intermediate, and advanced topics. This guide is designed for freshers, candidates with 1-3 years of experience, and professionals with 3-6 years, featuring conceptual explanations, practical examples, and real-world scenarios strictly focused on SQL.

Basic SQL Interview Questions (1-10)

1. What is SQL and what is its primary purpose?

SQL (Structured Query Language) is a standard language used to manage and manipulate relational databases. It handles tasks like querying data, updating records, and defining database structures.

SELECT * FROM employees;

2. What are the main categories of SQL commands?

SQL commands are categorized into DDL (Data Definition Language) for structure, DML (Data Manipulation Language) for data operations, DQL (Data Query Language) for retrieval, and DCL (Data Control Language) for permissions.

CREATE TABLE employees (id INT PRIMARY KEY);

3. Explain the basic syntax of a SELECT statement.

The SELECT statement retrieves data using SELECT (columns), FROM (table), WHERE (filter), ORDER BY (sort), and LIMIT (row count).

SELECT name, salary FROM employees WHERE department = 'IT' ORDER BY salary DESC LIMIT 10;

4. What is the difference between DELETE and TRUNCATE?

DELETE removes specific rows based on a condition and logs each deletion, allowing rollback. TRUNCATE removes all rows quickly without logging individual deletions.

DELETE FROM employees WHERE id = 5;
TRUNCATE TABLE employees;

5. What are SQL constraints and name a few types?

Constraints enforce data integrity rules. Types include PRIMARY KEY (unique identifier), FOREIGN KEY (referential integrity), UNIQUE (no duplicates), NOT NULL (no null values), and CHECK (value conditions).

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT FOREIGN KEY REFERENCES customers(id)
);

6. What is a PRIMARY KEY in SQL?

A PRIMARY KEY uniquely identifies each record in a table and cannot be NULL. A table can have only one primary key, which may be composite (multiple columns).

CREATE TABLE products (
    product_id INT,
    category_id INT,
    PRIMARY KEY (product_id, category_id)
);

7. How does the WHERE clause work in SQL?

The WHERE clause filters rows based on conditions using operators like =, >, LIKE, BETWEEN, IN, and logical operators AND, OR.

SELECT * FROM products WHERE price BETWEEN 100 AND 500 AND category = 'Electronics';

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

UNION combines result sets and removes duplicates. UNION ALL combines result sets including duplicates, making it faster.

SELECT name FROM customers UNION SELECT name FROM suppliers;
SELECT name FROM customers UNION ALL SELECT name FROM suppliers;

9. What does NULL mean in SQL?

NULL represents the absence of a value in a column. Use IS NULL or IS NOT NULL for comparisons, as NULL != NULL returns unknown.

SELECT * FROM employees WHERE manager_id IS NULL;

10. Explain the ORDER BY clause with ASC and DESC.

ORDER BY sorts results by specified columns. ASC (ascending, default) sorts low to high; DESC (descending) sorts high to low.

SELECT name, salary FROM employees ORDER BY salary DESC, name ASC;

Intermediate SQL Interview Questions (11-20)

11. What are the different types of JOINs in SQL?

JOINs combine rows from multiple tables: INNER JOIN (matching rows), LEFT JOIN (all left table rows), RIGHT JOIN (all right table rows), FULL OUTER JOIN (all rows from both).

SELECT e.name, d.department_name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;

12. What is a subquery and provide an example?

A subquery is a query nested inside another query, often in WHERE or SELECT. It executes first to provide values for the outer query.

SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

13. Explain GROUP BY and HAVING clauses.

GROUP BY groups rows by column values for aggregates like COUNT, SUM. HAVING filters grouped results (WHERE filters before grouping).

SELECT department, AVG(salary)
FROM employees GROUP BY department HAVING AVG(salary) > 50000;

14. What are aggregate functions in SQL?

Aggregate functions perform calculations on groups: COUNT (rows), SUM (total), AVG (average), MAX (highest), MIN (lowest).

SELECT department, COUNT(*), AVG(salary) FROM employees GROUP BY department;

15. How do you find the second highest salary in SQL? (Scenario: Flipkart e-commerce database)

Use a subquery or LIMIT with OFFSET to find the nth highest value.

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

16. What is normalization and why is it used?

Normalization organizes data to reduce redundancy using forms like 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies).

-- Example: Separate customer and address tables to avoid duplication

17. Explain indexes in SQL and their benefits.

Indexes speed up data retrieval like a book index, but slow down INSERT/UPDATE. Use on frequently queried columns.

CREATE INDEX idx_employee_dept ON employees(dept_id);

18. What is the difference between a VIEW and a TABLE?

A TABLE stores data physically. A VIEW is a virtual table based on a query, not storing data but simplifying complex queries.

CREATE VIEW high_salary_employees AS
SELECT * FROM employees WHERE salary > 60000;

19. How do you remove duplicate records from a table? (Scenario: Paytm transaction logs)

Use ROW_NUMBER() window function or GROUP BY with COUNT to identify and delete duplicates.

DELETE e1 FROM employees e1
INNER JOIN employees e2 WHERE e1.id > e2.id AND e1.email = e2.email;

20. What are transactions in SQL and the ACID properties?

Transactions group SQL statements as a single unit. ACID ensures Atomicity (all or none), Consistency (valid state), Isolation (independent), Durability (permanent after commit).

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Advanced SQL Interview Questions (21-30)

21. What is a correlated subquery?

A correlated subquery references columns from the outer query, executing once per outer row, making it slower than non-correlated.

SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id);

22. Explain window functions with ROW_NUMBER().

Window functions compute over a set of rows without grouping: ROW_NUMBER() assigns unique sequential numbers.

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

23. What are Common Table Expressions (CTEs)?

CTEs are temporary result sets defined with WITH, improving readability for complex queries or recursion.

WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 50000
)
SELECT * FROM high_earners;

24. How do LAG and LEAD functions work? (Scenario: Zoho sales trend analysis)

LAG accesses previous row values; LEAD accesses next row values within a partition.

SELECT sale_date, amount,
LAG(amount) OVER (ORDER BY sale_date) as prev_amount
FROM sales;

25. What is a recursive CTE and when is it used?

Recursive CTEs solve hierarchical queries like employee-manager trees, with anchor and recursive members.

WITH RECURSIVE emp_tree AS (
    SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id FROM employees e
    INNER JOIN emp_tree et ON e.manager_id = et.id
)
SELECT * FROM emp_tree;

26. Explain PIVOT in SQL. (Scenario: Salesforce reporting)

PIVOT transforms rows into columns for cross-tabulation, useful for summary reports.

SELECT * FROM (
    SELECT year, product, sales FROM sales_data
) src
PIVOT (SUM(sales) FOR product IN ('Laptop', 'Phone'));

27. How do you optimize SQL queries? (Scenario: Atlassian project tracking)

Optimize by using indexes, avoiding SELECT *, rewriting subqueries as JOINs, limiting rows, and analyzing execution plans.

-- Add index and limit
CREATE INDEX idx_project_status ON projects(status);
SELECT * FROM projects WHERE status = 'active' LIMIT 100;

28. What are materialized views?

Materialized views store query results physically, refreshed periodically for performance on complex aggregations.

CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(amount) FROM sales GROUP BY region;

29. How do you find gaps in sequential data? (Scenario: Adobe sequence numbering)

Use window functions like ROW_NUMBER() minus ID to detect missing numbers.

SELECT id + 1 as missing FROM (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) as rn FROM numbers
) t WHERE id != rn;

30. What is a deadlock in SQL and how to prevent it? (Scenario: SAP inventory system)

A deadlock occurs when transactions wait for each other’s locks indefinitely. Prevent by consistent lock ordering, short transactions, and timeouts.

-- Always lock tables in same order
BEGIN TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
SELECT * FROM items WHERE order_id = 1 FOR UPDATE;
COMMIT;