zucc 数据库pta作业

10-1 查询订单表中的平均运费 (20分)
SELECT AVG(Freight)
AS avgFreight
FROM orders;
10-2 查询国家为Mexico、Germany的客户数量 (20分)
提示：请使用SELECT语句作答。

select count(Country)
as custCount
from customers
where Country in ('Mexico','Germany');
10-3 查找产品表中最低的单价 (20分)
提示：请使用SELECT语句作答。

select min(UnitPrice)
as minUnitPrice
from products;
10-4 查询产品表中最大库存量 (20分)
提示：请使用SELECT语句作答。

select max(UnitsInStock)
as maxUnitsInStock
from products;
10-5 查找订单表中每位顾客的平均运费 (20分)
提示：请使用SELECT语句作答。

select CustomerID,avg(Freight) as avgFreight
from orders
group by CustomerID;
10-6 统计顾客表中每个国家的顾客数量 (20分)
提示：请使用SELECT语句作答。

select Country,count(CustomerID) as custCount
from customers
group by Country;
10-7 在订单表中查找特定国家且平均运费不小于10的信息 (20分)
提示：请使用SELECT语句作答。

select CustomerID,avg(Freight) as avgFreight
from orders
where ShipCountry in ('Belgium','Switzerland')
group by CustomerID
having avg(Freight)>=10;
10-8 统计职工表中职务的数量 (20分)
select count(distinct Title) as countTitle from employees
10-9 查找订单表中特定顾客编号的相关信息 (20分)
提示：请使用SELECT语句作答。

select CustomerID,sum(Freight) as sumFreight from orders where CustomerID like'V%'group by CustomerID
10-10 查找产品表中平均订购数大于特定值的产品信息 (20分)
提示：请使用SELECT语句作答。

select ProductID,sum(UnitsOnOrder) sumUnitsOnOrder
from products
group by ProductID
having sumUnitsOnOrder>15
10-11 在订单详细信息表中查找包含产品种类数超过特定值的订单信息 (20分)
select OrderID,sum(Quantity) as totalQuantity from orderdetails group by OrderID HAVING count(ProductID) >2
10-12 查找职员信息及其直接上级的相关信息 (20分)
这是一个SQL编程题模板。

请在这里写题目描述。

例如：本题目要求编写SQL语句，检
SELECT stName,A.FirstName,A.ReportsTo EmployeeID,B.Title
FROM employees A,employees B
WHERE A.ReportsTo=B.EmployeeID
10-13 查询供应商及其供应的产品情况 (20分)
提示：请使用SELECT语句作答。

select
products.ProductID,products.ProductName,suppliers.SupplierID,suppliers. CompanyName
from products,suppliers
where suppliers.SupplierID = products.SupplierID
10-14 查询特定供应商及其供应的产品情况 (20分)
提示：请使用SELECT语句作答。

select
products.ProductID,products.ProductName,suppliers.SupplierID,suppliers. CompanyName
from products,suppliers
where suppliers.SupplierID = products.SupplierID
and suppliers.Country in ('Japan','USA')
返回
10-15 统计各个供应商及其供应情况 (20分)
提示：请使用SELECT语句作答。

select suppliers.Country,sum(products.UnitsInStock) as sumUnitsInStock,avg(products.UnitPrice) as avgUnitPrice
from products,suppliers
where suppliers.SupplierID = products.SupplierID
group by suppliers.Country
10-16 查询客户的订单情况 (20分)
提示：请使用SELECT语句作答。

SELECT B.CustomerID,panyName,A.OrderID,A.EmployeeID
FROM orders A,customers B
WHERE A.CustomerID=B.CustomerID AND A.ShipCity=B.City
ORDER BY B.CustomerID ASC
10-17 统计客户的订单信息 (20分)
提示：请使用SELECT语句作答。

SELECT CompanyName,COUNT(OrderID) AS countOrder,AVG(Freight) AS avgFreight FROM orders O,customers C
WHERE O.CustomerID=C.CustomerID
GROUP BY CompanyName
10-18 查找每位领导的直接下属数量 (20分)
提示：请使用SELECT语句作答。

SELECT B.EmployeeID,count(A.ReportsTo) as countSub
FROM employees A,employees B
WHERE A.ReportsTo=B.EmployeeID
group by B.EmployeeID
10-19 查询特定订单的详细信息 (20分)
提示：请使用SELECT语句作答。

select
customers.CustomerID,panyName,orderdetails.OrderID,orderde tails.ProductID
from orders,orderdetails,customers
where orders.ShipCity ='Madrid'
and orders.OrderID = orderdetails.OrderID
and orders.CustomerID = customers.CustomerID。