select maker,speed
from product inner join laptop on product.model=laptop.model
where hd>=30
--b)查询制造商 B 生产的任意类型的所有产品的型号和价格。
select product.model,price from product,pc where product.model=pc.model and maker='B'
union
select product.model,price from product,laptop where product.model=laptop.model and
maker='B'
union
select product.model,price from product,printer where product.model=printer.model and
maker='B'
--c)查询卖笔记本电脑不卖 PC 的厂商。
select maker from product where type='laptop'
except
select maker from product where type='pc'
--!d)查询出现在两种或两种以上 PC 中硬盘的大小。
select pc1.model,pc1.hd,pc2.hd,pc2.model
from pc as pc1,pc as pc2
where pc1.hd=pc2.hd
and pc1.model<>pc2.model
--!e)查询每对具有相同速度和 RAM 容量的 PC 型号。每一对只能列出一次,例如若(i,j)
已被列出,则(j,i)就不能再被列出。
select pc1.model,pc1.speed,pc1.ram,pc2.ram,pc2.speed,pc2.model
from pc as pc1,pc as pc2
where pc1.speed=pc2.speed
and pc1.ram=pc2.ram
and pc1.model>pc2.model
--!!f)查询生产至少两种速度至少 3.0 的电脑(PC 或笔记本电脑)的厂商。
--步 1:找出速度至少 3.0 电脑(PC 或笔记本电脑)的型号、速度和厂商
create view R as
select pc.model,speed,maker from pc,product where pc.model=product.model and
speed>=3.0
union
select laptop.model,speed,maker from laptop,product where laptop.model=product.model and
speed>=3.0
--步 2:找出生产至少两种上述电脑的厂商
--select r1.model,r1.maker,r2.maker,r2.model
select R1.maker
from R as R1,R as R2