10-112 - 10-131

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

select CustomerID,Phone
from  customers 
where City<>'Madrid' and City<>'Torino' and City<>'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

10-118 spj-统计各供应商的零件供应量

select s.sno as 供应商号, sname as 供应商 , sum(qty) as 供应总量
from  s join spj using(sno)
where s.sno not in
(
    select distinct sno
    from spj 
    where qty <100
)
group by s.sno
order by s.sno

10-119 spj-显示供应商供应零件的汇总列表

-- select coalesce(sno,'所有供应商')'供应商',coalesce (pno,'所有零件') '零件',sum(qty) '供应量'
-- from spj
-- group by sno ,pno
-- with rollup;
select ifnull(sno,'所有供应商')'供应商',ifnull (pno,'所有零件') '零件',sum(qty) '供应量'
from spj
group by sno ,pno
with rollup;

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

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

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)  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='Belgium' or ShipCountry='Switzerland'
group by CustomerID
having avg(Freight) >10

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

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

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

select distinct maker
from product join pc using(model)
where speed>=160

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

select model
from  printer
order by price desc
limit 1

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

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