Prepare for your SQL interview with this comprehensive guide featuring 30 essential questions and answers. These cover basic concepts for freshers, practical queries for 1-3 years experience, and advanced scenarios for 3-6 years professionals at companies like Zoho, Paytm, Salesforce, and Atlassian.
Basic SQL Questions (1-10)
1. What is SQL?
SQL stands for Structured Query Language, used to manage and manipulate relational databases. It handles data creation, retrieval, updates, and deletion through standardized commands.[1]
2. What are the main types of SQL commands?
SQL commands are categorized into DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language), DCL (Data Control Language), and TCL (Transaction Control Language).[3]
3. What is the difference between DELETE and TRUNCATE?
DELETE removes specific rows based on a condition and can be rolled back. TRUNCATE removes all rows quickly without logging individual deletions and resets auto-increment counters.[1]
4. What is a PRIMARY KEY?
A PRIMARY KEY uniquely identifies each record in a table. It cannot contain NULL values and ensures no duplicate entries.[9]
5. What is a FOREIGN KEY?
A FOREIGN KEY in one table points to the PRIMARY KEY in another table, maintaining referential integrity between related tables.[9]
6. Explain the SELECT statement with an example.
The SELECT statement retrieves data from a database table.
SELECT name, salary FROM employees;This fetches name and salary columns from the employees table.[3]
7. What is the WHERE clause used for?
The WHERE clause filters rows based on specified conditions.
SELECT * FROM employees WHERE salary > 50000;This returns employees with salary greater than 50000.[7]
8. What are SQL constraints?
Constraints enforce data integrity rules like NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.[9]
9. What is a NULL value in SQL?
NULL represents missing or unknown data in a column. It is not equal to zero or empty string.[4]
10. What is the difference between UNIQUE and PRIMARY KEY?
UNIQUE ensures unique values but allows one NULL. PRIMARY KEY ensures unique non-NULL values and identifies each row.[4]
Intermediate SQL Questions (11-20)
11. What are the different types of JOINs in SQL?
JOIN types include INNER JOIN (matching rows), LEFT JOIN (all left table rows), RIGHT JOIN (all right table rows), FULL OUTER JOIN (all rows from both), and CROSS JOIN (cartesian product).[1]
12. Write a query for INNER JOIN.
An INNER JOIN returns only matching rows from both tables.
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;[7]
13. What is the difference between UNION and UNION ALL?
UNION removes duplicate rows from combined results. UNION ALL includes all rows including duplicates.[4]
14. What are aggregate functions? Name some examples.
Aggregate functions perform calculations on multiple rows and return a single value: COUNT(), SUM(), AVG(), MIN(), MAX().
SELECT AVG(salary) FROM employees;[6]
15. Explain GROUP BY and HAVING.
GROUP BY groups rows with same values. HAVING filters groups after aggregation.
SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 60000;[3]
16. What is a subquery? Provide an example.
A subquery is a query nested inside another query.
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);[7]
17. What is a correlated subquery?
A correlated subquery references columns from the outer query and executes row-by-row.[4]
18. What is the ORDER BY clause?
ORDER BY sorts result set by specified column(s) in ASC (default) or DESC order.
SELECT * FROM employees ORDER BY salary DESC;[3]
19. How do you limit rows in SQL?
Use LIMIT (MySQL/PostgreSQL) or TOP (SQL Server).
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;This gets top 5 highest salaries.[3]
20. What are indexes in SQL?
Indexes improve query performance by creating data structures for faster lookups, similar to a book index.[1]
Advanced SQL Questions (21-30)
21. What is normalization? Explain 1NF and 2NF.
Normalization reduces data redundancy. 1NF requires atomic values and unique rows. 2NF eliminates partial dependencies on composite keys.[2]
22. What is a VIEW?
A VIEW is a virtual table based on a SELECT query result, simplifying complex queries and enhancing security.[3]
23. Explain SQL transactions and ACID properties.
Transactions ensure reliable operations. ACID: Atomicity (all or nothing), Consistency (valid state), Isolation (independent), Durability (permanent).[4]
24. How do you find the nth highest salary?
Use subquery with LIMIT.
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 1;This finds 2nd highest.[1]
25. What is the typical order of execution of SQL clauses?
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.[1]
26. What is a composite primary key?
A composite primary key uses multiple columns together to uniquely identify rows.[1]
27. How do you optimize a slow SQL query?
Use indexes on WHERE/JOIN columns, avoid SELECT *, rewrite subqueries as JOINs, limit rows, and analyze execution plans.[2][6]
28. What is EXPLAIN in SQL?
EXPLAIN shows query execution plan, revealing index usage, join methods, and performance bottlenecks.
EXPLAIN SELECT * FROM employees WHERE salary > 50000;[2]
29. What are window functions?
Window functions perform calculations across row sets related to current row: ROW_NUMBER(), RANK(), LAG(), LEAD().
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees;30. Scenario: At Paytm, how would you remove duplicate records keeping one copy?
Use ROW_NUMBER() window function.
DELETE e1 FROM employees e1
INNER JOIN employees e2
WHERE e1.id > e2.id AND e1.email = e2.email;This keeps the lowest ID duplicate.[1]
