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-- OLD-STYLE comma join = INNER JOIN
 2SELECT c.name, p.name
 3FROM   customer c, sale s, product p
 4WHERE  c.customer_id = s.customer_id
 5  AND  p.product_id  = s.product_id;
 6  
 7-- INNER: matches only
 8SELECT c.name, p.name AS product, s.qty, s.sale_date
 9FROM   customer c
10JOIN   sale s    ON s.customer_id = c.customer_id
11JOIN   product p ON p.product_id  = s.product_id;
12
13-- LEFT: every customer, NULLs where no match
14SELECT c.name, s.product_id, s.qty
15FROM   customer c
16LEFT JOIN sale s ON s.customer_id = c.customer_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 pass; UNION ALL keeps dupes (use when dupes impossible: faster)
 3SELECT 'Online' AS city; -- col count+order must match; names from FIRST branch
 4-- ORDER BY / LIMIT: once, at the very end
 5
 6SELECT customer_id FROM sale
 7INTERSECT -- if unsure it exists: ≡ WHERE customer_id IN (subquery)
 8SELECT customer_id FROM customer WHERE YEAR(joined) = 2024;
 9
10SELECT customer_id FROM customer
11EXCEPT -- ≡ anti-join: NOT EXISTS / LEFT JOIN…IS NULL (NULL-safe, unlike NOT IN)
12SELECT 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 -- AFTER aggregation; alias OK…
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 — aggregate here = ILLEGAL
16GROUP  BY s.customer_id -- every non-agg SELECT col must appear here (ONLY_FULL_GROUP_BY)
17HAVING SUM(s.qty) > 10; -- …or repeat the aggregate expression

 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 — NOT IN + one NULL in subquery → EMPTY result)
13SELECT c.name FROM customer c
14WHERE  NOT EXISTS (SELECT 1 FROM sale s WHERE s.customer_id = c.customer_id);
15       -- stops at first match; SELECT 1: select list ignored
16
17-- CORRELATED scalar: above own category average
18SELECT p.name, p.category, p.price
19FROM   product p
20WHERE  p.price > (SELECT AVG(p2.price) FROM product p2
21                  WHERE p2.category = p.category);
22
23-- DERIVED TABLE: pre-aggregate then filter (alias mandatory)
24SELECT t.customer_id, t.total
25FROM ( SELECT customer_id, SUM(qty) AS total
26       FROM   sale GROUP BY customer_id ) AS t
27WHERE  t.total > 5;
28
29-- WINDOW: rank within category, rows not collapsed
30-- runs AFTER WHERE/GROUP BY/HAVING → to filter on rn, wrap in a derived table (above)
31SELECT name, category, price,
32       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn,  -- unique 1,2,3
33       RANK()       OVER (PARTITION BY category ORDER BY price DESC) AS rnk -- ties 1,1,3 (DENSE_RANK: 1,1,2)
34FROM   product;

 1-- RESULT-SET shape (most common exam ask)
 2DROP PROCEDURE IF EXISTS sales_summary;
 3DELIMITER // -- before ANY CREATE PROC/FUNC/TRIGGER (§1.9–1.12); reset ; after
 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))
 4-- IN read-only (default) · OUT write-back · INOUT both; no RETURN (that's a function)
 5BEGIN
 6    SELECT COALESCE(SUM(s.qty * p.price), 0.00) -- COALESCE: no-sale customer = 0
 7    INTO total -- must be exactly ONE row/value or it errors
 8    FROM sale s, product p -- comma join
 9    WHERE s.product_id = p.product_id
10      AND s.customer_id = cid;
11END //
12DELIMITER ;
13CALL 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; -- ① vars first
 7    DECLARE v_i INT; DECLARE v_done INT DEFAULT 0;
 8
 9    DECLARE cur_proc CURSOR FOR -- ② cursor second
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; -- ③ handlers last (wrong order = compile error)
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 -- {BEFORE|AFTER} {INSERT|UPDATE|DELETE}; guard=BEFORE, maintain-alone=AFTER
 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        -- the reject: for rules a CHECK can't hold (needs subquery)
13    END IF;
14    UPDATE product SET stock = stock - NEW.qty -- maintenance leg (alone: AFTER, no DECLARE/IF)
15    WHERE product_id = NEW.product_id;
16END //
17DELIMITER ;
18-- NEW = incoming (INSERT/UPDATE) · OLD = existing (UPDATE/DELETE)
19-- SET NEW.col = … legal ONLY in BEFORE (SET NEW.joined = OLD.joined freezes a col)

 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'
10
11row = cur.fetchone() # ONE row tuple (v1,v2) → scalar row[0]; no rows → None (None[0]=TypeError!)
12if cur.fetchone(): # existence test — None falsy
13rows = cur.fetchall() # list of tuples [(..),(..)]; no rows → [] NOT None ([][0]=IndexError)
14# cursor(dictionary=True) → rows are dicts: row['col'], [0] breaks
15
16request.method == 'POST' # form submitted; GET renders empty form
17request.form.get('x') # <input name="x">; None if absent (form['x'] raises)