SQL基础编程练习及答案

根据题目和运行结果写出sql的运行代码

where条件筛选

1.所有价格在10到20这个区间的产品
效果：
答案：

select *
from 产品
where 产品_售价 between 10 and 20

2.找到所有的笔
效果：

答案：

select *
from 产品 as 'h'
where h.产品_售价 between 10 and 20 and h.产品_描述 like '%笔%'

3.所有河南、山东和山西的客户
效果：

答案：

select*
from 客户 
where 客户_省 in ('河南','山东','山西')

4.所有广东的非传媒公司的所有信息
效果：

答案：

select*
from 客户 as 'h'
where h.客户_省 in ('广东') and h.客户_名称 not in ('传媒')

5.想看看客户：万讯公司都有哪些子公司有业务往来。但是记不起来是万讯还是万迅了
效果：

答案：

select*
from 客户 as 'h'
where h.客户_名称 like '%万讯%' or h.客户_名称 like '%万迅%'

基础条件筛选2

6.得到极简文具公司数据库有业务的省份
效果：

答案：

select distinct
h.客户_省
from 客户 as h

7.得到极简文具公司数据库有业务的月份
效果：

答案：

select distinct 
h.销售订单_日期_月
from 销售 as h
order by 销售订单_日期_月 asc

8.得到极简文具公司数据库的重要客户ID
效果：找到销售表格中订单总金额排名前20的客户ID
要求：
-不能有重复的客户ID
-加入一列常量列客户类型，列内容为最大额订单客户前20

答案：

select distinct 
'最大额订单客户前20' as '客户类型',
h.客户_ID
from 销售 as h
order by 销售数量*产品_价格 desc
limit 20

9.看看平安银行股票信息2020年的最好表现

• 问题量转型
• 找到2020年平安银行股票信息单日均价最高的5天的全部信息和均价
• 单日均价 = 单日成交金额 / 单日交易量

要求:
-要按照均价降序排列
效果：

答案：

select *,
h.money/volume as 'avg'
from pingan_bank_table as h
where h.date like '%2020%'
order by avg desc
limit 5

10. 看看平安银行股票的整体最好表现
问题量转型
-查询平安银行股票单日涨幅最高的前十天
要求: 单日涨幅=当日收盘价-当日开盘价
效果：

答案：

select *,
 h.close-open as '单日涨幅'
from pingan_bank_table as h
order by 单日涨幅 desc
limit 10

函数

11.得到极简文具公司数据库中销售表中每一个产品的总销量
要求：销量从大到小排列
效果：

答案：

select 
h.产品_代码 as '产品',
sum(h.销售数量) as '总销量'
from 销售 as h
group by h.产品_代码
order by 总销量 desc

12.得到极简文具公司数据库中销售表中每一个产品的总订单量
要求：订单数量从大到小排列
效果：

答案：

select h.产品_代码 as '产品',
count(h.产品_代码) as '总订单量'
from 销售 as h
group by h.产品_代码
order by 总订单量 desc

13.得到极简文具公司数据库中销售表中每一个产品的多项汇总信息
要求：订单总量，订单总收入，平均一笔订单的收入，要按照平均订单收入从大到小排序，平均订单收入保留小数点后2位
效果：

答案：

select 
h.产品_代码 as '产品',

count(h.产品_代码) as '总订单量',
sum(h.销售数量)*产品_价格 as '订单总收入',
round(avg(h.销售数量*h.产品_价格),2) as '平均订单收入'

from 销售 as h 
group by h.产品_代码
order by 平均订单收入 desc

14. 把上一题的结果细化
要求：给后三列添加单位， 字母与数字之间要有一个空格
效果：

答案：1.

select
  h.产品_代码 as '产品',
  concat(count(h.销售_订单_ID),' U') as '总订单量',
  concat(sum(h.销售数量 * h.产品_价格),' RMB') as '订单总收入',
  concat(round(avg(h.销售数量 * h.产品_价格),2),' RMB') as '平均订单收入'
from
  销售 as h
group by
  产品
  order by 平均订单收入 desc;

select h.产品_代码 as '产品',
count(h.产品_代码) || ' U '  as '总订单量',
(sum(h.销售数量)*产品_价格)  || ' RMB' as '订单总收入',
round(avg(h.销售数量*h.产品_价格),2) || ' RMB' as '平均订单收入'
from 销售 as h
group by h.产品_代码
order by 平均订单收入 desc

子查询

找到合适的极简文具公司数据库相关数据
15.比马克笔贵的笔
效果：

答案：

select *
from 产品 as c
where c.产品_描述 like '%笔' and c.产品_描述 in(select s.产品_描述
from 产品 as s
where s.产品_售价 > (select h.产品_售价
from 产品 as h
where h.产品_描述='马克笔'))

16.单笔购买产品数量大于50000的客户的信息
效果：

答案：

select *
from 客户 as s
where s.客户_ID in(select h.客户_ID
from 销售 as h
where 销售数量>50000)

17.没有任何单笔订单销量>30000的省
效果：

答案：

select h.客户_省
from 客户 as h
where h.客户_ID not in(
  select p.客户_ID
  from 销售 as p
  where p.销售数量 > 30000);

18.没有任何单笔订单销量>45000的月份
要求：在XXX之前加上关键字distinct可以确保显示的XXX内容不重复
效果：

答案：

select distinct h.销售订单_日期_月
from 销售 as h
where h.销售订单_日期_月 not in(
  select p.销售订单_日期_月
  from 销售 as p
  where p.销售数量>45000);

JOIN

19.找到极简文具公司数据库中所有产品的销售数量
要求：按照销售数量倒序排列，会用到对产品_描述的group by
效果：

答案：

select s.产品_描述,
       sum(h.销售数量) as '销售数量'
from 销售 as h join 产品 as s
on h.产品_代码=s.产品_代码
group by s.产品_描述
order by 销售数量 desc

20.找到极简文具公司数据库中所有产品的销售总额
要求：按照销售总额倒序排列，会用到对产品_描述的group by
效果：

答案：

select s.产品_描述,
sum(h.销售数量)*产品_价格 as '销量总额'
from 销售 as h join 产品 as s
on h.产品_代码=s.产品_代码
group by s.产品_描述
order by 销量总额 desc

21.找到极简文具公司数据库中所有省的总销售额前10
要求：按照全省销售额倒序排列，会用到对客户_省的group by
效果：

答案：

select s.客户_省,
sum(h.销售数量*h.产品_价格) as '全省销售额'
from 销售 as h join 客户 as s
on h.客户_ID=s.客户_ID
group by s.客户_省
order by 全省销售额 desc
limit 10

null&内连接&外连接

22.统计极简文具公司数据库_升级版中有产品_代码的产品有多少个?
要求：结果列标题为有代码产品个数
效果：

答案：

select count(h.产品_代码) as '有代码产品个数'
from 产品 as h

23.统计极简文具公司数据库_升级版中无产品_代码的产品有多少个?
要求：结果列标题为无代码产品个数
效果：

答案：

select count(h.产品_代码 is null) as '无代码产品个数'
from 产品 as h
where h.产品_代码 is null

24.统计极简文具公司数据库_升级版中无产品_售价的产品有多少个?
效果：

答案：

select
 (select 
  count(h.产品_描述)
  from 产品 as h
  where h.产品_售价 is NULL) as "无价格产品个数",
    (select
     count(p.产品_售价)
     from 产品 as p) as "有价格产品个数"

25.统计极简文具公司数据库_升级版中所有产品对应的订单数量
要求：没有订单的要统计订单数量等于0，要按照订单数量结果正向排序
效果：

答案：

select h.产品_代码,h.产品_描述,count(s.产品_代码) as '订单数量'
from 产品 as h left join 销售 as s on h.产品_代码=s.产品_代码
group by h.产品_描述
order by 订单数量 asc

综合测试题1

电子产品专卖店数据库
26.显示中国的品牌
效果：
答案：

select*
from 品牌 as 'h'
where h.所属国家='中国'

27. 显示所有中国产品明细

select *
from 产品明细 as h join 品牌 as s on h.品牌名称=s.品牌名称
where s.所属国家='中国'

28. 显示所有类别产品的总盈利

select h.产品类别,sum(s.销售数量*h.销售单价-s.销售数量*h.采购价格)/10000 as '总盈利_万元'
from 产品明细 as h join 销售明细 as s on h.产品编号=s.产品编号
group by h.产品类别
order by 总盈利_万元 desc

29. 列举总盈利最高前3名的产品
    

select h.产品名称,sum(s.销售数量*h.销售单价-s.销售数量*h.采购价格)/10000 as '总盈利_万元'
from 产品明细 as h join 销售明细 as s on h.产品编号=s.产品编号
group by h.产品名称
order by 总盈利_万元 desc
limit 3

30. 显示B品牌平板的总盈利（总销售额减去总采购额）（以万元记）

select h.产品名称,sum(s.销售数量*h.销售单价-s.销售数量*h.采购价格)/10000 as '总盈利_万元'
from 产品明细 as h join 销售明细 as s on h.产品编号=s.产品编号
where h.产品名称='B品牌平板'

31. 得到A品牌手机的总销售额(以万元计算)

select h.产品名称,sum(s.销售数量)*h.销售单价/10000
from 产品明细 as h join 销售明细 as s on h.产品编号=s.产品编号
where h.产品名称='A品牌手机'

32. 显示所有产品的销售数量（以销售数量降序排列）

select h.产品类别,sum(s.销售数量) as '销售数量'
from 产品明细 as h join 销售明细 as s on h.产品编号=s.产品编号
group by h.产品类别
order by 销售数量 desc

33. 显示每一个品牌的电子产品的总销售数量
    

select h.产品类别,sum(s.销售数量) as '销售数量'
from 产品明细 as h join 销售明细 as s on h.产品编号=s.产品编号

where h.产品类别='电脑'

34. 显示最贵的手机的详细信息(包括国家)

select *
from 产品明细 as h join 品牌 as s on h.品牌名称=s.品牌名称
where h.产品类别='手机' 
order by h.销售单价 desc
limit 1

35. 显示不同国家所有电子产品平均销售单价

select s.所属国家,avg(h.销售单价) as '平均销售单价'
from 产品明细 as h join 品牌 as s on h.品牌名称=s.品牌名称
group by s.所属国家

36. 列出英国和美国的手机的详细信息
    

select *
from 产品明细 as h join 品牌 as s on h.品牌名称=s.品牌名称
where s.所属国家 in ('美国','英国') and h.产品类别='手机'

37. 显示盈利最好的5个城市

select s.门店城市,(sum(h.销售单价*s.销售数量)-sum(h.采购价格*s.销售数量))/10000 as '总盈利_万元'
from 产品明细 as h join 销售明细 as s on h.产品编号=s.产品编号
group by s.门店城市 
order by 总盈利_万元 desc
limit 5

38. 汇总年度总销量(数量)

select h.年度,sum(s.销售数量) as '总销量'
from 日期 as h join 销售明细 as s on h.日期=s.订单日期
where h.年度 in (2017,2018)
group by h.年度

39. 汇总年度的季度总销量
    

select h.年度,h.季度,sum(s.销售数量) as '总销量'
from 日期 as h join 销售明细 as s on h.日期=s.订单日期

group by h.年度,h.季度

40. 同比对比2017和2018年的3,4季度的销量

select h.年度,h.季度,sum(s.销售数量) as '总销量'
from 日期 as h join 销售明细 as s on h.日期=s.订单日期
where h.季度 in (3,4)
group by h.年度,h.季度

41. 汇总年度销售总额
    

select c.年度,round(sum(h.销售单价*s.销售数量)/100000000,2) as '销售总额_亿元'
from 产品明细 as h join 销售明细 as s on h.产品编号=s.产品编号 
                  join 日期 as c on s.订单日期=c.日期
where c.年度 in (2017,2018)
group by c.年度

42. 环比对比2018年6,7,8月销售总额
    

select c.年度,c.月,round(sum(h.销售单价*s.销售数量)/100000000,2) as '销售总额_亿元'
from 产品明细 as h join 销售明细 as s on h.产品编号=s.产品编号 
                  join 日期 as c on s.订单日期=c.日期
where c.月 in (6,7,8) and c.年度=2018
group by c.月,c.年度