Due: 2005-04-01
Friday 8.30 a.m. by printout and in SQL Server. NO LATE LABS ACCEPTED.
PURPOSE:
The purpose of
this lab is to learn about SQL statements using SQL Server 2000, using the SPJ tables
attached below (Data is different than Lab 1, hence you need to delete your
original Lab 1 data, and re-import this new Lab 4 data).
DESCRIPTION/IMPLEMENTATION:
A)
Use the
data imported in attached spreadsheet (NOTE: IT IS NOT THE SAME AS IN LAB1, HENCE
DELETE YOUR LAB1 DATA BEFORE IMPORTING). For each of the 4 tables PART,
SUPPLIER, PROJECT, and SHIPMENT; set the Primary Key for the appropriate
column(s). You can do this by opening the table in “Design Mode” (i.e.
right-click on the table name), highlight the column(s) needed for PK, and
click on the icon in the menu bar for PK. Assume all non-PK columns in all 4
tables allow nulls.
B) For
each of the 5 queries,
a)
Execute a “CREATE TABLE ………” statement in
your SQL Server database, corresponding to the output of the query.
b)
Use an “INSERT INTO ……… SELECT FROM ………”
statement to populate this new table; where the SELECT statement is based on
the English statement of the query.
For example,
Query 0)
For
each shipping blue part, show the name of the part along with its
shipping quantity.
If
Query 0 is shown as above,
Then
execute the following CREATE TABLE statement in your SQL Server database
account:
CREATE TABLE QUERY0
(
PARTNAME nvarchar
(255),
TOTALQUANTITY float
);
NOTE the appropriate data type selection.
Then, you will execute the following DML statement:
INSERT INTO QUERY0
SELECT
PART.NAME, SUM (SHIPMENT.QTY) AS TOTALQUANTITY
FROM
PART, SHIPMENT
WHERE
PART.PID
= SHIPMENT.PID
AND
PART.COLOR = 'Blue'
GROUP
BY PART.NAME;
Resulting in Query0 table being populated as
QUERY0
|
PARTNAME |
TOTALQUANTITY |
|
|
1100.0 |
|
Screw |
3700.0 |
1)
For each part, get its
1. ID
and Name,
2. Total
number of shipments,
3. Total
number of quantities shipped.
4. Total
shipped weight. (= part weight * total shipped qty). {Note: Will be 0 for parts
that did not ship).
5. Total
shipped weight / Total shipped weight of same color parts. (If the denominator
is 0, return a NULL).
HINT: You might need to use
some built-in SQL Server functions such as CASE or ISNULL or NULLIF or
COALESCE. Look at link # 11 on class web page for appropriate syntax.
OUTPUT:
QUERY1
|
PID |
PNAME |
SHIPMENTS |
QUANTITIES |
SHIP_WEIGHT |
AVG_CLR_WEIGHT |
|
|
|
|
|
|
|
CREATE TABLE QUERY1
(
PID nvarchar
(25),
PNAME nvarchar
(25),
SHIPMENTS integer,
QUANTITIES integer,
SHIP_WEIGHT decimal
(10, 1),
AVG_CLR_WEIGHT decimal
(10, 2) NULL
);
INSERT INTO QUERY1
SELECT
P.PID
PID,
P.NAME
PNAME,
SUM
(TOTAL_SHIP_WEIGHT.SHIPMENTS) SHIPMENTS,
SUM
(TOTAL_SHIP_WEIGHT.EACH_PART_QUANT) QUANTITIES,
SUM
(TOTAL_SHIP_WEIGHT.EACH_PART_WEIGHT) SHIP_WEIGHT,
SUM
(TOTAL_SHIP_WEIGHT.EACH_PART_WEIGHT)/ NULLIF (SUM
(TOTAL_COLOR_WEIGHT.EACH_COLOR_QUANT), 0) AVG_CLR_WEIGHT
FROM
PART P
LEFT
OUTER JOIN
(
SELECT
P.PID
PID,
SUM
(ISNULL (SH.QTY, 0)) EACH_PART_QUANT,
COUNT
(SH.PID) SHIPMENTS,
SUM
(ISNULL (SH.QTY, 0) * P.WEIGHT) EACH_PART_WEIGHT
FROM
PART
P
LEFT
OUTER JOIN
SHIPMENT
SH
ON
P.PID = SH.PID
GROUP
BY
P.PID
)
TOTAL_SHIP_WEIGHT
ON
TOTAL_SHIP_WEIGHT.PID = P.PID
LEFT
OUTER JOIN
(
SELECT
P.COLOR
COLOR,
SUM
(ISNULL (SH.QTY, 0) * P.WEIGHT) EACH_COLOR_QUANT
FROM
PART
P
LEFT
OUTER JOIN
SHIPMENT
SH
ON
P.PID = SH.PID
GROUP
BY
P.COLOR
)
TOTAL_COLOR_WEIGHT
ON
TOTAL_COLOR_WEIGHT.COLOR = P.COLOR
GROUP BY
P.PID,
P.NAME
ORDER BY
P.PID;
PID PNAME SHIPMENTS QUANTITIES SHIP_WEIGHT AVG_CLR_WEIGHT
P1 Nut 3 1000 12200.0 .20
P2 Bolt 2 300 5100.0 1.00
P3 Screw 10 3700 62900.0 .83
P4 Screw 2 1300 18200.0 .30
P5
P6 Cog 4 1300 24700.0 .41
P7
P8 Nut 1 300 4590.0 .08
P9 Bolt 0 0 .0 .00
1) Get the part colors for non-shipping parts. For each of those distinct colors, determine the total quantity of parts shipped.
OUTPUT:
QUERY2
|
PCOLOR |
SHIP_QUANT |
|
|
|
CREATE TABLE QUERY2
(
PCOLOR nvarchar
(25),
SHIP_QUANT integer
);
INSERT INTO QUERY2
SELECT
P.COLOR PCOLOR,
ISNULL (SUM (SH.QTY), 0) SHIP_QUANT
FROM
PART P
LEFT OUTER JOIN
SHIPMENT SH
ON
P.PID = SH.PID
WHERE
P.COLOR IN
(
SELECT
DISTINCT
P.COLOR
COLOR
FROM
PART
P
WHERE
P.PID NOT IN
(
SELECT
DISTINCT
SH.PID
FROM
SHIPMENT
SH
)
)
GROUP BY
P.COLOR;
PCOLOR SHIP_QUANT
Green 300
Yellow 0
2) Get all shipments (ordered descending by quantity) where, for each shipment, at least two of supplier, part, or projects, are from the same city. Each of these shipments must have a quantity of at least 200.
OUTPUT:
QUERY3
|
SNAME |
SCITY |
PNAME |
PCITY |
JNAME |
JCITY |
QTY |
|
|
|
|
|
|
|
|
CREATE TABLE QUERY3
(
SNAME nvarchar
(25),
SCITY nvarchar
(25),
PNAME nvarchar
(25),
PCITY nvarchar
(25),
JNAME nvarchar
(25),
JCITY nvarchar
(25),
QTY integer
);
INSERT INTO QUERY3
SELECT
S.NAME,
S.CITY,
P.NAME,
P.CITY,
J.NAME,
J.CITY,
SH.QTY
FROM
SUPPLIER
S,
PART P,
PROJECT
J,
SHIPMENT
SH
WHERE
SH.SID =
S.SID
AND
SH.PID =
P.PID
AND
SH.JID =
J.JID
AND
(
S.CITY
= P.CITY
OR
S.CITY
= J.CITY
OR
P.CITY
= J.CITY
)
AND
SH.QTY
>= 200
ORDER BY SH.QTY DESC;
SNAME SCITY PNAME
PCITY JNAME JCITY QTY
Adams
Smith
Adams
Jones
Adams
Clark
Clark
Adams
Jones
Blake
Smith
3) For each distinct shipping part, return the total quantities of those parts that had above-average quantity of shipments. The average is computed by taking the total of all quantities shipped, and dividing it by the total number of distinct shipped parts.
OUTPUT:
QUERY4
|
PID |
PNAME |
QTY |
QTY_MINUS_AVG |
|
|
|
|
|
CREATE TABLE QUERY4
(
PID nvarchar
(25),
PNAME nvarchar
(25),
QTY integer,
QTY_MINUS_AVG decimal
(10,1)
);
INSERT INTO QUERY4
SELECT
P.PID,
P.NAME,
SUM (SH.QTY) QTY,
SUM (SH.QTY) - MAX(COMPUTED_AVG.AVG_PARTS)
QTY_MINUS_AVG
FROM
PART P,
SHIPMENT SH,
(
SELECT
SUM (SH.QTY) / NULLIF (COUNT (DISTINCT
SH.PID), 0) AVG_PARTS
FROM
SHIPMENT
SH
)
COMPUTED_AVG
WHERE
SH.PID = P.PID
GROUP BY
P.PID,
P.NAME
HAVING
SUM (SH.QTY)
> MAX(COMPUTED_AVG.AVG_PARTS);
PID PNAME
QTY QTY_MINUS_AVG
P6 Cog 1300 14.3
P3 Screw 3700 2414.3
P4 Screw 1300 14.3
4) Get the names of suppliers (in descending order of their names) along with the number of shipments who had the most or least number of shipments. In the last column of the output, return a text ‘HIGHEST’ or ‘LOWEST’. (HINT: Look at the CASE or UNION syntax).
OUTPUT:
QUERY5
|
SID |
SNAME |
NUM_SHIPS |
HIGHEST_LOWEST |
|
|
|
|
|
CREATE TABLE QUERY5
(
SID nvarchar
(25),
SNAME nvarchar
(25),
NUM_SHIPS integer,
HIGHEST_LOWEST nvarchar
(25)
);
INSERT INTO QUERY5
SELECT
S.SID
SID,
S.NAME
SNAME,
COUNT
(SH.SID) CNT_SHIP,
'LOWEST'
FROM
SUPPLIER
S
LEFT
OUTER JOIN SHIPMENT SH ON
S.SID =
SH.SID
GROUP BY
S.SID,
S.NAME
HAVING
(
COUNT
(SH.SID)
=
(
SELECT
MIN
(CNT_SHIP) MIN_CNT
FROM
(
SELECT
S.SID
SID,
COUNT
(SH.SID) CNT_SHIP
FROM
SUPPLIER
S
LEFT
OUTER JOIN SHIPMENT SH ON
S.SID
= SH.SID
GROUP
BY
S.SID
)
SUP_TOT_SHIP
)
)
UNION ALL
SELECT
S.SID
SID,
S.NAME
SNAME,
COUNT
(SH.SID) CNT_SHIP,
'HIGHEST'
FROM
SUPPLIER
S
LEFT OUTER
JOIN SHIPMENT SH ON
S.SID =
SH.SID
GROUP BY
S.SID,
S.NAME
HAVING
(
COUNT
(SH.SID)
=
(
SELECT
MAX
(CNT_SHIP) MAX_CNT
FROM
(
SELECT
S.SID
SID,
COUNT
(SH.SID) CNT_SHIP
FROM
SUPPLIER
S
LEFT
OUTER JOIN SHIPMENT SH ON
S.SID
= SH.SID
GROUP
BY
S.SID
)
SUP_TOT_SHIP
)
)
ORDER BY S.NAME DESC;
SID SNAME NUM_SHIPS HOGHEST_LOWEST
S8 Mary 0 LOWEST
S7 Joe 0 LOWEST
S5 Adams 10 HIGHEST
SUBMISSION:
Please submit a paper
copy of all SQL statements and their results in class.
The printout
(MS-Word document) should contain all the DDL and DML used to create the
5-tables, and populating it from the 5-table SQL statements. The TA will be
checking your SQL Server account to see the appropriate tables along with its
appropriate data types and data.
For each of the
N queries, your doc should contain
b)
English
QueryN (as stated above)
c)
CREATE
TABLE statement for table QueryN
d)
INSERT
INTO ……… SELECT FROM ……… statement
e)
Resulting
QueryN table data.
Where N =
{1...5}
FOR PRACTICE
PROBLEMS, go to
http://www.cs.utexas.edu/users/ajay/2003/fall/cs327eExt/labs/lab5/lab5Solution.html
Consult the
class web page for
a) Late
submission policy
b) Academic
Dishonesty Policy
Suppliers-Parts-Projects Excel Spreadsheet [.zip file]
Last Updated: 4/4/2005 6:07:09 PM