习题2-SQL查询语句测试

SQL测试题第二部分SQL查询强化测试结合第一天所看的SQL查询内容。用一天时间独立完成以下习题,并将相应的查询语句和视图截图贴到相应题目下方。1.找出供应商名称，所在城市SELECT公司名称,城市from`供应商`2.找出华北地区能够供应海鲜的所有供应商列表。SELECT产品.`产品名称`,`供应商`.公司名称FROM`供应商`,`类别`,`产品`WHERE`供应商`.`供应商ID`=产品.`供应商ID`and`类别`.`类别ID`=`产品`.`类别ID`and`供应商`.`地区`='华北'and`类别`.`类别名称`='海鲜'3.找出订单销售额前五的订单是经由哪家运货商运送的。select订单明细.单价*订单明细.数量*(1-订单明细.`折扣`)as销售额from订单明细,运货商,`订单`where`订单明细`.`订单ID`=`订单`.`订单ID`and`订单`.`运货商`=`运货商`.`运货商ID`orderby销售额desc4.找出按箱包装的产品名称。SELECT*FROM`产品`WHERE`单位数量`LIKE'%箱%'5.找出重庆的供应商能够供应的所有产品列表。SELECT`产品`.`产品名称`FROM`供应商`,`产品`WHERE`供应商`.`城市`='重庆'And`产品`.`供应商ID`=`供应商`.`供应商ID`6.找出雇员郑建杰所有的订单并根据订单销售额排序。SELECT`订单`.`订单ID`,sum(`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`))销售额FROM`订单`,`雇员`,`订单明细`WHERE`订单明细`.`订单ID`=`订单`.`订单ID`and`订单`.`雇员ID`=`雇员`.`雇员ID`and`雇员`.`姓氏`='郑'and`雇员`.`名字`='建杰'GROUPBY`订单`.`订单ID`7.找出订单10284的所有产品以及订单金额，运货商。SELECT产品.产品名称,订单明细.单价*订单明细.数量*(1-订单明细.折扣)as订单金额,`运货商`.`公司名称`FROM订单,订单明细,产品,`运货商`where`订单`.`订单ID`=`订单明细`.`订单ID`and`订单明细`.`产品ID`=`产品`.`产品ID`and订单.`订单ID`='10284'and`运货商`.`运货商ID`=`订单`.`运货商`8.建立产品与订单的关联。SELECT*FROM订单,订单明细,产品where`订单`.`订单ID`=`订单明细`.`订单ID`and`订单明细`.`产品ID`=`产品`.`产品ID`9.计算销量前10位的订单明细，结果集返回订单ID，订单日期，公司名称，发货日期，销售额，并排序SELECTDISTINCT`订单明细`.`订单ID`,`订单`.`订购日期`,`客户`.`公司名称`,`订单`.`发货日期`,`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`)as销售额,`订单明细`.`数量`FROM订单,订单明细,产品,`客户`where`订单`.`订单ID`=`订单明细`.`订单ID`and订单.`客户ID`=`客户`.`客户ID`ORDERBY`订单明细`.`数量`desclimit1010.按年度统计销售额SELECTDISTINCTsum(`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`))销售额,sum(`订单明细`.`数量`)数量FROM订单,订单明细,产品where`订单`.`订单ID`=`订单明细`.`订单ID`andyear(`订单`.`订购日期`)BETWEEN'1996'AND'1998'GROUPBYyear(`订单`.`订购日期`)------以上开始时间一个半小时11.查询供应商中能够供应的产品样数最多的供应商。select`产品`.`供应商ID`,`供应商`.`公司名称`,count(产品.`产品ID`)from产品,供应商where`产品`.`供应商ID`=`供应商`.`供应商ID`groupby`产品`.`供应商ID`ORDERBYcount(产品.`产品ID`)desc12.查询产品类别中包含的产品数量最多的类别。select`类别`.`类别名称`,count(产品.`产品ID`)from产品,`类别`where`产品`.`类别ID`=类别.`类别ID`groupby`类别`.`类别名称`ORDERBYcount(产品.`产品ID`)desc13.找出所有的订单中经由哪家运货商运货次数最多。select`运货商`.`公司名称`,count(订单.`订单ID`)as订单总数from`订单`,`运货商`where`订单`.`运货商`=`运货商`.`运货商ID`groupby`运货商`.`公司名称`14.按类别，产品分组，统计销售额。SELECTDISTINCT`类别`.`类别名称`,`产品`.`产品名称`,`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`)as销售额,`订单明细`.`数量`FROM订单,订单明细,产品,`类别`where`订单`.`订单ID`=`订单明细`.`订单ID`and`产品`.`类别ID`=`类别`.`类别ID`and`订单明细`.`产品ID`=`产品`.`产品ID`15.查询海鲜类别最大的一笔订单。SELECTDISTINCT`类别`.`类别名称`,`产品`.`产品名称`,`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`)as销售额,`订单明细`.`数量`FROM订单,订单明细,产品,`类别`where`订单`.`订单ID`=`订单明细`.`订单ID`and`产品`.`类别ID`=`类别`.`类别ID`and`订单明细`.`产品ID`=`产品`.`产品ID`and`类别`.`类别名称`='海鲜'ORDERBY销售额desclimit116.按季度统计销售量selectyear(订单.订购日期)年份,sum(casewhenmonth(订单.订购日期)between1and3then`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`)else0end)一季度销售金额,sum(casewhenmonth(订单.订购日期)between4and6then`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`)else0end)二季度销售金额,sum(casewhenmonth(订单.订购日期)between7and9then`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`)else0end)三季度销售金额,sum(casewhenmonth(订单.订购日期)between10and12then`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`)else0end)四季度销售金额from订单,订单明细where订单.订单ID=订单明细.订单IDgroupbyyear(订单.订购日期)17.查出订单总额超出5000的所有订单，客户名称，客户所在地区。SELECT`订单`.`订单ID`,`客户`.`公司名称`,`客户`.`地区`,sum(`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`))订单总额FROM`订单`,`订单明细`,`客户`WHERE`订单明细`.`订单ID`=`订单`.`订单ID`and`订单`.`客户ID`=`客户`.`客户ID`GROUPBY`订单`.`订单ID`HAVING订单总额500018.查询哪些产品的年度销售额低于2000SELECT`产品`.`产品名称`,sum(`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`))订单总额FROM`订单`,`订单明细`,`产品`WHERE`订单明细`.`订单ID`=`订单`.`订单ID`and`订单明细`.`产品ID`=`产品`.`产品ID`GROUPBY`产品`.`产品ID`HAVING订单总额200019.查询所有订单ID开头为102的订单SELECT*FROM`订单`WHERE订单IDLIKE'102%'20.查询所有“中硕贸易”，“学仁贸易”，“正人资源”，“中通”客户的订单，（要求使用in函数）SELECT`订单`.`订单ID`,`客户`.`公司名称`FROM`订单`,`客户`WHERE`客户`.`公司名称`in('中硕贸易','学仁贸易','正人资源','中通')and`订单`.`客户ID`=`客户`.`客户ID`-----以上考试时间一个班销售21.查询所有订单中月份不是单数的订单SELECT*from`订单`whereRIGHT(month(`订购日期`),1)*1%2!=122.分别各写一个查询，得到订单中折扣为15%，20%的所有订单，并将两个查询再组成一个。select*from订单,订单明细whereCAST(`订单明细`.`折扣`asCHAR)in('0.15','0.2')and`订单`.`订单ID`=`订单明细`.`订单ID`23.找出在入职时已超过30岁的所有员工信息select*from`雇员`whereYEAR(雇用日期)-YEAR(出生日期)'30'（本题数据有问题，如下图）24.找出所有单价大于30的产品(附加要求，产品类别，供应商作为参数，当产品类别和供应商都为空的时候，nofilter)select`产品`.`产品名称`,类别.`类别ID`,`供应商`.`公司名称`from`产品`,`类别`,`供应商`where`单价`'30'and`产品`.`类别ID`=`类别`.`类别ID`and`产品`.`供应商ID`=`供应商`.`供应商ID`z25.查询所有库存产品的总额，并按照总额排序select*,`库存量`*`单价`as总额from`产品`orderby总额26.检索出职务为销售代表的所有订单中，每笔订单总额低于2000的订单明细，以及相关供应商名称。SELECT`供应商`.`公司名称`,sum(`订单明细`.`单价`*`订单明细`.`数量`*(1-`订单明细`.`折扣`))订单总额FROM`订单`,`订单明细`,`产品`,`客户`,`供应商`WHERE`订单明细`.`订单ID`=`订单`.`订单ID`and`订单明细`.`产品ID`=`产品`.`产品ID`and`客户`.`客户ID`=`订单`.`客户ID`and`供应商`.`供应商ID`=`产品`.`供应商ID`and`客户`.`联系人职务`='销售代表'GROUPBY`供应商`.`公司名称`HAVING订单总额'2000'27.检索出向艾德高科技提供产品的供应商所在的城市。SELECT`客户`.`公司名称`,`供应商`.`城市`FROM`订单`,`订单明细`,`产品`,`客户`,`供应商`WHERE`订单明细`.`订单ID`=`订单`.`订单ID`and`订单明细`.`产品ID`=`产品`.`产品ID`and`客户`.`客户ID`=`订单`.`客户ID`and`供应商`.`供应商ID`=`产品`.`供应商ID`and`客户`.`公司名称`='艾德高科技'28.计算每一笔订单的发货期（从订购到发货），运货期（从发货到到货）的时常，并按照发货期从长到短的顺序进行排序。SELECT订单ID,TIMESTAMPDIFF(day,`订购日期`,`发货日期`)as发货期,TIMESTAMPDIFF(day,`到货日期`,`发货日期`)as运货期FROM`订单`orderby发货期desc29.将产品表和运货商两个无关的表整合为一个表SELECT`产品`.*,`运货商`.*FROM`产品`,`运货商`,`订单`,`订单明细`where`产品`.`产品ID`=`订单明细`.`产品ID`and`订单明细`.`订单ID`=`订单`.`订单ID`and`运货商`.`运货商ID`=`订单`.`