sql语句关系代数练习

对于表结构：

product(maker,model,type)

maker:表示生产厂商

model：生产的产品型号

type：产品类型，有pc laptop printer三种

pc(model,speed,ram,hd,price)

表示型号，速度，内存大小，硬盘大小，价格

laptop(model,speed,ram,hd,screen,price)

表示型号，速度，内存大小，硬盘大小，屏幕大小和价格

printer（model,color,type,price)

model：打印机型号；

color：是否彩色， T 彩色，F 黑白

type:类型，ink-jet 表示喷墨， laser 表示激光；

price：单价

这是一套难度大得不溜秋的题嘤嘤嘤，本人水平有限，对语句的理解还不够深，语句不一定都是最优解，有的题目有多种解，如有疑问错误欢迎指出~

查询生产pc也生产laptop的厂商

select product.maker
from product 
join laptop
on product.model = laptop.model
intersect
select product.maker
from product 
join pc
on product.model = pc.model

查询生产型号为2001的厂商信息及该型号所属产品的种类

select maker,type from product where model='2001'

查询厂商A 生产的PC中price大于900的产品型号

select product.model
from product
inner join pc
on product.model=pc.model
where pc.price>900 and product.maker='A'

查询厂商B生产的所有产品的型号和价格

select product.maker,printer.price
from product
inner join printer
on product.model = printer.model
where printer.type = 'ink-jet'

分类统计厂商（maker）A生产的各种产品的数量

select type,count(model) from product where maker='A' group by type

查询所有彩色激光打印机的生产商及型号

select a.maker,a.model
from product a
inner join printer b
on a.model = b.model
where b.color = 'T' and b.type = 'laser'

找出生产产品型号最多的厂商

select top 1 maker from product group by maker order by count(*) desc

查询出哪个生产厂商的笔记本电脑的硬盘容量至少100G

select a.maker
from product a
inner join laptop b
on a.model = b.model
group by a.maker
having min(b.hd)>=100

找出那些既出售笔记本电脑又出售PC的厂商

select product.maker
from product
inner join pc
on product.model = pc.model
group by product.maker
intersect
select product.maker
from product
inner join laptop
on product.model = laptop.model

查询具有同样处理速度和同样内存大小的PC对

显示满足条件的pc对的型号,同样的pc对只出现1次，如001 与 002 符合条件，则仅出现001 002，不出现002 001

select a.model,b.model
from pc a,pc b
where a.speed=b.speed and a.ram=b.ram and a.model<>b.model and a.model<b.model

查询至少生产三种不同处理速度电脑（含pc和laptop）的厂商

select maker from
(
	select maker, product.model, speed from 
	product join pc on product.model = pc.model 
	union
	select maker, product.model, speed from 
	product join laptop on product.model = laptop.model
) as a
group by maker
having count(distinct speed) >= 3;

统计出pc，laptop，printer三种产品的不同型号数量，并按数量从大到小排序

select type, count(model) as c from
(
	select type, product.model as model from 
	product join pc on product.model = pc.model 
	union
	select type, product.model as model
	from product join laptop on product.model = laptop.model
	union 
	select product.type, product.model as model
	from product join printer on product.model = printer.model
) as a
group by type
order by c desc;

有客户有1500元钱，买laptop或pc，要求硬盘容量不小于80，请给出可能的产品型号，生产厂商

select product.maker,product.model from product
inner join laptop on product.model = laptop.model where laptop.hd >=80 and laptop.price <=1500
union
select product.maker,product.model from product
inner join pc on product.model = pc.model where pc.hd >=80 and pc.price <=1500

找出至少生产5种不同型号产品的厂商

select maker from product
group by maker
having count(*)>=5

完成功能使得：厂商A的产品升级，所有pc速度提高100，硬盘容量增加50，价格上调300

Update pc SET pc.speed = pc.speed +100,pc.hd=pc.hd+50,pc.price=pc.price+300
FROM product INNER JOIN pc ON product.model=pc.model
where product.maker='A'

完成功能：厂商E新增一种pc产品，型号1090，速度200，内存128，硬盘160，价格800

insert into pc values ("1090",200,128,160,800);

insert into product values ("E","1090","pc");

完成功能：厂商C生产的laptop，型号2013停产，需要从相关关系种去除

delete a
from product a
inner join laptop b
on a.model = b.model
where a.maker='C' and a.model = '2013'

完成功能：厂商A生产的产品1001型号变更为1091，其余信息不变

Update b SET b.model = '1091'
FROM product a INNER JOIN pc b
ON a.model=b.model
where a.maker='A' and b.model = '1001';

Update a SET a.model = '1091'
FROM product a 
where a.model = '1001';

厂商H破产，所有其生产的产品需移除

delete b from product a inner join pc b
on a.model = b.model
where a.maker = 'H';
delete b from product a inner join laptop b
on a.model = b.model
where a.maker = 'H';
delete b from product a inner join printer b
on a.model = b.model
where a.maker = 'H';

delete a from product a
where a.maker ='H';