PTA mysql 题集（98-117）

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

select distinct starName as name from StarsIn
where movieTitle = 'M3' 
and starName in(select name from MovieStar where gender='M');

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

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

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

select distinct starName from StarsIn
where movieTitle in(select title from Movie where year='2018' and studioName='st1')
and movieYear=2018;

10-101 A1-2根据所在国家查找订单信息

select OrderID,CustomerID from orders
where ShipCountry='Germany' 
or ShipCountry='Brazil' or ShipCountry='France';

10-102 A1-3查询顾客表中所有不重复的城市

select distinct City from customers;

10-103 A1-4在产品表中找出库存数量大于50的产品的信息

select ProductID,ProductName from products
where UnitsInStock>50;

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

select title,year from Movie
where length>100;

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

select name from MovieExec
where netWorth>any(select netWorth from MovieExec where name='a1')
and certID in(select presCertID from Studio );

10-106 A1-5在顾客表中找出特定名字的顾客信息

select CustomerID,CompanyName from customers
where CompanyName like '%th%';

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

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

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

select model,price from pc where model in(select model from product where maker='B')
union
select model,price from laptop where model in(select model from product where maker='B')
union
select model,price from printer where model in(select model from product where maker='B')

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

select distinct maker from product where type ='便携式电脑'
and maker not in(select maker from product where type='个人电脑');

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

select hd from
(select hd,count(*) as num from pc 
 group by hd) as a
 where num>=2
 order by hd asc;
 
 
 select hd from pc
 group by hd
 having count(*)>=2
 order by hd asc;

这两种方法都可以 😃

10-111 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; 

10-112 A1-6在顾客表中找出不是特定城市的顾客信息

select CustomerID,Phone from customers
where City not in('Madrid','Torino','Paris');

10-113 A1-7在产品表中找出库存量小于订购量的产品信息

select ProductID,ProductName from products
where UnitsInStock<UnitsOnOrder;

10-114 A1-8查询传真号码不为空的供货商信息

select SupplierID,CompanyName from suppliers
where Fax is not null;

10-115 A2-1查找产品表中再次订购量大于15的产品信息

select ProductID,ProductName,SupplierID from products
where ReorderLevel>15;

10-116 A2-2查找产品表中再次订购量大于等于10且修订量大于订货数量的产品信息

select ProductID,ProductName,SupplierID from products
where ReorderLevel>=10 and ReorderLevel>UnitsOnOrder;

10-117 A2-3查询产品表中单价不在范围内的的产品信息

select ProductID,ProductName,CategoryID from products
where UnitPrice<15 or UnitPrice>45;