Skip to content
pitonmert

SQL Quick Reference

SQL concepts, command groups, and short examples for querying data and managing database structures.

Published
Updated
Tags
  • SQL
  • PostgreSQL
  • Reference

SQL is a language for querying and changing data and managing structures in relational databases. This page brings together core concepts and frequently used commands with short examples. The examples use customer and order data; database-specific syntax follows PostgreSQL.

On this page

Why use SQL?

A customer can have several orders. Instead of repeating customer details in every order, we can relate two tables through a key. SQL lets us query them together to find a customer’s orders, total spending, or customers who have never ordered.

Relational databases also enforce rules at the data layer: an email can be unique, an order amount cannot be negative, and an order must reference an existing customer. Related changes can complete together or be undone in a transaction. These capabilities are useful in order management, accounting, and reporting.

How do you choose between SQL and NoSQL?

SQL is a language; NoSQL is an umbrella term for database models such as document, key-value, graph, and wide-column stores. The choice depends on the shape of the data and how it will be accessed.

A relational database is a good starting point when relationships, shared integrity rules, and reports joining multiple tables matter. A document model may fit nested data read together; a key-value model may fit cache data fetched by key; a graph model may fit traversing connections.

NoSQL is not automatically faster, and relational databases are not always the best fit. Transactions are not exclusive to relational systems; MongoDB also supports multi-document transactions. Consider the data model, queries, consistency needs, and operating cost together.

Core concepts and example schema

  • Database: A collection of related tables and other objects.
  • Table: Holds records of the same kind, such as customers.
  • Row: One record, such as a single customer.
  • Column: One field of a record, such as a name or email.
  • Data type: The kind of value a column can hold, such as INTEGER, VARCHAR, DECIMAL, or DATE.
  • NULL: An unknown or missing value; not an empty string or zero.

The examples assume the following two tables. IDs are assigned manually to keep the examples small.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  city VARCHAR(100)
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  status VARCHAR(20) NOT NULL DEFAULT 'preparing'
    CHECK (status IN ('preparing', 'shipped', 'cancelled')),
  total_amount DECIMAL(12, 2) NOT NULL CHECK (total_amount >= 0),
  created_on DATE NOT NULL DEFAULT CURRENT_DATE
);

A small dataset to try:

INSERT INTO customers (customer_id, name, email, city)
VALUES
  (1, 'Avery Taylor', 'avery@example.com', 'London'),
  (2, 'Morgan Reed', 'morgan@example.com', 'Bristol'),
  (3, 'Riley Green', 'riley@example.com', NULL);

INSERT INTO orders
  (order_id, customer_id, status, total_amount, created_on)
VALUES
  (1, 1, 'shipped', 1200, '2026-09-01'),
  (2, 1, 'preparing', 300, '2026-09-02'),
  (3, 2, 'shipped', 600, '2026-09-03');

Read the following examples independently against this starting dataset; they are not one script to run in order. An example that changes data or structures can affect the result of the next experiment.

Querying Data — DQL

DQL (Data Query Language) groups expressions used to read data. SELECT is a query; clauses such as WHERE and ORDER BY are parts of a query.

DQL, DML, DDL, TCL, and DCL are learning categories here. Sources may classify statements differently; for example, Oracle includes SELECT in DML.

SELECT

Query result / Column

Chooses the columns and calculated values to return. FROM identifies the source.

SELECT name, email
FROM customers;

Returns only customer names and email addresses.

AS (Alias)

Column / Table

Gives a column or table a temporary name within the query.

SELECT c.name AS customer_name
FROM customers AS c;

Uses c as the table alias and names the result column customer_name. The actual table and column names do not change.

DISTINCT

Result rows

Removes duplicate result rows formed by the selected columns together.

SELECT DISTINCT customer_id
FROM orders;

Returns each customer ID once, even if the customer has several orders.

WHERE

Row

Selects rows matching a condition. Conditions can be combined with AND, OR, and NOT.

SELECT name, email
FROM customers
WHERE city = 'London';

Returns only customers in London.

ORDER BY

Result rows

Sorts results in ascending (ASC) or descending (DESC) order.

SELECT order_id, total_amount
FROM orders
ORDER BY total_amount DESC, order_id ASC;

Shows larger amounts first; orders with equal amounts are sorted by ID.

LIMIT / OFFSET

Result rows

Limits the number of returned rows and skips rows at the beginning.

SELECT order_id, total_amount
FROM orders
ORDER BY order_id
LIMIT 2 OFFSET 1;

Skips the first order and returns the next two. For stable pagination, complete the ordering with a unique field.

PostgreSQL note: LIMIT / OFFSET is supported. Pagination syntax differs in systems such as SQL Server.

Relationships and Advanced Queries

This section covers combining queries, creating intermediate results, and calculating values. These are not a separate language family from DQL.

JOIN

Table / Result rows

Combines tables using a condition; a predefined foreign key is not required.

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id;

Returns all customers. Customers without orders have NULL in the order field; customers with several orders appear in several rows.

INNER JOIN keeps matches; LEFT JOIN preserves all left rows; RIGHT JOIN preserves all right rows; FULL JOIN also keeps unmatched rows from both sides. CROSS JOIN produces every pair of rows.

SUBQUERY

Query / Intermediate result

Uses the result of one query inside another.

SELECT order_id, total_amount
FROM orders
WHERE total_amount > (
  SELECT AVG(total_amount)
  FROM orders
);

Returns orders above the average order amount.

CTE (WITH)

Query / Named intermediate result

Names an intermediate result for use within one statement; it does not create a permanent table.

WITH customer_totals AS (
  SELECT customer_id, SUM(total_amount) AS total_spend
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total_spend
FROM customer_totals
WHERE total_spend > 1000;

Calculates customer totals, then returns those whose total spending exceeds 1000.

CASE WHEN

Column / Value

Produces a calculated value based on conditions.

SELECT order_id,
  CASE
    WHEN total_amount >= 1000 THEN 'High'
    ELSE 'Standard'
  END AS amount_level
FROM orders;

Labels each order by amount without changing the stored data.

UNION / UNION ALL

Result set

Appends query results with the same column count and compatible corresponding data types.

SELECT customer_id FROM customers WHERE city = 'London'
UNION
SELECT customer_id FROM orders WHERE total_amount >= 500;

Returns unique IDs of customers who live in London or have an order worth at least 500. Using UNION ALL retains duplicates.

INTERSECT / EXCEPT

Result set

INTERSECT returns shared rows; EXCEPT returns rows in the first result but not the second.

-- Customers who have placed an order
SELECT customer_id FROM customers
INTERSECT
SELECT customer_id FROM orders;

-- Customers who have never ordered
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

The first query finds customers with orders, the second those without. Both operations remove duplicates by default.

AGGREGATE (COUNT / SUM / AVG)

Row set / Summary value

Calculates a count, total, or average across multiple rows.

SELECT
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_amount,
  AVG(total_amount) AS average_amount
FROM orders;

Summarizes all orders in one row. MIN and MAX return the smallest and largest values.

GROUP BY

Row group

Summarizes rows sharing the same field values.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Returns a separate order count for each customer.

HAVING

Row group

Filters groups after grouping.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

Returns customers with at least two orders. WHERE filters rows before grouping; HAVING filters the groups.

WINDOW FUNCTIONS (OVER / PARTITION BY)

Row / Calculated column

Calculates row numbers and running totals without collapsing rows into a summary.

SELECT customer_id, order_id,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_on, order_id
  ) AS order_number,
  SUM(total_amount) OVER (
    PARTITION BY customer_id
    ORDER BY created_on, order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders
ORDER BY customer_id, created_on, order_id;

Numbers each customer’s orders and adds the total so far to each row. PARTITION BY separates calculations by customer; ROWS defines which rows contribute to the calculation.

Changing Data — DML

DML (Data Manipulation Language) covers inserting, changing, and deleting records.

INSERT

Row

Adds a record to a table.

INSERT INTO customers (customer_id, name, email, city)
VALUES (4, 'Jordan Blake', 'jordan@example.com', 'York');

Creates customer 4. Running it again with the same ID raises a primary key error.

UPDATE

Row

Changes selected fields in matching records.

UPDATE customers
SET city = 'Bristol'
WHERE customer_id = 1;

Updates customer 1’s city. Without WHERE, every row is updated.

DELETE

Row

Removes matching records while keeping the table structure.

DELETE FROM orders
WHERE order_id = 2;

Deletes only order 2. Without WHERE, every row in the table is deleted.

Defining Structures — DDL

DDL (Data Definition Language) covers creating, changing, and dropping tables and other database objects. Constraints, views, indexes, and triggers are also part of these object definitions.

CREATE

Database object / Table

Creates an object. Besides tables, objects include schemas, views, and indexes.

CREATE TABLE categories (
  category_id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Creates a table for category IDs and names.

ALTER

Table structure

Changes an existing table definition.

ALTER TABLE customers
ADD COLUMN phone VARCHAR(30);

Adds a phone column; the field is initially NULL in existing rows.

DROP

Database object

Removes an object completely.

DROP TABLE IF EXISTS categories;

Drops the category table and its data if it exists. IF EXISTS only avoids an error when the object is absent; it does not protect the data.

TRUNCATE

Table / All rows

Removes all rows while retaining the columns and table definition.

TRUNCATE TABLE orders;

Empties the order table. It cannot be restricted with WHERE.

PostgreSQL note: It can be rolled back within a transaction and takes a strong table lock. Foreign keys can prevent the operation. RESTART IDENTITY also resets identity sequences owned by the table; these examples assign IDs manually, so it is unnecessary.

PRIMARY KEY / FOREIGN KEY

Column / Table integrity

A primary key uniquely identifies a row and does not allow NULL. A foreign key preserves references to existing records; it does not enforce uniqueness itself.

CREATE TABLE addresses (
  address_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  street_address VARCHAR(255) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

Each address has a unique ID and references an existing customer. A customer can have several addresses.

CHECK / DEFAULT / NOT NULL

Column / Data rules

CHECK validates a condition, DEFAULT supplies a value when a field is omitted, and NOT NULL prevents a missing value.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(12, 2) NOT NULL CHECK (price >= 0),
  stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

A product requires a name and price; price and stock cannot be negative. Stock defaults to zero when omitted. DEFAULT does not replace an explicitly supplied NULL.

VIEW

Virtual table

Names a query so it can be reused in later queries.

CREATE VIEW shipped_orders AS
SELECT order_id, customer_id, total_amount
FROM orders
WHERE status = 'shipped';

SELECT * FROM shipped_orders;

Returns shipped orders through the view. A normal view does not store a separate copy of its results; a materialized view is a different object.

INDEX

Table / Access path

Creates an index that may speed up specific searches, joins, and sorts.

CREATE INDEX idx_orders_customer
ON orders (customer_id);

May help queries that find orders by customer. The query planner decides whether to use the index.

TRIGGER

Table / Event

Runs an operation automatically on selected events; it can fire before or after an event.

CREATE FUNCTION protect_order_amount()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF OLD.status = 'shipped'
     AND NEW.total_amount IS DISTINCT FROM OLD.total_amount THEN
    RAISE EXCEPTION 'Cannot change the amount of a shipped order';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER shipped_order_amount
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION protect_order_amount();

Raises an error when an update tries to change the amount of an already shipped order.

PostgreSQL note: The example includes its PL/pgSQL trigger function. BEGIN / END inside the function is a code block; it does not start a transaction.

Managing Transactions — TCL

TCL (Transaction Control Language) manages several changes as one transaction. ACID describes applying an operation as a whole (atomicity), preserving defined rules (consistency), managing concurrent effects according to the isolation level (isolation), and retaining committed changes (durability).

BEGIN

Transaction / Data changes

Starts a transaction.

BEGIN;

UPDATE orders
SET status = 'shipped'
WHERE order_id = 2;

SELECT status FROM orders WHERE order_id = 2;

ROLLBACK;

Lets you see the change in the same session, then undoes it at the end of the example.

COMMIT

Transaction / Persistence

Confirms changes in the open transaction.

BEGIN;
UPDATE customers SET city = 'Bristol' WHERE customer_id = 1;
COMMIT;

Makes the city change persistent. Running ROLLBACK later does not undo this commit.

ROLLBACK

Transaction / Undo

Undoes uncommitted changes in the open transaction.

BEGIN;
DELETE FROM orders WHERE order_id = 2;
ROLLBACK;

Undoes the deletion and keeps the order. In PostgreSQL, a failed transaction must be rolled back before proceeding; with savepoints, it can be rolled back to the relevant point.

Managing Permissions — DCL

DCL (Data Control Language) manages user and role access to objects. The examples assume an existing reporting_role and a session allowed to grant privileges; connection and schema access may also be required.

GRANT

Role / Database object

Grants a specified privilege on an object.

GRANT SELECT ON orders TO reporting_role;

Allows the reporting role to read the order table.

REVOKE

Role / Database object

Removes a previously granted privilege.

REVOKE SELECT ON orders FROM reporting_role;

Removes this direct read privilege. Access can remain if privileges are received through another route, such as role membership or PUBLIC.

Common distinctions and practical notes

NULL, empty strings, and zero

Use IS NULL or IS NOT NULL, rather than = NULL.

SELECT name
FROM customers
WHERE city IS NULL;

Returns customers with no city information. '' is an empty string; 0 is a number. COALESCE(city, 'Not specified') can display a label instead of NULL in the result.

WHERE versus HAVING

WHERE selects rows to summarize; HAVING selects calculated groups. “Take shipped orders” belongs in WHERE; “take customers whose total exceeds 1000” belongs in HAVING.

COUNT(*) versus COUNT(column)

COUNT(*) counts all rows; COUNT(city) counts only rows where city is not NULL. SUM and AVG also ignore NULL values. For an empty set, COUNT returns zero, while SUM and AVG return NULL.

DELETE, TRUNCATE, and DROP

DELETE removes selected rows; TRUNCATE empties a table; DROP removes an object. Rollback, locking, and trigger behavior vary by database system. In PostgreSQL, the table operations shown here can be rolled back in an open transaction; after commit, ROLLBACK is not enough.

Result ordering

Row order is not guaranteed without ORDER BY. Ordering inside a window does not itself set the outer query’s result order.

Index cost

Indexes use disk space and must be maintained as data changes. Indexing every column can slow writes. Inspect the query plan with EXPLAIN; EXPLAIN ANALYZE actually executes the query.

Parameterized queries

Use the database driver’s parameter binding instead of appending user input to SQL text.

SELECT name, email
FROM customers
WHERE email = $1;

A PostgreSQL driver binds the value for $1 separately; this is not a standalone command to paste into a console. Parameter syntax varies by driver. SQL identifiers such as table names cannot be value parameters; select dynamic identifiers from an allowlist.

References