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)∪
Pro