cs327e Lab4

# Lab 4

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
Cam | 1100.0
Screw | 3700.0

# QUERIES (20 points each)

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 Cam 4 1100 13200.0 .17

P6 Cog 4 1300 24700.0 .41

P7 Cam 0 0 .0 NULL

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 Athens Screw London Console Athens 800

Smith London Nut London Console Athens 700

Adams Athens Cog London Console Athens 500

Jones Paris Screw Rome Sorter Paris 400

Adams Athens Cam Paris Console Athens 400

Clark London Cog London OCR Athens 300

Clark London Cog London Tape London 300

Adams Athens Screw Rome Console Athens 200

Jones Paris Screw Rome Display Rome 200

Blake Paris Screw Rome Sorter Paris 200

Smith London Nut London Sorter Paris 200

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