pta mysql训练题集 (101-120)

10-101 spj-查询比p6零件供应数量都高的零件

select distinct pno
from spj
where pno not in 
(
    select pno from spj where qty<=(select max(qty) from spj where pno='p6')
);

10-102 A3-1查询订单表中的平均运费

select avg(Freight) as avgFreight
from orders;

10-103 A3-2查询国家为Mexico、Germany的客户数量

select count(*) as custCount
from customers
where Country in ('Mexico','Germany');

10-104 A3-3查找产品表中最低的单价

select min(UnitPrice) as minUnitPrice
from products;

10-105 A3-4查询产品表中最大库存量

select max(UnitsInStock) as maxUnitsInStock
from products;

10-106 A4-1查找订单表中每位顾客的平均运费

select CustomerID,avg(Freight) as avgFreight 
from orders
group by CustomerID;

10-107 A4-2统计顾客表中每个国家的顾客数量

select Country,count(*) as custCount
from customers
group by Country;

10-108 A4-3在订单表中查找特定国家且平均运费不小于10的

select CustomerID,avg(Freight) as avgFreight
from orders
where ShipCountry in ('Belgium','Switzerland')
group by CustomerID
having avg(Freight)>=10;

10-109 4-1 查询速度至少为160MHz的PC的制造

select distinct maker
from pc,product
where product.model=pc.model and speed>=160;
-- 这题目有问题,没加 speed > 160 也是对的

10-110 4-2 查询价格最高的打印机型号

select model
from printer
order by price desc
limit 0,1;

10-111 4-3 查询速度低于任何PC的便携式

select model
from laptop
where speed < (select min(speed) from pc)

10-112 4-4 查询具有最高价格的机器的型号，机器包括PC、Laptop、Printer

select model
from
(
    select model,max(price) as price from pc group by model
    union
    select model,max(price) as price from laptop group by model
    union
    select model,max(price) as price from printer group by model
) as a
order by price desc
limit 0,1;

10-113 4-5 查询具有最低价格的的彩色打印机的制造

select maker
from product
where model in (select model from printer where price = (select min(price) from printer where color = 1)) and type = '打印机'

10-114 4-6 查询在具有最小内存容量的所有PC中具有最快处理器的PC制造商

-- 查询在具有最小内存容量的所有PC中具有最快处理器的PC制造商。
select distinct maker
from product,
(
select * 
from pc 
where ram = (select min(ram) from pc)
order by speed desc
limit 0,1
) as a
where product.model = a.model

10-115 5-1 查询销售便携式电脑但不销售PC的厂商

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

10-116 5-2 查询至少生产两种不同的计算机(PC或便携式电脑)且机器速度至少为133的厂商

-- 查询至少生产两种不同型号的计算机(PC或便携式电脑)且机器速度至少为133的厂商
select distinct maker
from product
where maker in
(
    select maker
    from
    (
        select maker,count(*) as num
        from product
        group by maker
        having num >= 2
    ) as b
) and model in
(
    select model
    from
    (
        select * from pc where speed>=133
        union
        select * from laptop where speed>=133
    ) as a
)

10-117 5-4 查询至少生产三种不同速度PC的厂商

SELECT maker 
from product,pc
where product.model = pc.model
group by maker
having count(distinct speed) >= 3

10-118 A4-5统计职工表中职务的数量

select count(distinct Title) as countTitle
from employees

10-119 6-1 查询PC的平均速度

select avg(speed) as avg_speed
from pc

10-120 6-2 查询价格超过2500美元的便携式电脑的平均速度

select avg(speed) as avg_speed
from laptop
where price>2500;