SQL interview questions and answers

WHERE filters rows before grouping. HAVING filters groups after GROUP BY, so it can use aggregates like COUNT.

INNER returns matches only. LEFT keeps all rows from the left table, RIGHT from the right, FULL from both. CROSS gives every combination.

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); or use DENSE_RANK().

DELETE removes chosen rows and can be rolled back. TRUNCATE removes all rows fast and resets auto-increment. DROP removes the table itself.

A table has one primary key and it can’t be NULL. It can have many unique keys, which allow NULL values.

It speeds up reads on the indexed columns but slows inserts and updates, and takes space.

Organising tables to remove duplicate data: 1NF has atomic values, 2NF removes partial dependencies, 3NF removes transitive ones.

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

Functions over a set of rows without grouping them away: ROW_NUMBER, RANK, DENSE_RANK, and running totals with SUM() OVER().

UNION removes duplicates; UNION ALL keeps them and is faster.

A named temporary result defined with WITH, which makes long queries easier to read and can be recursive.

A group of statements that succeed or fail together. ACID: atomicity, consistency, isolation, durability.

SELECT d.name, COUNT(e.id) FROM departments d LEFT JOIN employees e ON e.dept_id = d.id GROUP BY d.name;