4．使用SQL语言实现上述计算，并使输出结果不重复。(8) 5．使用SQL语言完成下面的计算：(8)
1) 计算2001年12月签订的所有订单的订单号、签单日期、销售数量和销售金额，
并按销售金额从小到大排列。
2) 计算2001年12月签订的销售金额最大的订单号、销售员姓名和销售金额。(注意
利用已有查询定义视图)
3) 计算2001年12月所有销售员的员工号、姓名及其销售金额。(注意使用外连接，
没有订单的销售员的销售金额应为0)
求员工号为“S0025”的销售员的部门经理的员工号及其姓名
product(maker,model,type) pc(model,speed,ram,hd,cd,price) laptop(model,speed,ram,hd,screen,price) printer(model, color,type,price)
p187 4.1.1
1. What PC models have a speed of at least 150? ∏model(бspeed>=150(PC))
2. Which manufacturers make laptops with a hard disk of at least one gigabyte? ∏maker(бhd>=1(Laptop) //∏maker(Laptop Product)
Product)
Product) - ∏maker(бhd<1(Laptop) 3. Find the model number and price of all products(of any type) made by manufacturer B. ∏model,price(бmaker=’B’(PC ∏model,price(бmaker=’B’(Laptop ∏printer.model,price(бmaker=’B’(Printer Product))∪
Product))∪
printer.model=product.model Product))
29
4. Find the model numbers of all color laser printers. ∏model(бtype=’laser’ and color(Printer))
5. Find those manufacturers that sell Laptops, but not PC’s. ∏maker(Laptop Product) - ∏maker(PC Product)
6. Find those hard-disk sizes that occur in two or more PC’s. ∏pc1.hd(ρpc1(PC) pc1.model<>pc.model and pc1.hd=pc.hd PC)
7. Find those pairs of PC models that have both the same speed and RAM. ∏pc1.model,pc.model(ρpc1(PC) pc1.model 8. Find those manufacturers of at least two different computers (PC’s or laptops) with speeds of at least 133. P2:=P1:= ∏maker,model(бspeed>=133(PC) ∏maker,model(бspeed>=133(Laptop) Answer:=∏P1.maker(P1 Product)∪ Product) P1.model<>P2.model and P1.maker=P2.maker P2) 9. Find the manufacturer(s) of the computer (PC or laptop) with the highest available speed. P2:=P1:= ∏model,speed(PC)∪∏model,speed(Laptop) Answer:=∏maker(Product (∏model(P1) - ∏P1.model(P1 P1.speed 10. Find the manufacturers of PC’s with at least three different speeds. P3:=P2:=P1:= ∏maker,speed(Product Answer:=∏P1.maker((P1 PC) P1.speed P2.speed 11. Find the manufacturers who sell exactly three different models of PC. P4:=P3:=P2:=P1:= ∏maker,model(Product PC) 30 M1(maker,m1,m2,m3):=∏P1.maker,P1.model,P2.model,P3.model((P1 P2.model and P1.maker=P2.maker P2) P1.model< P2.model Answer:=∏maker(M1) - ∏M1.maker(M1 P253 5.1.3 M1.m3 a) Find the model number, speed, and hard-disk size for all PC’s whose price is under $1600. select model,speed,hd from pc where price<1600; b) Do the same as (a), but rename the speed column megahertz and the hd column gigabytes. select model,speed as megahertz, hd as gigabytes from pc where price<1600; c) Find the manufacturers of printers. select maker from product where type=’printer’; d) Find the model number, memory size, and screen size for laptops costing more than $2000. select model,ram,screen from laptop where price>=2000; e) Find all the tuples in the Printer relation for color printers. select * from printer where color; f) Find the model number, speed, and hard-disk size for those PC’s that have either a 6x or 8x CD and a 31 price less than $2000. select model,speed,hd from pc where price<2000 and (cd=’6x’ or cd=’8x’); p262 5.2.2 a) Give the manufacturer and speed of laptops with a hard disk of at least one gigabyte? Select laptop.model,maker,speed From laptop, product Where laptop.model=product.model and hd>1; b) Find the model number and price of all products (of any type) made by manufacturer B. (Select product.model,price,’pc’ as type1 From product,pc Where maker=’B’ and product.model=pc.model ) Union (Select product.model,price,’laptop’ From product,laptop Where maker=’B’ and product.model=laptop.model ) Union (Select product.model,price,’printer’ From product,printer Where maker=’B’ and product.model=printer.model ) c) Find those manufacturers that sell Laptops, but not PC’s. Select maker From product,laptop Where product.model=laptop.model and maker not in ( 32