Master SQL Interviews: Basic to Advanced Questions Explained
Prepare for SQL interviews at companies like Amazon, Zoho, and Atlassian with these 30 essential questions. This guide progresses from basic concepts for freshers to advanced scenarios for 3-6 years experienced professionals, covering practical SQL queries and real-world applications.
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 retrieval, insertion, updates, and deletions in RDBMS systems.[1][3]
2. What are the main types of SQL commands?
SQL commands include DDL (Data Definition Language) like CREATE and ALTER, DML (Data Manipulation Language) like INSERT and UPDATE, DQL (Data Query Language) like SELECT, and DCL (Data Control Language) like GRANT.[3][6]
3. What is a primary key in SQL?
A primary key is a unique identifier for each record in a table, ensuring no duplicate or null values. It enforces data integrity.[2][9]
4. Explain 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][2]
5. What is a UNIQUE constraint?
A UNIQUE constraint ensures all values in a column are distinct, allowing one null value unlike primary key.[4][9]
6. How do you use the WHERE clause?
The WHERE clause filters rows based on conditions. Example:
SELECT * FROM employees WHERE salary > 50000;It applies before GROUP BY.[4][7]
7. What is NULL in SQL?
NULL represents missing or unknown data. It cannot be compared with =; use IS NULL instead.[4]
8. What are aggregate functions? Give examples.
Aggregate functions compute a single value from multiple rows, like COUNT(), SUM(), AVG(), MAX(), MIN(). Example: SELECT AVG(salary) FROM employees;.[6]
9. Explain the ORDER BY clause.
ORDER BY sorts result sets in ASC (ascending) or DESC (descending) order. Example: SELECT * FROM products ORDER BY price DESC;.[3]
10. What is the difference between UNION and UNION ALL?
UNION removes duplicates; UNION ALL includes them for faster performance.[4]
Intermediate SQL Questions (11-20)
11. What are the types of JOINs in SQL?
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).[1][4][7]
12. Write a query for INNER JOIN.
Example at Flipkart for order data:
SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;Returns only matching records.[7]
13. What is a subquery?
A subquery is a nested SELECT inside another query. Example: SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);.[4][7]
14. Explain GROUP BY and HAVING.
GROUP BY groups rows for aggregates; HAVING filters groups unlike WHERE which filters rows. Example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000;.[3]
15. What is the typical order of clauses in a SELECT statement?
SELECT – FROM – JOIN – ON – WHERE – GROUP BY – HAVING – ORDER BY – LIMIT.[1]
16. How do you find the second highest salary?
Scenario at Paytm for employee records:
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);[1]
17. What are constraints in SQL?
Constraints like NOT NULL, CHECK, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY enforce rules on data.[9][4]
18. Explain FOREIGN KEY.
FOREIGN KEY links tables by referencing a primary key in another table, maintaining referential integrity.[2][9]
19. What is a VIEW?
A VIEW is a virtual table from a query, simplifying complex queries and enhancing security.[3][4]
20. Write a query to remove duplicates.
Practical at Salesforce:
SELECT DISTINCT * FROM transactions;Or use ROW_NUMBER() for advanced deduplication.[1]
Advanced SQL Questions (21-30)
21. What is normalization? Explain 1NF and 2NF.
Normalization reduces redundancy. 1NF: Atomic values, no repeating groups. 2NF: No partial dependencies on composite keys.[2][4]
22. What are ACID properties?
Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent safety), Durability (committed changes persist).[4][8]
23. Explain correlated subquery.
A correlated subquery references outer query columns, executing per row. Example: SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);.[4]
24. What is an INDEX? When to use it?
INDEX speeds up queries on large tables but slows inserts/updates. Use on frequently searched columns.[2][6]
25. How to optimize a slow query?
Use EXPLAIN, add indexes, avoid SELECT *, limit rows, rewrite subqueries as JOINs.[2][6]
26. What is a transaction? Example with COMMIT/ROLLBACK.
Transaction groups operations. Example at SAP:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id=1;
COMMIT;ROLLBACK undoes if error.[4]
27. Difference between IN and EXISTS.
IN checks value list; EXISTS checks subquery existence (faster for large sets).[4]
28. What is a composite primary key?
A primary key from multiple columns, uniquely identifying rows together.[1]
29. Explain deadlock and prevention.
Deadlock: Two transactions wait for each other's locks. Prevent with consistent lock order or timeouts.[4]
30. What is a stored procedure?
A stored procedure is reusable SQL code block for complex logic, improving performance at Oracle-like environments.[3][5]
