FirstName

# CS327e

# Lab 5

Fall 2003

Due: 2003-11-06 Thursday 6.00 p.m. by email and in SQL Server.

PURPOSE:

The purpose of this lab is to learn about SQL statements using SQL Server 2000, using the SPJ tables in the attached Excel spreadsheet for lab 1.

DESCRIPTION/IMPLEMENTATION:

A) Use the data in the Excel spreadsheet in lab1 and import it into SQL Server. For each of the 4 tables PART, SUPPLIER, PROJECT, 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 10 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 datatype 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 | 3500.0

# QUERIES (10 points each)

NOTE: “Supplies” and “Ships” are synonymous.

1) Get part numbers for parts shipped by more than one supplier.

2) Get distinct supplier names of suppliers who ship red parts, sorted in ascending order.

3) Get distinct supplier names for suppliers who do not ship part P2.

4) Get distinct part numbers for parts that either weigh more than 14 pounds or are shipped by supplier S2, or both. HINT: Use UNION.

5) Get the names of suppliers and projects who ship more parts in the project than the average quantity for that project.

6) Get all pairs of city names such that a supplier in the first city supplies a project in the second city, where the 2 cities are not the same.

7) Get project numbers for projects supplied by at least one supplier not in the same city.

8) Get supplier numbers for suppliers with a status lower than that of supplier S4.

9) Get project numbers for projects supplied entirely by supplier S1.

10) Get unique part names in descending order for parts shipped to all projects in Athens.

SUBMISSION:

Please submit an electronic zipped file by emailing it to the TA (CC: the instructor) by the Due Date/Time. Consult the class web page for

a) Email addresses of Instructor/TA

b) Late submission policy

c) Academic Dishonesty Policy

The zipped file should contain all the DDL and DML used to create the 10-tables, and populating it from the 10-table SQL statements. The TA will be checking your SQL Server account to see the appropriate tables.

For each of the 10 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..10}

NOTE: For electronic submissions, use file naming conventions such as lab5fffllll.zip, lab5fffllll.mdb etc., where fff are the first 3 characters of your first name, llll are the last 4 characters of your last name.

For example, if I were submitting a MS-Word solution, then I would email a file named lab5ajagava.zip containing the file lab5ajagava.doc

Last Updated: 10/14/2003 3:40:50 PM