Basic SQL questions
1. What are the types of joins?
INNER JOIN returns matching rows from both tables. LEFT JOIN returns all left rows plus matches (NULLs otherwise), RIGHT JOIN the reverse, FULL OUTER JOIN all rows from both, and CROSS JOIN every combination.
2. Primary key vs unique key vs foreign key?
A primary key uniquely identifies each row and cannot be NULL; a table has one. A unique key also enforces uniqueness but allows NULL and a table can have many. A foreign key references a key in another table to enforce referential integrity.
3. WHERE vs HAVING?
WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups after GROUP BY and can use aggregates such as COUNT(*) > 5.
4. DELETE vs TRUNCATE vs DROP?
DELETE removes selected rows, can use WHERE and fires triggers. TRUNCATE quickly removes all rows and resets storage. DROP removes the entire table structure along with its data.
Intermediate SQL questions
5. How do you find the second highest salary?
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); or with window functions: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) r FROM employees) t WHERE r = 2;
6. What is normalization?
Normalization organizes tables to reduce duplication and update anomalies. 1NF requires atomic values, 2NF removes partial dependency on a composite key, and 3NF removes transitive dependencies between non-key columns.
7. What is an index and when can it hurt?
An index (usually a B-tree) lets the database find rows without scanning the whole table, speeding up WHERE, JOIN and ORDER BY. Indexes cost storage and slow down INSERT, UPDATE and DELETE, so index columns that are actually filtered or joined on.
8. RANK vs DENSE_RANK vs ROW_NUMBER?
ROW_NUMBER gives a unique sequence. RANK gives equal values the same rank and leaves gaps (1, 1, 3). DENSE_RANK gives equal ranks without gaps (1, 1, 2).
Advanced SQL questions
9. What are ACID properties?
Atomicity (all or nothing), Consistency (constraints always hold), Isolation (concurrent transactions do not interfere, controlled by isolation levels) and Durability (committed data survives crashes).
10. How do you find duplicate rows?
Group by the columns that define a duplicate and filter: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1; To delete extras, keep one row per group using ROW_NUMBER() OVER (PARTITION BY email ORDER BY id).
Want to practise these with a trainer? APEX live batches include mock interviews, real projects and placement support. See upcoming batches.