題目一：貨車調度管理系統
ER 關係建模
- 貨車：車號(PK)、類型、載重量 (CHECK > 3)。
- 司機：司機號(PK)、姓名(NOT NULL)、地址、薪水 (CHECK >= 2000)。
- 技師：技師號(PK)、姓名、地址、薪水 (CHECK >= 1500)。
- 運輸記錄：司機號(FK)、車號(FK)、貨物名、目的地、出發時間、返回時間、駕駛時間。
- 維修記錄：車號(FK)、技師號(FK)、維修時間、取車時間、費用。
- 司機-車型資質：多對多關聯表 (司機號, 車型)。
核心 SQL 實現片段
-- 1. 建表 (節選)
CREATE TABLE Truck (TruckNo CHAR(10) PRIMARY KEY, Type VARCHAR(20), Capacity DECIMAL(6,2) CHECK (Capacity > 3));
CREATE TABLE Driver (DriverNo CHAR(10) PRIMARY KEY, Name VARCHAR(20) NOT NULL, Address VARCHAR(50), Salary MONEY CHECK (Salary >= 2000));
CREATE TABLE Transport (DriverNo CHAR(10) REFERENCES Driver, TruckNo CHAR(10) REFERENCES Truck, Goods VARCHAR(30), Dest VARCHAR(30), DepTime DATETIME, RetTime DATETIME, DriveHours INT);
-- 2. 開過 Joe 開過的車的司機
SELECT DISTINCT d.DriverNo, d.Name FROM Driver d
JOIN Transport t1 ON d.DriverNo = t1.DriverNo
WHERE t1.TruckNo IN (SELECT TruckNo FROM Transport WHERE DriverNo = (SELECT DriverNo FROM Driver WHERE Name = 'Joe'));
-- 3. 平均維修費
SELECT AVG(Cost) FROM Repair;
-- 4. 駕駛時間薪水調整
UPDATE Driver SET Salary = Salary * 1.05 WHERE DriverNo IN (SELECT DriverNo FROM Transport GROUP BY DriverNo HAVING SUM(DriveHours) > 50);
UPDATE Driver SET Salary = Salary * 1.03 WHERE DriverNo NOT IN (SELECT DriverNo FROM Transport GROUP BY DriverNo HAVING SUM(DriveHours) > 50);
-- 5. 刪除高維修費記錄
DELETE FROM Repair WHERE Cost > 100000;
題目二：汽車保險管理系統
關鍵差異
- 人-車：一對多 (一個人多輛車)。
- 車-事故：一對多 (一輛車多次事故)。
- 事故記錄駕駛員 (外鍵關聯人)、日期、損失量。
查詢示例
-- 3. 1998年出過車禍的人數
SELECT COUNT(DISTINCT p.SSN) FROM Person p
JOIN Car c ON p.SSN = c.OwnerSSN
JOIN Accident a ON c.License = a.CarLicense
WHERE YEAR(a.Date) = 1998;
-- 4. 沒出過事故的人
SELECT Name FROM Person WHERE SSN NOT IN (SELECT DISTINCT DriverSSN FROM Accident);
-- 6. 刪除 John Smith 相關事故
DELETE FROM Accident WHERE DriverSSN = (SELECT SSN FROM Person WHERE Name = 'John Smith');
