PTA myaql 基础题集（121-133）

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

select avg(Freight) as avgFreight from orders;

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

select count(*) as custCount from customers
where Country='Mexico' or Country='Germany';

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

select min(UnitPrice) as minUnitPrice from products;

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

select max(UnitsInStock) as maxUnitsInStock from products;

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

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

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

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

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

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

10-128 A4-4查找产品表中平均订购数大于特定值的产品信息

select ProductID,sum(UnitsOnOrder) as sumUnitsOnOrder from products
group by ProductID
having sumUnitsOnOrder >15;

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

select distinct maker from product
where type='个人电脑'
and model in(select model from pc where speed>=160);

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

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

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

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

10-132 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-133 4-5 查询具有最低价格的的彩色打印机的制造商

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