charles
charles
- 组别:管理员
- 性别:
- 来自:
- 积分:2811
- 帖子:2679
- 注册:
2006-12-12
|
SQL基础操作检测题
大宝整理的一份SQL测试题，觉得不错。放在这里。 利用企业管理器创建一个数据库TEST完全备份数据库TEST到磁盘文件c:\TEST.bak删除数据库TEST从磁盘文件c:\TEST.bak中还原数据库为CUST利用企业管理器，在数据库CUST中创建三张表：客户资料表—Customers：客户编码CUST_id—主键(char，10，不允许为空)客户名称CUST_name（char，10）客户地址CUST_add（varchar，60）客户邮编CUST_ZIP（char，12）客户所在大区area（char，10）客户所属分公司company（char，10）；客户销售订单表—Orders：客户订单号corder_no—主键（char ，15，不允许为空）客户编号CUST_id（char ，10）销售员编号sales（char，10）销售数量qty（numeric，9（精度16，小数位数6））销售日期sale_date（datatime，8）物料编号item（char，15）物料单价price（numeric，9（精度16，小数位数6））销售类型order_type（char，1，以I表示销售，以O表示退货）；分公司资料表—Companies分公司编码company—主键（char，10，不允许为空）分公司名称comy_name（char，50）分公司地址comy_add（varchar，50）分公司邮编comy_code（char，12）客户资料表—Customers客户编码
| 客户名称
| 客户地址
| 客户邮编
| 客户所在大区
| 客户所属分公司
| CUST_id
| CUST_name
| CUST_add
| CUST_ZIP
| area
| company
| 000001
| 奉化市康星机械有限公司
| 奉化市浦东陶林路18号
| 110036
| 华东区
| 001
| 000002
| 苏州球豹阀门有限公司
| 苏州市逸仙路899号北区7158室
| 110037
| 华东区
| 002
| 000003
| 测试供应商
| 苏州工业园区唯亭科技园
| 110038
| 华东区
| 002
| 000004
| 上海宝树钢铁有限公司
| 上海嘉定南门沪宜公路3180号
| 110039
| 华东区
| 001
| 000005
| 奉化市凤珠橡塑五金厂
| 奉化市环科园南岳路25号
| 110040
| 华东区
| 001
| 000006
| 上海创盛机电供应站
| 上海市宜城镇氿滨南路326号
| 110041
| 华中区
| 001
| 000007
| 苏州工业园区永晋阀门有限公司
| 苏州市鄞州区云龙镇甲村
| 110042
| 华中区
| 002
| 000008
| 上海建蓓铸造有限公司
| 上海市江东区江东南路145-5号
| 110043
| 华中区
| 001
| 000009
| 无锡市泰伯电镀有限公司
| 无锡市云龙镇三星工业区
| 110044
| 华中区
| 002
| 000010
| 宜兴万利机械制造有限公司
| 山东省蓬莱西城临港工业区
| 110045
| 华中区
| 002
|
客户销售订单表—Orders：客户订单号
| 客户编号
| 销售员编号
| 销售数量
| 销售日期
| 物料编号
| 物料单价
| 销售类型
| corder_no
| CUST_id
| sales
| qty
| sale_date
| item
| price
| order_type
| ZHB0806040001
| 000001
| 001
| 10
| 2008-6-1
| 000001
| 7.12
| I
| ZHB0806040001
| 000001
| 001
| 11
| 2008-6-1
| 000002
| 8.12
| I
| ZHB0806040002
| 000002
| 003
| 12
| 2008-6-2
| 000003
| 9.12
| O
| ZHB0806040003
| 000003
| 004
| 13
| 2008-6-3
| 000004
| 10.12
| I
| ZHB0806040003
| 000003
| 004
| 14
| 2008-6-3
| 000005
| 11.12
| I
| ZHB0806040003
| 000003
| 004
| 15
| 2008-6-3
| 000006
| 12.12
| I
| ZHB0806040004
| 000006
| 007
| 16
| 2008-6-4
| 000001
| 7.12
| I
| ZHB0806040004
| 000006
| 007
| 17
| 2008-6-4
| 000002
| 8.12
| I
| ZHB0806040004
| 000006
| 007
| 18
| 2008-6-4
| 000003
| 9.12
| I
| ZHB0806040004
| 000006
| 007
| 19
| 2008-6-4
| 000004
| 10.12
| I
| ZHB0806040004
| 000006
| 007
| 20
| 2008-6-4
| 000005
| 11.12
| I
|
分公司资料表—Companies分公司编码
| 分公司名称
| 分公司地址
| 分公司邮编
| company
| comy_name
| comy_add
| comy_code
| 001
| 上海分公司
| 上海浦东陆家嘴
| 113006
| 002
| 苏州分公司
| 苏州市
| 113006
|
分别利用企业管理器和查询分析器对所建表进行数据的插入操作。按照以上表格数据进行插入，供下列各题查询使用。要求写出利用查询分析器进行操作的SQL语句。请查询出某一客户某段时间内所有销售情况，以销售日期由大到小排序。答案：SELECT dbo.Customers.CUST_name, dbo.Orders.corder_no,
dbo.Orders.CUST_id, dbo.Orders.sales, dbo.Orders.qty, dbo.Orders.sale_data,
dbo.Orders.item, dbo.Orders.price, dbo.Orders.order_typeFROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON
dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.companyWHERE (dbo.Customers.CUST_id = '000001')ORDER BY dbo.Orders.sale_data DESC请查询出某一客户某段时间内所有销售情况，要求列出客户名称及客户所在大区信息。答案：SELECT TOP 100 PERCENT dbo.Customers.CUST_name, dbo.Orders.corder_no,
dbo.Orders.CUST_id, dbo.Orders.sales, dbo.Orders.qty, dbo.Orders.sale_data,
dbo.Orders.item, dbo.Orders.price, dbo.Orders.order_type, dbo.Customers.area,
dbo.Companies.comy_nameFROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.companyWHERE (dbo.Customers.CUST_id = '000001')ORDER BY dbo.Orders.sale_data DESC请查询出某一客户在大于某个时间的销售情况。答案：SELECT TOP 100 PERCENT dbo.Customers.CUST_name, dbo.Orders.corder_no,
dbo.Orders.sales, dbo.Orders.qty, dbo.Orders.sale_data, dbo.Orders.item,
dbo.Orders.price, dbo.Orders.order_typeFROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON
dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.companyWHERE (dbo.Customers.CUST_id = '000001') AND
(dbo.Orders.sale_data > CONVERT(DATETIME, '2008-06-01 00:00:00', 102))请统计某一客户某段时间内的销售总金额（注意销售类型）。答案：SELECT TOP 100 PERCENT dbo.Customers.CUST_name,
'销售总金额'=sum(case dbo.Orders.order_type
when 'i'
then dbo.Orders.price * dbo.Orders.qty
when 'o' then -1*dbo.Orders.price * dbo.Orders.qty end)
FROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON
dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.companyWHERE (dbo.Customers.CUST_id = '000001') AND
(dbo.Orders.sale_data > CONVERT(DATETIME, '2004-01-01 00:00:00', 102))group by dbo.Customers.CUST_name请统计某一分公司下所有客户某段时间内销售总金额。答案：SELECT TOP 100 PERCENT dbo.Companies.comy_name, '销售总金额'=sum(case dbo.Orders.order_type
when 'i'
then dbo.Orders.price * dbo.Orders.qty
when 'o' then -1*dbo.Orders.price * dbo.Orders.qty end)FROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON
dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.companyWHERE (dbo.Orders.sale_data > CONVERT(DATETIME, '2004-01-01 00:00:00', 102)) and (dbo.Companies.comy_name='上海分公司')group by dbo.Companies.comy_name请统计某一分公司下所有客户某段时间内销售总金额，要求列出分公司地址及邮编。答案：SELECT TOP 100 PERCENT
CUST_ZIP,CUST_add,dbo.Customers.CUST_name, '所有客户某段时间内销售总金额'=sum(case dbo.Orders.order_type
when 'i'
then dbo.Orders.price * dbo.Orders.qty
when 'o' then -1*dbo.Orders.price * dbo.Orders.qty end)FROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON
dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.companyWHERE (dbo.Orders.sale_data > CONVERT(DATETIME, '2004-03-31 00:00:00', 102)) AND
(dbo.Companies.comy_name = '上海')group by CUST_ZIP,CUST_add,dbo.Customers.CUST_name请统计某一分公司下所有客户某段时间内销售总金额，要求列出分公司名称、地址和邮编以及客户的名称、地址和所在大区。答案：SELECT TOP 100 PERCENT
dbo.Companies.comy_name,dbo.Companies.comy_add,CUST_ZIP,CUST_add,dbo.Customers.CUST_name,dbo.Customers.area, '所有客户某段时间内销售总金额'=sum(case dbo.Orders.order_type
when 'i'
then dbo.Orders.price * dbo.Orders.qty
when 'o' then -1*dbo.Orders.price * dbo.Orders.qty end)FROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON
dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.companyWHERE (dbo.Orders.sale_data > CONVERT(DATETIME, '2004-03-31 00:00:00', 102)) AND
(dbo.Companies.comy_name = '上海分公司')group by dbo.Companies.comy_name,dbo.Companies.comy_add,CUST_ZIP,CUST_add,dbo.Customers.CUST_name,dbo.Customers.area请统计某一物料某段时间内的销售总数和总金额。答案：SELECT TOP 100 PERCENT dbo.Orders.item, '销售总数'=sum(qty),'总金额'=sum(dbo.Orders.price*dbo.Orders.qty)FROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON
dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.companyWHERE (dbo.Orders.sale_data > CONVERT(DATETIME, '2004-01-01 00:00:00', 102)) AND
(dbo.Orders.order_type = 'i') AND (dbo.Orders.item = '11')group by dbo.Orders.item请分别计算所有销售情况中，销售数量最大和销售金额最大的数据行答案：SELECT TOP 100 PERCENT
'销售数量最大'=max(dbo.Orders.qty)FROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON
dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.companyWHERE (dbo.Orders.order_type = 'i')SELECT TOP 100 PERCENT
'销售金额最大'=max(dbo.Orders.qty*dbo.Orders.price)FROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON
dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.companyWHERE (dbo.Orders.order_type = 'i')利用子查询，计算出单行销售数量最大的某个客户在某段时间内的销售总金额。答案：SELECT TOP 100 PERCENT '销售总金额'=(dbo.Orders.price*dbo.Orders.qty), dbo.Orders.order_type,
dbo.Orders.sale_data, dbo.Customers.CUST_name, dbo.Orders.itemFROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON
dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.companyWHERE (dbo.Orders.order_type = 'i') AND (dbo.Orders.sale_data > CONVERT(DATETIME,
'2004-01-01 00:00:00', 102))and dbo.Orders.qty=(SELECT TOP 100 PERCENT '单行销售数量最大'=max( dbo.Orders.qty)FROM dbo.Orders LEFT OUTER JOIN
dbo.Customers ON
dbo.Orders.CUST_id = dbo.Customers.CUST_id LEFT OUTER JOIN
dbo.Companies ON dbo.Customers.company = dbo.Companies.company)
|
