The
CS 327e Fall 2004 Test
2 Solution
NAME:
__________________________________ EID: __________________
Duration: 75 mins Total:
4 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) in such a row). Assume all of the columns in all of the 4
tables do not allow Nulls. Assume there is at least 1 row in each of the 4
tables at any given time. The sample data shown is a snapshot. At any given
time, there could be more or less data. Hence you cannot assume that, for
example, this is the final/fixed set of data.
UNLESS OTHERWISE STATED,
VIEWS ARE NOT ALLOWED, WHEN ASKED TO WRITE THE SQL STATEMENT.
Q1) For the following English statement:
NOTE:
The Solution tries to answer the following Q:
For each project that had any parts shipped, give me
the count of unique parts that were not shipped to it.
As mentioned
in class, for the test query
For
each project, give me the count of unique parts that were not shipped to it.
We
would need project table to be left outer join with shipment table. Either
answers are acceptable
Return the following column alias names: ProjName,
CntUnshippedParts
Example: For the sample data, “Sorter” project has 4
parts that were not shipped.
Note: You need to compute all counts using SQL.
a)
Write the SQL. (15
points)
/* NOTE: Any other aggregate function
like MAX or AVG is acceptable too */
SELECT
J.Name
ProjName, (MIN(AllParts.cntAllParts) - COUNT (DISTINCT SH.PID)) CntUnshippedParts
FROM
SHIPMENT SH, PROJECT J,
(SELECT COUNT(*) cntAllParts
FROM Part) AllParts
WHERE
SH.JID = J.JID
GROUP BY J.Name
a)
Based on your
query in a), write the result set based on the sample data. (10 points)
ProjName CntUnshippedParts
Console 0
Display 1
EDS 5
OCR 4
RAID 4
Sorter 4
Tape 3
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 = 'P2';
CREATE VIEW V2 (SID)
AS
SELECT DISTINCT SH.SID
FROM SUPPLIER S, SHIPMENT SH
WHERE S.SID = SH.SID AND
SH.PID = 'P4';
a) For the following SQL,
Write the result set based on the sample data. (10 points)
SELECT * FROM V1
WHERE SID NOT IN
(SELECT SID FROM V2)
SID
b) For the following SQL,
Write the result set based on the sample data. (10 points)
SELECT * FROM V2
WHERE SID NOT IN
(SELECT SID FROM V1)
SID
S3
c) For the following SQL,
Write the result set based on the sample data. (5 points)
SELECT SUPPLIER.NAME SUP_NAME
FROM V1, V2, SUPPLIER
WHERE
V1.SID = V2.SID AND
V1.SID = SUPPLIER.SID
SUP_NAME
Q3) For the following SQL:
SELECT DISTINCT S.Name SUP_NAME
FROM Supplier S, Shipment SH
WHERE
SH.SID
= S.SID AND
S.City NOT IN
(
SELECT P.City
FROM Shipment SH, Part P
WHERE SH.PID
= P.PID AND
P.Color
<> 'Blue')
a)
Write the English
statement. (10 points)
Give
me the unique names of supplier(s) who made at least 1 shipment, where for this shipment, the supplier doesn’t
belong to any of the cities that all the non-blue parts were shipped from.
b)
For the query,
write the result set based on the sample data. (10 points)
SUP_NAME
Q4) For the following English statement:
For the project(s) that had the highest quantity of
parts shipped, give the project id, name of the project, the total quantity of
parts shipped, along with the project city.
Return the following column alias names: Project_ID,
Project_Name, Parts_Qty, Project_City
a)
Write the SQL. (20
points)
SELECT J.JID
Project_ID, J.Name Project_Name, SUM (SH.Qty) Parts_Qty, J.City Project_City
FROM SHIPMENT
SH, PROJECT J
WHERE J.JID
= SH.JID
GROUP BY J.JID,
J.Name, J.City
HAVING SUM
(SH.Qty) =
(SELECT
MAX (ProjShip.TotalQty)
MaxQty
FROM
(SELECT
SH.JID JID, SUM(SH.Qty)
TotalQty
FROM Shipment
SH
GROUP BY SH.JID)
ProjShip
)
b)
Based on your
query in a), write the result set based on the sample data. (10 points)
Project_ID Project_Name Parts_Qty Project_City
J4 Console 3300.0
|
SHIPMENT |
|
|
|
|
SUPPLIER |
|
|
|
|
|
SID |
PID |
JID |
Qty |
|
SID |
Name |
Status |
City |
|
|
S1 |
P1 |
J1 |
200 |
|
S1 |
Smith |
20 |
|
|
|
S1 |
P1 |
J4 |
700 |
|
S2 |
Jones |
10 |
|
|
|
S2 |
P3 |
J1 |
400 |
|
S3 |
Blake |
30 |
|
|
|
S2 |
P3 |
J2 |
200 |
|
S4 |
|
20 |
|
|
|
S2 |
P3 |
J3 |
200 |
|
S5 |
|
30 |
|
|
|
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 |
|
|
S2 |
P5 |
J2 |
100 |
|
P2 |
Bolt |
Green |
17.0 |
|
|
S3 |
P3 |
J1 |
200 |
|
P3 |
Screw |
Blue |
17.0 |
|
|
S3 |
P4 |
J2 |
500 |
|
P4 |
Screw |
Red |
14.0 |
|
|
S4 |
P6 |
J3 |
300 |
|
P5 |
|
Blue |
12.0 |
|
|
S4 |
P6 |
J7 |
300 |
|
P6 |
Cog |
Red |
19.0 |
|
|
S5 |
P2 |
J2 |
200 |
|
|
|
|
|
|
|
S5 |
P2 |
J4 |
100 |
|
PROJECT |
|
|
|
|
|
S5 |
P5 |
J5 |
500 |
|
JID |
Name |
City |
|
|
|
S5 |
P5 |
J7 |
100 |
|
J1 |
Sorter |
|
|
|
|
S5 |
P6 |
J2 |
200 |
|
J2 |
Display |
|
|
|
|
S5 |
P1 |
J4 |
100 |
|
J3 |
OCR |
|
|
|
|
S5 |
P3 |
J4 |
200 |
|
J4 |
Console |
|
|
|
|
S5 |
P4 |
J4 |
800 |
|
J5 |
RAID |
|
|
|
|
S5 |
P5 |
J4 |
400 |
|
J6 |
EDS |
|
|
|
|
S5 |
P6 |
J4 |
500 |
|
J7 |
Tape |
|
|
|