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

作业讲评 6.2.2 6.3.1 6.4.6 6.5.1 6.2.2 a 硬盘容量至少30G的笔记本电脑制造商及该电脑的速度 SELECT maker, PC.model, speed FROM Product, Laptop WHERE Product. model= Laptop.model AND hd30 6.2.2 a 只生产至少30G硬盘笔记本电脑的制造商该电脑的速度 ( SELECT maker, Laptop.model, speed FROM Product, Laptop WHERE Product. model= Laptop.model AND hd=30 ) EXCEPT ( SELECT maker, Laptop.model, speed FROM Product, Laptop WHERE Product. model= Laptop.model AND hd30 ) 6.2.2 b B生产的所有产品的型号和价格 SELECT Product.model, price FROM Product, Laptop, PC, Printer WHERE maker=‘B’AND ( Product. model= Laptop.model OR Product. model= PC.model OR Product. model= Printer.model ) WRONG 6.2.2 b ( SELECT Product.model, price FROM Product, PC WHERE maker=‘B’AND Product. model= PC.model ) UNION ( SELECT Product.model, price FROM Product, Laptop WHERE maker=‘B’AND Product. model= Laptop.model ) UNION ( SELECT Product.model, price FROM Product, Printer WHERE maker=‘B’AND Product. model= Printer.model ) 6.2.2 c 只卖笔记本不卖PC的厂商 ( SELECT DISTINCT maker FROM Product WHERE type=‘Laptop’ ) EXCEPT ( SELECT DISTINCT maker FROM Product WHERE type=‘PC’ ) 6.2.2 d 出现在两种或两种以上PC中硬盘的大小 SELECT hd FROM PC GROUP BY hd HAVING COUNT(*)=2 6.2.2 d 出现在两种或两种以上PC中硬盘的大小 SELECT hd FROM PC P1, PC P2 WHERE P1.hd=P2.hd AND P1.modelP2.model (P1.model P2.model?) 6.2.2 e 每对具有相同速度和内存的PC型号 SELECT P1.model, P2.model FROM PC P1, PC P2 WHERE P1.speed= P2.speed AND P1.ram= P2.ram AND P1.modelP2.model 6.2.2 f 生产至少两种速度至少为3.0的电脑（PC或笔记本）的厂商 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 Product. model= Laptop.model )) 6.2.2 f 续 SELECT maker FROM COM GROUP BY maker HAVING COUNT(*)=2 6.3.1 a 速度在3.0以上的PC的制造商 SELECT DISTINCT maker FROM Prod