(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
数据库产品销售系统作业
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)
![](/skin/haowen/images/icon_star.png)