SQL from scratch: SELECT, WHERE, ORDER BY

-- Training database of an online store: clients, products, orders, order_items
-- Safe to re-run: the tables are recreated from scratch.
DROP TABLE IF EXISTS order_items, orders, products, clients;

CREATE TABLE clients (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    city       text,
    email      text UNIQUE,
    created_on date NOT NULL DEFAULT current_date
);

CREATE TABLE products (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     text NOT NULL,
    category text NOT NULL,
    price    numeric(10, 2) NOT NULL CHECK (price > 0),
    weight_g integer CHECK (weight_g > 0)
);

CREATE TABLE orders (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    client_id  bigint NOT NULL REFERENCES clients (id),
    ordered_on date NOT NULL,
    status     text NOT NULL
               CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
    promo_code text
);

CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    product_id bigint NOT NULL REFERENCES products (id),
    quantity   integer NOT NULL CHECK (quantity > 0),
    price      numeric(10, 2) NOT NULL CHECK (price > 0),
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO clients (name, city, email, created_on) VALUES
    ('Aigerim Seitova',  'Almaty',    'aigerim@example.com', '2026-06-02'), -- id 1
    ('Daniyar Akhmetov', 'Astana',    NULL,                  '2026-06-15'), -- id 2
    ('Madina Omarova',   'Almaty',    'madina@example.com',  '2026-07-01'), -- id 3
    ('Timur Kim',        'Shymkent',  'timur@example.com',   '2026-07-20'), -- id 4
    ('Aliya Bekova',     NULL,        'aliya@example.com',   '2026-08-05'), -- id 5
    ('Ruslan Ivanov',    'Almaty',    NULL,                  '2026-08-28'), -- id 6
    ('Zarina Kaliyeva',  'Karaganda', 'zarina@example.org',  '2026-09-10'), -- id 7
    ('Sergey Pak',       'Astana',    'sergey@example.com',  '2026-09-30'); -- id 8

INSERT INTO products (name, category, price, weight_g) VALUES
    ('Espresso Blend 1 kg',         'coffee',    12900, 1000), -- id 1
    ('Colombia Supremo 250 g',      'coffee',     4900,  250), -- id 2
    ('Ethiopia Yirgacheffe 250 g',  'coffee',     5600,  250), -- id 3
    ('Green Sencha 100 g',          'tea',        3200,  100), -- id 4
    ('Masala Chai 200 g',           'tea',        3800,  200), -- id 5
    ('Ceramic Mug 350 ml',          'tableware',  2500, NULL), -- id 6
    ('French Press 600 ml',         'equipment',  8900, NULL), -- id 7
    ('Hand Grinder',                'equipment', 18500, NULL), -- id 8
    ('Gift Box',                    'gifts',     15000, NULL); -- id 9

INSERT INTO orders (client_id, ordered_on, status, promo_code) VALUES
    (1, '2026-09-02', 'shipped',   NULL),       -- id 1
    (2, '2026-09-05', 'shipped',   'AUTUMN10'), -- id 2
    (1, '2026-09-14', 'paid',      NULL),       -- id 3
    (3, '2026-09-20', 'cancelled', NULL),       -- id 4
    (4, '2026-09-28', 'shipped',   NULL),       -- id 5
    (5, '2026-10-01', 'paid',      'AUTUMN10'), -- id 6
    (3, '2026-10-03', 'shipped',   NULL),       -- id 7
    (6, '2026-10-05', 'new',       NULL),       -- id 8
    (1, '2026-10-08', 'paid',      'VIP'),      -- id 9
    (2, '2026-10-09', 'new',       NULL);       -- id 10: no items yet

-- price is the price at order time: the catalogue may change, the order must not
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
    (1, 1, 1, 12900), (1, 6, 2,  2500),
    (2, 2, 2,  4900), (2, 4, 1,  3200),
    (3, 7, 1,  8900),
    (4, 8, 1, 18500),
    (5, 3, 1,  5600), (5, 5, 2,  3800), (5, 6, 1, 2500),
    (6, 1, 2, 12900),
    (7, 2, 1,  4900), (7, 3, 1,  5600),
    (8, 4, 3,  3200),
    (9, 8, 1, 18500), (9, 1, 1, 12900);

# 1. Start PostgreSQL 17 in a container (it is removed when stopped)
docker run -d --rm --name shop-db -e POSTGRES_PASSWORD=test postgres:17-alpine

# 2. Wait a few seconds, then load the training database
#    (run from the folder that contains shop.sql)
docker exec -i shop-db psql -U postgres < shop.sql

# 3. Open psql; NULL values are printed as NULL
docker exec -it shop-db psql -U postgres -P null=NULL

$ docker exec -i shop-db psql -U postgres < shop.sql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?

Get-Content shop.sql | docker exec -i shop-db psql -U postgres

$ docker exec -i shop-db psql -U postgres < shop.sql
NOTICE:  table "order_items" does not exist, skipping
NOTICE:  table "orders" does not exist, skipping
NOTICE:  table "products" does not exist, skipping
NOTICE:  table "clients" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 8
INSERT 0 9
INSERT 0 10
INSERT 0 15

postgres=# \dt
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | clients     | table | postgres
 public | order_items | table | postgres
 public | orders      | table | postgres
 public | products    | table | postgres
(4 rows)

SELECT * FROM products;

SELECT name,
       price,
       round(price * 0.9) AS price_with_10_off,
       'KZT' AS currency
FROM products;

SELECT DISTINCT category
FROM products
ORDER BY category;

SELECT name, price
FROM products
WHERE price > 10000;

-- Intent: coffee or equipment cheaper than 6000
SELECT name, category, price
FROM products
WHERE category = 'coffee' OR category = 'equipment' AND price < 6000;

SELECT name, category, price
FROM products
WHERE (category = 'coffee' OR category = 'equipment') AND price < 6000;

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

SELECT id, ordered_on, status
FROM orders
WHERE ordered_on BETWEEN '2026-09-01' AND '2026-09-30';

SELECT name FROM products WHERE name LIKE '%250 g';

SELECT name FROM clients WHERE name LIKE '%ova';
SELECT name FROM clients WHERE name LIKE 'a%';
SELECT name FROM clients WHERE name ILIKE 'a%';

--       name
-- -----------------
--  Aigerim Seitova
--  Madina Omarova
--  Aliya Bekova
-- (3 rows)
--
--  name
-- ------
-- (0 rows)
--
--       name
-- -----------------
--  Aigerim Seitova
--  Aliya Bekova
-- (2 rows)

SELECT name, email FROM clients WHERE email = NULL;

--  name | email
-- ------+-------
-- (0 rows)

SELECT name, email FROM clients WHERE email IS NULL;

SELECT NULL = NULL      AS null_eq_null,
       7 <> NULL        AS seven_ne_null,
       NULL IS NULL     AS null_is_null,
       true OR NULL     AS true_or_null,
       false AND NULL   AS false_and_null;

SELECT name, city FROM clients WHERE city <> 'Almaty';

SELECT name, city FROM clients WHERE city IS DISTINCT FROM 'Almaty';

SELECT name, coalesce(email, '(no email)') AS email
FROM clients
ORDER BY id;

SELECT name, category, price
FROM products
ORDER BY category, price DESC;

SELECT name, city FROM clients ORDER BY city, id;
SELECT name, city FROM clients ORDER BY city DESC, id;
SELECT name, city FROM clients ORDER BY city DESC NULLS LAST, id;

SELECT name, price
FROM products
ORDER BY price DESC, id
LIMIT 3;

SELECT name, price
FROM products
ORDER BY price DESC, id
LIMIT 3 OFFSET 3;

SELECT name, round(price * 0.9) AS sale_price
FROM products
WHERE sale_price < 5000;

-- ERROR:  column "sale_price" does not exist
-- LINE 3: WHERE sale_price < 5000;
--               ^

SELECT name, round(price * 0.9) AS sale_price
FROM products
WHERE round(price * 0.9) < 5000
ORDER BY sale_price;