数据库第四次实验

目录

10-1 3-1-(a) 查询电影“M3”中的男影星

10-2 3-1-(b) 查询st1制片公司的总裁

10-3 3-1-(c)查询在st1公司于2018年制作的电影中出演的影星

10-4 3-1-(d)查询比电影《M1》时间更长的电影

10-5 3-1-(e)查询比a1更富有的行政长官

10-6 3-2-(a)查询配置了容量至少为1G字节硬盘的便携式电脑的生产厂商及其速度

10-7 3-2-(b)查询由生产厂商B生产的所有产品的型号（model) 和价格(price)

10-8 3-2-(c)查询所有出售便携式电脑（而不出售PC机）的生产厂商

10-9 3-2-(d)查询在两种或两种以上PC机上出现的硬盘容量

10-10 3-2-(e)查询拥有相同速度和内存的PC机的成对的型号

---

10-1 3-1-(a) 查询电影“M3”中的男影星

select distinct MovieStar.name
from MovieStar,StarsIn
where StarsIn.movieTitle = 'M3' and MovieStar.gender = 'M';

10-2 3-1-(b) 查询st1制片公司的总裁

select distinct MovieExec.name
from MovieExec,Studio
where Studio.name = 'st1' and Studio.presCertID = MovieExec.certID;

10-3 3-1-(c)查询在st1公司于2018年制作的电影中出演的影星

select distinct StarsIn.starName
from Movie,StarsIn
where Movie.year = 2018 and Movie.studioName = 'st1' and Movie.title = StarsIn.movieTitle and StarsIn.movieYear = 2018

10-4 3-1-(d)查询比电影《M1》时间更长的电影

select distinct  a.title ,a.year
from Movie as a,Movie as b
where b.title = 'M1' and a.length >b.length

10-5 3-1-(e)查询比a1更富有的行政长官

select distinct b.name
from MovieExec as a,MovieExec as b , Studio
where a.name = 'a1' and b.netWorth >a.netWorth and b.certID = Studio.presCertID

10-6 3-2-(a)查询配置了容量至少为1G字节硬盘的便携式电脑的生产厂商及其速度

select product.maker,laptop.speed
from laptop,product
where laptop.hd>=1 and product.type = '便携式电脑' and laptop.model = product.model;

10-7 3-2-(b)查询由生产厂商B生产的所有产品的型号（model) 和价格(price)

select a.model,price
from product as a,pc as b
where a.model = b.model and maker= 'B'
union
select a.model,price
from product as a,laptop as b
where a.model = b.model and maker= 'B'
union
select a.model,price
from product as a,printer as b
where a.model = b.model and maker= 'B'

10-8 3-2-(c)查询所有出售便携式电脑（而不出售PC机）的生产厂商

select distinct product.maker from product,laptop
where product.model=laptop.model
    and product.maker not in
    (select product.maker from pc,product where pc.model=product.model);

10-9 3-2-(d)查询在两种或两种以上PC机上出现的硬盘容量

select hd
from pc
group by hd
having count(*) >= 2

10-10 3-2-(e)查询拥有相同速度和内存的PC机的成对的型号

select a.model as model1,b.model as model2
from pc as a,pc as b
where a.speed=b.speed and a.ram=b.ram and a.model<b.model
order by model1;