SQL · Beginner interview

SQL (Structured Query Language) is the standard language for querying and manipulating relational databases. A relational database stores data in tables of rows and columns, with relationships between tables expressed through keys. SQL lets you declare what data you want (SELECT, INSERT, UPDATE, DELETE) and the engine figures out how to retrieve it.

SELECT chooses which columns to return; WHERE filters which rows by a condition. The database applies the WHERE filter to each row, keeping only those that match, then returns the chosen columns. You combine conditions with AND/OR and use operators like =, >, IN, LIKE, and BETWEEN.

SELECT name, age FROM users WHERE age >= 18 AND country = 'US';

ORDER BY sorts the result by one or more columns, ascending by default or DESC. LIMIT (or TOP/FETCH) caps how many rows come back, often with OFFSET for paging. DISTINCT removes duplicate rows from the result. Together they're how you get, say, the 10 most recent unique entries.

A JOIN combines rows from two tables based on a related column, usually a foreign key matching a primary key. An INNER JOIN returns only the rows that have a match in both tables — unmatched rows are dropped. It's how you pull related data together, like orders with their customer details.

SELECT o.id, u.name FROM orders o
JOIN users u ON u.id = o.user_id;

A LEFT JOIN returns all rows from the left table plus matches from the right (NULLs where there's no match) — useful for 'all users and their orders, even those with none'. RIGHT JOIN is the mirror image. FULL OUTER JOIN returns all rows from both sides, matched where possible. The choice depends on which unmatched rows you need to keep.

A self join is a table joined to itself, using aliases to treat it as two tables. It's used for hierarchical or comparative data within one table — for example, matching employees to their managers when both are rows in the same employees table. You alias the table twice and join on the relating columns.

GROUP BY collapses rows that share values in the grouped columns into a single row per group, so you can compute aggregates per group — like total sales per customer. Every column in the SELECT must either be in the GROUP BY or wrapped in an aggregate function. It's the foundation of summary queries.

SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. So you use WHERE to restrict which rows are aggregated and HAVING to restrict the grouped results based on an aggregate, like 'customers with more than 5 orders'. WHERE can't reference aggregate functions; HAVING can.

SELECT user_id, COUNT(*) FROM orders
GROUP BY user_id HAVING COUNT(*) > 5;

Most aggregates ignore NULLs: COUNT(column) counts non-NULL values, while COUNT(*) counts all rows including those with NULLs. SUM and AVG skip NULLs too, so AVG isn't the same as SUM/COUNT(*) when nulls exist. Comparisons with NULL also yield 'unknown', so NULL = NULL is not true — you use IS NULL. Forgetting this is a common source of wrong totals.

A primary key uniquely identifies each row in a table and can't be NULL or duplicated. A foreign key is a column in one table that references the primary key of another, enforcing a relationship and referential integrity (you can't reference a row that doesn't exist). Primary keys identify; foreign keys link tables together.

NOT NULL requires a value; UNIQUE forbids duplicate values; PRIMARY KEY combines unique and not-null; FOREIGN KEY enforces a reference to another table; CHECK enforces a custom condition; DEFAULT supplies a value when none is given. Constraints push data-integrity rules into the database so bad data can't be inserted regardless of the app.

Normalization organizes tables to reduce redundancy and avoid update anomalies. 1NF: atomic values, no repeating groups. 2NF: no partial dependency on part of a composite key. 3NF: no non-key column depends on another non-key column. The goal is each fact stored once; you sometimes denormalize later for read performance, as a deliberate trade-off.