FirstName

# Lab 3

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.

PRIMARY KEY Creation:

1) ALTER TABLE Customer ADD CONSTRAINT Customer_PK PRIMARY KEY (CUS_CODE);

2) ALTER TABLE Invoice ADD CONSTRAINT Invoice_PK PRIMARY KEY (INV_NUMBER);

3) ALTER TABLE Line ADD CONSTRAINT Line_PK PRIMARY KEY (INV_NUMBER, LINE_NUMBER);

4) ALTER TABLE Product ADD CONSTRAINT Product_PK PRIMARY KEY (P_CODE);

5) ALTER TABLE Vendor ADD CONSTRAINT Vendor_PK PRIMARY KEY (V_CODE);

FOREIGN KEY Creation:

1) ALTER TABLE Invoice ADD CONSTRAINT Invoice_Customer_FK FOREIGN KEY (CUS_CODE) REFERENCES CUSTOMER ON DELETE NO ACTION ON UPDATE NO ACTION;

2) ALTER TABLE Line ADD CONSTRAINT Line_Invoice_FK FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ON DELETE NO ACTION ON UPDATE NO ACTION;

3) ALTER TABLE Line ADD CONSTRAINT Line_Product_FK FOREIGN KEY (P_CODE) REFERENCES PRODUCT ON DELETE NO ACTION ON UPDATE NO ACTION;

4) ALTER TABLE Product ADD CONSTRAINT Product_Vendor_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR ON DELETE NO ACTION ON UPDATE NO ACTION;

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.

## CHPTR5PROB18

CREATE TABLE CHPTR5PROB18

(

CUS_CODE int,

INV_NUMBER int,

INV_DATE smalldatetime,

P_DESCRIPT nvarchar (255),

LINE_UNITS int,

LINE_PRICE money

);

INSERT INTO CHPTR5PROB18

SELECT CUS_CODE, LINE.INV_NUMBER, INV_DATE, P_DESCRIPT, LINE_UNITS, LINE_PRICE

FROM INVOICE, LINE, PRODUCT

WHERE

INVOICE.INV_NUMBER = LINE.INV_NUMBER

AND

LINE.P_CODE = PRODUCT.P_CODE

ORDER BY CUS_CODE;

10011 1002 2002-01-16 00:00:00 Rat-tail file, 1/8-in. fine 2 4.9900

10011 1004 2002-01-17 00:00:00 Rat-tail file, 1/8-in. fine 3 4.9900

10011 1004 2002-01-17 00:00:00 Claw hammer 2 9.9500

10011 1008 2002-01-17 00:00:00 PVC pipe, 3.5-in., 8-ft 5 5.8700

10011 1008 2002-01-17 00:00:00 Steel matting, 4'x8'x1/6", .5" mesh 3 119.9500

10011 1008 2002-01-17 00:00:00 Claw hammer 1 9.9500

10012 1003 2002-01-16 00:00:00 B&D cordless drill, 1/2-in. 1 38.9500

10012 1003 2002-01-16 00:00:00 Hrd. cloth, 1/4-in., 2x50 1 39.9500

10012 1003 2002-01-16 00:00:00 7.25-in. pwr. saw blade 5 14.9900

10014 1001 2002-01-16 00:00:00 7.25-in. pwr. saw blade 1 14.9900

10014 1001 2002-01-16 00:00:00 Claw hammer 1 9.9500

10014 1006 2002-01-17 00:00:00 1.25-in. metal screw, 25 3 6.9900

10014 1006 2002-01-17 00:00:00 B&D jigsaw, 12-in. blade 1 109.9200

10014 1006 2002-01-17 00:00:00 Claw hammer 1 9.9500

10014 1006 2002-01-17 00:00:00 Hicut chain saw, 16 in. 1 256.9900

10015 1007 2002-01-17 00:00:00 7.25-in. pwr. saw blade 2 14.9900

10015 1007 2002-01-17 00:00:00 Rat-tail file, 1/8-in. fine 1 4.9900

10018 1005 2002-01-17 00:00:00 PVC pipe, 3.5-in., 8-ft 12 5.8700

18 rows.

## CHPTR5PROB20

CREATE TABLE CHPTR5PROB20

(

CUS_CODE int,

CUS_BALANCE money,

TOTAL_PRICE money

);

INSERT INTO CHPTR5PROB20

SELECT CUSTOMER.CUS_CODE, CUS_BALANCE, SUM (LINE_UNITS*LINE_PRICE)

FROM CUSTOMER, INVOICE, LINE

WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE

AND

INVOICE.INV_NUMBER = LINE.INV_NUMBER

GROUP BY CUSTOMER.CUS_CODE, CUS_BALANCE;

10011 .0000 444.0000

10012 345.8600 153.8500

10014 .0000 422.7700

10015 .0000 34.9700

10018 216.5500 70.4400

5 rows.

## CHPTR5PROB25

CREATE TABLE CHPTR5PROB25

(

CUS_CODE int,

CNT_UNQ_INV_NUMBER int,

TOTAL_PRICE money

);

INSERT INTO CHPTR5PROB25

SELECT CUS_CODE, COUNT (DISTINCT LINE.INV_NUMBER), SUM (LINE_UNITS*LINE_PRICE)

FROM INVOICE, LINE

WHERE INVOICE.INV_NUMBER = LINE.INV_NUMBER

GROUP BY CUS_CODE

ORDER BY CUS_CODE;

10011 3 444.0000

10012 1 153.8500

10014 2 422.7700

10015 1 34.9700

10018 1 70.4400

5 rows.

## CHPTR5PROB28

CREATE TABLE CHPTR5PROB28

(

MIN_CUS_BALANCE money,

MAX_CUS_BALANCE money,

AVG_CUS_BALANCE money

);

INSERT INTO CHPTR5PROB28

SELECT MIN (CUS_BALANCE), MAX (CUS_BALANCE), AVG (CUS_BALANCE)

FROM CUSTOMER

WHERE CUSTOMER.CUS_CODE IN

(

SELECT DISTINCT INVOICE.CUS_CODE

FROM INVOICE

);

.0000 345.8600 112.4820

1 row.

## CHPTR5PROB31

CREATE TABLE CHPTR5PROB31

(

TOTAL_CUS_BALANCE money,

MIN_CUS_BALANCE money,

MAX_CUS_BALANCE money,

AVG_CUS_BALANCE money

);

INSERT INTO CHPTR5PROB31

SELECT SUM (CUS_BALANCE), MIN (CUS_BALANCE), MAX (CUS_BALANCE), AVG (CUS_BALANCE)

FROM CUSTOMER

WHERE CUS_CODE NOT IN

(

SELECT DISTINCT CUS_CODE FROM INVOICE

);

1526.8700 .0000 768.9300 305.3740

1 row.

Last Updated: 4/6/2003 4:49:28 PM