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