PostgreSQL SQL Fundamentals - Complete Notes

CREATE TABLE customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text NOT NULL UNIQUE,
    full_name   text NOT NULL,
    city        text,
    active      boolean NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    category   text NOT NULL,
    price      numeric(12, 2) NOT NULL CHECK (price >= 0),
    stock      integer NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
    order_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(customer_id),
    status     text NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    ordered_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES orders(order_id),
    product_id bigint NOT NULL REFERENCES products(product_id),
    quantity   integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);

SELECT column_or_expression AS output_name
FROM table_or_source
WHERE row_condition
GROUP BY grouping_expression
HAVING group_condition
ORDER BY sort_expression
LIMIT row_count
OFFSET rows_to_skip;

SELECT
    product_id,
    name,
    price,
    price * 1.18 AS price_with_tax
FROM products;

-- A literal and an expression can appear without coming from a table.
SELECT CURRENT_DATE AS today, 2 * 3 AS answer;

-- Qualify columns when more than one source could contain the same name.
SELECT p.product_id, p.name
FROM products AS p;

SELECT product_id, name, price
FROM products
WHERE category = 'book'
  AND price >= 500
  AND stock <> 0;

-- Standard comparisons: =  <>  <  <=  >  >=
-- PostgreSQL also accepts !=, but <> is standard SQL.

SELECT *
FROM orders
WHERE status IN ('paid', 'shipped');

SELECT *
FROM products
WHERE price BETWEEN 500 AND 1000;  -- inclusive at both ends

SELECT *
FROM customers
WHERE city = 'Pune' OR city = 'Mumbai';

SELECT * FROM customers WHERE full_name LIKE 'A%';   -- starts with A
SELECT * FROM customers WHERE full_name LIKE '%sha'; -- ends with sha
SELECT * FROM customers WHERE full_name LIKE '_avi'; -- one character, then avi

-- PostgreSQL extension: case-insensitive LIKE
SELECT * FROM customers WHERE full_name ILIKE '%asha%';

SELECT * FROM customers WHERE city IS NULL;
SELECT * FROM customers WHERE city IS NOT NULL;

-- Null-safe comparison: true when the values differ, including one-null cases.
SELECT *
FROM customers
WHERE city IS DISTINCT FROM 'Pune';

-- Provide a display fallback without changing stored data.
SELECT full_name, COALESCE(city, 'City not provided') AS city
FROM customers;

SELECT product_id, name, price
FROM products
ORDER BY price DESC, product_id ASC;

-- PostgreSQL lets you choose where nulls appear.
SELECT customer_id, full_name, city
FROM customers
ORDER BY city ASC NULLS LAST, customer_id ASC;

SELECT order_id, ordered_at
FROM orders
ORDER BY ordered_at DESC, order_id DESC
LIMIT 20;

-- Page 6 with 20 rows per page. Simple, but the database must skip 100 rows.
SELECT order_id, ordered_at
FROM orders
ORDER BY ordered_at DESC, order_id DESC
LIMIT 20 OFFSET 100;

-- Keyset pagination: continue after the final row of the previous page.
SELECT order_id, ordered_at
FROM orders
WHERE (ordered_at, order_id) < (:last_ordered_at, :last_order_id)
ORDER BY ordered_at DESC, order_id DESC
LIMIT 20;

SELECT
    o.order_id,
    o.status,
    c.customer_id,
    c.full_name
FROM orders AS o
INNER JOIN customers AS c
    ON c.customer_id = o.customer_id;

-- INNER is optional. JOIN means INNER JOIN.
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id

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

SELECT c.customer_id, c.full_name
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- Keep every customer. Attach only paid orders.
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.status = 'paid';

-- Keeps only customers that have a paid order. Customers without one disappear.
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.status = 'paid';

-- RIGHT JOIN preserves every customer. Usually clearer when rewritten as LEFT JOIN.
SELECT o.order_id, c.customer_id
FROM orders AS o
RIGHT JOIN customers AS c ON c.customer_id = o.customer_id;

-- Reconcile imported products with the real catalogue.
SELECT p.product_id, i.external_product_id
FROM products AS p
FULL OUTER JOIN imported_products AS i
    ON i.sku = p.sku
WHERE p.product_id IS NULL OR i.external_product_id IS NULL;

-- Every size paired with every colour: 3 sizes x 4 colours = 12 rows.
SELECT s.size, c.colour
FROM sizes AS s
CROSS JOIN colours AS c;

SELECT order_id, customer_id, full_name
FROM orders
JOIN customers USING (customer_id);

-- Equivalent matching condition:
SELECT o.order_id, o.customer_id, c.full_name
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id;

-- employees.manager_id references employees.employee_id
SELECT
    employee.full_name AS employee_name,
    manager.full_name  AS manager_name
FROM employees AS employee
LEFT JOIN employees AS manager
    ON manager.employee_id = employee.manager_id;

SELECT
    COUNT(*)          AS product_count,
    MIN(price)        AS cheapest,
    MAX(price)        AS most_expensive,
    AVG(price)        AS average_price,
    COALESCE(SUM(stock), 0) AS total_units
FROM products;

SELECT
    category,
    COUNT(*)   AS product_count,
    AVG(price) AS average_price
FROM products
WHERE stock > 0
GROUP BY category
HAVING COUNT(*) >= 3
ORDER BY average_price DESC;

SELECT
    customer_id,
    COUNT(*) AS all_orders,
    COUNT(*) FILTER (WHERE status = 'paid')      AS paid_orders,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders
FROM orders
GROUP BY customer_id;

SELECT
    o.order_id,
    SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.order_id;

-- COUNT DISTINCT protects against repeated customer ids after the join.
SELECT COUNT(DISTINCT o.customer_id) AS customers_who_ordered
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id;

SELECT DISTINCT city
FROM customers;

SELECT DISTINCT city, active
FROM customers;
-- The second query removes duplicate (city, active) PAIRS, not duplicate cities alone.

SELECT COUNT(DISTINCT customer_id)
FROM orders;

SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;

-- Prefer ALL when duplicate removal is not required.
SELECT customer_id, 'order' AS source FROM orders
UNION ALL
SELECT customer_id, 'support' AS source FROM support_tickets;

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

SELECT category_totals.category, category_totals.average_price
FROM (
    SELECT category, AVG(price) AS average_price
    FROM products
    GROUP BY category
) AS category_totals
WHERE category_totals.average_price > 1000;

SELECT c.customer_id, c.full_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
);

SELECT c.customer_id, c.full_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

SELECT *
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE status = 'paid'
);

-- More expensive than at least one product price.
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'book');

-- More expensive than every product price in that category.
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'book');

INSERT INTO customers (email, full_name, city)
VALUES ('asha@example.com', 'Asha Rao', 'Pune');

INSERT INTO products (name, category, price, stock)
VALUES
    ('SQL Handbook', 'book', 799.00, 20),
    ('Mechanical Keyboard', 'electronics', 5999.00, 5),
    ('Notebook', 'stationery', 120.00, 100);

-- All columns use their declared default or NULL when allowed.
INSERT INTO audit_heartbeat DEFAULT VALUES;

INSERT INTO customers (email, full_name, city)
VALUES (:email, :full_name, :city)
RETURNING customer_id, email, created_at;

INSERT INTO archived_customers (customer_id, email, full_name, archived_at)
SELECT customer_id, email, full_name, CURRENT_TIMESTAMP
FROM customers
WHERE active = false
RETURNING customer_id;

UPDATE products
SET
    price = price * 1.05,
    stock = stock + 10
WHERE category = 'book'
RETURNING product_id, name, price, stock;

UPDATE products AS p
SET price = change.new_price
FROM price_changes AS change
WHERE change.product_id = p.product_id
RETURNING p.product_id, p.price;

DELETE FROM orders
WHERE status = 'cancelled'
  AND ordered_at < CURRENT_DATE - INTERVAL '1 year'
RETURNING order_id, customer_id, ordered_at;

DELETE FROM products AS p
USING discontinued_products AS d
WHERE d.product_id = p.product_id
RETURNING p.product_id, p.name;

INSERT INTO customers (email, full_name, city)
VALUES (:email, :full_name, :city)
ON CONFLICT (email) DO NOTHING
RETURNING customer_id;

INSERT INTO customers AS existing (email, full_name, city)
VALUES (:email, :full_name, :city)
ON CONFLICT (email) DO UPDATE
SET
    full_name = EXCLUDED.full_name,
    city      = EXCLUDED.city,
    active    = true
RETURNING existing.customer_id, existing.email, existing.full_name;

INSERT INTO inventory_snapshots AS current
    (product_id, quantity, observed_at)
VALUES
    (:product_id, :quantity, :observed_at)
ON CONFLICT (product_id) DO UPDATE
SET
    quantity    = EXCLUDED.quantity,
    observed_at = EXCLUDED.observed_at
WHERE EXCLUDED.observed_at > current.observed_at
RETURNING product_id, quantity, observed_at;

-- Conceptual prepared statement. Placeholder syntax depends on the client library.
SELECT customer_id, email
FROM customers
WHERE email = :email;

-- Never build SQL like this in application code:
-- "SELECT ... WHERE email = '" + userInput + "'"

BEGIN;

SELECT order_id, status
FROM orders
WHERE status = 'cancelled'
  AND ordered_at < CURRENT_DATE - INTERVAL '1 year';

DELETE FROM orders
WHERE status = 'cancelled'
  AND ordered_at < CURRENT_DATE - INTERVAL '1 year'
RETURNING order_id;

-- If the returned rows are correct:
COMMIT;

-- If anything is wrong, use this instead of COMMIT:
-- ROLLBACK;

SELECT *
FROM orders
WHERE ordered_at >= TIMESTAMPTZ '2026-07-01 00:00:00+05:30'
  AND ordered_at <  TIMESTAMPTZ '2026-08-01 00:00:00+05:30';

SELECT
    c.customer_id,
    c.full_name,
    COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY c.customer_id;