数据库作业2：SQL

作业2：SQL

姓名	学号	班号
姚元淇	1180500105	1803106

第1题回答如下：

(a)

select maker 
from Product 
where type = 'pc' 
except 
select maker 
from Product 
where type = 'laptop';

(b)

select distinct A.maker 
from product as A 
left join product as B on A.type = 'pc' and B.type = 'laptop' and A.maker = B.maker 
where A.type = 'pc' and B.maker is null;

©
解1：

select distinct maker 
from Product as S 
where maker in (select maker from Product where type  = 'pc') and maker not in (select maker from Product where type = 'laptop');

解题思路：
找到生产商中对应生产的type为pc但不对应laptop的生产商即为所求。

解2：

select maker 
from (select maker from (select distinct maker,type from product where type='pc' or type='laptop') as A 
group by maker having count(*)=1) as B 
where maker not in (select maker from product where type='laptop');

解题思路：
A为投影满足生产pc或laptop的不重复的maker和type属性，对A关于maker分组并找到分组结果中计数count值为1的元组，记为B，B即为只生产pc或laptop的厂商。此时对B的结果进行嵌套查询，找到B中元组不在生产laptop的厂商中的元组，即为只生产pc不生产laptop的厂商。

(d)

select distinct maker 
from product as A 
where A.type = 'pc' and not exists (select * from (select maker from product where type='laptop' ) as B 
where B.maker =A.maker);

解题思路：
A和B分别为生产pc和生产laptop的厂商，找到A中不满足与B的生产商相同的元组即可。

第2题回答如下：

(a)

select printer.model 
from printer 
join (select * from printer where model = 3002) as com_p 
on printer.price > com_p.price ;

(b)

select model 
from printer 
where price > (select price from printer where model = 3002);

©

select model 
from printer as A 
where exists (select * from printer as B where B.model=3002 and A.price > B.price);

第3题回答如下：

(a)

select distinct com_p.model 
from pc 
right join pc as com_p 
on pc.model != com_p.model and pc.speed < com_p.speed 
where pc.model is null;

解题思路：
右外连接意找到满足条件的join左边关系与全部join右边关系内元组的连接，因此对于关系pc，当其与重命名后的自身右外连接时，由于对于最小的speed值，其在pc关系中找不到满足比其值更小的model，因此会在对应拥有最小的speed的com_p的pc元组中存在为null的model属性值，这时投影出对应的com_p.model即可。

(b)

select model 
from pc 
where speed in (select speed from(select speed from pc order by speed limit 1) as T);

©

select model 
from pc 
where speed <= all (select speed from pc );

(d)

select model 
from pc as A 
where not exists (select * from pc as B where B.speed < A.speed);

第4题回答如下：

(a)

纯用内连接解法：

select distinct maker 
from (select C.maker ,C.m1,C.m2,D.model from product as D 
join (select A.maker ,A.model as m1,B.model as m2 from product as A 
join product as B on (A.maker = B.maker and A.model != B.model) 
where A.type =B.type and A.type ='pc') as C 
on ( C.maker  = D.maker and D.model != C.m1 and D.model != C.m2) 
where D.type = 'pc') as E 
join (select p3.model,A.m1,A.m2 from pc as p3 
join (select p1.model as m1,p1.speed as s1,p2.model as m2,p2.speed as s2 from pc as p1 join pc as p2 
on (p1.speed != p2.speed) 
where p1.speed >=1.8 and p2.speed >=1.8) as A 
on(p3.speed != A.s1 and A.s2 != p3.speed) 
where p3.speed >= 1.8) as F on (E.m1 = F.m1 and E.m2 = F.m2 and E.model = F.model );

解题思路：
先对于product里生产pc的厂商的model建立一个由两次内连接构成的表，其中每个元组包括不同的三个model值以及厂商maker，再对速度大于1.8GHZ的pc元组经过两次内连接构成每个元组包含不同的三个model值（其中三个model值对应的速度不同）的表，最后对这两个表进行内连接，找到满足元组在第二个表内存在的厂商maker。

(b)

select A.maker 
from (select distinct maker,speed from product join pc as S using (model) 
where speed >= 1.8) as A 
group by maker 
having count(*) >= 3;

©

select B.maker 
from (select product.maker,A.speed from product 
join (select model,speed from pc where speed >= 1.8 ) as A 
using (model) group by A.speed,product.maker) as B 
group by maker having count(*) >= 3;

第5题回答如下：

(a)

select maker 
from (select maker from product 
join (select model,type from printer) as A 
using (model) group by A.type,maker) as B 
group by maker having count(*)=2 ;

(b)

select distinct maker from Product as T1 where 
not exists( 
select * from (select distinct type from Printer) as T2 
where not exists( 
select * from (select maker, model, Printer.type from Product join Printer using(model)) as T3 
where T1.maker = T3.maker and T2.type = T3.type));

第6题回答如下：

select distinct maker from product as T1 where 
not exists( 
select * from (select distinct type from product where maker = 'A') as T2 
where not exists( 
select * from product as T3 
where T1.maker = T3.maker and T2.type = T3.type));

第7题回答如下：

(a)

update pc 
set price = price * 1.1 
where 'A' = (select distinct maker from product where pc.model = product.model);

(b)

update pc 
set price = price * 1.1 
where model in (select model from product where type = 'pc' and maker = 'A');

©

update pc 
set price = price * 1.1 
where exists (select * from product where pc.model = product.model and product.maker = 'A');