SQL Basics Cheat Sheet: Runnable PostgreSQL Queries

A compact PostgreSQL syntax reference with one fixture

Use this sheet to refresh clause syntax, then run each example against the same tiny PostgreSQL 16 dataset. The setup creates temporary departments and employees inside a transaction. Run the blocks in order in a disposable learning session; the final ROLLBACK removes everything. NULL salary means unknown, not zero.

Shared setup

BEGIN;
CREATE TEMP TABLE basic_departments(id integer PRIMARY KEY,name text NOT NULL) ON COMMIT DROP;
CREATE TEMP TABLE basic_employees(id integer PRIMARY KEY,name text NOT NULL,
 department_id integer REFERENCES basic_departments(id),salary numeric) ON COMMIT DROP;
INSERT INTO basic_departments VALUES (1,'Data'),(2,'Sales'),(3,'Support');
INSERT INTO basic_employees VALUES
 (1,'Ada',1,100),(2,'Ben',1,80),(3,'Cara',2,NULL),(4,'Dee',NULL,80);

The department foreign key belongs on the employee for this chosen many-employees-to-one-department model. A missing department_id is allowed; a non-NULL unknown department ID is not. Exact numeric values are used for salary arithmetic rather than floating-point approximation.

Read the clauses in their written order

Filter, sort, and limit deterministically

SELECT id,name,salary FROM pg_temp.basic_employees
WHERE salary>=80 AND (department_id=1 OR department_id IS NULL)
ORDER BY salary DESC,id LIMIT 2;

id | name | salary
1 | Ada | 100
2 | Ben | 80

Parentheses state the intended AND/OR grouping. Ben wins the tie at 80 because id breaks it; removing that secondary order leaves the chosen tied row unspecified. Cara’s unknown salary does not satisfy salary >= 80.

Count rows separately from observed values

SELECT COUNT(*) AS employees,COUNT(salary) AS known_salaries,
 SUM(salary) AS total_known_salary,ROUND(AVG(salary),2) AS average_known_salary
FROM pg_temp.basic_employees;

employees | known_salaries | total_known_salary | average_known_salary
4 | 3 | 260 | 86.67

AVG divides by three known salaries, not all four employees. Reporting the average alongside the observed count makes that denominator visible. Replacing unknown salary with zero would answer a different question.

Use WHERE and HAVING together

SELECT department_id,COUNT(*) AS employees,SUM(salary) AS total_salary
FROM pg_temp.basic_employees WHERE department_id IS NOT NULL
GROUP BY department_id HAVING COUNT(*)>=2 ORDER BY department_id;

department_id | employees | total_salary
1 | 2 | 180

Keep an unmatched department with a LEFT JOIN

SELECT d.id,d.name,COUNT(e.id) AS employees
FROM pg_temp.basic_departments d LEFT JOIN pg_temp.basic_employees e
 ON e.department_id=d.id
GROUP BY d.id,d.name ORDER BY d.id;

id | name | employees
1 | Data | 2
2 | Sales | 1
3 | Support | 0

COUNT(e.id) ignores the null-extended employee row for Support. COUNT(*) would count that preserved join row as one. Dee belongs to no department and therefore does not appear in this department population.

Ask for absence with NOT EXISTS

SELECT d.id,d.name FROM pg_temp.basic_departments d
WHERE NOT EXISTS(SELECT 1 FROM pg_temp.basic_employees e WHERE e.department_id=d.id)
ORDER BY d.id;

id | name
3 | Support

This avoids NOT IN’s surprising behavior when its comparison list contains NULL. EXISTS asks only whether a qualifying related row exists; repeated matches do not multiply output rows.

Find the second distinct known salary

WITH ranked AS (
 SELECT id,name,salary,DENSE_RANK() OVER(ORDER BY salary DESC) AS salary_rank
 FROM pg_temp.basic_employees WHERE salary IS NOT NULL
)
SELECT id,name,salary FROM ranked WHERE salary_rank=2 ORDER BY id;

id | name | salary
2 | Ben | 80
4 | Dee | 80

Second distinct salary is a value rank; it may belong to several employees. ROW_NUMBER would instead choose a row position. If there is only one distinct known salary, this query returns no rows rather than manufacturing a second value.

Rehearse a scoped update only on temporary data

SAVEPOINT salary_demo;
UPDATE pg_temp.basic_employees SET salary=110 WHERE id=1 AND salary=100
RETURNING id,salary;
ROLLBACK TO SAVEPOINT salary_demo;
SELECT id,salary FROM pg_temp.basic_employees WHERE id=1;
ROLLBACK;

id | salary
1 | 110

id | salary
1 | 100

The first result confirms the temporary change; the second confirms the savepoint undo. A committed production update needs its own reviewed scope and concurrency safeguards. A stored procedure is not automatically injection-safe: bind values and allowlist any dynamically selected identifiers.

Use the reference to predict outputs

Before adapting a query, name the row grain, NULL policy, tie rule, and time boundaries. For concept explanations, see the SQL concept sheet. PostgreSQL references: SELECT, aggregates, and transactions.

FAQ

Is this syntax universal? The examples target PostgreSQL 16; temporary-table, LIMIT, RETURNING, and NULL ordering syntax vary. Should I add an index to every filter column? No; choose indexes from measured access patterns and maintenance costs.