SQL-Server2012综合练习题2 - 参考答案

SQL Server练习题2
一.建库建表：
1.向数据库CPXS中的产品表CP加入数据。

产品表CP
CPBH CPMC JG KCL 100001 彩色电视机3000.0 10 100002 洗衣机1200.0 20 100003 冰箱1800.0 12 100004 电热水器2000.0 30 100005 太阳能热水器2200.0 8 100006 1匹空调1800.0 5 100007 1.5匹空调2400.0 20 100008 2匹空调3800.0 6 100009 音响3500.0 3 100010 台式计算机6000.0 5 100011 MP3 300.0 10 100012 复读机200.0 20
----插入CP表记录
USE CPXS
GO
INSERT INTO CP
VALUES('100001','彩色电视机',3000.0,10)
GO
INSERT INTO CP
VALUES('100002','洗衣机',1200.0,20)
GO
INSERT INTO CP
VALUES('100003','冰箱',1800.0,12)
GO
INSERT INTO CP
VALUES('100004','电热水器',2000.0,30)
GO
INSERT INTO CP
VALUES('100005','太阳能热水器',2200.0,8)
GO
INSERT INTO CP
VALUES('100006','1匹空调',1800.0,5)
GO
INSERT INTO CP
VALUES('100007','1.5匹空调',2400.0,20)
GO
INSERT INTO CP
VALUES('100008','2匹空调',3800.0,6)
GO
INSERT INTO CP
VALUES('100009','音响',3500.0,3)
GO
INSERT INTO CP
VALUES('100010','台式计算机',6000.0,5)
GO
INSERT INTO CP
VALUES('100011','MP3',300.0,10)
GO
INSERT INTO CP
VALUES('100012','复读机',200.0,20)
GO
2.向数据库CPXS中的销售商表XSS加入数据。

销售商表XSS
XSBH XSMC DQ FZR DH BZ 000001 广电公司南京张建025111111 NULL 000002 家电市场无锡李思成051022222 NULL 000003 电器商场上海王京平021333333 NULL 000004 小家电市场南京赵雅如025666666 NULL
----插入XSS表记录
USE CPXS
GO
INSERT INTO XSS
VALUES('000001','广电公司','南京','张建','025111111',NULL)
GO
INSERT INTO XSS
VALUES('000002','家电市场','无锡','李思成','051022222',NULL) GO
INSERT INTO XSS
VALUES('000003','电器商场','上海','王京平','021333333',NULL) GO
INSERT INTO XSS
VALUES('000004','小家电市场','南京','赵雅如','025666666',NULL) GO
3.向数据库CPXS中的销售商表XSS加入数据。

产品销售表XSCP
CPBH XSBH XSSJ SL JE 100001 000001 2009-03-10 1 3000.0 100001 000003 2009-05-20 2 6000.0 100002 000001 2009-03-12 1 1200.0 100002 000002 2009-02-22 2 2400.0 100002 000003 2009-05-29 3 3600.0 100011 000004 2009-06-01 5 1000.0
----插入XSCP表记录:
USE CPXS
GO
INSERT INTO XSCP
VALUES('100001','000001','2009-03-10',1,3000.0)
GO
INSERT INTO XSCP
VALUES('100001','000003','2009-05-20',2,6000.0)
GO
INSERT INTO XSCP
VALUES('100002','000001','2009-03-12',1,1200.0)
GO
INSERT INTO XSCP
VALUES('100002','000002','2009-02-22',2,2400.0)
GO
INSERT INTO XSCP
VALUES('100002','000003','2009-05-29',3,3600.0)
GO
INSERT INTO XSCP
VALUES('100011','000004','2009-06-01',5,1000.0)
GO
二.修改记录
1. 修改CP表的记录：
100006 1匹空调1500.0 5
100012 复读机200.0 15
----修改记录
USE CPXS
GO
UPDATE CP
SET JG=1500.0
WHERE CPBH='100006'
GO
UPDATE CP
SET KCL=15
WHERE CPBH='100012'
GO
2. 修改销售商表XSS的记录：
000001 广电公司南京张建025118888 NULL UPDATE XSS
SET DH='025118888'
WHERE XSBH='000001'
GO
3. 修改产品销售表XSCP的记录：
100001 000003 2009-05-21 2 6000.0 UPDATE XSCP
SET XSSJ='2009-05-21'
WHERE CPBH='100001'AND XSBH='000003'
GO
4. 将CP表的所有商品价格增加100。

UPDATE CP
SET JG=JG+100
三. 删除表中的记录和清表：
1. 删除CP表中产品编号为100008的记录；
----删除记录
DELETE FROM CP
WHERE CPBH='100008'
2. 清空CP表中所有记录。

----清空表
TRUNCATE TABLE XSCP
四. 数据查询
1. 用SELECT查询CP、XSS和XSCP表的所有记录。

SELECT*FROM CP
SELECT*FROM XSS
SELECT*FROM XSCP
2. 查询产品表CP中每种产品的价格和库存量。

SELECT JG,KCL FROM CP
3. 查询XSS表中的XSBH为的销售商的地区和电话。

SELECT DQ,DH FROM XSS
WHERE XSBH='000001'
4. 查询XSS表中的XSBH为的销售商的地区和电话。

要求：使用AS子句将结果中各列的标题分别指定为地区、电话。

SELECT DQ AS地区,DH AS电话
FROM XSS
WHERE XSBH='000001'
5. 计算CP表中是所有产品的总价值。

要求：查询结果中包含“总价值”字段。

SELECT CPBH,总价值=JG*KCL
FROM CP
6. 找出所有名称中含“电”的产品的编号和价格。

要求：使用LIKE子句查询。

SELECT CPBH,JG
FROM CP
WHERE CPMC LIKE'%电%'
7. 找出所有价格在～之间的产品编号和产品名称。

要求：使用BETWEEN……END子句。

SELECT CPBH,CPMC
FROM CP
WHERE JG BETWEEN 1500 AND 2800
五. 连接查询
1. 查询每种产品的情况及其销售的情况。

SELECT CP.*,XSCP.*
FROM CP,XSCP
WHERE CP.CPBH=XSCP.CPBH
2. 查询每个销售商的情况及其销售产品的情况。

SELECT XSS.*,XSCP.*
FROM XSS,XSCP
WHERE XSS.XSBH=XSCP.XSBH
3. 查询价格在及以上的产品名称及其销售情况。

SELECT CPMC,XSMC,XSSJ,SL,JE
FROM CP,XSS,XSCP
WHERE CP.CPBH=XSCP.CPBH AND XSS.XSBH=XSCP.XSBH
AND JG>=2000
4. 查询库存量在及以上的产品名称及其销售情况。

SELECT CPMC,XSMC,XSSJ,SL,JE
FROM CP,XSS,XSCP
WHERE CP.CPBH=XSCP.CPBH AND XSS.XSBH=XSCP.XSBH
AND SL>=10
5. 查询在年月日以前销售的产品名称及其销售情况。

SELECT CPMC,XSMC,XSSJ,SL,JE
FROM CP,XSS,XSCP
WHERE CP.CPBH=XSCP.CPBH AND XSS.XSBH=XSCP.XSBH AND XSSJ<='20090701'
六. 数据汇总
1. 求所有产品的平均价格。

SELECT AVG(JG)AS'产品平均价格'
FROM CP
2. 查询产品的最高和最低价格。

SELECT MAX(JG)AS'产品最高价格',MIN(JG)AS'产品最低价格' FROM CP
3. 求地区为南京的总销售商数。

SELECT COUNT(XSBH)
FROM XSS
WHERE DQ='南京'
4. 统计销售商总数。

SELECT SUM(JE)AS'销售总值'
FROM XSCP
5. 统计年月日以来的销售总值。

SELECT SUM(JE)AS'销售总值'
FROM XSCP
WHERE XSSJ>='20040101'
七. GROUP BY、ORDER BY子句的使用
1. 查询各地区的销售商数。

SELECT DQ,COUNT(XSBH)
FROM XSS
GROUP BY DQ
2. 求被销售产品的名称和销售该种产品的销售商数。

SELECT CPMC AS'产品名称',COUNT(XSBH)AS'销售商数' FROM XSCP,CP
WHERE XSCP.CPBH=CP.CPBH
GROUP BY CPMC
3. 将各产品的情况按价格由低到高排列。

SELECT*
FROM CP
ORDER BY JG
4. 将各产品的情况按总值由低到高排列。

SELECT*
FROM CP
ORDER BY JG*KCL
5. 将各产品的情况按总销售额值由低到高排序。

SELECT XSBH,SUM(JE)
FROM XSCP
GROUP BY XSBH
ORDER BY SUM(JE)。