Prepare for your next SQL interview with these 30 carefully curated questions and answers. Covering basic, intermediate, and advanced topics, this guide is perfect for freshers, candidates with 1-3 years of experience, and professionals with 3-6 years in the field. Each question includes clear explanations and practical code examples.
Basic SQL Interview Questions (1-10)
1. What is SQL and what is its primary purpose?
SQL stands for Structured Query Language. It is used to manage and manipulate relational databases, including creating tables, inserting data, querying information, and maintaining database structures.[1][2]
2. What are the main categories 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).[2][3]
3. What is a primary key in SQL?
A primary key is a unique identifier for each record in a table. It ensures no duplicate values and does not allow NULL values.[3][5]
4. 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, resets auto-increment counters, and cannot be rolled back in some databases.[3][5]
DELETE FROM employees WHERE department = 'HR';
TRUNCATE TABLE employees;
5. Explain the SELECT statement with an example.
The SELECT statement retrieves data from a database table. It specifies columns and can include filtering with WHERE.[2][3]
SELECT name, salary FROM employees WHERE salary > 50000;
6. What is a foreign key?
A foreign key is a column that creates a relationship between two tables by referencing the primary key of another table.[3]
7. What does the WHERE clause do?
The WHERE clause filters rows based on specified conditions before returning results.[3][5]
8. What is NULL in SQL?
NULL represents the absence of a value in a column. It is not equal to zero or empty string.[3]
9. What are SQL constraints?
Constraints enforce rules on data in tables, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.[1][3]
10. What is the ORDER BY clause?
ORDER BY sorts the result set by one or more columns in ascending (ASC) or descending (DESC) order.[2]
SELECT * FROM products ORDER BY price DESC;
Intermediate SQL Interview Questions (11-20)
11. What are the different types of JOINs in SQL?
Common 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).[3][5]
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
12. Explain GROUP BY and HAVING.
GROUP BY groups rows with the same values into summary rows. HAVING filters groups based on aggregate conditions, unlike WHERE which filters rows.[2][3]
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
13. What is the difference between UNION and UNION ALL?
UNION combines result sets and removes duplicates. UNION ALL combines result sets including duplicates.[3][5]
14. What is a subquery?
A subquery is a query nested inside another query, often used in WHERE or SELECT clauses.[3][5]
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
15. What are aggregate functions in SQL?
Aggregate functions perform calculations on multiple rows, such as COUNT(), SUM(), AVG(), MIN(), and MAX().[1][2]
16. At Flipkart, how would you find the second highest salary using SQL?
Use a subquery or LIMIT with OFFSET to find the second highest salary.[1]
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
17. What is normalization in SQL?
Normalization organizes data to reduce redundancy and improve integrity, following forms like 1NF, 2NF, and 3NF.[3][5]
18. Explain INNER JOIN vs LEFT JOIN.
INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table and matching rows from the right.[5]
19. What is a view in SQL?
A view is a virtual table based on a SELECT query result, used for simplifying complex queries or restricting data access.[2][3]
CREATE VIEW high_earners AS
SELECT * FROM employees WHERE salary > 70000;
20. What are indexes in SQL and their benefits?
Indexes improve query performance by allowing faster data retrieval, similar to a book index, but slow down writes.[4]
Advanced SQL Interview Questions (21-30)
21. What are ACID properties in SQL transactions?
ACID stands for Atomicity (all or nothing), Consistency (valid state), Isolation (no interference), Durability (persistent changes).[3][4]
22. What is a correlated subquery?
A correlated subquery references columns from the outer query and executes row-by-row.[3]
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id);
23. Explain the typical order of SQL clauses in a SELECT statement.
The order is: SELECT, FROM, JOIN, ON, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.[1]
24. What is a stored procedure?
A stored procedure is a reusable set of SQL statements stored in the database, executed by calling its name.[2][4]
25. How do you optimize SQL queries?
Optimize by using indexes, selecting only needed columns, avoiding functions in WHERE, using EXPLAIN plans, and rewriting subqueries as JOINs.[4]
26. At Zoho, how would you handle finding duplicates and removing them?
Use GROUP BY with HAVING COUNT(*) > 1 to find duplicates, then DELETE with a subquery.[1]
DELETE FROM users WHERE user_id IN (
SELECT user_id FROM (
SELECT user_id, ROW_NUMBER() OVER(PARTITION BY email ORDER BY user_id) rn
FROM users
) t WHERE rn > 1
);
27. What is a deadlock in SQL and how to prevent it?
A deadlock occurs when transactions wait for each other's locks indefinitely. Prevent by consistent lock ordering and short transactions.[4]
28. What is the difference between IN and EXISTS?
IN checks for value membership in a list or subquery. EXISTS checks if a subquery returns any rows and stops early.[3]
29. At Salesforce, explain using CTEs for complex queries.
Common Table Expressions (CTEs) define temporary result sets for readability in complex queries.[4]
WITH high_salary AS (
SELECT * FROM employees WHERE salary > 80000
)
SELECT * FROM high_salary WHERE dept = 'Sales';
30. What is a composite primary key?
A composite primary key uses multiple columns to uniquely identify rows in a table.[1]
CREATE TABLE order_items (
order_id INT,
product_id INT,
PRIMARY KEY (order_id, product_id)
);
