《数据库系统原理教学课件》作业讲评.ppt

《《数据库系统原理教学课件》作业讲评.ppt》由会员分享，可在线阅读，更多相关《《数据库系统原理教学课件》作业讲评.ppt（40页珍藏版）》请在三一办公上搜索。

1、1,作业讲评,2,6.2.2 a,硬盘容量至少30G的笔记本电脑制造商及该电脑的速度SELECT maker,PC.model,speedFROM Product,Laptop WHERE Product.model=Laptop.model AND hd30,3,6.2.2 a,只生产至少30G硬盘笔记本电脑的制造商该电脑的速度(SELECT maker,Laptop.model,speedFROM Product,LaptopWHERE Product.model=Laptop.model AND hd=30)EXCEPT(SELECT maker,Laptop.model,speedFR

2、OM Product,LaptopWHERE Product.model=Laptop.model AND hd30),4,6.2.2 b,B生产的所有产品的型号和价格SELECT Product.model,priceFROM Product,Laptop,PC,PrinterWHERE maker=BAND(Product.model=Laptop.model ORProduct.model=PC.model ORProduct.model=Printer.model)WRONG,5,6.2.2 b,(SELECT Product.model,priceFROM Product,PCWHE

3、RE maker=BAND Product.model=PC.model)UNION(SELECT Product.model,priceFROM Product,LaptopWHERE maker=BAND Product.model=Laptop.model)UNION(SELECT Product.model,priceFROM Product,PrinterWHERE maker=BAND Product.model=Printer.model),6,6.2.2 c,只卖笔记本不卖PC的厂商(SELECT DISTINCT maker FROM Product WHERE type=L

4、aptop)EXCEPT(SELECT DISTINCT maker FROM Product WHERE type=PC),7,6.2.2 d,出现在两种或两种以上PC中硬盘的大小SELECT hdFROM PCGROUP BY hdHAVING COUNT(*)=2,8,6.2.2 d,出现在两种或两种以上PC中硬盘的大小SELECT hdFROM PC P1,PC P2WHERE P1.hd=P2.hd AND P1.modelP2.model?),9,6.2.2 e,每对具有相同速度和内存的PC型号SELECT P1.model,P2.modelFROM PC P1,PC P2WHER

5、E P1.speed=P2.speed AND P1.ram=P2.ram AND P1.modelP2.model,10,6.2.2 f,生产至少两种速度至少为3.0的电脑（PC或笔记本）的厂商,11,6.2.2 f,CREATE VIEW COM(maker,model)AS(SELECT maker,Product.model FROM Product,PC WHERE speed=3.0 AND Product.model=PC.model)UNION(SELECT maker,Product.Model FROM Product,Laptop WHERE speed=3.0 AND

6、Product.model=Laptop.model),12,6.2.2 f 续,SELECT makerFROM COMGROUP BY makerHAVING COUNT(*)=2,13,6.3.1 a,速度在3.0以上的PC的制造商SELECT DISTINCT makerFROM ProductWHERE model IN(SELECT model FROM PC WHERE speed 3.0),14,6.3.1 a,速度在3.0以上的PC的制造商SELECT DISTINCT makerFROM Product,PCWHERE speed 3.0 AND Product.model

7、=PC.model,15,6.3.1 b,价格最高的打印机SELECT modelFROM PrinterWHERE price in(SELECT MAX(price)FROM Printer),16,6.3.1 c,速度比任何一台PC都慢的笔记本SELECT modelFROM LaptopWHERE speed(SELECT MIN(speed)FROM PC),17,价格最高的产品的型号CREATE VIEW AM AS(SELECT model,price FROM PC)UNION(SELECT model,price FROM Laptop)UNION(SELECT model,

8、price FROM Printer);,18,6.3.1d 续,SELECT modelFROM AMWHERE price IN(SELECT MAX(price)FROM AM),19,价格最低的彩打制造商 select DISTINCT maker from Product,Printer where Product.model=Printer.model and price in(select min(price)from Printer where color=true)and color=true,20,内存容量最小而PC中速度最快者的制造商 create view ss（spe

9、ed）as select max(speed)from PC where ram in(select min(ram)from PC),21,内存容量最小而PC中速度最快者的制造商 select DISTINCT maker from Product,PC where Product.model=PC.model and speed in(select speed from ss)and ram in(select min(ram)from PC),22,内存容量最小且速度最快的PC制造商？select DISTINCT maker from Product,PC where Product.

10、model=PC.model and speed in(select max(speed)from PC)and ram in(select min(ram)from PC),23,6.4.6 a,PC的平均速度SELECT AVG(speed)FROM PC,24,6.4.6 b,价格高于1000的笔记本的平均速度SELECT AVG(price)FROM LaptopWHERE price1000,25,6.4.6 c,A生产的PC的平均价格select AVG(price)from Product,PC where Product.model=PC.model and maker=A,2

11、6,6.4.6 d-1,D生产的PC和笔记本的平均价格CREATE VIEW PRI(maker,model,price)AS(SELECT maker,Product.model,price FROM Product,PC WHERE Product.model=PC.model)UNION(SELECT maker,Product.model,price FROM Product,Laptop WHERE Product.model=Laptop.model);,27,6.4.6 d-2,select AVG(price)from PRIwhere maker=D,28,6.4.6 f,每

12、家厂商生产的笔记本屏幕尺寸的平均值SELECT AVG(screen)FROM Product,LaptopWHERE Product.model=Laptop.modelGROUP BY maker,29,6.4.6 g,至少生产3种不同型号PC的制造商SELECT makerFROM Product,PCWHERE Product.model=PC.modelGROUP BY makerHAVING COUNT(DISTINCT PC.model)=3,30,6.4.6 h,每个销售PC的厂商的PC最高价SELECT maker,MAX(price)FROM Product,PCWHERE

13、 Product.model=PC.modelGROUP BY maker,31,6.4.6 i,每种高于2.0速度的PC的平均价格SELECT AVG(price)FROM PCWHERE speed2.0GROUP BY speed,32,6.4.6 j,所有生产打印机的厂商生产的PC的硬盘平均大小SELECT AVG(hd)FROM Product,PCWHERE Product.model=PC.model AND maker IN(SELECT maker FROM Product WHERE type=printer)不能用3表同时连接做,33,INSERT INTO PRODUC

14、TVALUES(C,1100,pc)INSERT INTO PCVALUES(1100,3.2,1024,180,2499),34,删除所有硬盘小于100G的PCDELETE FROM PRODUCTWHERE model IN(SELECT model FROM PC WHERE hd=100)DELETE FROM PCWHERE hd=100,35,6.5.1 d-1,删除所有不生产打印机的厂商生产的笔记本DELETE FROM Laptop WHERE model IN(SELECT model FROM Product WHERE maker NOT IN(SELECT maker

15、FROM Product WHERE type=printer),36,6.5.1 d-2,DELETE FROM ProductWHERE maker NOT IN(SELECT maker FROM Product WHERE type=printer)AND type=laptop),37,UPDATE ProductSET maker=AWHERE maker=B,38,6.5.1 f,UPDATE PCSET ram=ram*2,hd=hd+60,39,把B生产的笔记本。UPDATE LaptopSET screen=screen+1,price=price-100WHERE model IN(SELECT model FROM Product WHERE maker=B),40,把B生产的笔记本。UPDATE LaptopSET screen=screen+1,price=price-100FROM Laptop,ProductWHERE Laptop.model=Product.model AND maker=B,