第四章习题答案.sql

SQL SERVER2000实用教程蒋文沛主编课

create database sales
on primary
(name=sales,filename='d:\sales.mdf')
log on
(name=sales_log,filename='d:\sales_log.ldf')
for attach
go
use sales
go
--(1)
select top 6 * from goods
--(2)
select 商品名称,进货价 FROM GOODS ORDER BY 进货价 DESC
--(3)
SELECT 商品编号,SUM(数量) AS 销售数量 INTO TABLE1 FROM SELL GROUP BY 商品编号
SELECT 商品名称,进货价,零售价,GOODS.数量-TABLE1.销售数量 AS 剩余数量 FROM GOODS,TABLE1 WHERE GOODS.商品编号=TABLE1.商品编号
--(4)
SELECT AVG(零售价) AS 平均零售价格 FROM GOODS

SELECT 商品编号,零售价 FROM GOODS COMPUTE AVG(零售价) 

--(5)
SELECT * FROM GOODS
SELECT * FROM SELL
SELECT 商品编号 FROM SELL WHERE 售出时间 BETWEEN '2004.1.1' AND '2005.1.1'

SELECT 商品名称,GOODS.数量,售出时间 FROM GOODS,SELL WHERE GOODS.商品编号=SELL.商品编号 AND 售出时间 BETWEEN '2004.1.1' AND '2005.1.1'

SELECT 商品名称,数量 AS 进货数量 FROM GOODS WHERE 商品编号 IN (SELECT 商品编号 FROM SELL WHERE 售出时间 BETWEEN '2004.1.1' AND '2005.1.1')

--(6)
SELECT * FROM EMPLOYEES WHERE 部门='销售部'

SELECT * FROM EMPLOYEES WHERE 编号=ANY(SELECT 售货员工编号 FROM SELL)
SELECT 售货员工编号,商品编号,数量 FROM SELL ORDER BY 售货员工编号 COMPUTE SUM(数量) BY 售货员工编号

--(7)
SELECT 商品编号,SUM(数量) AS 销售数量 INTO TABLE2 FROM SELL GROUP BY 商品编号 
SELECT 商品名称,进货价,零售价,GOODS.数量-TABLE2.销售数量 AS 剩余数量 FROM GOODS,TABLE2 WHERE GOODS.商品编号=TABLE2.商品编号

SELECT 商品名称,数量 进货数量,GOODS.数量-TABLE2.销售数量 AS 剩余数量 FROM GOODS,TABLE2 WHERE GOODS.商品编号*=TABLE2.商品编号  AND 商品名称 LIKE '%显示器%'

--(8)
SELECT SUM(SELL.数量) AS 销售数量 FROM SELL,GOODS WHERE SELL.商品编号=GOODS.商品编号 AND 商品名称='打印机'