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);