Solutions -- Easy SQL Exercises


There are several ways each of these SQL statements could be written - this is only an example solution for each.

1. Get the name of all parts that have been shipped.

SELECT Parts.PartName
     FROM Parts, Shipments
         WHERE Parts.PartNum = Shipments.PartNum;

2. Get the name of all suppliers that have shipped to Paris.

SELECT DISTINCT Suppliers.SupplierName, Projects.City
     FROM Suppliers, Shipments, Projects
          WHERE Suppliers.SupplierNum = Shipments.SupplierNum
               AND Shipments.ProjectNum = Projects.ProjectNum
               AND  Projects.City = "Paris";

3. Get all supplier numbers, part numbers, and project numbers where the indicated supplier, part, and project are located in the same city. (Note: this isn't particularly useful, as this query doesn't tell you which suppliers are supplying which parts to which projects.)

SELECT Parts.PartNum, Projects.ProjectNum, Suppliers.SupplierNum, Parts.City, Projects.City, Suppliers.City
     FROM Parts, Projects, Suppliers
          WHERE Projects.City = Suppliers.City
               AND Parts.City = Projects.City;

What's wrong with this query?  It works, but fails to link the tables together through PK-FK linkage.

The statement below will return all those parts supplied by a supplier in the same city to a project in the same city:

SELECT Parts.PartNum, Projects.ProjectNum, Suppliers.SupplierNum, parts.City, Projects.City, Suppliers.City
     FROM Parts, Shipments, Projects, Suppliers
          WHERE  Parts.PartNum = Shipments.PartNum
               AND Shipments.ProjectNum = Projects.ProjectNum
               AND Shipments.ProjectNum = Projects.ProjectNum
              AND Shipments.SupplierNum = Suppliers.SupplierNum
              AND Parts.City = Projects.City
              AND Projects.City = Suppliers.City

4. Get part numbers for parts supplied by a supplier in London.

SELECT DISTINCT Shipments.PartNum, Suppliers.SupplierNum, Suppliers.City
     FROM Suppliers, Shipments
          WHERE Suppliers.SupplierNum = Shipments.SupplierNum
               AND Suppliers.City = "London";

5. Get part numbers for parts supplied by a supplier in London to a project based in London.

SELECT Shipments.PartNum, Shipments.SupplierNum, Shipments.ProjectNum, Suppliers.City
     FROM Suppliers, Shipments, Projects
          WHERE Suppliers.SupplierNum = Shipments.SupplierNum
               AND Shipments.ProjectNum = Projects.ProjectNum
               AND (Suppliers.City = "London")
               AND (Projects.City = "London");

6. Get project names for projects supplied by supplier S1.

SELECT Projects.ProjectName
     FROM Projects, Shipments
          WHERE Projects.ProjectNum = Shipments.ProjectNum
               AND Shipments.SupplierNum = "S1";