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

SQL Server练习题2一. 建库建表：1. 向数据库CPXS中的产品表CP加入数据。产品表CPCPBHCPMCJGKCL100001彩色电视机3000.010100002洗衣机1200.020100003冰箱1800.012100004电热水器2000.030100005太阳能热水器2200.081000061匹空调1800.051000071.5匹空调2400.0201000082匹空调3800.06100009音响3500.03100010台式计算机6000.05100011MP3300.010100012复读机200.020-插入CP表记录USE CPXSGOINSERT INTO CPVALUES(100001,彩色电视机,3000.0,10)GOINSERT INTO CPVALUES(100002,洗衣机,1200.0,20)GOINSERT INTO CPVALUES(100003,冰箱,1800.0,12)GOINSERT INTO CPVALUES(100004,电热水器,2000.0,30)GOINSERT INTO CPVALUES(100005,太阳能热水器,2200.0,8)GOINSERT INTO CPVALUES(100006,1匹空调,1800.0,5)GOINSERT INTO CPVALUES(100007,1.5匹空调,2400.0,20)GOINSERT INTO CPVALUES(100008,2匹空调,3800.0,6)GOINSERT INTO CPVALUES(100009,音响,3500.0,3)GOINSERT INTO CPVALUES(100010,台式计算机,6000.0,5)GOINSERT INTO CPVALUES(100011,MP3,300.0,10)GOINSERT INTO CPVALUES(100012,复读机,200.0,20)GO2. 向数据库CPXS中的销售商表XSS加入数据。销售商表XSSXSBHXSMCDQFZRDHBZ000001广电公司南京张建025111111NULL000002家电市场无锡李思成051022222NULL000003电器商场上海王京平021333333NULL000004小家电市场南京赵雅如025666666NULL-插入XSS表记录USE CPXSGOINSERT INTO XSSVALUES(000001,广电公司,南京,张建,025111111,NULL)GOINSERT INTO XSSVALUES(000002,家电市场,无锡,李思成,051022222,NULL)GOINSERT INTO XSSVALUES(000003,电器商场,上海,王京平,021333333,NULL)GOINSERT INTO XSSVALUES(000004,小家电市场,南京,赵雅如,025666666,NULL)GO3. 向数据库CPXS中的销售商表XSS加入数据。产品销售表XSCPCPBHXSBHXSSJSLJE1000010000012009-03-1013000.01000010000032009-05-2026000.01000020000012009-03-1211200.01000020000022009-02-2222400.01000020000032009-05-2933600.01000110000042009-06-0151000.0-插入XSCP表记录:USE CPXSGOINSERT INTO XSCPVALUES(100001,000001,2009-03-10,1,3000.0)GOINSERT INTO XSCPVALUES(100001,000003,2009-05-20,2,6000.0)GOINSERT INTO XSCPVALUES(100002,000001,2009-03-12,1,1200.0)GOINSERT INTO XSCPVALUES(100002,000002,2009-02-22,2,2400.0)GOINSERT INTO XSCPVALUES(100002,000003,2009-05-29,3,3600.0)GOINSERT INTO XSCPVALUES(100011,000004,2009-06-01,5,1000.0)GO二.修改记录1. 修改CP表的记录：1000061匹空调1500.05100012复读机200.015-修改记录USE CPXSGOUPDATE CPSET JG=1500.0WHERE CPBH=100006GOUPDATE CPSET KCL=15WHERE CPBH=100012GO2. 修改销售商表XSS的记录：000001广电公司南京张建025118888NULLUPDATE XSSSET DH=025118888WHERE XSBH=000001GO3. 修改产品销售表XSCP的记录：1000010000032009-05-2126000.0UPDATE XSCPSET XSSJ=2009-05-21WHERE CPBH=100001 AND XSBH=000003GO4. 将CP表的所有商品价格增加100。UPDATE CPSET JG=JG+100三. 删除表中的记录和清表：1. 删除CP表中产品编号为100008的记录；-删除记录DELETE FROM CPWHERE CPBH=1000082. 清空CP表中所有记录。-清空表TRUNCATE TABLE XSCP四. 数据查询1.用SELECT查询CP、XSS和XSCP表的所有记录。SELECT * FROM CPSELECT * FROM XSSSELECT * FROM XSCP2.查询产品表CP中每种产品的价格和库存量。SELECT JG,KCL FROM CP3.查询XSS表中的XSBH为的销售商的地区和电话。SELECT DQ,DH FROM XSSWHERE XSBH=0000014.查询XSS表中的XSBH为的销售商的地区和电话。要求：使用AS子句将结果中各列的标题分别指定为地区、电话。SELECT DQ AS 地区,DH AS 电话FROM XSSWHERE XSBH=0000015. 计算CP表中是所有产品的总价值。要求：查询结果中包含“总价值”字段。SELECT CPBH,总价值 =JG*KCLFROM CP6. 找出所有名称中含“电”的产品的编号和价格。要求：使用LIKE子句查询。SELECT CPBH,JGFROM CPWHERE CPMC LIKE %电%7. 找出所有价格在之间的产品编号和产品名称。要求：使用BETWEENEND子句。SELECT CPBH,CPMCFROM CPWHERE JG BETWEEN 1500 AND 2800五. 连接查询1. 查询每种产品的情况及其销售的情况。SELECT CP.*,XSCP.*FROM CP,XSCPWHERE CP.CPBH=XSCP.CPBH2. 查询每个销售商的情况及其销售产品的情况。SELECT XSS.*,XSCP.*FROM XSS,XSCPWHERE XSS.XSBH=XSCP.XSBH3. 查询价格在及以上的产品名称及其销售情况。SELECT CPMC,XSMC,XSSJ,SL,JEFROM CP,XSS,XSCPWHERE CP.CPBH=XSCP.CPBH AND XSS.XSBH=XSCP.XSBHAND JG=20004. 查询库存量在及以上的产品名称及其销售情况。SELECT CPMC,XSMC,XSSJ,SL,JEFROM CP,XSS,XSCPWHERE CP.CPBH=XSCP.CPBH AND XSS.XSBH=XSCP.XSBHAND SL=105. 查询在年月日以前销售的产品名称及其销售情况。SELECT CPMC,XSMC,XSSJ,SL,JEFROM CP,XSS,XSCPWHERE CP.CPBH=XSCP.CPBH AND XSS.XSBH=XSCP.XSBHAND XSSJ=20040101七. GROUP BY、ORDER BY子句的使用1. 查询各地区的销售商数。SELECT DQ,COUNT(XSBH)FROM XSSGROUP BY DQ2. 求被销售产品的名称和销售该种产品的销售商数。SELECT CPMC AS 产品名称,COUNT(XSBH) AS 销售商数FROM XSCP,CPWHERE XSCP.CPBH=CP.CPBHG