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)

SELECT DISTINCT S.SID

FROM SUPPLIER S, SHIPMENT SH

WHERE S.SID < SH.SID

CREATE VIEW V2 (SID)

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

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

SELECT * FROM V2

SID

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

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

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