SQL練習題(咖啡廳篇)_byBonnieChou

You must execute the code again to save those values in the database.

/* 會員 */
CREATE TABLE customer (
  cid       TEXT PRIMARY KEY,
  cname     TEXT,
  gender    TEXT,
  age       INTEGER,
  signup_dt TEXT      -- yyyy-mm-dd
);

/* 飲品菜單 */
CREATE TABLE drink (
  did     TEXT PRIMARY KEY,
  dname   TEXT,
  price   REAL,
  hot     INTEGER     -- 1=熱飲, 0=冰飲
);

/* 店員 */
CREATE TABLE barista (
  bid    TEXT PRIMARY KEY,
  bname  TEXT,
  hire_dt TEXT
);

/* 排班 */
CREATE TABLE shift (
  bid     TEXT,
  sdate   TEXT,   -- yyyy-mm-dd
  shift   TEXT,   -- Morning / Afternoon / Evening
  PRIMARY KEY (bid, sdate, shift),
  FOREIGN KEY (bid) REFERENCES barista(bid)
);

/* 訂單主檔 */
CREATE TABLE orders (
  oid        TEXT PRIMARY KEY,
  cid        TEXT,
  bid        TEXT,          -- 當班店員
  order_dt   TEXT,          -- yyyy-mm-dd hh:mm
  FOREIGN KEY (cid) REFERENCES customer(cid),
  FOREIGN KEY (bid) REFERENCES barista(bid)
);

/* 訂單明細 */
CREATE TABLE order_item (
  oid     TEXT,
  did     TEXT,
  qty     INTEGER,
  PRIMARY KEY (oid, did),
  FOREIGN KEY (oid) REFERENCES orders(oid),
  FOREIGN KEY (did) REFERENCES drink(did)
);

/* customer */
INSERT INTO customer VALUES
('c001','Amber','F',25,'2023-02-14'),
('c002','Benson','M',31,'2022-11-05'),
('c003','Cathy','F',28,'2024-01-20'),
('c004','Derek','M',35,'2023-08-09'),
('c005','Eva','F',22,'2024-03-12'),
('c006','Felix','M',40,'2022-06-30'),
('c007','Grace','F',27,'2023-12-01'),
('c008','Henry','M',33,'2021-09-17');

/* drink */
INSERT INTO drink VALUES
('d001','Espresso', 90 ,1),
('d002','Americano',100,1),
('d003','Latte'    ,120,1),
('d004','Mocha'    ,130,1),
('d005','Cold Brew',110,0),
('d006','Iced Latte',125,0),
('d007','Matcha'   ,140,1),
('d008','Lemon Tea',95 ,0);

/* barista */
INSERT INTO barista VALUES
('b001','Ivy'   ,'2020-05-01'),
('b002','Jason' ,'2021-03-15'),
('b003','Kelly' ,'2019-11-23'),
('b004','Leo'   ,'2022-07-07'),
('b005','Mia'   ,'2023-04-18');

/* shift (一週示意) */
INSERT INTO shift VALUES
('b001','2025-01-19','Morning'),('b001','2025-01-20','Afternoon'),
('b002','2025-01-19','Afternoon'),('b002','2025-01-21','Morning'),
('b003','2025-01-20','Morning'),('b003','2025-01-22','Evening'),
('b004','2025-01-21','Evening'),('b004','2025-01-23','Morning'),
('b005','2025-01-22','Afternoon'),('b005','2025-01-23','Evening');

/* orders */
INSERT INTO orders VALUES
('o001','c001','b001','2025-01-19 09:15'),
('o002','c002','b001','2025-01-19 09:45'),
('o003','c003','b002','2025-01-19 15:10'),
('o004','c004','b003','2025-01-20 10:05'),
('o005','c001','b003','2025-01-20 10:30'),
('o006','c005','b002','2025-01-21 08:55'),
('o007','c006','b004','2025-01-21 18:20'),
('o008','c007','b004','2025-01-23 09:40'),
('o009','c008','b005','2025-01-23 19:05'),
('o010','c002','b005','2025-01-23 19:25');

/* order_item */
INSERT INTO order_item VALUES
('o001','d003',1),('o001','d001',1),
('o002','d002',2),
('o003','d006',1),('o003','d005',1),
('o004','d004',1),
('o005','d001',2),
('o006','d002',1),('o006','d008',1),
('o007','d005',2),('o007','d007',1),
('o008','d003',1),('o008','d008',1),
('o009','d007',1),
('o010','d006',2),('o010','d002',1);

(no result to output)