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 (displayed in ascending order by part ID), get its

1. ID & 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

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

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

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

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

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: 3/29/2005 11:19:19 AM