CS327e

Fall 2003

Lab 5 Solution

DESCRIPTION/IMPLEMENTATION:

A) Use the data imported in Lab1. For each of the 4 tables PART, SUPPLIER, PROJECT, SHIPMENT; set the Primary Key for the appropriate column(s). You can do this by opening the table in “Design Mode” (i.e. right-click on the table name), highlight the column(s) needed for PK, and click on the icon in the menu bar for PK. Assume all non-PK columns in all 4 tables allow nulls.

B) For each of the 10 queries,

a) Execute a “CREATE TABLE ………” statement in your SQL Server database, corresponding to the output of the query.

b) Use an “INSERT INTO ……… SELECT FROM ………” statement to populate this new table; where the SELECT statement is based on the english statement of the query.

For example,

Query 0)

For each shipping blue part, show the name of the part along with its shipping quantity.

If Query 0 is shown as above,

Then execute the following CREATE TABLE statement in your SQL Server database account:

CREATE TABLE QUERY0

(

PARTNAME nvarchar (255),

TOTALQUANTITY float

);

NOTE the appropriate datatype selection.

Then, you will execute the following DML statement:

INSERT INTO QUERY0

SELECT PART.NAME, SUM (SHIPMENT.QTY) AS TOTALQUANTITY

FROM PART, SHIPMENT

WHERE

PART.PID = SHIPMENT.PID

AND PART.COLOR = 'Blue'

GROUP BY PART.NAME;

Resulting in Query0 table being populated as

QUERY0

PARTNAME	TOTALQUANTITY
Cam	1100.0
Screw	3500.0

QUERIES (10 points each)

NOTE: “Supplies” and “Ships” are synonymous.

1) Get part numbers for parts shipped by more than one supplier.

CREATE TABLE Query1

(
PartNumber NVARCHAR (10)

);

INSERT INTO Query1 (PartNumber)

SELECT Shipment.PID

FROM Shipment

GROUP BY Shipment.PID

HAVING COUNT(DISTINCT SID) > 1;

PartNumber

P1

P3

P4

P5

P6

2) Get distinct supplier names of suppliers who ship red parts, sorted in ascending order.

CREATE TABLE Query2

(
SupplierName NVARCHAR (255)

);

INSERT INTO Query2 (SupplierName)

SELECT DISTINCT Supplier.Name

FROM Supplier, Shipment, Part

WHERE Shipment.SID = Supplier.SID AND

Shipment.PID = Part.PID AND

Part.Color = 'Red'

ORDER BY Supplier.Name ASC;

SupplierName

Adams

Blake

Clark

Smith

3) Get distinct supplier names for suppliers who do not ship part P2.

CREATE TABLE Query3

(
SupplierName NVARCHAR (255)

);

INSERT INTO Query3 (SupplierName)

SELECT DISTINCT Supplier.Name

FROM Supplier, Shipment

WHERE Shipment.SID = Supplier.SID AND

Shipment.SID NOT IN

(

SELECT Shipment.SID

FROM Shipment

WHERE Shipment.PID = 'P2'

);

SupplierName

Blake

Clark

Jones

Smith

4) Get distinct part numbers for parts that either weigh more than 14 pounds or are shipped by supplier S2, or both.

CREATE TABLE Query4

(
PartNumber NVARCHAR (10)

);

INSERT INTO Query4 (PartNumber)

SELECT Part.PID

FROM Part

WHERE Part.Weight > 14.0

UNION

SELECT Shipment.PID

FROM Shipment

WHERE Shipment.SID = 'S2';

PartNumber

P2

P3

P5

P6

5) Get the names of suppliers and projects who ship more parts in the project than the average quantity for that project.

CREATE TABLE Query5

(
SupplierName NVARCHAR (255),

PartNumber NVARCHAR (10)

);

INSERT INTO Query5 (SupplierName, PartNumber)

SELECT Supplier.Name, SP.JID

FROM Supplier, Project, Shipment SP

WHERE SP.SID = Supplier.SID AND

SP.JID = Project.JID

GROUP BY Supplier.Name, SP.JID

HAVING SUM (SP.Qty) >

(

SELECT AVG (SC.Qty)

FROM Shipment SC

WHERE SC.JID = SP.JID

);

SupplierName PartNumber

Jones J1

Adams J2

Blake J2

Jones J2

Clark J3

Adams J4

Jones J4

Smith J4

Jones J5

Jones J7

Note: Returning the project id is OK instead of the project name.

6) Get all pairs of city names such that a supplier in the first city supplies a project in the second city, where the 2 cities are not the same.

CREATE TABLE Query6

(
SupplierCity NVARCHAR (255),

ProjectCity NVARCHAR (255)

);

INSERT INTO Query6 (SupplierCity, ProjectCity)

SELECT DISTINCT Supplier.City, Project.City

FROM Supplier, Project

WHERE EXISTS

(

SELECT *

FROM Shipment

WHERE Shipment.SID = Supplier.SID AND

Shipment.JID = Project.JID

)

AND

Supplier.City <> Project.City;

SupplierCity ProjectCity

Athens London

Athens Rome

London Athens

London Paris

Paris Athens

Paris London

Paris Oslo

Paris Rome

7) Get project numbers for projects supplied by at least one supplier not in the same city.

CREATE TABLE Query7

(
ProjectNumber NVARCHAR (10)

);

INSERT INTO Query7 (ProjectNumber)

SELECT DISTINCT Project.JID

FROM Supplier, Project

WHERE EXISTS

(

SELECT *

FROM Shipment

WHERE Shipment.SID = Supplier.SID AND

Shipment.JID = Project.JID

)

AND

Supplier.City <> Project.City;

ProjectNumber

J1

J2

J3

J4

J5

J6

J7

8) Get supplier numbers for suppliers with a status lower than that of supplier S4.

CREATE TABLE Query8

(
SupplierNumber NVARCHAR (10)

);

INSERT INTO Query8 (SupplierNumber)

SELECT Supplier.SID

FROM Supplier

WHERE Supplier.Status <

(

SELECT Supplier.Status

FROM Supplier

WHERE Supplier.SID = 'S4'

);

SupplierNumber

S2

9) Get project numbers for projects supplied entirely by supplier S1.

CREATE TABLE Query9

(
ProjectNumber NVARCHAR (10)

);

INSERT INTO Query9 (ProjectNumber)

SELECT PROJECT.JID

FROM PROJECT

WHERE NOT EXISTS

(

SELECT *

FROM SHIPMENT

WHERE SHIPMENT.JID = PROJECT.JID

AND SHIPMENT.SID <> 'S1'

);

ProjectNumber

Note: 0 Rows.

10) Get unique part names in descending order for parts shipped to all projects in Athens.

CREATE TABLE Query10

(
PartName NVARCHAR (255)

);

INSERT INTO Query10 (PartName)

SELECT DISTINCT Part.Name

FROM Shipment, Part, Project

WHERE Shipment.PID = Part.PID AND

Shipment.JID = Project.JID AND

Project.City = 'Athens'

ORDER BY Part.Name DESC;

PartName

Screw

Nut

Cog

Cam

Bolt

Last Updated: 10/14/2003 3:25:06 PM