数据库习题3-19答案

1、u ANSWERS TO JAMES RIVER JEWELRY PROJECT QUESTIONS数据库JAMES_RIVER_JEWELRY.包括下述4张表:CUSTOMER (CustomerID, LastName, FirstName, Phone, Email)PURCHASE (InvoiceNumber, Date, PreTaxAmount, CustomerID)PURCHASE_ITEM (InvoiceNumber, ItemNumber, RetailPrice)ITEM (ItemNumber, Description, Cost, ArtistName)其中参照完

2、整性约束如下：CustomerID in PURCHASE must exist in CustomerID in CUSTOMERInvoiceNumber in PURCHASE_ITEM must exist in InvoiceNumber in PURCHASEItemNumber in PURCHASE_ITEM must exist in ItemNumber in ITEM假设 CustomerID of CUSTOMER, ItemNumber of ITEM, InvoiceNumber of PURCHASE, and ItemNumber of PURCHASE_ITE

3、M 都是代理键，具有如下值:CustomerID Start at 1 Increment by 1ItemNumber Start at 1 Increment by 1InvoiceNumber Start at 1 Increment by 1ItemNumber Start at 1 Increment by 1部分数据如P87 图2-30所示。上面的数据并不完整，我们将在后面练习过程中增加数据，后面的练习使用SQLSERVER 2008，数据库命名为 JAMES_RIVER_JEWELRY.。A 用CREATE TABLE语句创建上述4个表，不包含参照完整性约束.For SQL Se

4、rver 2008: CREATE TABLE CUSTOMER(CustomerIDIntNOT NULL IDENTITY(1, 1),LastNameChar(35)NOT NULL,FirstNameChar(25)NOT NULL,PhoneChar(12)NULL,EmailVarChar(100)NULL,CONSTRAINTCUSTOMER_PKPRIMARY KEY(CustomerID);CREATE TABLE ITEM(ItemNumberIntNOT NULL IDENTITY(1,1),DescriptionVarChar(255)NOT NULL,CostNume

5、ric(9,2)NOT NULL,ArtistNameChar(50)NULL,CONSTRAINTITEM_PKPRIMARY KEY(ItemNumber);CREATE TABLE PURCHASE (InvoiceNumberIntNOT NULL IDENTITY (1,1),DateDateTimeNOT NULL,PreTaxAmountNumeric(9,2)NOT NULL,CustomerIDIntNOT NULL,CONSTRAINTPURCHASE_PKPRIMARY KEY(InvoiceNumber); Date用方括号是由于Date在SQLSERVER是关键字，不

6、用括号也能正常运行。CREATE TABLE PURCHASE_ITEM (InvoiceNumberIntNOT NULL,ItemNumberIntNOT NULL,RetailPriceNumeric(9,2)NOT NULL,CONSTRAINT PURCHASE_ITEM_PKPRIMARY KEY (InvoiceNumber, ItemNumber);B 添加参照完整性约束For SQL Server 2008: ALTER TABLE PURCHASEADD CONSTRAINT PURCHASE_CUSTOMER_FKFOREIGN KEY (CustomerID) REFE

7、RENCES CUSTOMER(CustomerID)ON DELETE NO ACTION;CustomerID没有级联删除是因为我们不希望再删除客户信息时删除其订单记录。ALTER TABLE PURCHASE_ITEMADD CONSTRAINT PURCHASE_ITEM_PURCHASE_FKFOREIGN KEY (InvoiceNumber) REFERENCES PURCHASE(InvoiceNumber)ON DELETE CASCADE;当订单被删除时，该订单包含的订单项也应被删除。ALTER TABLE PURCHASE_ITEMADD CONSTRAINT PURCH

8、ASE_ITEM_ITEM_FKFOREIGN KEY (ItemNumber) REFERENCES ITEM(ItemNumber)ON DELETE NO ACTION;当某个商品被删除时，以前订单中该项商品的交易记录不应删除。对于上述三个约束均未设置级联更新的原因是因为他们都是代理键。在创建表的同时创建参照完整性约束的SQL语句：CREATE TABLE PURCHASE(InvoiceNumberIntNOT NULL IDENTITY (1,1),DateDateTimeNOT NULL,PreTaxAmountNumeric(9,2)NOT NULL,CustomerIDIntN

9、OT NULL,CONSTRAINTPURCHASE_PKPRIMARY KEY(InvoiceNumber),CONSTRAINT PURCHASE_CUSTOMER_FK FOREIGN KEY (CustomerID)REFERENCES CUSTOMER(CustomerID)ON DELETE NO ACTION);CREATE TABLE PURCHASE_ITEM (InvoiceNumberIntNOT NULL,ItemNumberIntNOT NULL,RetailPriceNumeric(9,2)NOT NULL,CONSTRAINT PURCHASE_ITEM_PKPR

10、IMARY KEY (InvoiceNumber, ItemNumber),CONSTRAINTPURCHASE_ITEM_PURCHASE_FKFOREIGN KEY (InvoiceNumber)REFERENCES PURCHASE(InvoiceNumber)ON DELETE CASCADE,CONSTRAINTPURCHASE_ITEM_ITEM_FK FOREIGN KEY (ItemNumber)REFERENCES ITEM(ItemNumber)ON DELETE NO ACTION);For Access:CREATE TABLE CUSTOMER(CustomerIDI

11、ntNOT NULL,LastNameChar(35)NOT NULL,FirstNameChar(25)NOT NULL,PhoneChar(12)NULL,EmailVarChar(100)NULL,CONSTRAINTCUSTOMER_PKPRIMARY KEY(CustomerID);CREATE TABLE ITEM(ItemNumberIntNOT NULL,DescriptionVarChar(255)NOT NULL,CostNumericNOT NULL,ArtistNameChar(50)NULL,CONSTRAINTITEM_PKPRIMARY KEY(ItemNumbe

12、r);CREATE TABLE PURCHASE(InvoiceNumberIntNOT NULL,PurchaseDateDateTimeNOT NULL,PreTaxAmountNumericNOT NULL,CustomerIDIntNOT NULL,CONSTRAINTPURCHASE_PKPRIMARY KEY(InvoiceNumber),CONSTRAINT PURCHASE_CUSTOMER_FK FOREIGN KEY (CustomerID)REFERENCES CUSTOMER(CustomerID);CREATE TABLE PURCHASE_ITEM (Invoice

13、NumberIntNOT NULL,ItemNumberIntNOT NULL,RetailPriceNumericNOT NULL,CONSTRAINT PURCHASE_ITEM_PKPRIMARY KEY (InvoiceNumber, ItemNumber),CONSTRAINTPURCHASE_ITEM_PURCHASE_FKFOREIGN KEY (InvoiceNumber)REFERENCES PURCHASE(InvoiceNumber),CONSTRAINTPURCHASE_ITEM_ITEM_FK FOREIGN KEY (ItemNumber)REFERENCES IT

14、EM(ItemNumber);C 使用SQL语句在表中添加数据，数据如图2-30.由于存在中参照完整性约束，因此对上述表进行数据插入应按照如下顺序：CUSTOMERNo foreign keysITEMNo foreign keysPURCHASEForeign key:CustomerID referencesCustomerID in CUSTOMERPURCHASE_ITEMForeign Keys:InvoiceNumber referencesInvoiceNumber in PURCHASEItemNumber referencesItemNumber in ITEMl CUSTO

15、MER表的数据INSERT INTO CUSTOMER VALUES('Stanley', 'Elizabeth','555-236-7789', 'ES');INSERT INTO CUSTOMER VALUES('Price', 'Fred', '555-236-0091', 'FP');INSERT INTO CUSTOMER VALUES('Becky', 'Linda', '555-236-0392', 

16、9;LB');INSERT INTO CUSTOMER VALUES('Birch', 'Pamela', '555-236-4493', 'PB');INSERT INTO CUSTOMER VALUES('Romez', 'Ricardo', '555-236-3334', 'RR');INSERT INTO CUSTOMER VALUES('Jackson', 'Samantha', '555-236-1095&#

17、39;, 'SJ');l ITEM表的数据INSERT INTO ITEM VALUES('Gold Bracelet', 120.00, 'Mary Josephson');INSERT INTO ITEM VALUES('Gold Necklace', 160.00, 'Baker');INSERT INTO ITEM VALUES('Bead Earrings', 50.00, 'Mary Josephson');INSERT INTO ITEM VALUES('Gol

18、d Bracelet', 180.00, 'Baker');INSERT INTO ITEM VALUES('Silver Necklace', 135.00, 'Baxter');INSERT INTO ITEM VALUES('Bead Earrings', 25.00, 'Mary Josephson');INSERT INTO ITEM VALUES('Bead Earrings', 22.50, 'Mary Josephson');INSERT INTO ITEM

19、VALUES('Gold Earrings', 50.00, 'John Lintz');INSERT INTO ITEM VALUES('Gold Necklace', 160.00, 'John Lintz');INSERT INTO ITEM VALUES('Bead Earrings', 20.00, 'Mary Josephson');INSERT INTO ITEM VALUES('Bead Earrings', 35.00, 'Mary Josephson

20、9;);INSERT INTO ITEM VALUES('Bead Earrings', 45.00, 'Mary Josephson');INSERT INTO ITEM VALUES('Gold Necklace', 225.00, 'John Lintz');INSERT INTO ITEM VALUES('Silver Earrings', 55.00, 'John Lintz');INSERT INTO ITEM VALUES('Gold Bracelet', 200.00

21、, 'John Lintz');l PURCHASE表的数据：INSERT INTO PURCHASE VALUES('06-04-05', 155.00, 1);INSERT INTO PURCHASE VALUES('06-04-07', 203.00, 2);INSERT INTO PURCHASE VALUES('06-04-11', 75.00, 3);INSERT INTO PURCHASE VALUES('06-04-15', 67.00, 4);INSERT INTO PURCHASE VALUES

22、('06-04-15', 330.00, 5);INSERT INTO PURCHASE VALUES('06-04-16', 25.00, 1);INSERT INTO PURCHASE VALUES('06-04-16', 45.00, 3);INSERT INTO PURCHASE VALUES('06-04-18', 445.00, 1);INSERT INTO PURCHASE VALUES('06-04-19', 72.00, 6);l PURCHASE_ITEM表的数据INSERT INTO PURC

23、HASE_ITEM VALUES(1, 1, 155.00);INSERT INTO PURCHASE_ITEM VALUES(2, 2, 120.00);INSERT INTO PURCHASE_ITEM VALUES(3, 3, 75.00);INSERT INTO PURCHASE_ITEM VALUES(4, 6, 35.00);INSERT INTO PURCHASE_ITEM VALUES(4, 7, 32.00);INSERT INTO PURCHASE_ITEM VALUES(5, 4, 240.00);INSERT INTO PURCHASE_ITEM VALUES(5, 8

24、, 90.00);INSERT INTO PURCHASE_ITEM VALUES(6, 10, 25.00);INSERT INTO PURCHASE_ITEM VALUES(7, 11, 45.00);INSERT INTO PURCHASE_ITEM VALUES(8, 5, 175.00);INSERT INTO PURCHASE_ITEM VALUES(8, 9, 215.00);INSERT INTO PURCHASE_ITEM VALUES(8, 12, 55.00);INSERT INTO PURCHASE_ITEM VALUES(9, 14, 72.00);D 用SQL语句显

25、示所有表的所有列./* CUSTOMER DATA */SELECT * FROM CUSTOMER;/* ITEM DATA */SELECT * FROM ITEM;/* PURCHASE DATA */SELECT * FROM PURCHASE;/* PURCHASE_ITEM */SELECT * FROM PURCHASE_ITEM;E 用SQL语句列出所有花费超过$100的珠宝的ItemNumber和Description.SELECTItemNumber, DescriptionFROMITEMWHERECost > 100;F 用SQL语句列出所有花费超过$100且Ar

26、tistName以 son结尾的珠宝的ItemNumber和DescriptionFor Access: SELECTItemNumber, DescriptionFROMITEMWHERECost > 100ANDArtistName LIKE '*son*'For SQL Server 2008 and MySQL: SELECTItemNumber, DescriptionFROMITEMWHERECost > 100ANDArtistName LIKE '%son'G 用子查询SQL语句显示所购商品中至少一件的PreTaxAmount 超过$

27、200的客户的LastName 和 FirstName SELECTLastName, FirstNameFROMCUSTOMERWHERECustomerID IN(SELECTCustomerID FROMPURCHASE WHEREPreTaxAmount > 200);H 用 连接查询完成问题G.SELECTLastName, FirstNameFROMCUSTOMER, PURCHASEWHERECUSTOMER.CustomerID = PURCHASE.CustomerIDANDPreTaxAmount > 200;Note: 和G的结果一致但顺序不同。I W用子查询

28、SQL语句显示所购买珠宝单价（RetailPrice）超过$50的客户的LastName 和 FirstName .SELECTLastName, FirstNameFROMCUSTOMERWHERECustomerID IN(SELECTCustomerID FROMPURCHASE WHEREInvoiceNumber IN(SELECTInvoiceNumber FROMPURCHASE_ITEM WHERERetailPrice > 50);J 用连接回答问题I.SELECTDISTINCT LastName, FirstNameFROMCUSTOMER, PURCHASE, P

29、URCHASE_ITEMWHERECUSTOMER.CustomerID = PURCHASE.CustomerIDANDPURCHASE.InvoiceNumber = PURCHASE_ITEM.InvoiceNumberANDRetailPrice > 50;Note: 需要使用 DISTINCT 关键字.K 用子查询SQL语句显示购买ArtistName以字母J开头开头的珠宝的所有客户的LastName 和 FirstName。For Access: SELECTLastName, FirstNameFROMCUSTOMERWHERECustomerID IN(SELECTCus

30、tomerID FROMPURCHASE WHEREInvoiceNumber IN(SELECTInvoiceNumber FROMPURCHASE_ITEM WHEREItemNumber IN(SELECTItemNumber FROMITEM WHEREArtistName LIKE 'J*');For SQL Server 2008 and MySQL: SELECTLastName, FirstNameFROMCUSTOMERWHERECustomerID IN(SELECTCustomerID FROMPURCHASE WHEREInvoiceNumber IN(

31、SELECTInvoiceNumber FROMPURCHASE_ITEM WHEREItemNumber IN(SELECTItemNumber FROMITEM WHEREArtistName LIKE 'J%');L 用连接回答问题K.For Access: SELECTLastName, FirstNameFROMCUSTOMER, PURCHASE, PURCHASE_ITEM, ITEMWHERECUSTOMER.CustomerID = PURCHASE.CustomerIDANDPURCHASE.InvoiceNumber = PURCHASE_ITEM.Inv

32、oiceNumberANDPURCHASE_ITEM.ItemNumber = ITEM.ItemNumberANDArtistName LIKE 'J*'For SQL Server 2008 and MySQL: SELECTDISTINCT LastName, FirstNameFROMCUSTOMER, PURCHASE, PURCHASE_ITEM, ITEMWHERECUSTOMER.CustomerID = PURCHASE.CustomerIDANDPURCHASE.InvoiceNumber = PURCHASE_ITEM.InvoiceNumberANDPU

33、RCHASE_ITEM.ItemNumber = ITEM.ItemNumberANDArtistName LIKE 'J%'M 用SQL语句列出每个客户的LastName、FirstName和PreTaxAmount 的总和。.SELECTLastName, FirstName, SUM(PreTaxAmount) AS TotalPreTaxAmountFROMCUSTOMER, PURCHASEWHERECUSTOMER.CustomerID = PURCHASE.CustomerIDGROUP BYLastName, FirstName;N 用SQL语句为每个 Arti

34、stName统计PreTaxAmount 的总和，按 ArtistName降序排列。SELECTArtistName, SUM(PreTaxAmount) AS TotalPreTaxAmountFROMITEM, PURCHASE_ITEM, PURCHASEWHEREITEM.ItemNumber = PURCHASE_ITEM.ItemNumberANDPURCHASE_ITEM.InvoiceNumber = PURCHASE.InvoiceNumberGROUP BYArtistNameORDER BYArtistName DESC;O 用SQL语句为每个 ArtistName统计PreTaxAmount 的总和，但排除PreTaxAmount小