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