The University of Texas

CS 327e Spring 2005 Test 2 Solution (All answers in BOLD)

NAME: __________________________________ EID: __________________

Duration: 70 mins Total: 3 Questions (100 pts)

For all questions,

Consider the suppliers-parts-projects database, sample data (same as lab4) 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) such a row). Assume none of the columns in all of the 4 tables allow Nulls. Assume there is at least 1 row in each of the 4 tables.

Q1) For the following English statement:

For Suppliers that had at least 3 shipments, give me the total quantity of parts that they shipped, by color. (OUTPUT: PCOLOR, PQUANT)

NOTE: No CREATE VIEW, CREATE TABLE statements allowed. Only SELECT statement.

a) Write the SQL. (15 points)

SELECT

P.COLOR PCOLOR,

SUM (SH.QTY) PQUANT

FROM

PART P,

SHIPMENT SH

WHERE

P.PID = SH.PID

AND

SH.SID IN

(

SELECT

SH.SID

FROM

SHIPMENT SH

GROUP BY

SH.SID

HAVING

COUNT (SH.SID) >= 3

)

GROUP BY P.COLOR;

Or

SELECT

P.COLOR PCOLOR,

SUM (SH.QTY) PQUANT

FROM

PART P,

SHIPMENT SH,

(

SELECT

SH.SID SID

FROM

SHIPMENT SH

GROUP BY

SH.SID

HAVING

COUNT (SH.SID) >= 3

) THREE_SHIPPERS

WHERE

P.PID = SH.PID

AND

SH.SID = THREE_SHIPPERS.SID

GROUP BY P.COLOR;

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

PCOLOR PQUANT

Blue 4400

Green 300

Red 1600

Q2) Assume the following 2 views exist:

CREATE VIEW V1 (SID)

AS

SELECT DISTINCT SH.SID

FROM SUPPLIER S LEFT OUTER JOIN SHIPMENT SH

ON

S.SID = SH.SID;

CREATE VIEW V2 (SID)

AS

SELECT DISTINCT S.SID

FROM SUPPLIER S RIGHT OUTER JOIN SHIPMENT SH

ON

S.SID = SH.SID;

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

SELECT * FROM V1;

SID

NULL

S1

S2

S3

S4

S5

S6

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

SELECT * FROM V2;

SID

S1

S2

S3

S4

S5

S6

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

SELECT V1.SID, V2.SID

FROM V1, V2

WHERE

V1.SID = V2.SID;

SID SID

S1 S1

S2 S2

S3 S3

S4 S4

S5 S5

S6 S6

Q3) For the following SQL:

SELECT DISTINCT P.Name PART_NAME

FROM Part P, Shipment SH

WHERE

SH.PID = P.PID AND

SH.SID NOT IN

(

SELECT SH.SID

FROM Shipment SH, Supplier S

WHERE SH.SID = S.SID AND

S.City = ‘London’

)

AND

P.City = ‘London’;

a) Write the English statement for query above. (10 points)

Give me distinct part names from London that were shipped by suppliers not from London.

Or

Give me unique part names from London that were shipped by suppliers not from the same city.

b) Write an alternate SELECT statement (without using sub queries or views) for a) that results in the same solution. (10 points)

SELECT DISTINCT P.Name PART_NAME

FROM Part P, Shipment SH, Supplier S

WHERE

SH.PID = P.PID AND

SH.SID = S.SID AND

S.City <> ‘London’ AND

P.City = ‘London’;

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

PART_NAME

Cog

Nut

Screw