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