SQL (Structured Query Language) is the declarative language for relational databases. You describe what you want, not how to get it. Interviews test writing SELECT queries with filtering, joining tables, grouping and aggregating, and they love the subtle distinctions (WHERE vs HAVING, the JOIN types, DELETE vs TRUNCATE).
The shape of a query
A SELECT flows logically as: FROM (and JOINs) pick the rows, WHERE filters them, GROUP BY buckets them, HAVING filters the groups, SELECT chooses columns/aggregates, and ORDER BY sorts. Knowing this order explains most query behaviour.
Joins and grouping
-- employees per department with avg salary above 50000 SELECT d.name, COUNT(*) AS headcount, AVG(e.salary) AS avg_sal FROM employees e JOIN departments d ON e.dept_id = d.id WHERE e.active = 1 -- filter rows GROUP BY d.name HAVING AVG(e.salary) > 50000 -- filter groups ORDER BY avg_sal DESC;
⚡ The edge
- WHERE filters rows before grouping; HAVING filters groups after — aggregates (AVG, COUNT) belong in HAVING, not WHERE.
- Know the joins: INNER keeps only matches; LEFT keeps all left rows (nulls for no match); RIGHT the mirror; FULL keeps both sides.
Worked example
'What is the difference between WHERE and HAVING?'
- WHERE filters individual rows before any grouping happens.
- HAVING filters the grouped results after GROUP BY, so it can use aggregates like COUNT or AVG.
- Rule of thumb: conditions on raw columns go in WHERE; conditions on aggregates go in HAVING.
Answer: WHERE filters rows before grouping; HAVING filters groups after, and can use aggregate functions.
Worked example
'DELETE vs TRUNCATE vs DROP?'
- DELETE removes selected rows (with a WHERE), is logged row-by-row, and can be rolled back.
- TRUNCATE removes all rows quickly with minimal logging and resets identity, but keeps the table structure.
- DROP removes the entire table — structure and data — from the database.
Answer: DELETE: rows (filterable, rollback). TRUNCATE: all rows fast, keeps table. DROP: removes the whole table.
⚠ Watch out
- You cannot use an aggregate (e.g. COUNT(*) > 5) in WHERE — it must go in HAVING.
- An INNER JOIN drops non-matching rows; if you need all of one side, use a LEFT/RIGHT JOIN.
- DELETE is row-level and rollback-able; TRUNCATE is a fast bulk operation that's harder to undo.