Prepare for your next SQL interview with this comprehensive guide featuring 30 essential questions. Covering basic, intermediate, and advanced topics, these questions help freshers, candidates with 1-3 years experience, and professionals with 3-6 years gain confidence in SQL concepts, practical queries, and real-world scenarios.
Basic SQL Interview Questions (1-10)
1. What is SQL and what is its primary purpose?
SQL stands for Structured Query Language, used to manage and manipulate relational databases. It handles tasks like querying data, updating records, and defining database structures.
2. What are the main categories of SQL commands?
SQL commands are categorized into DDL (Data Definition Language) for structure like CREATE and ALTER, DML (Data Manipulation Language) for data like SELECT and INSERT, DCL (Data Control Language) for permissions, and TCL (Transaction Control Language) for transactions like COMMIT.
3. Explain the difference between DELETE, TRUNCATE, and DROP.
DELETE removes specific rows based on conditions and can be rolled back. TRUNCATE removes all rows quickly without logging individual deletions. DROP deletes the entire table structure and data permanently.
4. What is a primary key in SQL?
A primary key is a unique identifier for each record in a table. It enforces NOT NULL and UNIQUE constraints, ensuring no duplicate or null values.
5. What are SQL constraints and name a few types?
Constraints enforce data integrity rules. Types include NOT NULL (no null values), UNIQUE (distinct values), PRIMARY KEY (unique identifier), FOREIGN KEY (referential integrity), and CHECK (custom conditions).
6. How do you retrieve all columns from a table using SQL?
Use the SELECT statement with asterisk:
SELECT * FROM employees;This fetches all columns from the employees table.
7. What does the WHERE clause do in a SELECT statement?
The WHERE clause filters rows based on conditions. Example:
SELECT name FROM employees WHERE salary > 50000;returns names of employees with salary greater than 50000.
8. Explain the ORDER BY clause.
ORDER BY sorts result rows by specified columns. Example:
SELECT * FROM products ORDER BY price DESC;sorts products by highest price first.
9. What is the difference between UNION and UNION ALL?
UNION combines result sets and removes duplicates. UNION ALL combines them including duplicates, performing faster without deduplication.
10. What is a NULL value in SQL?
NULL represents missing or unknown data. It differs from zero or empty string and requires IS NULL or IS NOT NULL for checking.
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).
12. Write a query for INNER JOIN between two tables.
For departments and employees:
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;13. Explain GROUP BY and aggregate functions like COUNT and SUM.
GROUP BY groups rows for aggregation. Example:
SELECT dept_id, COUNT(*) as emp_count
FROM employees
GROUP BY dept_id;counts employees per department.
14. What is the HAVING clause and how does it differ from WHERE?
HAVING filters grouped results after GROUP BY. WHERE filters rows before grouping. Use HAVING for aggregates like
HAVING COUNT(*) > 5;15. How do you find the second highest salary in SQL?
Use subquery:
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);or with LIMIT/OFFSET in some dialects.
16. What is normalization in SQL databases?
Normalization organizes data to reduce redundancy using normal forms (1NF, 2NF, 3NF). It splits large tables into smaller related ones via primary and foreign keys.
17. Explain indexes and their benefits.
Indexes speed up SELECT queries by creating data structures like B-trees. They improve read performance but slow INSERT/UPDATE due to maintenance overhead.
18. In a Paytm-like scenario, how would you query top 5 transactions by amount?
SELECT * FROM transactions
ORDER BY amount DESC
LIMIT 5;This retrieves the highest value transactions efficiently with indexing on amount.
19. What are subqueries? Provide an example.
Subqueries are nested queries. Example:
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);finds above-average earners.
20. Differentiate between correlated and non-correlated subqueries.
Non-correlated subqueries run independently. Correlated depend on outer query, executing per row, like
SELECT e1.name FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id);Advanced SQL Interview Questions (21-30)
21. What are window functions? Give an example using ROW_NUMBER().
Window functions perform calculations across row sets. Example:
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;ranks employees by salary.
22. Explain ACID properties in SQL transactions.
ACID ensures reliability: Atomicity (all or nothing), Consistency (valid state), Isolation (no interference), Durability (persists after commit).
23. How do you handle transactions in SQL? Use COMMIT and ROLLBACK.
Begin with BEGIN TRANSACTION, use COMMIT to save, ROLLBACK to undo:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id=1;
COMMIT;24. What is a deadlock and how to prevent it?
Deadlock occurs when transactions wait for each other’s locks. Prevent by consistent lock ordering, short transactions, and timeouts.
25. In an Atlassian scenario for issue tracking, write a query for issues created in last 7 days grouped by status.
SELECT status, COUNT(*)
FROM issues
WHERE created_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY status;26. What is the typical execution order of clauses in a SELECT statement?
FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
27. Explain CTE (Common Table Expression) with an example.
CTE simplifies complex queries:
WITH high_earners AS (
SELECT * FROM employees WHERE salary > 80000
)
SELECT AVG(salary) FROM high_earners;28. How do you optimize a slow-running SQL query?
Use EXPLAIN for execution plans, add indexes on WHERE/JOIN columns, avoid SELECT *, limit subqueries, and rewrite with CTEs or window functions.
29. What is a composite primary key?
A composite primary key uses multiple columns to uniquely identify rows, like (order_id, product_id) in an order_items table.
30. For a Zoho CRM scenario, find duplicate customer emails and delete all but the latest.
DELETE c1 FROM customers c1
INNER JOIN customers c2
WHERE c1.email = c2.email AND c1.id < c2.id;Keeps the record with highest id.
