Herhaling: SELECT

• SELECT ... FROM ... WHERE ...
• Aliases met AS
• Sorteren ORDER BY; beperken LIMIT/OFFSET

SELECT id, name AS gebruiker, created_at
FROM users
WHERE created_at >= date('now','-30 days')
ORDER BY created_at DESC
LIMIT 20;
      

JOINs (INNER)

SELECT o.id AS order_id, u.name AS gebruiker, o.placed_at
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY o.placed_at DESC
LIMIT 10;
      

Aggregaties & GROUP BY

SELECT date(o.placed_at) AS dag,
       ROUND(SUM(ol.quantity * ol.unit_price), 2) AS omzet
FROM order_lines ol
JOIN orders o ON o.id = ol.order_id
WHERE o.placed_at >= date('now','-7 days')
GROUP BY date(o.placed_at)
ORDER BY dag;
      

Domein: Webshop

• Gebruikers
• Producten
• Orders & order_lines
• Winkels & stock

ERD (schets)

Cardinaliteiten benoemen en sleutelkolommen voorzien.

Attribuutkeuzes

SQLite setup

PRAGMA foreign_keys = ON;
      

Schema: kern-tabellen

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  sku TEXT NOT NULL UNIQUE,
  unit_price NUMERIC NOT NULL CHECK(unit_price >= 0),
  vat_rate NUMERIC NOT NULL DEFAULT 21 CHECK(vat_rate IN (0,6,9,21)),
  is_active INTEGER NOT NULL DEFAULT 1 CHECK(is_active IN (0,1))
);
      

Schema: orders & lijnen

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  placed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status TEXT NOT NULL DEFAULT 'new' CHECK(status IN ('new','paid','shipped','cancelled','refunded')),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_lines (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL CHECK(quantity > 0),
  unit_price NUMERIC NOT NULL CHECK(unit_price >= 0),
  vat_rate NUMERIC NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id)
);
      

Schema: winkels & stock

CREATE TABLE shops (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE stock (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  shop_id INTEGER NOT NULL,
  qty INTEGER NOT NULL DEFAULT 0 CHECK(qty >= 0),
  UNIQUE(product_id, shop_id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (shop_id) REFERENCES shops(id)
);
      

Seed: startdata (kort)

INSERT INTO users (name, email) VALUES
 ('Mila Janssens','mila@example.com'),
 ('Youssef El Amrani','youssef@example.com');

INSERT INTO products (title, slug, sku, unit_price, vat_rate)
VALUES
 ('Acme Ultrabook 13"', 'acme-ultrabook-13', 'AC-UB13', 999.00, 21),
 ('Nordic Pro Mouse', 'nordic-pro-mouse', 'NO-MOU-PRO', 49.90, 21),
 ('ZenWare USB-C Kabel 2m', 'zen-usbc-2m', 'ZW-USBC-2', 12.50, 21);

INSERT INTO shops (name) VALUES ('Centrum'),('West');
INSERT INTO stock (product_id, shop_id, qty) VALUES (1,1,5),(2,1,20),(3,1,100);
      

Verkoop: voorbeeldorder

BEGIN;
INSERT INTO orders (user_id) VALUES (1);

INSERT INTO order_lines (order_id, product_id, quantity, unit_price, vat_rate)
VALUES (last_insert_rowid(), 1, 1, 999.00, 21);

INSERT INTO order_lines (order_id, product_id, quantity, unit_price, vat_rate)
VALUES (last_insert_rowid(), 3, 2, 12.50, 21);
COMMIT;
      

Rapport: omzet per product

SELECT p.title,
       SUM(ol.quantity) AS stuks,
       ROUND(SUM(ol.quantity * ol.unit_price),2) AS omzet
FROM order_lines ol
JOIN products p ON p.id = ol.product_id
GROUP BY p.id
ORDER BY omzet DESC;
      

Rapport: topgebruikers laatste 30 dagen

SELECT u.name,
       ROUND(SUM(ol.quantity * ol.unit_price),2) AS omzet
FROM orders o
JOIN order_lines ol ON ol.order_id = o.id
JOIN users u ON u.id = o.user_id
WHERE o.placed_at >= date('now','-30 days')
GROUP BY u.id
ORDER BY omzet DESC
LIMIT 10;
      

Low stock alert

SELECT p.sku, p.title, s.qty, sh.name AS shop
FROM products p
JOIN stock s ON s.product_id = p.id
JOIN shops sh ON sh.id = s.shop_id
WHERE s.qty <= 5
ORDER BY s.qty ASC;
      

Transacties & integriteit

• BEGIN / COMMIT / ROLLBACK
• Foreign keys + ON DELETE CASCADE
• CHECK-constrains voor status/hoeveelheden

Oefeningen — overzicht

1. O1 (15') SELECT warm-up
2. O2 (25') Constraints & domeinregels
3. O3 (40') Schema + seed (6 tabellen)
4. O4 (25') Rapportqueries

Werk in duo's. Lever SQL-fragmenten + korte motivatie.

O1 — SELECT warm-up

• Toon 5 nieuwste producten (titel, prijs)
• Orders met totaalbedrag (order_id, totaal)
• Stock per winkel (product, winkel, qty)

SELECT title, unit_price FROM products ORDER BY id DESC LIMIT 5;

SELECT o.id AS order_id,
       ROUND(SUM(ol.quantity * ol.unit_price),2) AS totaal
FROM orders o
JOIN order_lines ol ON ol.order_id = o.id
GROUP BY o.id;

SELECT p.title, sh.name AS shop, s.qty
FROM stock s
JOIN products p ON p.id = s.product_id
JOIN shops sh ON sh.id = s.shop_id;
        

O2 — Constraints & domeinregels

Schrijf 5 regels die je databank moet afdwingen (bv. geen negatieve prijzen/qty, status alleen uit een set, unieke SKU, ...). Voeg waar mogelijk CHECK, NOT NULL of UNIQUE toe.

O3 — Schema & seed

Maak de 6 tabellen aan, zet PRAGMA foreign_keys=ON, en vul met eigen zinvolle testdata (min. 5 producten, 2 winkels, 3 orders met lijnen).

O4 — Rapportqueries

• Omzet per product
• Topgebruikers laatste 30 dagen
• Low stock <= 5 per winkel