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