The University of Texas

CS 327e Spring 2004 Test 2Solution

NAME: __________________________________ EID: __________________

Duration: 75 mins Total: 7 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) 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:

Give me the name of projects, its total number of shipments (not quantities), along with average number of shipments. The average number of shipments is computed as the total number of shipments divided by total number of distinct projects who had shipments. [Use the column alias of PRJ_NAME, TOT_SHPMNTS, and AVG_SHPMNTS respectively in your SELECT statement]

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

a) Write the SQL. (20 points)

SELECT TOT_SH.NAME PRJ_NAME, TOT_SH.TOT_SHPMNTS, AVG_SH.AVG_SHPMNTS

FROM

(

SELECT CONVERT (DECIMAL (9,2) ,

(

CONVERT (DECIMAL (9,2) , COUNT (*)) /

CONVERT (DECIMAL (9,2) , COUNT (DISTINCT JID))

)) AVG_SHPMNTS

FROM SHIPMENT

) AVG_SH,

(

SELECT J.NAME NAME, COUNT (SH.JID) TOT_SHPMNTS

FROM

SHIPMENT SH,

PROJECT J

WHERE

SH.JID = J.JID

GROUP BY J.NAME

) TOT_SH;

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

PRJ_NAME TOT_SHPMNTS AVG_SHPMNTS

Console 8 3.43

Display 5 3.43

EDS 1 3.43

OCR 2 3.43

RAID 2 3.43

Sorter 3 3.43

Tape 3 3.43

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 = ‘P1’;

CREATE VIEW V2 (SID)

AS

SELECT DISTINCT SH.SID

FROM SUPPLIER S, SHIPMENT SH

WHERE S.SID = SH.SID AND

SH.PID = ‘P3’;

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

SELECT * FROM V1

SID

S1

S5

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

SELECT * FROM V2

SID

S2

S3

S5

c) For the following SQL, Write the result set based on the sample data. (10 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.SID NOT IN

(

SELECT SH.SID

FROM Shipment SH, Part P

WHERE SH.PID = P.PID AND

P.City = ‘London’

)

a) Write the English statement. (10 points)

Give me names of suppliers who haven’t made any shipment of parts from London.

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

SUP_NAME

Jones

Using the following SQL Server CREATE PROCEDURE, answer the next 4 multiple-choice questions (5 points each) by circling the most appropriate answer.

CREATE PROCEDURE GET_SPPCOUNTS_FOR_SUPPLIER

@SupplierName nvarchar (255),

@NumShipments int OUTPUT,

@NumParts int OUTPUT,

@NumProjects int OUTPUT,

@Status int OUTPUT

AS

BEGIN

-- Initialize output variables

SET @NumShipments = 0

SET @NumParts = 0

SET @NumProjects = 0

SET @Status = -1

-- Get Number of Shipments belonging to @SupplierName

SELECT @NumShipments = COUNT (SH.SID) FROM SHIPMENT SH, SUPPLIER S

WHERE UPPER (RTRIM (S.Name)) = UPPER (RTRIM (@SupplierName))

AND S.SID = SH.SID;

-- Get Number of Parts shipped by @SupplierName

SELECT @NumParts = SUM (SH.QTY) FROM SHIPMENT SH, SUPPLIER S

WHERE UPPER (RTRIM (S.Name)) = UPPER (RTRIM (@SupplierName))

AND S.SID = SH.SID;

-- Get Number of Projects that @SupplierName has shipped parts to

SELECT @NumProjects = COUNT (DISTINCT SH.JID) FROM SHIPMENT SH, SUPPLIER S

WHERE UPPER (RTRIM (S.Name)) = UPPER (RTRIM (@SupplierName))

AND S.SID = SH.SID;

-- Set Successful Status

SET @Status = 0

END;

Assume all SPJ tables exist.

Hence, for the sample data showed in the last page, if we execute the following statements on Query Analyzer in SQL Server:

DECLARE @myShipments int

DECLARE @myParts int

DECLARE @myProjects int

DECLARE @myStatus int

EXEC GET_SPPCOUNTS_FOR_SUPPLIER 'Athens', @myShipments OUTPUT, @myParts OUTPUT, @myProjects OUTPUT, @myStatus OUTPUT

PRINT 'This supplier had ' + CAST (@myShipments AS nvarchar(20)) + ' shipments, ' +

CAST (@myParts AS nvarchar(20)) + ' parts shipped, and shipped to ' + CAST (@myProjects AS nvarchar(20)) + ' projects.'

GO

Q4) For the EXEC statement above, the PRINT statement would print:

a) This supplier had 1 shipments, 800 parts shipped, and shipped to 2 projects.

b) This supplier had 2 shipments, 3200 parts shipped, and shipped to 1 projects.

c) This supplier had 1 shipments, 900 parts shipped, and shipped to 2 projects.

d) None of the above.

Q5) If the EXEC statement was changed as shown below, the PRINT statement would print:

EXEC GET_SPPCOUNTS_FOR_SUPPLIER 'Smith', @myShipments OUTPUT, @myParts OUTPUT, @myProjects OUTPUT, @myStatus OUTPUT

a) This supplier had 1 shipments, 900 parts shipped, and shipped to 2 projects.

b) This supplier had 2 shipments, 900 parts shipped, and shipped to 3 projects.

c) This supplier had 10 shipments, 3100 parts shipped, and shipped to 4 projects.

d) None of the above.

Q6) If the EXEC statement was changed as shown below, the PRINT statement would print:

EXEC GET_SPPCOUNTS_FOR_SUPPLIER 'Blake', @myShipments OUTPUT, @myParts OUTPUT, @myProjects OUTPUT, @myStatus OUTPUT

a) This supplier had 2 shipments, 700 parts shipped, and shipped to 2 projects.

b) This supplier had 2 shipments, 900 parts shipped, and shipped to 2 projects.

c) This supplier had 10 shipments, 3100 parts shipped, and shipped to 3 projects.

d) None of the above.

Q7) If the EXEC statement was changed as shown below, the PRINT statement would print:

EXEC GET_SPPCOUNTS_FOR_SUPPLIER 'Adams', @myShipments OUTPUT, @myParts OUTPUT, @myProjects OUTPUT, @myStatus OUTPUT

a) This supplier had 10 shipments, 3100 parts shipped, and shipped to 4 projects.

b) This supplier had 1 shipments, 800 parts shipped, and shipped to 2 projects.

c) This supplier had 2 shipments, 700 parts shipped, and shipped to 1 projects.

d) None of the above.

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