SQL reference: queries, data changes, DDL and transactions

SELECT category,
       count(*)   AS items,
       min(price) AS from_price
FROM products
WHERE price < 15000
GROUP BY category
HAVING count(*) > 1
ORDER BY from_price
LIMIT 3;

SELECT name, price * 0.9 AS sale_price
FROM products
WHERE sale_price < 4000;

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

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

SELECT id FROM products ORDER BY id LIMIT 3, 3;

-- ERROR:  LIMIT #,# syntax is not supported
-- LINE 1: SELECT id FROM products ORDER BY id LIMIT 3, 3;
--                                             ^
-- HINT:  Use separate LIMIT and OFFSET clauses.

SELECT c.name
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;

SELECT count(*)                 AS all_rows,
       count(weight_g)          AS with_weight,
       count(DISTINCT category) AS categories,
       sum(weight_g)            AS total_g,
       round(avg(weight_g))     AS avg_g
FROM products;

SELECT p.category,
       sum(oi.quantity * oi.price)                                  AS revenue,
       count(DISTINCT o.id) FILTER (WHERE o.promo_code IS NOT NULL) AS promo_orders
FROM order_items AS oi
JOIN orders   AS o ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY p.category
HAVING sum(oi.quantity * oi.price) > 10000
ORDER BY revenue DESC;

SELECT client_id, id AS order_id, ordered_on, status
FROM (
    SELECT o.*,
           row_number() OVER (PARTITION BY client_id ORDER BY ordered_on DESC) AS rn
    FROM orders AS o
) AS t
WHERE rn = 1
ORDER BY client_id;

WITH totals AS (
    SELECT o.id, o.ordered_on, sum(oi.quantity * oi.price) AS total
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.id
    WHERE o.status IN ('paid', 'shipped')
    GROUP BY o.id
)
SELECT id, ordered_on, total,
       sum(total) OVER (ORDER BY ordered_on)          AS running_total,
       total - lag(total) OVER (ORDER BY ordered_on)  AS diff_prev,
       round(100 * total / sum(total) OVER (), 1)     AS pct
FROM totals
ORDER BY ordered_on;

SELECT p.name, oi.price,
       rank()       OVER (ORDER BY oi.price DESC)         AS rnk,
       dense_rank() OVER (ORDER BY oi.price DESC)         AS dense,
       row_number() OVER (ORDER BY oi.price DESC, p.name) AS rn
FROM order_items AS oi
JOIN products AS p ON p.id = oi.product_id
WHERE oi.order_id IN (1, 5, 9)
ORDER BY oi.price DESC, p.name;

SELECT name, row_number() OVER (ORDER BY price) AS rn
FROM products
WHERE row_number() OVER (ORDER BY price) <= 3;

-- ERROR:  window functions are not allowed in WHERE
-- LINE 3: WHERE row_number() OVER (ORDER BY price) <= 3;
--               ^

SELECT name, price, round(price - (SELECT avg(price) FROM products), 2) AS vs_avg
FROM products WHERE category = 'coffee' ORDER BY id;
SELECT name FROM clients
WHERE id IN (SELECT client_id FROM orders WHERE promo_code IS NOT NULL) ORDER BY id;
SELECT p.name FROM products AS p
WHERE NOT EXISTS (SELECT 1 FROM order_items AS oi WHERE oi.product_id = p.id);

--             name            |  price   |  vs_avg  
-- ----------------------------+----------+----------
--  Espresso Blend 1 kg        | 12900.00 |  4533.33
--  Colombia Supremo 250 g     |  4900.00 | -3466.67
--  Ethiopia Yirgacheffe 250 g |  5600.00 | -2766.67
-- (3 rows)
--
--        name       
-- ------------------
--  Aigerim Seitova
--  Daniyar Akhmetov
--  Aliya Bekova
-- (3 rows)
--
--    name   
-- ----------
--  Gift Box
-- (1 row)

WITH RECURSIVE days AS (
    SELECT date '2026-10-01' AS day
    UNION ALL
    SELECT day + 1 FROM days WHERE day < date '2026-10-07'
)
SELECT d.day, count(o.id) AS orders
FROM days AS d
LEFT JOIN orders AS o ON o.ordered_on = d.day
GROUP BY d.day
ORDER BY d.day;

SELECT c.name, last.id AS order_id, last.ordered_on
FROM clients AS c
CROSS JOIN LATERAL (
    SELECT o.id, o.ordered_on
    FROM orders AS o
    WHERE o.client_id = c.id
    ORDER BY o.ordered_on DESC
    LIMIT 1
) AS last
ORDER BY c.id
LIMIT 3;

INSERT INTO clients (name, city, email)
VALUES ('Asel Nurlanova', 'Almaty', 'asel@example.com'),
       ('Bauyrzhan Ospanov', 'Astana', NULL)
RETURNING id, name;

--  id |       name        
-- ----+-------------------
--   9 | Asel Nurlanova
--  10 | Bauyrzhan Ospanov
-- (2 rows)
--
-- INSERT 0 2

INSERT INTO clients (name, city, email)
VALUES ('Madina Omarova', 'Astana', 'madina@example.com')
ON CONFLICT (email) DO UPDATE
SET city = EXCLUDED.city
RETURNING id, name, city;

INSERT INTO clients (name, city, email)
VALUES ('Madina O.', 'Shymkent', 'madina@example.com')
ON CONFLICT (email) DO NOTHING
RETURNING id;

INSERT INTO clients (name, city, email)
VALUES ('Nurlan Abenov', 'Almaty', 'nurlan@example.com')
RETURNING id;

--  id |      name      |  city  
-- ----+----------------+--------
--   3 | Madina Omarova | Astana
-- (1 row)
--
-- INSERT 0 1
--  id 
-- ----
-- (0 rows)
--
-- INSERT 0 0
--  id 
-- ----
--  11
-- (1 row)
--
-- INSERT 0 1

INSERT INTO products (name, category, price)
VALUES ('Gift Box', 'gifts', 16000)
ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price;

-- ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

UPDATE orders AS o
SET status = 'shipped'
FROM clients AS c
WHERE c.id = o.client_id
  AND c.city = 'Almaty'
  AND o.status = 'paid'
RETURNING o.id, c.name, o.status;

--  id |      name       | status  
-- ----+-----------------+---------
--   3 | Aigerim Seitova | shipped
--   9 | Aigerim Seitova | shipped
-- (2 rows)
--
-- UPDATE 2

DELETE FROM order_items AS oi
USING orders AS o
WHERE o.id = oi.order_id
  AND o.status = 'cancelled'
RETURNING oi.order_id, oi.product_id, oi.price;

--  order_id | product_id |  price   
-- ----------+------------+----------
--         4 |          8 | 18500.00
-- (1 row)
--
-- DELETE 1

INSERT INTO clients (name, city, email)
VALUES ('Madina Omarova', 'Astana', 'madina@example.com') AS new
ON DUPLICATE KEY UPDATE city = new.city;

UPDATE orders AS o
JOIN clients AS c ON c.id = o.client_id
SET o.status = 'shipped'
WHERE c.city = 'Almaty' AND o.status = 'paid';

DELETE o FROM orders AS o
JOIN clients AS c ON c.id = o.client_id
WHERE o.status = 'cancelled';

CREATE TABLE reviews (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id bigint NOT NULL REFERENCES products (id) ON DELETE CASCADE,
    client_id  bigint NOT NULL REFERENCES clients (id),
    rating     smallint NOT NULL CHECK (rating BETWEEN 1 AND 5),
    body       text,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (product_id, client_id)
);
ALTER TABLE reviews ADD COLUMN is_published boolean NOT NULL DEFAULT false;
ALTER TABLE reviews ALTER COLUMN body TYPE varchar(2000);
ALTER TABLE reviews RENAME COLUMN body TO comment;
ALTER TABLE reviews ADD CONSTRAINT comment_not_blank CHECK (comment <> '');
ALTER TABLE reviews DROP COLUMN is_published;

CREATE INDEX orders_client_id_idx ON orders (client_id);
CREATE INDEX orders_status_date_idx ON orders (status, ordered_on DESC);
CREATE INDEX orders_new_idx ON orders (ordered_on) WHERE status = 'new';
CREATE UNIQUE INDEX clients_email_lower_key ON clients (lower(email));
CREATE INDEX CONCURRENTLY products_category_idx ON products (category);
DROP INDEX IF EXISTS products_category_idx;
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders' ORDER BY indexname;

BEGIN;
UPDATE products SET price = price * 1.1 WHERE category = 'tea';
SAVEPOINT before_mugs;
UPDATE products SET price = 0 WHERE id = 6;
ROLLBACK TO SAVEPOINT before_mugs;
COMMIT;
SELECT id, name, price FROM products WHERE id IN (4, 5, 6) ORDER BY id;

-- BEGIN
-- UPDATE 2
-- SAVEPOINT
-- ERROR:  new row for relation "products" violates check constraint "products_price_check"
-- DETAIL:  Failing row contains (6, Ceramic Mug 350 ml, tableware, 0.00, null).
-- ROLLBACK
-- COMMIT
--  id |        name        |  price  
-- ----+--------------------+---------
--   4 | Green Sencha 100 g | 3520.00
--   5 | Masala Chai 200 g  | 4180.00
--   6 | Ceramic Mug 350 ml | 2500.00
-- (3 rows)

BEGIN;
INSERT INTO clients (name, email) VALUES ('Test', 'aigerim@example.com');
SELECT count(*) FROM clients;
ROLLBACK;

-- BEGIN
-- ERROR:  duplicate key value violates unique constraint "clients_email_key"
-- DETAIL:  Key (email)=(aigerim@example.com) already exists.
-- ERROR:  current transaction is aborted, commands ignored until end of transaction block
-- ROLLBACK

BEGIN;
CREATE TABLE tmp_promo (code text PRIMARY KEY);
ROLLBACK;
SELECT to_regclass('tmp_promo') AS table_after_rollback;

-- BEGIN
-- CREATE TABLE
-- ROLLBACK
--  table_after_rollback 
-- ----------------------
--  NULL
-- (1 row)

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT id, status FROM orders WHERE id = 8 FOR UPDATE;
UPDATE orders SET status = 'paid' WHERE id = 8;
COMMIT;

-- BEGIN
--  id | status 
-- ----+--------
--   8 | new
-- (1 row)
--
-- UPDATE 1
-- COMMIT

SELECT id,
       ordered_on,
       ordered_on + 7                         AS plus_7_days,
       ordered_on + interval '1 month'        AS plus_month,
       date_trunc('month', ordered_on)::date  AS month_start,
       extract(isodow FROM ordered_on)        AS iso_weekday,
       to_char(ordered_on, 'DD.MM.YYYY')      AS ru_format,
       date '2026-10-12' - ordered_on         AS days_ago
FROM orders
WHERE id IN (1, 10)
ORDER BY id;

SELECT name,
       upper(split_part(name, ' ', 1))  AS first_word,
       length(name)                     AS len,
       left(name, 8) || '...'           AS short,
       replace(name, ' g', ' гр')       AS ru_unit,
       coalesce(weight_g::text, 'n/a')  AS weight,
       nullif(category, 'gifts')        AS cat_or_null
FROM products
WHERE id IN (2, 4, 9)
ORDER BY id;

SELECT category, string_agg(name, ', ' ORDER BY price) AS items
FROM products
GROUP BY category
ORDER BY category;

SELECT name, price,
       CASE
           WHEN price >= 15000 THEN 'premium'
           WHEN price >= 5000  THEN 'mid'
           ELSE 'budget'
       END AS segment,
       greatest(price - 3000, 0) AS after_coupon,
       price::int                AS as_int
FROM products
WHERE id IN (1, 4, 8)
ORDER BY price;