SQL Base — Cheat Sheet

Tags: sql, database, relational, ddl, dml, queries, backend

Level: basic

TL;DR: Quick reference for foundational SQL syntax, clause evaluation order, joins, aggregation, and everyday interview queries.

Key concepts

SQL divides into DDL (schema definition) and DML (data manipulation).

Query logical execution order: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.

WHERE filters individual rows before aggregation takes place.

HAVING filters aggregated groups created by GROUP BY.

PRIMARY KEY guarantees unique, non-null row identification in a table.

FOREIGN KEY enforces referential integrity across related relational tables.

INNER JOIN returns only records matching on both sides of the condition.

LEFT JOIN preserves all left-table rows and fills missing right-table columns with NULL.

NULL represents missing or unknown values, not zero or an empty string.

Aggregate functions (SUM, AVG, COUNT, MIN, MAX) ignore NULL values, except COUNT(*).

Typical questions and ready answers

Q: What is the difference between WHERE and HAVING?R: I use WHERE to filter individual rows before grouping, and HAVING to filter aggregated metrics produced by GROUP BY.

Q: What is the difference between INNER JOIN and LEFT JOIN?R: INNER JOIN keeps only matching records, while LEFT JOIN retains all records from the left table and inserts NULLs for unmatched right rows.

Q: How do you handle NULL in equality comparisons?R: Standard equality operators fail with NULL; I always use IS NULL or IS NOT NULL, or functions like COALESCE.

Q: What is the difference between COUNT(*) and COUNT(column_name)?R: COUNT(*) counts every row including rows with NULLs, whereas COUNT(column_name) counts only non-null values for that column.

Q: What does UNION do compared to UNION ALL?R: UNION merges result sets and performs deduplication, whereas UNION ALL merges them directly without removing duplicates, making it faster.

Pitfalls and common mistakes

Comparing NULL using = NULL instead of IS NULL returns UNKNOWN instead of TRUE.

Selecting non-aggregated columns alongside GROUP BY without aggregating them breaks standard SQL.

Forgetting WHERE on UPDATE or DELETE modifies every record in the table.

Relying on implicit sorting without an explicit ORDER BY clause.

Cartesian product caused by omitting JOIN conditions.

Counting NULL values accidentally by writing COUNT(column) instead of COUNT(*).

Snippets and commandsSQL

```-- Standard SELECT with grouping, filtering, and ordering

SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary

FROM employees

WHERE is_active = 1

GROUP BY department_id

HAVING COUNT(*) > 5

ORDER BY avg_salary DESC

LIMIT 10;

-- Basic joins demonstration

SELECT e.id, e.name, d.department_name

FROM employees e

INNER JOIN departments d ON e.department_id = d.id;

SELECT e.id, e.name, d.department_name

FROM employees e

LEFT JOIN departments d ON e.department_id = d.id;

-- Safe NULL fallback

SELECT name, COALESCE(phone_number, 'N/A') AS contact_phone

FROM customers

WHERE email IS NOT NULL;

-- DDL definition

CREATE TABLE departments (

id INT PRIMARY KEY,

department_name VARCHAR(100) NOT NULL UNIQUE

);

CREATE TABLE employees (

id INT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

department_id INT,

salary DECIMAL(10, 2),

is_active BOOLEAN DEFAULT TRUE,

CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(id)

);

-- Common DML statements

INSERT INTO employees (id, name, department_id, salary)

VALUES (1, 'Alice', 10, 75000.00);

UPDATE employees

SET salary = salary * 1.05

WHERE department_id = 10;

DELETE FROM employees

WHERE id = 1;

```

To review

[ ] Clause execution lifecycle (FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY)

[ ] Set operations (UNION vs UNION ALL, INTERSECT, EXCEPT)

[ ] Three-valued logic (TRUE, FALSE, UNKNOWN) with NULL handling

[ ] Self joins and cross joins

[x] Basic CRUD syntax (INSERT, SELECT, UPDATE, DELETE)

[x] Primary and Foreign Key relational constraints