SQL練習題(叫車服務篇)_byBonnieChou

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

/* 司機 */
CREATE TABLE driver (
  did     TEXT PRIMARY KEY,
  dname   TEXT,
  rating  REAL,         -- 平均星等
  city    TEXT
);

/* 車輛 (一位司機一台車) */
CREATE TABLE vehicle (
  vid      TEXT PRIMARY KEY,
  did      TEXT UNIQUE,
  make     TEXT,
  model    TEXT,
  year     INTEGER,
  capacity INTEGER,
  FOREIGN KEY (did) REFERENCES driver(did)
);

/* 乘客 */
CREATE TABLE rider (
  rid        TEXT PRIMARY KEY,
  rname      TEXT,
  signup_dt  TEXT,      -- yyyy-mm-dd
  city       TEXT
);

/* 行程 */
CREATE TABLE trip (
  tid          TEXT PRIMARY KEY,
  rid          TEXT,
  did          TEXT,
  pickup_time  TEXT,    -- yyyy-mm-dd hh:mm
  dropoff_time TEXT,    -- yyyy-mm-dd hh:mm
  distance_km  REAL,
  fare         REAL,
  promo_code   TEXT,
  FOREIGN KEY (rid) REFERENCES rider(rid),
  FOREIGN KEY (did) REFERENCES driver(did)
);

/* 付款 */
CREATE TABLE payment (
  pid      TEXT PRIMARY KEY,
  tid      TEXT,
  paid_at  TEXT,        -- yyyy-mm-dd hh:mm
  method   TEXT,        -- Card / Cash
  status   TEXT,        -- Paid / Failed
  amount   REAL,
  FOREIGN KEY (tid) REFERENCES trip(tid)
);

/* 評價 (乘客對司機打分) */
CREATE TABLE feedback (
  fid      TEXT PRIMARY KEY,
  tid      TEXT UNIQUE,
  stars    INTEGER,     -- 1~5
  comment  TEXT,
  FOREIGN KEY (tid) REFERENCES trip(tid)
);

/* 優惠碼 */
CREATE TABLE promo (
  code        TEXT PRIMARY KEY,
  description TEXT,
  discount_pct INTEGER,
  start_dt    TEXT,
  end_dt      TEXT
);

/* driver */
INSERT INTO driver VALUES
('d001','Alice' ,4.90,'Taipei'),
('d002','Brian' ,4.70,'Taipei'),
('d003','Cathy' ,4.30,'Taichung'),
('d004','David' ,4.80,'Kaohsiung'),
('d005','Ethan' ,4.10,'Taipei'),
('d006','Flora' ,4.95,'Taichung');

/* vehicle */
INSERT INTO vehicle VALUES
('v001','d001','Toyota' ,'Altis' ,2021,4),
('v002','d002','Honda'  ,'Civic' ,2019,4),
('v003','d003','Nissan' ,'Leaf'  ,2020,4),
('v004','d004','Tesla'  ,'Model 3',2022,4),
('v005','d005','Hyundai','Tucson',2016,5),
('v006','d006','Mazda'  ,'CX-5'  ,2023,4);

/* rider */
INSERT INTO rider VALUES
('r001','Gina' , '2024-01-12','Taipei'),
('r002','Henry','2023-11-03','Taipei'),
('r003','Irene','2025-02-01','Taichung'),
('r004','Jack' ,'2022-08-25','Kaohsiung'),
('r005','Kelly','2024-03-05','Taipei'),
('r006','Leo'  ,'2023-06-14','Taichung'),
('r007','Mia'  ,'2024-01-09','Taipei'),
('r008','Nick' ,'2023-05-01','Kaohsiung');

/* promo */
INSERT INTO promo VALUES
('SAVE10','10% off any ride',10,'2025-01-15','2025-02-15'),
('MORNING5','5% off before 10AM',5,'2025-01-01','2025-12-31');

/* trip */
INSERT INTO trip VALUES
('t101','r001','d001','2025-02-18 09:10','2025-02-18 09:40',12.5,300,'MORNING5'),
('t102','r002','d001','2025-02-18 10:20','2025-02-18 10:55',15.0,360,NULL),
('t103','r003','d002','2025-02-18 15:05','2025-02-18 15:35',14.2,340,NULL),
('t104','r001','d002','2025-02-19 08:05','2025-02-19 08:25', 8.8,220,'MORNING5'),
('t105','r004','d003','2025-02-19 09:50','2025-02-19 10:30',22.0,480,'SAVE10'),
('t106','r005','d002','2025-02-19 16:15','2025-02-19 16:45',11.7,280,NULL),
('t107','r006','d004','2025-02-20 18:20','2025-02-20 18:55',18.3,420,NULL),
('t108','r007','d004','2025-02-20 09:40','2025-02-20 10:05', 9.1,230,'MORNING5'),
('t109','r008','d005','2025-02-20 19:05','2025-02-20 19:45',20.4,500,NULL),
('t110','r002','d005','2025-02-21 14:25','2025-02-21 14:55',12.0,300,'SAVE10'),
('t111','r003','d006','2025-02-21 11:10','2025-02-21 11:40',13.3,310,NULL),
('t112','r001','d006','2025-02-22 08:30','2025-02-22 08:55',10.5,250,'MORNING5'),
('t113','r005','d001','2025-02-22 21:15','2025-02-22 21:45',12.8,320,NULL),
('t114','r006','d003','2025-02-22 07:50','2025-02-22 08:20',14.0,330,'MORNING5'),
('t115','r007','d002','2025-02-23 09:05','2025-02-23 09:35',13.6,330,'MORNING5');

/* payment */
INSERT INTO payment VALUES
('p901','t101','2025-02-18 09:41','Card','Paid',285),
('p902','t102','2025-02-18 10:56','Card','Paid',360),
('p903','t103','2025-02-18 15:36','Cash','Paid',340),
('p904','t104','2025-02-19 08:26','Card','Paid',209),
('p905','t105','2025-02-19 10:31','Card','Paid',432),
('p906','t106','2025-02-19 16:46','Cash','Paid',280),
('p907','t107','2025-02-20 18:56','Card','Paid',420),
('p908','t108','2025-02-20 10:06','Card','Paid',219),
('p909','t109','2025-02-20 19:46','Cash','Failed',0),
('p910','t110','2025-02-21 14:56','Card','Paid',270),
('p911','t111','2025-02-21 11:41','Card','Paid',310),
('p912','t112','2025-02-22 08:56','Card','Paid',238),
('p913','t113','2025-02-22 21:46','Cash','Paid',320),
('p914','t114','2025-02-22 08:21','Card','Paid',314),
('p915','t115','2025-02-23 09:36','Card','Paid',314);

/* feedback */
INSERT INTO feedback VALUES
('f001','t101',5,'Smooth ride'),
('f002','t103',4,'Good driver'),
('f003','t105',3,'Car not very clean'),
('f004','t107',5,'Excellent'),
('f005','t110',4,'On time'),
('f006','t111',5,'Great'),
('f007','t112',4,'Nice chat'),
('f008','t114',5,'Early pickup');

(no result to output)