The University of Texas

CS327e Fall 2003 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.

Q1) For the following English statement:

Give me the names of projects, and its total number of shipments (not quantities), for projects that have at least 3 shipments.

a) Write the SQL. (20 points)

SELECT P.NAME ProjectName, COUNT(Qty) NumShipments

FROM PROJECT P, SHIPMENT SH

WHERE SH.JID = P.JID

GROUP BY P.NAME

HAVING COUNT(Qty) >= 3

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

ProjectName | NumShipments
Console | 8
Display | 5
Sorter | 3
Tape | 3

Q2) Assume the following 2 views exist:

CREATE VIEW V1 (SID)

AS

SELECT DISTINCT S.SID

FROM SUPPLIER S, SHIPMENT SH

WHERE S.SID < SH.SID

CREATE VIEW V2 (SID)

AS

SELECT DISTINCT SH.SID

FROM SUPPLIER S, SHIPMENT SH

WHERE S.SID < SH.SID

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

SELECT * FROM V1

SID
S1
S2
S3
S4

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

SELECT * FROM V2

SID
S2
S3
S4
S5

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

SELECT SUPPLIER.NAME

FROM V1, V2, SUPPLIER

WHERE

V1.SID = V2.SID AND

V1.SID = SUPPLIER.SID

NAME
Jones
Blake
Clark

Q3) For the following SQL:

SELECT DISTINCT S.Name

FROM Supplier S, Shipment SH

WHERE

SH.SID = S.SID AND

SH.SID NOT IN

(

SELECT SH.SID

FROM Shipment SH

WHERE SH.PID = 'P2'

)

a) Write the English statement. (10 points)

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

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

Name
Blake
Clark
Jones
Smith

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_SPJCOUNTS_FOR_CITY

@City nvarchar (255),

@NumSuppliers int OUTPUT,

@NumParts int OUTPUT,

@NumProjects int OUTPUT,

@Status int OUTPUT

AS

BEGIN

-- Initialize output variables

SET @NumSuppliers = 0

SET @NumParts = 0

SET @NumProjects = 0

SET @Status = -1

-- Get Number of Suppliers belonging to @City

SELECT @NumSuppliers = COUNT (DISTINCT SID) FROM SUPPLIER

WHERE UPPER (RTRIM (SUPPLIER.City)) = UPPER (RTRIM (@City));

-- Get Number of Parts belonging to @City

SELECT @NumParts = COUNT (DISTINCT PID) FROM PART

WHERE UPPER (RTRIM (PART.City)) = UPPER (RTRIM (@City));

-- Get Number of Projects belonging to @City

SELECT @NumProjects = COUNT (DISTINCT JID) FROM PROJECT

WHERE UPPER (RTRIM (PROJECT.City)) = UPPER (RTRIM (@City));

-- Set Successful Status

SET @Status = 0

END

Assume all SPJ tables exist. Note that for this procedure, the SHIPMENT table does not play any role.

Hence, for the data shown in the last page, if we execute the following statements on Query Analyzer in SQLServer:

DECLARE @mySuppliers int

DECLARE @myParts int

DECLARE @myProjects int

DECLARE @myStatus int

EXEC GET_SPJCOUNTS_FOR_CITY 'Athens', @mySuppliers OUTPUT, @myParts OUTPUT, @myProjects OUTPUT, @myStatus OUTPUT

PRINT 'There are ' + CAST (@mySuppliers AS nvarchar(20)) + ' suppliers, ' + CAST (@myParts AS nvarchar(20)) + ' parts, and ' + CAST (@myProjects AS nvarchar(20)) + ' projects in your city.'

GO

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

a) There are 1 suppliers, 0 parts, and 2 projects in your city.

b) There are 0 suppliers, 1 parts, and 2 projects in your city.

c) There are 2 suppliers, 1 parts, and 0 projects in your city.

d) None of the above.

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

EXEC GET_SPJCOUNTS_FOR_CITY 'London', @mySuppliers OUTPUT, @myParts OUTPUT, @myProjects OUTPUT, @myStatus OUTPUT

a) There are 1 suppliers, 0 parts, and 2 projects in your city.

b) There are 0 suppliers, 1 parts, and 2 projects in your city.

c) There are 2 suppliers, 3 parts, and 2 projects in your city.

d) None of the above.

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

EXEC GET_SPJCOUNTS_FOR_CITY 'Austin', @mySuppliers OUTPUT, @myParts OUTPUT, @myProjects OUTPUT, @myStatus OUTPUT

a) There are 1 suppliers, 0 parts, and 2 projects in your city.

b) There are 0 suppliers, 1 parts, and 0 projects in your city.

c) There are 0 suppliers, 0 parts, and 0 projects in your city.

d) None of the above.

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

EXEC GET_SPJCOUNTS_FOR_CITY NULL, @mySuppliers OUTPUT, @myParts OUTPUT, @myProjects OUTPUT, @myStatus OUTPUT

a) There are 1 suppliers, 0 parts, and 2 projects in your city.

b) There are 0 suppliers, 1 parts, and 2 projects in your city.

c) There are 2 suppliers, 3 parts, and 2 projects in your city.

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