SQL Exercises/Planet Express

Relational Schema

PK and FK stand for primary key and foreign key respectively.

Exercises

The first several exercises build on each other and illustrate the use of subqueries.

Who received a 1.5kg package?

Click to see solution

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

What is the total weight of all the packages that he sent?

Click to see solution

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.

Which pilots transported those packages?

Click to see solution

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.

Table creation code

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

Please note the syntax presented here is for the SQLite system. It has been tested by the authors on sqlite3 specifically.

Also note that the NOT NULL constraint on the primary key fields is semantically redundant, but a syntactic necessity in SQLite.

Click to see MySQL syntax.

 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;

Sample dataset

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