TCX2003 | Database Systems Finals Helpsheet

 1CREATE TABLE IF NOT EXISTS customer (          -- IF NOT EXISTS: idempotent re-run
 2  customer_id INT PRIMARY KEY,
 3  name        VARCHAR(60) NOT NULL,            -- "required"
 4  email       VARCHAR(120) UNIQUE,             -- no dup, NULL allowed
 5  city        VARCHAR(40),
 6  joined      DATE,                            -- DATETIME/TIMESTAMP if time needed
 7  referred_by INT,                             -- self-FK column (NULL = direct signup)
 8  CHECK (email REGEXP '^[^@]+@[^@]+\\.[^@]+$'),
 9  -- FIXED-FORMAT ID: CHECK (id REGEXP '^D[0-9]{4}$') = D0001 · '^A[0-9]{7}[A-Z]$' = matric
10  --   {n} = exactly n · ^...$ anchor the WHOLE value (omit → any substring matches!) · REGEXP is case-INSENSITIVE
11  FOREIGN KEY (referred_by) REFERENCES customer(customer_id)  -- self-FK: MUST be nullable
12    ON DELETE SET NULL
13);
14
15CREATE TABLE IF NOT EXISTS product (
16  product_id INT AUTO_INCREMENT PRIMARY KEY,   -- AUTO_INCREMENT must be leftmost key column
17  sku        CHAR(8) UNIQUE,                   -- fixed width = CHAR; variable = VARCHAR
18  name       VARCHAR(80) NOT NULL,
19  category   ENUM('book','tech','food'),       -- invalid value rejected
20  price      DECIMAL(8,2) NOT NULL DEFAULT 0.00,
21  stock      INT NOT NULL DEFAULT 0,
22  added      DATE,
23  CHECK (price >= 0 AND stock >= 0)            -- multi-column CHECK
24);
25
26CREATE TABLE IF NOT EXISTS sale (
27  customer_id INT,
28  product_id  INT,
29  sale_date   DATE,
30  qty         INT NOT NULL,
31  PRIMARY KEY (customer_id, product_id, sale_date),            -- composite PK (table-level)
32  FOREIGN KEY (customer_id) REFERENCES customer(customer_id)   -- FK must reference a PK/UNIQUE column
33    ON UPDATE CASCADE ON DELETE CASCADE,
34  FOREIGN KEY (product_id) REFERENCES product(product_id)
35    ON UPDATE CASCADE ON DELETE RESTRICT,
36  CHECK (qty > 0)
37);

1CREATE TABLE IF NOT EXISTS bin (
2  warehouse VARCHAR(10), aisle INT, shelf INT, label VARCHAR(40),
3  PRIMARY KEY (warehouse, aisle, shelf)
4);
5CREATE TABLE IF NOT EXISTS slot (
6  warehouse VARCHAR(10), aisle INT, shelf INT, slot_no INT,
7  PRIMARY KEY (warehouse, aisle, shelf, slot_no),
8  FOREIGN KEY (warehouse, aisle, shelf) REFERENCES bin(warehouse, aisle, shelf)
9);

 1INSERT INTO customer (customer_id, name, email, city, joined)
 2VALUES (1, 'Ada', 'ada@mail.com', 'Singapore', '2026-01-10');
 3
 4INSERT INTO product (sku, name, category, price, stock, added) VALUES  -- multi-row: atomic, id omitted -> AUTO_INCREMENT
 5  ('BK000001', 'SQL Primer', 'book', 19.90, 50, '2026-02-01'),
 6  ('TC000002', 'USB-C Hub',  'tech', 45.00, 12, '2026-02-03');
 7
 8UPDATE product
 9SET price = price * 1.10, stock = stock - 5
10WHERE category = 'tech';                       -- no WHERE = EVERY row changed!
11
12DELETE FROM sale
13WHERE sale_date < '2026-01-01';                -- no WHERE = whole table gone!

 1SELECT product_id, name, price, stock
 2FROM   product
 3WHERE  category = 'tech'
 4  AND  price < 50.00
 5  AND  stock > 0
 6ORDER  BY added DESC
 7LIMIT  10;
 8
 9SELECT name, city, joined
10FROM   customer
11WHERE  city IN ('Singapore', 'Penang')                    -- set membership
12  AND  joined BETWEEN '2024-01-01' AND '2024-12-31'       -- inclusive both ends
13  AND  name LIKE 'A%'                                     -- % = any run, _ = one char
14  AND  email IS NOT NULL                                  -- never = NULL
15ORDER  BY joined;
16
17SELECT DISTINCT city FROM customer ORDER BY city;         -- DISTINCT = whole selected row

 1-- INNER: matches only
 2SELECT c.name, p.name AS product, s.qty, s.sale_date
 3FROM   customer c
 4JOIN   sale s    ON s.customer_id = c.customer_id
 5JOIN   product p ON p.product_id  = s.product_id;
 6
 7-- LEFT: every customer, NULLs where no match
 8SELECT c.name, s.product_id, s.qty
 9FROM   customer c
10LEFT JOIN sale s ON s.customer_id = c.customer_id;
11
12-- OLD-STYLE comma join (JIANG KAN idiom) = INNER JOIN
13SELECT c.name, p.name
14FROM   customer c, sale s, product p
15WHERE  c.customer_id = s.customer_id
16  AND  p.product_id  = s.product_id;
17
18-- SELF-join, shared attribute: same city as 'Alice'
19SELECT a.name, b.name AS same_city
20FROM   customer a
21JOIN   customer b ON a.city = b.city
22WHERE  a.name = 'Alice' AND b.name <> a.name;
23
24-- SELF-join via self-FK (hierarchy): who referred them (LEFT keeps roots)
25SELECT c.name AS customer, r.name AS referred_by
26FROM   customer c
27LEFT JOIN customer r ON r.customer_id = c.referred_by;
28
29-- ANTI-join: products NEVER sold
30SELECT p.product_id, p.name
31FROM   product p
32LEFT JOIN sale s ON s.product_id = p.product_id
33WHERE  s.product_id IS NULL;

 1SELECT city FROM customer
 2UNION                                          -- dedup; UNION ALL keeps duplicates
 3SELECT 'Online' AS city;                       -- column count + order must match
 4
 5SELECT customer_id FROM sale
 6INTERSECT                                      -- MySQL 8.0.31+
 7SELECT customer_id FROM customer WHERE YEAR(joined) = 2024;
 8
 9SELECT customer_id FROM customer
10EXCEPT                                         -- MySQL 8.0.31+
11SELECT customer_id FROM sale;

 1SELECT p.category,
 2       COUNT(*)                AS n_products,
 3       COALESCE(SUM(s.qty), 0) AS units_sold,  -- NULL -> 0 when no sales
 4       ROUND(AVG(p.price), 2)  AS avg_price,
 5       MIN(p.price)            AS cheapest,
 6       MAX(p.price)            AS dearest
 7FROM   product p
 8LEFT JOIN sale s ON s.product_id = p.product_id
 9GROUP  BY p.category
10HAVING units_sold > 0                          -- filter AFTER aggregation
11ORDER  BY units_sold DESC;
12
13SELECT s.customer_id, SUM(s.qty) AS total_qty
14FROM   sale s
15WHERE  s.sale_date >= '2025-01-01'             -- row filter, BEFORE grouping
16GROUP  BY s.customer_id
17HAVING SUM(s.qty) > 10;                        -- group filter, AFTER grouping

 1-- SCALAR: above overall average (swap AVG->MAX, > to = : "find the dearest")
 2SELECT name, price FROM product
 3WHERE  price > (SELECT AVG(price) FROM product);
 4
 5-- IN: bought any 'tech' product
 6SELECT name FROM customer
 7WHERE  customer_id IN (
 8    SELECT s.customer_id
 9    FROM   sale s JOIN product p ON p.product_id = s.product_id
10    WHERE  p.category = 'tech');
11
12-- EXISTS / NOT EXISTS (correlated; NULL-safe anti-pattern)
13SELECT c.name FROM customer c
14WHERE  NOT EXISTS (SELECT 1 FROM sale s WHERE s.customer_id = c.customer_id);
15
16-- CORRELATED scalar: above own category average
17SELECT p.name, p.category, p.price
18FROM   product p
19WHERE  p.price > (SELECT AVG(p2.price) FROM product p2
20                  WHERE p2.category = p.category);
21
22-- DERIVED TABLE: pre-aggregate then filter (alias mandatory)
23SELECT t.customer_id, t.total
24FROM ( SELECT customer_id, SUM(qty) AS total
25       FROM   sale GROUP BY customer_id ) AS t
26WHERE  t.total > 5;
27
28-- WINDOW: rank within category, rows not collapsed
29SELECT name, category, price,
30       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn,
31       RANK()       OVER (PARTITION BY category ORDER BY price DESC) AS rnk
32FROM   product;

 1-- RESULT-SET shape (most common exam ask)
 2DROP PROCEDURE IF EXISTS sales_summary;
 3DELIMITER //
 4CREATE PROCEDURE sales_summary()                        -- NO params: returns ROWS
 5BEGIN
 6    SELECT c.customer_id, c.name, COALESCE(SUM(s.qty),0) AS total_qty
 7    FROM customer c LEFT JOIN sale s ON s.customer_id = c.customer_id
 8    GROUP BY c.customer_id, c.name ORDER BY total_qty DESC;  -- body = §1.7 pattern
 9END //
10DELIMITER ;
11CALL sales_summary();

 1-- SCALAR-OUT shape
 2DELIMITER //
 3CREATE PROCEDURE total_spent(IN cid INT, OUT total DECIMAL(10,2))
 4BEGIN
 5    SELECT COALESCE(SUM(s.qty * p.price), 0.00)         -- COALESCE: no-sale customer = 0
 6    INTO total
 7    FROM sale s, product p                              -- comma join (Jiang idiom)
 8    WHERE s.product_id = p.product_id
 9      AND s.customer_id = cid;
10END //
11DELIMITER ;
12CALL total_spent(1, @t);  SELECT @t;

 1DROP PROCEDURE IF EXISTS publish_plan;
 2DELIMITER $$
 3CREATE PROCEDURE publish_plan(IN p_job_id INT, IN p_quantity INT,
 4                              IN p_start_sn INT, IN p_process_csv VARCHAR(64))
 5BEGIN
 6    DECLARE v_step_id INT; DECLARE v_proc_id INT;
 7    DECLARE v_i INT; DECLARE v_done INT DEFAULT 0;
 8
 9    DECLARE cur_proc CURSOR FOR                         -- AFTER vars, BEFORE handlers
10        SELECT id FROM process
11        WHERE FIND_IN_SET(code, p_process_csv) > 0;
12
13    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
14
15    DECLARE EXIT HANDLER FOR SQLEXCEPTION               -- any error: undo all, re-raise
16    BEGIN
17        ROLLBACK;
18        RESIGNAL;
19    END;
20
21    START TRANSACTION;
22    OPEN cur_proc;
23    proc_loop: LOOP
24        FETCH cur_proc INTO v_proc_id;
25        IF v_done = 1 THEN LEAVE proc_loop; END IF;
26
27        INSERT INTO job_step (job_id, process_id) VALUES (p_job_id, v_proc_id);
28        SET v_step_id = LAST_INSERT_ID();
29
30        SET v_i = 0;
31        WHILE v_i < p_quantity DO
32            INSERT INTO pcb_task (job_step_id, serial_nb)
33            VALUES (v_step_id, p_start_sn + v_i);
34            SET v_i = v_i + 1;
35        END WHILE;
36    END LOOP;
37    CLOSE cur_proc;
38    COMMIT;
39END$$
40DELIMITER ;

 1DELIMITER //
 2CREATE FUNCTION discounted_price(p DECIMAL(8,2), pct INT)
 3RETURNS DECIMAL(8,2)                                    -- RETURNS = clause (type)
 4DETERMINISTIC                                           -- required under default binlog settings
 5BEGIN
 6    RETURN ROUND(p * (1 - pct / 100), 2);               -- RETURN = statement (value)
 7END //
 8DELIMITER ;
 9
10SELECT name, discounted_price(price, 10) AS sale_price FROM product;  -- inline anywhere an expression fits
11SELECT name FROM product WHERE discounted_price(price, 15) > 20.00;

 1-- ONE comprehensive trigger: guard (SIGNAL) + maintain (UPDATE side-effect)
 2DROP TRIGGER IF EXISTS trg_sale_stock;                  -- no CREATE OR REPLACE; drop first
 3DELIMITER //
 4CREATE TRIGGER trg_sale_stock
 5BEFORE INSERT ON sale
 6FOR EACH ROW
 7BEGIN
 8    DECLARE avail INT;
 9    SELECT stock INTO avail FROM product WHERE product_id = NEW.product_id;
10    IF NEW.qty > avail THEN
11        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
12    END IF;
13    UPDATE product SET stock = stock - NEW.qty          -- maintenance leg
14    WHERE product_id = NEW.product_id;
15END //
16DELIMITER ;

1# result-set proc: rows come via stored_results(), NOT fetchall() on the cursor
2cur = conn.cursor(dictionary=True)
3cur.callproc("sales_summary")
4for r in cur.stored_results():
5    rows = r.fetchall()
6
7# scalar-OUT proc: OUT slots filled in the returned tuple, read BY INDEX
8result = cur.callproc("total_spent", (1, 0))   # placeholder 0 for the OUT position
9total = result[1]                              # 2nd param = 'total'