结构化查询语言（SQL）基础语法习题——数据库查询（DQL）、数据库操作（DML）、建立视图（View）

1 数据库查询（DQL）语句练习

1.1 查询顾客表中所有不重复的城市

查询出顾客表(customers)中所有不重复的所在城市(City)

select distinct City from customers;

1.2 在产品表中找出库存量小于订购量的产品信息

在产品表(products)中找出库存量(UnitsInStock)小于订购量(UnitsOnOrder)的产品的产品编号（ProductID）和产品名称（ProductName）

select ProductID, ProductName
from products
where UnitsInStock < UnitsOnOrder;

1.3 查找产品表中再次订购量大于15的产品信息

在产品表(products)中找出再次订购量(ReorderLevel)大于15的产品的产品编号（ProductID），产品名称（ProductName）和供货商编号（SupplierID）

select ProductID, ProductName, SupplierID
from products
where ReorderLevel > 15;

1.4 查找产品表中再次订购量大于等于10且修订量大于订货数量的产品信息

在产品表(products)中找出再次订购量(ReorderLevel)大于等于10，且再次订购量(ReorderLevel)大于订购数量（UnitsOnOrder）的产品的产品编号（ProductID），产品名称（ProductName）和供货商编号（SupplierID）

select ProductID, ProductName, SupplierID
from products
where ReorderLevel >= 10 and ReorderLevel > UnitsOnOrder;

1.5 根据运费查询订单信息

在订单表(orders)中找出运费(Freight)在10到50之间的订单编号(orderid)、顾客编号(customerid)和职员编号(employeeid)

select orderid, customerid, employeeid
from orders
where Freight between 10 and 50;

1.6 查询国家为Mexico、Germany的客户数量

查询出顾客表(customers)中查询国家（Country）为Mexico、Germany的客户数量，并重命名为custCount

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

1.7 在顾客表中找出不是特定城市的顾客信息

在顾客表(customers)中找出所在城市(City)不是Madrid、Torino和Paris的顾客编号(CustomerID)及电话(Phone)

select CustomerID, Phone
from customers
where City not in ("Madrid", "Torino", "Paris");

1.8 查找产品表中最低的单价

在产品表(products)中查询最低的单价(UnitPrice)，并重命名为minUnitPrice

select min(UnitPrice) minUnitPrice
from products;

1.9 查找订单表中每位顾客的平均运费

查询订单表(orders)中每位顾客的平均运费，结果显示为顾客编号(CustomerID)和平均运费(avgFreight)

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

1.10 查找产品表中平均订购数大于特定值的产品信息

在产品表(products)中查找平均订购数量大于15的产品信息，显示为：产品编号(ProductID)，和总订货数量（重命名为sumUnitsOnOrder）

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

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

在订单详情表(orderdetails)中查找订单中包含的不同产品的个数超过2的订单信息，显示订单号（OrderID）和总数量（重命名为totalQuantity）

select OrderID, sum(Quantity) totalQuantity
from orderdetails
group by OrderID
having count(distinct ProductID) > 2;

1.12 统计各个供应商及其供应情况

统计各个国家（Country）的供应商的供应产品情况，显示为：国家（Country），库存总量（重命名为sumUnitsInStock）,平均单价（重命名为avgUnitPrice）

select Country, sum(UnitsInStock) sumUnitsInStock, avg(UnitPrice) avgUnitPrice
from suppliers
join products on suppliers.SupplierID = products.SupplierID
group by Country;

1.13 查询客户的订单情况

查找客户的订单情况，显示为：顾客编号（CustomerID） ，公司名称（CompanyName），订单编号（OrderID），员工编号（EmployeeID）

select o.CustomerID, c.CompanyName, o.OrderID, o.EmployeeID
from orders o, customers c
where c.CustomerID = o.CustomerID

1.14 查找每位领导的直接下属数量 ⭐️

查询每位领导的直接下属数量，显示：领导的员工编号（EmployeeID），下属数量（重命名为countSub）

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

1.15 查询特定订单的详细信息

查找城市（City）是Madrid的顾客，所下的订单详细信息，显示为：顾客编号（CustomerID），公司名称（CompanyName），订单编号（OrderID），产品编号（ProductID）

select o.CustomerID, c.CompanyName, o.OrderID, od.ProductID
from orders o, orderdetails od, customers c
where o.OrderID = od.OrderID and o.CustomerID = c.CustomerID and c.City = "Madrid"

1.16 查找订单数最多的员工信息 ⭐️

查找订单数最多的员工信息，显示为：姓（LastName），名（FirstName）和职务（Title）

select e.LastName, e.FirstName, e.Title
from employees e, orders o
where e.EmployeeID = o.EmployeeID
group by e.EmployeeID
order by count(o.CustomerID) desc
limit 1;

---

2 数据库操作（DML）语句练习

2.1 新增一个区域

在区域表（region）中添加一条记录：区域编号（RegionID）为5，区域描述（RegionDescription）为Center

提示：请使用INSERT语句作答。

insert into region(RegionID, RegionDescription) 
values (5, "Center");

2.2 新增订单统计信息

统计订单表（orders）中顾客的订单数量，并插入results表中

提示：请使用INSERT语句作答。

insert into results (CustomerID, OrderCount)
select CustomerID, count(OrderID) OrderCount
from orders
group by CustomerID;

2.3 根据运费调整订单单价

对于运费（Freight）超过30.00的订单，将其相关的订单详情表（orderdetails）中产品的单价（UnitPrice）提高15%

提示：请使用UPDATE语句作答。

update orderdetails od
set UnitPrice = UnitPrice * 1.15
where (
    select o.Freight
    from orders o
    where od.OrderID = o.OrderID
);

2.4 删除特定城市的顾客信息

删除顾客表（customers）中城市（City）为London的顾客信息

提示：请使用DELETE语句作答。

delete from customers 
where City = "London";

---

3 视图练习

3.1 建立供应商的销售记录视图vSupStatics

本题要求编写SQL语句，建立供应商的销售记录视图vSupStatics，显示供应商号和订单数量，订单总价，分别表示为Sid，ordcount，ordtotalPrice。

create view vSupStatics as
select Sid, count(OrdNo) ordcount, sum(Price * QTY) ordtotalPrice
from orders
group by Sid;

3.2 建立产品的销售记录视图vProductStatics

本题要求编写SQL语句，建立产品的销售记录视图vProductStatics，显示产品编号，销售总数量，最高价，最低价，分别表示为Pid，totalQTY，maxPrice，minPrice。

create view vProductStatics as
select Pid, sum(QTY) totalQTY, max(Price) maxPrice, min(Price) minPrice
from orders
group by Pid;