（2）.查询没有订购任何产品的客户名。（子查询）
SELECT Cname 客户名 FROM Customers WHERE Cid NOT IN (SELECT Cid FROM Orders)
（3）.查询订购“水笔”和“笔袋”的订单信息。（要求使用子查询和集合查询）
子查询方法： SELECT * FROM Orders
WHERE Pid IN (SELECT Pid FROM Products WHERE Pname='水笔' OR Pname='笔袋') 集合查询方法： SELECT * FROM Order
WHERE Pid IN(SELECT Pid FROM Products WHERE Pname='水笔') UNION
SELECT * FROM Orders
WHERE Pid IN(SELECT Pid FROM Products WHERE Pname='笔袋')
（4）.查询代理商是“广州”， 客户是“上海”的订单信息。用集合查询和子查询）
集合查询方法: SELECT * FROM Orders
WHERE Aid IN(SELECT Aid FROM Agents WHERE City='广州') INTERSECT
SELECT * FROM Orders
WHERE Cid IN(SELECT Cid FROM Customers WHERE City='上海') 子查询方法：
SELECT * FROM Orders WHERE Aid IN
(SELECT Aid FROM Agents WHERE City='广州' AND Cid IN (SELECT Cid FROM Customers WHERE City='上海'))
5
（要求练习3：
1. 打开产品销售管理数据库，并完成下列各题：
（1）. 创建一个视图V1存储：每个月每种产品的订货量和订货总金额。
方案1：
CREATE VIEW V1 AS SELECT Month 月份,Pname 产品,SUM(Qty) 订货量,SUM(Amount) 订货总金额 FROM Orders,Products
WHERE Orders.Pid=Products.Pid GROUP BY Products.Pname,Orders.Month 方案2：
CREATE VIEW V1 AS SELECT Month
月份,Pname
产品,SUM(Qty)
订货
量,SUM(Products.Price*Qty) 订货总金额 FROM Orders,Products
WHERE Orders.Pid=Products.Pid GROUP BY Products.Pname,Orders.Month
（2）.创建一个视图V2存储：月份、客户姓名、代理人姓名、产品名、订货数量。
CREATE VIEW V2 AS SELECT Month 月份,Cname 客户姓名,Aname 代理人姓名,Pname 产品名,Qty 订货数量
FROM Orders,Products,Customers,Agents
WHERE Products.Pid=Orders.Pid AND Customers.Cid=Orders.Cid AND Agents.Aid=Orders.Aid GROUP BY Products.Pname,Customers.Cname,Agents.Aname,Orders.Month
（3）.创建一个视图V3存储：产品编号、产品名称、库存量、单价和库存金额（其中：库存金额=库存量*单价）。
CREATE VIEW V3 AS
SELECT Pid 产品编号,Pname 产品名称,Quantity 库存量,Price 单价,(Price*Quantity)库存金额 FROM Products
2. 打开产品销售管理数据库，并完成下列各题：
（1）.向产品表添加一个产品：编号为P10，产品名为钢笔，库存量为155000，单价为8.5元。
INSERT INTO Products VALUES('P10','钢笔','155000','8.5')
6
（2）.向客户表添加客户：编号为C10，姓名为钟立，城市为南宁。
INSERT INTO Customers VALUES('C10','钟立','南宁')
（3）.将订单号为1021的订单的订货数量改为2000，并修改其相应的订货金额。
1.
UPDATE Orders SET Qty=2000 WHERE Ord_no='1021' 2.
UPDATE Orders
SET Amount=(Qty*(SELECT Price FROM Products WHERE Products.Pid=Orders.Pid)) WHERE Ord_no='1021'
（4）.请修改：将由A06供给C006的产品P01改为由A05供应。
UPDATE Orders SET Aid='A05' WHERE Aid='A06' AND Cid='C006' AND Pid='P01'
（5）.从客户表中删除城市是“北海”的客户信息。
DELETE Customers WHERE City= '北海'
（6）.删除昆明代理人的信息。
DELETE Agents WHERE City= '昆明'
7
练习4：
1. 打开产品销售管理数据库，并完成下列各题：
（1）.查询每种产品的库存量，并显示是否需要增加或减少库存量（库存量在100000—150000间为合适，若超过需减少，若不够需增加），要求按下列格式显示：
产品名称 笔袋 尺子 水彩笔 … 库存量 111400 203000 80500 … 是否增减 合适 需减少 需增加 … SELECT Pname 产品名称,Quantity 库存量,CASE WHEN Quantity>150000 THEN '需减少' WHEN Quantity>=100000 THEN '合适' WHEN Quantity<100000 THEN '需增加' END 是否增减 FROM Products ORDER BY Pid
（2）.查询每个城市是否需要增加或减少代理人（一个城市配两名代理人，若超过需减少，若不够需增加），要求按下列格式显示：
城市 北京 上海 杭州 … 代理人数 2 3 1 … 是否增减 合适 需减少 需增加 … SELECT DISTINCT City 城市,COUNT(City) 代理人数,CASE WHEN COUNT(City)>3 THEN '需减少' WHEN COUNT(City)>=2 THEN '合适' WHEN COUNT(City)<=1 THEN '需增加'
END 是否增减 FROM Agents GROUP BY Agents.City ORDER BY City
8
2. 打开产品销售管理数据库，并完成下列各题：
（1）.创建存储过程P1：给指定产品编号的产品单价增加0.5元。
创建：
CREATE PROCEDURE P1 @Products_ID CHAR(3) AS SELECT Pid 产品编号,(Price+0.5) 单价 FROM Products
WHERE Pid=@Products_ID
（2）.创建存储过程P2：查询某个月某个客户的订单份数，并把订单份数输出给调用者。以2月份客户“詹三”为参数执行该存储过程；
创建：
CREATE PROCEDURE P2 @Orders_MONTH CHAR(3),@Customers_NAME NCHAR(10) AS SELECT Month 月份,COUNT(Ord_no) 订单份数 FROM Orders,Customers WHERE
Customers.Cid=Orders.Cid
AND
Month=@Orders_MONTH
AND
Cname=@Customers_NAME GROUP BY Orders.Month 执行：
1.EXEC P2 '2','詹三'
2.EXEC P2 @Orders_MONTH='2',@Customers_NAME='詹三'
（3）.创建存储过程P3：根据输入的客户名称和产品名称查询其订货总量，并要求输出。并以“王勇”和“橡皮”为参数执行该存储过程。
创建：
CREATE PROCEDURE P3 @Customers_NAME NCHAR(10),@Products_NAME NCHAR(10) AS SELECT Cname 客户名称,Pname 产品名称,SUM(Qty) 订货总量 FROM Customers,Products,Orders WHERE
Customers.Cid=Orders.Cid
AND
Products.Pid=Orders.Pid
AND
Cname=@Customers_NAME AND Pname=@Products_NAME GROUP BY Customers.Cname,Products.Pname 执行：
1.EXEC P3 '王勇','橡皮'
2.EXEC P3 @Customers_NAME='王勇',@Products_NAME='橡皮'
9
数据库产品销售系统作业
