FirstName

# Lab 3

Due: 2003-03-03 Monday 8.30 a.m. by email and in SQL Server.

PURPOSE:

The purpose of this lab is to learn about SQL statements using SQL Server 2000, by importing a new database from MS-Excel spreadsheet.

DESCRIPTION/IMPLEMENTATION:

A) Import data from CH5_SALE_CO.xls as shown below. Use SQLServer DTS facility to import data, just as you did in lab1. Choose appropriate data types e.g. you can assume (by looking at the data) that PRODUCT.P_CODE is CHAR (8) etc. Note that some column values are NULL. Verify visually, to make sure your data has been imported correctly in SQL Server. The schema for this database is shown on page 306 of the text.

B) Create all the PK and FK (including appropriate DELETE/UPDATE rules) by executing ALTER TABLE DDL statements. Use link # 11 on our class web page for appropriate SQL Server syntax.

C) Solve problems 18,20,25,28,31 from Chapter 5, pages 306 through 313 of the text. 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,

Chapter 5, Problem 27)

List the balance characteristics of the customers who have made purchases during the current invoice cycle i.e. for the customers who appear in the INVOICE table.

If Query 27 is shown as above,

Then execute the following CREATE TABLE statement in your SQL Server database account:

CREATE TABLE CHPTR5PROB27

(

CUS_CODE integer,

CUS_BALANCE decimal (9,2)

);

NOTE the appropriate data type selection.

Then, you will execute the following DML statement:

INSERT INTO CHPTR5PROB27

SELECT DISTINCT INVOICE.CUS_CODE, CUS_BALANCE

FROM CUSTOMER, INVOICE

WHERE INVOICE.CUS_CODE = CUSTOMER.CUS_CODE;

Resulting in CHPTR5PROB27 table being populated as

CHPTR5PROB27

CUS_CODE | CUS_BALANCE
10011 | 0.00
10012 | 345.86
10014 | 0.00
10015 | 00.0
10018 | 216.55

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 a MS-Word doc with all the DDL and DML statements:

1) All the ALTER TABLE statements for all PKs. Clearly write your assumptions.

2) All the ALTER TABLE statements for all FKs. Clearly write your assumptions.

3) All the CREATE TABLE and INSERT-SELECT statements for the 5 tables. The TA will be checking your SQL Server account to see the appropriate 10 tables. Clearly write your assumptions.

NOTE: For electronic submissions, use file-naming conventions such as lab3xxyy.zip, where xxyy are the last 4 digits of your SSN.

For example, if you are submitting a MS-Word document, and the last 4 digits of your SSN are 3456, then submit a file named lab33456.zip containing the file lab33456.doc.

Chapter 5 Sales Company Database

Last Updated: 2/18/2003 5:24:08 PM