SQL 練習/星球快車

關係模式

PK 和 FK 分別代表主鍵和外部索引鍵。

練習

前幾個練習相互依賴，並展示了子查詢的用法。

誰收到了 1.5 公斤的包裹？

點選檢視解決方案

SELECT Client.Name
FROM Client JOIN Package   
  ON Client.AccountNumber = Package.Recipient 
WHERE Package.weight = 1.5;
--- The result is "Al Gore's Head".

他傳送的所有包裹的總重量是多少？

點選檢視解決方案

SELECT SUM(p.weight) 
FROM Client AS c 
  JOIN Package as P 
  ON c.AccountNumber = p.Sender
WHERE c.Name = "Al Gore's Head";

---  Or the entire the result from the first exercise could 
--- be embedded explicitly as a subquery, like the following,
--- which also returns the number of the packages.

SELECT SUM(p.weight), COUNT(1)
FROM Client AS c 
  JOIN Package as P 
  ON c.AccountNumber = p.Sender
WHERE c.AccountNumber = (
  SELECT Client.AccountNumber
  FROM Client JOIN Package 
    ON Client.AccountNumber = Package.Recipient 
  WHERE Package.weight = 1.5
);

--- The answer is 17.0kg in two packages.

哪些飛行員運送了這些包裹？

點選檢視解決方案

SELECT Employee.Name
FROM Employee
  JOIN Shipment ON Shipment.Manager = Employee.EmployeeID
  JOIN Package ON Package.Shipment = Shipment.ShipmentID
WHERE Shipment.ShipmentID IN (
  SELECT p.Shipment 
  FROM Client AS c 
    JOIN Package as P 
    ON c.AccountNumber = p.Sender
  WHERE c.AccountNumber = (
    SELECT Client.AccountNumber
    FROM Client JOIN Package 
      ON Client.AccountNumber = Package.Recipient 
    WHERE Package.weight = 1.5
  )
)
GROUP BY (Employee.Name);

-- The answer is that the two shipments were managed by Phillip J. Fry and Turanga Leela .
--
-- Without some sort of "GROUP BY" clause, the correct pilots are returned multiple times,
-- since the embedded JOIN over clients and packages returns one result
-- for each package, not each shipment.

表建立程式碼

 CREATE TABLE Employee (
   EmployeeID INTEGER PRIMARY KEY NOT NULL,
   Name TEXT NOT NULL,
   Position TEXT NOT NULL,
   Salary REAL NOT NULL,
   Remarks TEXT
 );
 
 CREATE TABLE Planet (
   PlanetID INTEGER PRIMARY KEY NOT NULL,
   Name TEXT NOT NULL,
   Coordinates REAL NOT NULL
 );
 
 CREATE TABLE Shipment (
   ShipmentID INTEGER PRIMARY KEY NOT NULL,
   Date DATE, 
   Manager INTEGER NOT NULL
     CONSTRAINT fk_Employee_EmployeeID REFERENCES Employee(EmployeeID) ON DELETE CASCADE, DELETE CASCADE
   Planet INTEGER NOT NULL
     CONSTRAINT fk_Planet_PlanetID REFERENCES Planet(PlanetID) ON DELETE CASCADE CASCADE
 );

CREATE TABLE Has_Clearance (
   Employee INTEGER NOT NULL
     CONSTRAINT fk_HasClearance_EmployeeID REFERENCES Employee(EmployeeID), 
   Planet INTEGER NOT NULL
     CONSTRAINT fk_HasClearance_PlanetID REFERENCES Planet(PlanetID), 
   Level INTEGER NOT NULL,
   PRIMARY KEY(Employee, Planet)
 );

 
 CREATE TABLE Client (
   AccountNumber INTEGER PRIMARY KEY NOT NULL,
   Name TEXT NOT NULL
 );
 
 CREATE TABLE Package (
   Shipment INTEGER NOT NULL
     CONSTRAINT fk_Shipment_ShipmentID REFERENCES Shipment(ShipmentID),
   PackageNumber INTEGER NOT NULL,
   Contents TEXT NOT NULL,
   Weight REAL NOT NULL,
   Sender INTEGER NOT NULL
     CONSTRAINT fk_Sender_AccountNumber REFERENCES Client(AccountNumber), 
   Recipient INTEGER NOT NULL
     CONSTRAINT fk_Recipient_AccountNumber REFERENCES Client(AccountNumber), 
   PRIMARY KEY(Shipment, PackageNumber)
 );

請注意這裡提供的語法是針對 SQLite 系統的。它經過作者在 sqlite3 上的測試。

另外請注意，主鍵欄位上的 NOT NULL 約束在語義上是多餘的，但在 SQLite 中是語法上的必要條件。

點選檢視 MySQL 語法。

 CREATE TABLE Employee (
   EmployeeID INTEGER PRIMARY KEY,
   Name VARCHAR(255) NOT NULL,
   Position VARCHAR(255) NOT NULL,
   Salary REAL NOT NULL,
   Remarks VARCHAR(255)
 ) ENGINE = InnoDB; 
 
 CREATE TABLE Planet (
   PlanetID INTEGER PRIMARY KEY,
   Name VARCHAR(255) NOT NULL,
   Coordinates REAL NOT NULL
 ) ENGINE = InnoDB; 
 
 CREATE TABLE Shipment (
   ShipmentID INTEGER PRIMARY KEY,
   Date DATE,
   Manager INTEGER NOT NULL,
   Planet INTEGER NOT NULL,
   FOREIGN KEY (Manager) REFERENCES Employee(EmployeeID),
   FOREIGN KEY (Planet) REFERENCES Planet(PlanetID)
 ) ENGINE = InnoDB;
 
 CREATE TABLE Has_Clearance (
   Employee INTEGER NOT NULL,
   Planet INTEGER NOT NULL,
   Level INTEGER NOT NULL,
   PRIMARY KEY(Employee, Planet),
   FOREIGN KEY (Employee) REFERENCES Employee(EmployeeID),
   FOREIGN KEY (Planet) REFERENCES Planet(PlanetID)
 ) ENGINE = InnoDB; 
 
 CREATE TABLE Client (
   AccountNumber INTEGER PRIMARY KEY,
   Name VARCHAR(255) NOT NULL
 ) ENGINE = InnoDB;
   
 CREATE TABLE Package (
   Shipment INTEGER NOT NULL,
   PackageNumber INTEGER NOT NULL,
   Contents VARCHAR(255) NOT NULL,
   Weight REAL NOT NULL,
   Sender INTEGER NOT NULL,
   Recipient INTEGER NOT NULL,
   PRIMARY KEY(Shipment, PackageNumber),
   FOREIGN KEY (Shipment) REFERENCES Shipment(ShipmentID),
   FOREIGN KEY (Sender) REFERENCES Client(AccountNumber),
   FOREIGN KEY (Recipient) REFERENCES Client(AccountNumber)
 ) ENGINE = InnoDB;

樣本資料集

INSERT INTO Client VALUES(1, 'Zapp Brannigan');
INSERT INTO Client VALUES(2, "Al Gore's Head");
INSERT INTO Client VALUES(3, 'Barbados Slim');
INSERT INTO Client VALUES(4, 'Ogden Wernstrom');
INSERT INTO Client VALUES(5, 'Leo Wong');
INSERT INTO Client VALUES(6, 'Lrrr');
INSERT INTO Client VALUES(7, 'John Zoidberg');
INSERT INTO Client VALUES(8, 'John Zoidfarb');
INSERT INTO Client VALUES(9, 'Morbo');
INSERT INTO Client VALUES(10, 'Judge John Whitey');
INSERT INTO Client VALUES(11, 'Calculon');
INSERT INTO Employee VALUES(1, 'Phillip J. Fry', 'Delivery boy', 7500.0, 'Not to be confused with the Philip J. Fry from Hovering Squid World 97a');
INSERT INTO Employee VALUES(2, 'Turanga Leela', 'Captain', 10000.0, NULL);
INSERT INTO Employee VALUES(3, 'Bender Bending Rodriguez', 'Robot', 7500.0, NULL);
INSERT INTO Employee VALUES(4, 'Hubert J. Farnsworth', 'CEO', 20000.0, NULL);
INSERT INTO Employee VALUES(5, 'John A. Zoidberg', 'Physician', 25.0, NULL);
INSERT INTO Employee VALUES(6, 'Amy Wong', 'Intern', 5000.0, NULL);
INSERT INTO Employee VALUES(7, 'Hermes Conrad', 'Bureaucrat', 10000.0, NULL);
INSERT INTO Employee VALUES(8, 'Scruffy Scruffington', 'Janitor', 5000.0, NULL);
INSERT INTO Planet VALUES(1, 'Omicron Persei 8', 89475345.3545);
INSERT INTO Planet VALUES(2, 'Decapod X', 65498463216.3466);
INSERT INTO Planet VALUES(3, 'Mars', 32435021.65468);
INSERT INTO Planet VALUES(4, 'Omega III', 98432121.5464);
INSERT INTO Planet VALUES(5, 'Tarantulon VI', 849842198.354654);
INSERT INTO Planet VALUES(6, 'Cannibalon', 654321987.21654);
INSERT INTO Planet VALUES(7, 'DogDoo VII', 65498721354.688);
INSERT INTO Planet VALUES(8, 'Nintenduu 64', 6543219894.1654);
INSERT INTO Planet VALUES(9, 'Amazonia', 65432135979.6547);
INSERT INTO Has_Clearance VALUES(1, 1, 2);
INSERT INTO Has_Clearance VALUES(1, 2, 3);
INSERT INTO Has_Clearance VALUES(2, 3, 2);
INSERT INTO Has_Clearance VALUES(2, 4, 4);
INSERT INTO Has_Clearance VALUES(3, 5, 2);
INSERT INTO Has_Clearance VALUES(3, 6, 4);
INSERT INTO Has_Clearance VALUES(4, 7, 1);
INSERT INTO Shipment VALUES(1, '3004/05/11', 1, 1);
INSERT INTO Shipment VALUES(2, '3004/05/11', 1, 2);
INSERT INTO Shipment VALUES(3, NULL, 2, 3);
INSERT INTO Shipment VALUES(4, NULL, 2, 4);
INSERT INTO Shipment VALUES(5, NULL, 7, 5);
INSERT INTO Package VALUES(1, 1, 'Undeclared', 1.5, 1, 2);
INSERT INTO Package VALUES(2, 1, 'Undeclared', 10.0, 2, 3);
INSERT INTO Package VALUES(2, 2, 'A bucket of krill', 2.0, 8, 7);
INSERT INTO Package VALUES(3, 1, 'Undeclared', 15.0, 3, 4);
INSERT INTO Package VALUES(3, 2, 'Undeclared', 3.0, 5, 1);
INSERT INTO Package VALUES(3, 3, 'Undeclared', 7.0, 2, 3);
INSERT INTO Package VALUES(4, 1, 'Undeclared', 5.0, 4, 5);
INSERT INTO Package VALUES(4, 2, 'Undeclared', 27.0, 1, 2);
INSERT INTO Package VALUES(5, 1, 'Undeclared', 100.0, 5, 1);