sql查询语句学习测试答案

第一部分SQL查询语句的学习 单表查询 1、--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值 use eee SELECT 订购日期,订单ID,客户ID,雇员ID FROM 订单 WHERE 订购日期BETWEEN ＇1996-7-1 00:00:00＇ AND ＇1996-7-15 23:59:59＇ 2、--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。条件是“地区等于华北”并且“联系人头衔等于销售代表”。

use eee SELECT 供应商ID,公司名称,地区,城市,电话 FROM 供应商 WHERE 地区=＇华北＇ AND 联系人职务=＇销售代表＇   3、--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津 use eee SELECT 供应商ID,公司名称,地区,城市,电话 FROM 供应商 WHERE 地区IN(＇华东＇, ＇华南＇) OR 城市=＇天津＇ 4、--查询“Northwind”示例数据库中位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话字段的值 use eee SELECT 供应商ID,公司名称,地区,城市,电话 FROM 供应商 WHERE 地区IN(＇华东＇, ＇华南＇) 多表查询 5、--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等字段的值，并将查询结果按雇员的“姓氏”和“名字”字段的升序排列，“姓氏”和“名字”值相同的记录按“订单 ID”的降序排列 use eee SELECT 订购日期,订单ID,公司名称,姓氏,名字 FROM 订单,雇员,客户 WHERE 订购日期BETWEEN ＇1996-7-1 00:00:00＇ AND ＇1996-7-15 23:59:59＇ AND 订单.雇员ID = 雇员.雇员ID AND 订单.客户ID = 客户.客户ID ORDER BY 姓氏,名字ASC,订单ID DESC 6、--查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称 use eee SELECT 订单.订单ID,公司名称,产品名称 FROM 订单,运货商,产品,订单明细 WHERE 订单.订单ID IN(＇10248＇,＇10254＇) AND 订单.订单ID = 订单明细.订单ID AND 订单明细.产品ID = 产品.产品ID AND 订单.运货商= 运货商.运货商ID 7、--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣 use eee SELECT 订单.订单ID,产品名称,数量,订单明细.单价,折扣 FROM 订单,产品,订单明细 WHERE 订单.订单ID IN(＇10248＇,＇10254＇) AND 订单.订单ID = 订单明细.订单ID AND 订单明细.产品ID = 产品.产品ID 8、--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称及其销售金额 use eee SELECT 订单.订单ID,产品名称,数量*订单明细.单价*(1-折扣) AS 销售金额 FROM 订单,产品,订单明细 WHERE 订单.订单ID IN(＇10248＇,＇10254＇) AND 订单.订单ID = 订单明细.订单ID AND 订单明细.产品ID = 产品.产品ID 综合查询 9、--查询所有运货商的公司名称和电话 use eee SELECT 公司名称,电话 FROM 运货商 10、--查询所有客户的公司名称、电话、传真、地址、联系人姓名和联系人头衔 use eee SELECT 公司名称,电话,传真,地址,联系人姓名,联系人职务 FROM 客户 11、--查询单价介于10至30元的所有产品的产品ID、产品名称和库存量 use eee SELECT 产品ID,产品名称,库存量 FROM 产品 WHERE 单价BETWEEN 10 AND 30 12、--查询单价大于20元的所有产品的产品名称、单价以及供应商的公司名称、电话 use eee SELECT 产品名称,单价,公司名称,电话 FROM 产品,供应商 WHERE 单价> 20 AND 产品.供应商ID = 供应商.供应商ID 13、--查询上海和北京的客户在1996年订购的所有订单的订单ID、所订购的产品名称和数量 use eee SELECT 订单.订单ID,产品名称,数量 FROM 产品,订单,订单明细,客户 WHERE 城市IN (＇上海＇,＇北京＇) AND YEAR(订购日期)=1996 AND 产品.产品ID = 订单明细.产品ID AND 订单.订单ID = 订单明细.订单ID AND 订单.客户ID = 客户.客户ID 14、--查询华北客户的每份订单的订单ID、产品名称和销售金额 use eee SELECT 订单.订单ID,产品名称,数量*订单明细.单价*(1-折扣) AS 销售金额 FROM 产品,订单,订单明细,客户 WHERE 地区= ＇华北＇ AND 产品.产品ID = 订单明细.产品ID AND 订单.订单ID = 订单明细.订单ID AND 订单.客户ID = 客户.客户ID 15、--按运货商公司名称，统计1997年由各个运货商承运的订单的总数量 use eee SELECT 公司名称,COUNT(订单ID) AS 订单总数量 FROM 订单,运货商 WHERE YEAR(发货日期)= 1997 AND 订单.运货商= 运货商.运货商ID GROUP BY 公司名称 16、--统计1997年上半年的每份订单上所订购的产品的总数量 use eee SELECT 订单.订单ID,SUM(数量) AS 总数量 FROM 订单,订单明细 WHERE 订购日期 BETWEEN ＇1997-1-1 00:00:00＇ AND ＇1997-6-30 23:59:59＇ AND 订单.订单ID = 订单明细.订单ID GROUP BY 订单.订单ID 17、--统计各类产品的平均价格 use eee SELECT 类别名称,SUM(单价*库存量)/SUM(库存量) AS 平均价格 FROM 产品,类别 WHERE 产品.类别ID = 类别.类别ID GROUP BY 类别名称 18、--统计各地区客户的总数量 use eee SELECT 地区,COUNT(客户ID) AS 客户数量 FROM 客户 GROUP BY 地区 第二部分SQL查询强化测试 1. 找出供应商名称，所在城市 use eee SELECT 公司名称,城市 FROM 供应商 2. 找出华北地区能够供应海鲜的所有供应商列表。

use eee SELECT 公司名称 FROM 供应商,类别,产品 WHERE 地区=＇华北＇ AND 类别名称=＇海鲜＇ AND 产品.类别ID = 类别.类别ID AND 产品.供应商ID = 供应商.供应商ID 3. 找出订单销售额前五的订单是经由哪家运货商运送的。

use eee SELECT TOP(5)订单.订单ID,SUM(订单明细.单价*(1-折扣)*数量) AS 销售额,公司名称 FROM 订单,订单明细,运货商 WHERE 订单.订单ID = 订单明细.订单ID AND 订单.运货商= 运货商.运货商ID GROUP BY 订单.订单ID,公司名称 ORDER BY 销售额DESC 4. 找出按箱包装的产品名称。

use eee SELECT 产品名称 FROM 产品 WHERE 单位数量LIKE ＇%箱%＇ 5. 找出重庆的供应商能够供应的所有产品列表。

use eee SELECT 产品名称 FROM 产品,供应商 WHERE 产品.供应商ID = 供应商.供应商ID AND 城市=＇重庆＇ 6. 找出雇员郑建杰所有的订单并根据订单销售额排序。

use eee SELECT 订单.订单ID,SUM(单价*数量*(1-折扣)) AS 销售额 FROM 订单,订单明细,雇员 WHERE 姓氏+名字=＇郑建杰＇ AND 订单.订单ID = 订单明细.订单ID AND 雇员.雇员ID = 订单.雇员ID GROUP BY 订单.订单ID ORDER BY 销售额DESC 7. 找出订单10284的所有产品以及订单金额，运货商。

use eee SELECT 订单.订单ID,产品名称,订单明细.单价*数量*(1-折扣) AS 订单金额,公司名称 FROM 订单,订单明细,产品,运货商 WHERE 订单.订单ID = 10284 AND 订单.订单ID = 订单明细.订单ID AND 订单明细.产品ID = 产品.产品ID AND 订单.运货商= 运货商.运货商ID 8. 建立产品与订单的关联。

use eee SELECT 产品.*,订单.* FROM 产品,订单,订单明细 WHERE 订单.订单ID = 订单明细.订单ID AND 订单明细.产品ID = 产品.产品ID 9. 计算销量前10位的订单明细，结果集返回订单ID，订单日期，公司名称，发货日期，销售额，并排序 use eee SELECT 订单.订单ID,订购日期,公司名称,发货日期,SUM(单价*数量*(1-折扣)) AS 销售额 FROM 订单,订单明细,客户 WHERE 订单.订单ID = 订单明细.订单ID AND 订单.订单ID IN(SELECT TOP(10) 订单ID FROM 订单明细 GROUP BY 订单ID ORDER BY SUM(数量) DESC) AND 订单.客户ID = 客户.客户ID GROUP BY 订单.订单ID,订购日期,公司名称,发货日期 ORDER BY 销售额DESC 10. 按年度统计销售额 use eee SELECT YEAR(订购日期) AS 年份,SUM(单价*数量*(1-折扣)) AS 销售额 FROM 订单,订单明细 WHERE 订单.订单ID = 订单明细.订单ID GROUP BY YEAR(订购日期) 11. 查询供应商中能够供应的产品样数最多的供应商。

SELECT TOP(1) 公司名称,COUNT(产品ID) AS 提供产品种类数量 FROM 产品,供应商 WHERE 产品.供应商ID = 供应商.供应商ID GROUP BY 公司名称 ORDER BY COUNT(产品ID) DESC 12. 查询产品类别中包含的产品数量最多的类别。

SELECT TOP(1) 类别名称,COUNT(产品ID) AS 产品种类数量 FROM 产品,类别 WHERE 产品.类别ID = 类别.类别ID GROUP BY 类别名称 ORDER BY COUNT(产品ID) DESC 13. 找出所有的订单中经由哪家运货商运货次数最多。

SELECT TOP(1) 公司名称,COUNT(运货商ID) AS 运货次数 FROM 订单,运货商 WHERE 订单.运货商= 运货商.运货商ID GROUP BY 公司名称 ORDER BY COUNT(运货商ID) DESC 14. 按类别，产品分组，统计销售额。

use eee SELECT 产品名称,类别名称,SUM(订单明细.单价*数量*(1-折扣)) AS 销售额 FROM 产品,类别,订单明细 WHERE 产品.产品ID = 订单明细.产品ID AND 产品.类别ID = 类别.类别ID GROUP BY 产品名称,类别名称 15. 查询海鲜类别最大的一笔订单。

SELECT TOP(1) 订单ID,SUM(订单明细.单价*数量*(1-折扣)) AS 销售额 FROM 产品,类别,订单明细 WHERE 类别名称=＇海鲜＇ AND 产品.产品ID = 订单明细.产品ID AND 产品.类别ID = 类别.类别ID GROUP BY 订单ID ORDER BY 销售额DESC 16. 按季度统计销售量 SELECT YEAR(订购日期) AS 年度,datename(quarter,订购日期) AS 季度, SUM(订单明细.单价*数量*(1-折扣)) AS 销售额 FROM 订单,订单明细 WHERE 订单.订单ID = 订单明细.订单ID GROUP BY YEAR(订购日期),datename(quarter,订购日期) ORDER BY 年度,季度 17. 查出订单总额超出5000的所有订单，客户名称，客户所在地区。

SELECT 订单.订单ID,公司名称,地区 FROM 订单,订单明细,客户 WHERE 客户.客户ID = 订单.客户ID AND 订单.订单ID = 订单明细.订单ID GROUP BY 订单.订单ID,公司名称,地区 HAVING SUM(单价*数量*(1-折扣))>5000 18. 查询哪些产品的年度销售额低于2000 SELECT YEAR(订购日期),产品名称,SUM(订单明细.单价*数量*(1-折扣)) AS 销售额 FROM 订单,产品,订单明细 WHERE 订单.订单ID = 订单明细.订单ID AND 产品.产品ID = 订单明细.产品ID GROUP BY YEAR(订购日期),产品名称 HAVING SUM(订单明细.单价*数量*(1-折扣))<2000 19. 查询所有订单ID开头为102的订单 SELECT 订单.* FROM 订单 WHERE 订单ID LIKE ＇102%＇ 20. 查询所有“中硕贸易”，“学仁贸易”，“正人资源”，“中通”客户的订单，（要求使用in函数）
SELECT 订单.* FROM 订单,客户 WHERE 订单.客户ID = 客户.客户ID AND 公司名称IN (＇中硕贸易＇,＇学仁贸易＇,＇正人资源＇,＇中通＇) -----以上考试时间一个半小时 21. 查询所有订单中月份不是单数的订单。

SELECT 订单ID,订购日期 FROM 订单 WHERE MONTH(订购日期)%2=0 22. 分别各写一个查询，得到订单中折扣为15%，20%的所有订单，并将两个查询再组成一个。

SELECT 订单.* FROM 订单,订单明细 WHERE 折扣IN(＇0.15＇,＇0.2＇) AND 订单.订单ID = 订单明细.订单ID 23. 找出在入职时已超过30岁的所有员工信息 use eee SELECT 雇员.* FROM 雇员 WHERE YEAR(雇用日期)-YEAR(出生日期)>=30 24. 找出所有单价大于30的产品(附加要求，产品类别，供应商作为参数，当产品类别和供应商都为空的时候，nofilter) SELECT 产品名称,类别名称,公司名称 FROM 产品,类别,供应商 WHERE 单价>30 AND 产品.类别ID = 类别.类别ID AND 产品.供应商ID = 供应商.供应商ID AND 类别名称IS NOT NULL AND 公司名称IS NOT NULL 25. 查询所有库存产品的总额，并按照总额排序 SELECT 产品ID,产品名称,SUM(单价*库存量) AS 库存总额 FROM 产品 GROUP BY 产品ID,产品名称 ORDER BY 库存总额DESC 26. 检索出职务为销售代表的所有订单中，每笔订单总额低于2000的订单明细，以及相关供应商名称。

SELECT 订单明细.*,公司名称 FROM 订单,订单明细,供应商,产品,雇员 WHERE 雇员.职务=＇销售代表＇ AND 订单明细.单价*数量*(1-折扣)<2000 AND 订单明细.订单ID = 订单.订单ID AND 订单.雇员ID = 雇员.雇员ID AND 产品.产品ID = 订单明细.产品ID AND 产品.供应商ID = 供应商.供应商ID 27. 检索出向艾德高科技提供产品的供应商所在的城市。

use eee SELECT 客户.公司名称,供应商.公司名称AS 供应商,供应商.城市 FROM 供应商,客户,订单明细,产品,订单 WHERE 客户.公司名称= ＇艾德高科技＇ AND 订单.客户ID = 客户.客户ID AND 产品.供应商ID = 供应商.供应商ID AND 订单.订单ID = 订单明细.订单ID AND 订单明细.产品ID = 产品.产品ID 28. 计算每一笔订单的发货期（从订购到发货），运货期（从发货到到货）的时常，并按照发货期从长到短的顺序进行排序。

use eee SELECT 订单ID,datediff(day,订购日期,发货日期) AS 发货期时常,datediff(day,发货日期,到货日期) AS 运货期时常 FROM 订单 GROUP BY 订单ID,订购日期,发货日期,到货日期 ORDER BY 发货期时常 DESC 29. 将产品表和运货商两个无关的表整合为一个表 SELECT DISTINCT 运货商.*,产品.* FROM 订单,运货商,产品,订单明细 WHERE 订单.订单ID = 订单明细.订单ID AND 订单明细.产品ID = 产品.产品ID AND 订单.运货商= 运货商.运货商ID ORDER BY 运货商ID,产品ID USE eee SELECT DISTINCT 运货商ID,公司名称,COUNT(订单明细.订单ID) AS 运送次数,产品.产品名称,产品.产品ID FROM 订单,订单明细,产品,运货商 WHERE 订单.运货商=运货商.运货商ID AND 订单.订单ID=订单明细.订单ID AND 订单明细.产品ID=产品.产品ID GROUP BY 运货商ID,公司名称,产品.产品名称,产品.产品ID ORDER BY 运货商ID,产品.产品ID 30. 获取在北京工作并向福星制衣厂股份有限公司发送过订单的职工名称。

use eee SELECT DISTINCT 姓氏+名字AS 职工 FROM 雇员,订单,客户 WHERE 雇员.城市=＇北京＇ AND 客户.公司名称= ＇福星制衣厂股份有限公司＇ AND 雇员.雇员ID = 订单.雇员ID AND 订单.客户ID = 客户.客户ID GROUP BY 姓氏,名字