The University of Texas

CS 327e Fall 2004 Test 2 Solution

NAME: __________________________________ EID: __________________

Duration: 75 mins Total: 4 Questions (100 pts)

For all questions,

Consider the suppliers-parts-projects database, sample data (same as lab1) for which is shown in the last page. There are 4 tables containing relevant data:

1) SUPPLIER (SID, Name, Status, City)

2) PART (PID, Name, Color, Weight, City)

3) PROJECT (JID, Name, City)

4) SHIPMENT (SID, PID, JID, Qty)

Suppliers (S), Parts (P), and Projects (J) are uniquely identified (PK) by supplier id (SID), part id (PID), and project id (JID), respectively. The significance of an SPJ (shipment) row is that the specified supplier supplies the specified part to the specified project in the specified quantity (and the combination SID-PID-JID uniquely identifies (PK) in such a row). Assume all of the columns in all of the 4 tables do not allow Nulls. Assume there is at least 1 row in each of the 4 tables at any given time. The sample data shown is a snapshot. At any given time, there could be more or less data. Hence you cannot assume that, for example, this is the final/fixed set of data.

UNLESS OTHERWISE STATED, VIEWS ARE NOT ALLOWED, WHEN ASKED TO WRITE THE SQL STATEMENT.

Q1) For the following English statement:

NOTE: The Solution tries to answer the following Q:

For each project that had any parts shipped, give me the count of unique parts that were not shipped to it.

As mentioned in class, for the test query

For each project, give me the count of unique parts that were not shipped to it.

We would need project table to be left outer join with shipment table. Either answers are acceptable

Return the following column alias names: ProjName, CntUnshippedParts

Example: For the sample data, “Sorter” project has 4 parts that were not shipped.

Note: You need to compute all counts using SQL.

a) Write the SQL. (15 points)

/* NOTE: Any other aggregate function like MAX or AVG is acceptable too */

SELECT

J.Name ProjName, (MIN(AllParts.cntAllParts) - COUNT (DISTINCT SH.PID)) CntUnshippedParts

FROM

SHIPMENT SH, PROJECT J,

(SELECT COUNT(*) cntAllParts

FROM Part) AllParts

WHERE

SH.JID = J.JID

GROUP BY J.Name

a) Based on your query in a), write the result set based on the sample data. (10 points)

ProjName CntUnshippedParts

Console 0

Display 1

EDS 5

OCR 4

RAID 4

Sorter 4

Tape 3

Q2) Assume the following 2 views exist:

CREATE VIEW V1 (SID)

AS

SELECT DISTINCT SH.SID

FROM SUPPLIER S, SHIPMENT SH

WHERE S.SID = SH.SID AND

SH.PID = 'P2';

CREATE VIEW V2 (SID)

AS

SELECT DISTINCT SH.SID

FROM SUPPLIER S, SHIPMENT SH

WHERE S.SID = SH.SID AND

SH.PID = 'P4';

a) For the following SQL, Write the result set based on the sample data. (10 points)

SELECT * FROM V1

WHERE SID NOT IN

(SELECT SID FROM V2)

SID

b) For the following SQL, Write the result set based on the sample data. (10 points)

SELECT * FROM V2

WHERE SID NOT IN

(SELECT SID FROM V1)

SID

S3

c) For the following SQL, Write the result set based on the sample data. (5 points)

SELECT SUPPLIER.NAME SUP_NAME

FROM V1, V2, SUPPLIER

WHERE

V1.SID = V2.SID AND

V1.SID = SUPPLIER.SID

SUP_NAME

Adams

Q3) For the following SQL:

SELECT DISTINCT S.Name SUP_NAME

FROM Supplier S, Shipment SH

WHERE

SH.SID = S.SID AND

S.City NOT IN

(

SELECT P.City

FROM Shipment SH, Part P

WHERE SH.PID = P.PID AND

P.Color <> 'Blue')

a) Write the English statement. (10 points)

Give me the unique names of supplier(s) who made at least 1 shipment, where for this shipment, the supplier doesn’t belong to any of the cities that all the non-blue parts were shipped from.

b) For the query, write the result set based on the sample data. (10 points)

SUP_NAME

Adams

Q4) For the following English statement:

For the project(s) that had the highest quantity of parts shipped, give the project id, name of the project, the total quantity of parts shipped, along with the project city.

Return the following column alias names: Project_ID, Project_Name, Parts_Qty, Project_City

a) Write the SQL. (20 points)

SELECT J.JID Project_ID, J.Name Project_Name, SUM (SH.Qty) Parts_Qty, J.City Project_City

FROM SHIPMENT SH, PROJECT J

WHERE J.JID = SH.JID

GROUP BY J.JID, J.Name, J.City

HAVING SUM (SH.Qty) =

(SELECT MAX (ProjShip.TotalQty) MaxQty

FROM

(SELECT SH.JID JID, SUM(SH.Qty) TotalQty

FROM Shipment SH

GROUP BY SH.JID) ProjShip

)

b) Based on your query in a), write the result set based on the sample data. (10 points)

Project_ID Project_Name Parts_Qty Project_City

J4 Console 3300.0 Athens

SHIPMENT |  |  |  |  | SUPPLIER |  |  |
SID | PID | JID | Qty |  | SID | Name | Status | City |
S1 | P1 | J1 | 200 |  | S1 | Smith | 20 | London |
S1 | P1 | J4 | 700 |  | S2 | Jones | 10 | Paris |
S2 | P3 | J1 | 400 |  | S3 | Blake | 30 | Paris |
S2 | P3 | J2 | 200 |  | S4 | Clark | 20 | London |
S2 | P3 | J3 | 200 |  | S5 | Adams | 30 | Athens |
S2 | P3 | J4 | 500 |  |  |  |  |  |
S2 | P3 | J5 | 600 |  | PART |  |  |  |
S2 | P3 | J6 | 400 |  | PID | Name | Color | Weight | City
S2 | P3 | J7 | 800 |  | P1 | Nut | Red | 12.2 | London
S2 | P5 | J2 | 100 |  | P2 | Bolt | Green | 17.0 | Paris
S3 | P3 | J1 | 200 |  | P3 | Screw | Blue | 17.0 | Rome
S3 | P4 | J2 | 500 |  | P4 | Screw | Red | 14.0 | London
S4 | P6 | J3 | 300 |  | P5 | Cam | Blue | 12.0 | Paris
S4 | P6 | J7 | 300 |  | P6 | Cog | Red | 19.0 | London
S5 | P2 | J2 | 200 |  |  |  |  |  |
S5 | P2 | J4 | 100 |  | PROJECT |  |  |
S5 | P5 | J5 | 500 |  | JID | Name | City |  |
S5 | P5 | J7 | 100 |  | J1 | Sorter | Paris |  |
S5 | P6 | J2 | 200 |  | J2 | Display | Rome |  |
S5 | P1 | J4 | 100 |  | J3 | OCR | Athens |  |
S5 | P3 | J4 | 200 |  | J4 | Console | Athens |  |
S5 | P4 | J4 | 800 |  | J5 | RAID | London |  |
S5 | P5 | J4 | 400 |  | J6 | EDS | Oslo |  |
S5 | P6 | J4 | 500 |  | J7 | Tape | London |  |