pta mysql训练题集 (121 - 140)

10-121 6-3 查询厂商"A"生产的PC的平均价格

select avg(price) as avg_price
from pc,product
where pc.model = product.model and product.maker = 'A';

10-122 A4-6查找订单表中特定顾客编号的相关信息

select CustomerID,sum(Freight) as sumFreight
from orders
where CustomerID like 'V%'
group by CustomerID;

10-123 6-4 查询厂商"D"生产的PC和便携式电脑的平均价格

select avg(price) as avg_price
from product,
(
    select * from pc
    union
    select * from laptop
) as a
where product.model=a.model and maker='D';

10-124 6-5 查询各种不同速度的PC的平均价格

select speed,avg(price) as avg_price
from pc
group by speed;

10-125 6-6 查询各厂商生产的便携式电脑的显示器平均尺寸

select maker,avg(screen) as avg_screen
from product,laptop
where type='便携式电脑' and product.model=laptop.model
group by maker;

10-126 6-7 查询生产三种不同型号的PC的厂商

select distinct maker
from product
where maker in (
    select maker
    from product
    where type = '个人电脑'
    group by maker
    having count(*) >2
)

10-127 6-8 查询各厂商生产的PC的最高价格

select maker,max(price) as max_price
from product
inner join pc on pc.model = product.model
group by maker
order by maker asc

10-128 6-9查询速度超过150MHZ的各种速度的PC的平均价格

select speed,avg(price) as avg_price
from pc
where speed>150
group by speed

10-129 6-10 查询所有生产打印机的厂商生产的PC的硬盘平均容量

select avg(hd) as avg_hd
from pc
where model in (
    select model
    from product
    where maker in (select maker from product where type = '打印机')
)

10-130 7-1 将下述事实存入数据库:生产厂商C制造的型号为1100的PC机，速度240，内存32，硬盘2.5G，售价2499美元

insert into product values('C',1100,'个人电脑');
insert into pc values(1100,240,32,2.5,null,2499);

10-131 A4-7在订单详细信息表中查找包含产品种类数超过特定值的订单信息

select OrderID,sum(Quantity) as totalQuantity
from orderdetails
group by OrderID
having count(*)>2

10-132 B1-1查找职员信息及其直接上级的相关信息

select a.LastName,a.FirstName,b.EmployeeID,b.Title
from employees as a,employees as b
where a.ReportsTo=b.EmployeeID
group by a.EmployeeID

10-133 B1-2查询供应商及其供应的产品情况

select a.ProductID,ProductName,a.SupplierID,CompanyName
from products as a,suppliers as b
where a.SupplierID=b.SupplierID;

10-134 B1-3查询特定供应商及其供应的产品情况

select a.ProductID,ProductName,a.SupplierID,CompanyName
from products as a,suppliers as b
where a.SupplierID=b.SupplierID and Country in ('Japan','USA');

10-135 B1-4统计各个供应商及其供应情况

select Country,sum(UnitsInStock) as sumUnitsInStock,avg(UnitPrice) as avgUnitPrice
from products as a,suppliers as b
where a.SupplierID=b.SupplierID
group by Country;

10-136 查询图书表中所有记录

select * from 图书;

10-137 查询2018年以后出版的图书的全部信息

select * from 图书
where year(出版日期)>=2018;

10-138 查询图书表中李凯所著的图书，要求查询结果中包括条形码，书名，作者，出版社4列

select 条形码,书名,作者,出版社
from 图书
where 作者='李凯';

10-139 B1-6统计客户的订单信息

select b.CompanyName,count(*) as countOrder,avg(a.Freight) as avgFreight
from orders as a,customers as b
where a.CustomerID = b.CustomerID
group by b.CompanyName;

10-140 B1-7查找每位领导的直接下属数量

select EmployeeID,countSub
from 
(
    select ReportsTo as EmployeeID,count(EmployeeID) as countSub
    from employees
    where ReportsTo is not null
    group by ReportsTo
) as a1;