SQL Interview Questions for Freshers India 2026 — Top 50 Q&A

1. What is SQL and what are its sublanguages? SQL = Structured Query Language. Four sublanguages: • DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE — defines schema • DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE — manipulates data • DCL (Data Control Language): GRANT, REVOKE — controls access • TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT — manages transactions 2. What is the difference between WHERE and HAVING? WHERE filters rows BEFORE aggregation. HAVING filters rows AFTER aggregation. Example: SELECT department, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY department HAVING COUNT(*) > 5; Here WHERE removes employees with salary ≤ 50000 before grouping, then HAVING removes departments with ≤5 employees. 3. What is the difference between DELETE, TRUNCATE, and DROP? DELETE: removes specific rows, can be rolled back, triggers fire, WHERE clause supported. TRUNCATE: removes all rows, faster than DELETE, cannot be rolled back in most databases, no triggers. DROP: removes the entire table (structure + data), cannot be rolled back. 4. What is a primary key vs a foreign key? Primary key: uniquely identifies each row in a table. Cannot be NULL. Only one per table. Foreign key: references the primary key of another table. Enforces referential integrity. A table can have multiple foreign keys. 5. What is the difference between CHAR and VARCHAR? CHAR(n): fixed length, always stores n characters (pads with spaces). VARCHAR(n): variable length, stores only what's needed + 1–2 bytes overhead. Use CHAR for fixed-length data (country codes, postal codes). Use VARCHAR for variable-length data (names, addresses).

6. What are the types of JOINs? INNER JOIN: returns rows where there is a match in BOTH tables. LEFT JOIN (LEFT OUTER JOIN): returns all rows from the left table + matching rows from the right. NULLs where no match. RIGHT JOIN: opposite of LEFT JOIN. FULL OUTER JOIN: returns all rows from both tables. NULLs where no match on either side. CROSS JOIN: every row from table A × every row from table B (Cartesian product). SELF JOIN: joins a table to itself — used for hierarchical data (employees and their managers). 7. Write a query to find employees who have NO department. SELECT e.employee_id, e.name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IS NULL; The LEFT JOIN includes all employees; WHERE d.department_id IS NULL keeps only those with no matching department. 8. Write a query to find the second highest salary. SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); OR using LIMIT/OFFSET: SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; OR using window functions: SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk FROM employees) t WHERE rnk = 2; 9. What is the difference between UNION and UNION ALL? UNION: combines result sets, removes duplicates (slower — requires sorting/hashing). UNION ALL: combines result sets, keeps all duplicates (faster). Use UNION ALL unless you need deduplication. 10. Write a query to find duplicate records. SELECT email, COUNT(*) as count FROM users GROUP BY email HAVING COUNT(*) > 1; To delete duplicates, keep the one with the lowest ID: DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);

11. What is GROUP BY and how does it work? GROUP BY collapses multiple rows with the same value in the specified column into a single row. Must be used with aggregate functions (COUNT, SUM, AVG, MAX, MIN). Every column in SELECT must either be in GROUP BY or wrapped in an aggregate function. 12. Write a query to find the department with the highest average salary. SELECT department_id, AVG(salary) as avg_salary FROM employees GROUP BY department_id ORDER BY avg_salary DESC LIMIT 1; 13. What is a subquery vs a CTE? Subquery: a query nested inside another query. Can be in SELECT, FROM, or WHERE. CTE (Common Table Expression): a named temporary result set defined with WITH clause. More readable for complex queries, can be referenced multiple times, and supports recursion. When to use CTE over subquery: when the logic is complex, repeated, or recursive (tree/hierarchy queries). 14. Write a query using EXISTS. SELECT customer_id, name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'completed'); EXISTS is often faster than IN for large datasets because it short-circuits as soon as a match is found.

Window functions are asked at data analyst, SDE-2+, and senior BA interviews: 15. What are window functions? Window functions perform calculations across a 'window' of rows related to the current row, WITHOUT collapsing rows like GROUP BY does. Syntax: FUNCTION() OVER (PARTITION BY column ORDER BY column ROWS/RANGE...) 16. Explain ROW_NUMBER, RANK, and DENSE_RANK. ROW_NUMBER(): assigns unique sequential number (1,2,3,4,5) — no ties. RANK(): assigns rank with gaps for ties (1,1,3,4,5 if two rows tie for 1st). DENSE_RANK(): assigns rank without gaps (1,1,2,3,4 if two rows tie for 1st). 17. Write a query to find the top 3 salaries per department. SELECT department_id, employee_id, salary FROM ( SELECT department_id, employee_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rnk FROM employees ) ranked WHERE rnk <= 3; 18. What is LAG and LEAD? LAG(column, n): returns the value from n rows BEFORE the current row. LEAD(column, n): returns the value from n rows AFTER the current row. Use case: calculate day-over-day order growth: LEAD(orders) - orders.

19. What is a database index and why use it? An index is a data structure (typically B-tree) that speeds up data retrieval by creating a sorted pointer to rows. Without an index, a query scans all rows (full table scan). With an index, it jumps directly to matching rows. When to create an index: • Columns used frequently in WHERE, JOIN, or ORDER BY clauses • Columns with high cardinality (many distinct values) When NOT to index: • Columns that are rarely queried • Tables that are written to more than read (indexes slow down INSERT/UPDATE/DELETE) • Small tables where a full scan is faster than index lookup 20. What is the difference between clustered and non-clustered index? Clustered index: the actual table data is stored in the index order. Only one per table (typically the primary key). Non-clustered index: a separate structure with pointers to the table rows. Multiple per table allowed. 21. What is EXPLAIN / EXPLAIN ANALYZE? EXPLAIN shows the query execution plan — how the database engine will execute the query: which indexes it will use, estimated row counts, join strategy. Essential for query optimization. Add ANALYZE (or EXPLAIN ANALYZE) to get actual execution statistics.