# SQL语法-题库题解
- 主要以MySQL的语法解决问题
- 用实战的方式,熟悉SQL语法
# 目录
[TOC]
# 《SQL必知必会》配套题单-题解
# 1.检索数据
# 60. 从 Customers 表中检索所有的 ID
select cust_id from Customers
1
# 61.检索并列出已订购产品的清单
- 记忆:distinct
select distinct(prod_id) from OrderItems
1
# 62. 检索所有列
select * from Customers
1
# 2.排序检索数据
# 63. 检索顾客名称并且排序
select cust_name
from Customers
order by cust_name desc
1
2
3
2
3
# 64. 对顾客ID和日期排序
select cust_id , order_num
from Orders
order by cust_id , order_date desc
1
2
3
2
3
# 65. 按照数量和价格排序
select quantity , item_price
from OrderItems
order by quantity desc, item_price desc
1
2
3
2
3
# 66. 检查SQL语句
SELECT vend_name
FROM Vendors
ORDER BY vend_name DESC;
1
2
3
2
3
# 3.过滤数据
# 67. 返回固定价格的产品
select prod_id , prod_name
from Products
where 9.49=prod_price
1
2
3
2
3
# 68.返回更高价格的产品
select prod_id , prod_name
from Products
where prod_price >= 9
1
2
3
2
3
# 69.返回产品并且按照价格排序
select prod_name , prod_price
from Products
where prod_price between 3 and 6
order by prod_price
1
2
3
4
2
3
4
# 70.返回更多的产品
select distinct(order_num)
from OrderItems
where quantity >= 100
1
2
3
2
3
# 4.高级数据过滤
# 71.检索供应商名称
select vend_name
from Vendors
where vend_country='USA' and vend_state='CA'
1
2
3
2
3
# 72.检索并列出已订购产品的清单
select order_num , prod_id , quantity
from OrderItems
where prod_id in ( 'BR01' , 'BR02' , 'BR03' ) and quantity >= 100
1
2
3
2
3
# 73.返回所有价格在 3美元到 6美元之间的产品的名称和价格
select prod_name , prod_price
from Products
where prod_price between 3 and 6
order by prod_price
1
2
3
4
2
3
4
# 74.纠错2
SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name
1
2
3
4
2
3
4
# 5.用通配符进行过滤
# 75.检索产品名称和描述(一)
select prod_name , prod_desc
from Products
where prod_desc like '%toy%'
1
2
3
2
3
# 76.检索产品名称和描述(二)
select prod_name , prod_desc
from Products
where not prod_desc like '%toy%'
1
2
3
2
3
# 77.检索产品名称和描述(三)
select prod_name , prod_desc
from Products
where
prod_desc like '%toy%'
and
prod_desc like '%carrots%'
1
2
3
4
5
6
2
3
4
5
6
# 78.检索产品名称和描述(四)
select prod_name , prod_desc
from Products
where
prod_desc like '%toy%carrots%'
1
2
3
4
2
3
4
# 6.创建计算字段
# 79.别名
select vend_id
, vend_name as vname
,vend_address as vaddress
,vend_city as vcity
from Vendors
order by vend_name
1
2
3
4
5
6
2
3
4
5
6
# 80.打折
select prod_id , prod_price
, prod_price*0.9 as sale_price
from Products
1
2
3
2
3
# 7.使用函数处理数据
# 81.顾客登录名
select cust_id, cust_name ,
upper(
concat(
substring(cust_contact,1,2)
, substring(cust_city,1,3)
)
)
as user_login
from Customers
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 82.返回 2020 年 1 月的所有订单的订单号和订单日期
select order_num , order_date
from Orders
where substring(order_date,1,7)='2020-01'
order by order_date
1
2
3
4
2
3
4
# 8.汇总数据
# 83.确定已售出产品的总数
select sum(quantity) as items_ordered
from OrderItems
1
2
2
# 84.确定已售出产品项 BR01 的总数
select sum(quantity) as items_ordered
from OrderItems
where prod_id='BR01'
1
2
3
2
3
# 85.确定 Products 表中价格不超过 10 美元的最贵产品的价格
select sum(quantity) as items_ordered
from OrderItems
where prod_id='BR01'
1
2
3
2
3
# 9.分组数据
# 86.返回每个订单号各有多少行数 (opens new window)
select order_num , count(*) as order_lines
from OrderItems
group by order_num
order by order_lines
1
2
3
4
2
3
4
# 87.每个供应商成本最低的产品
select vend_id, min(prod_price) as cheapest_item
from Products
group by vend_id
order by cheapest_item
1
2
3
4
2
3
4
# 88.确定最佳顾客
select order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num
1
2
3
4
5
2
3
4
5
# 89.确定最佳顾客的另一种方式(一)
select
order_num,
sum(quantity * item_price) as total_price
from
OrderItems
group by
order_num
having
sum(quantity * item_price) >= 1000
order by
order_num
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 90.纠错3
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
1
2
3
4
5
2
3
4
5
# 10.使用子查询
# 91.返回购买价格为 10 美元或以上产品的顾客列表 (opens new window)
select order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num
1
2
3
4
5
2
3
4
5
- 下面的不行!!!必须是上面的where
select order_num
from OrderItems
where sum(quantity)>=100 (错误答案)
group by order_num
order by order_num
1
2
3
4
5
2
3
4
5
# 92.确定哪些订单购买了 prod_id 为 BR01 的产品(一) (opens new window)
select cust_id,order_date
from Orders
where order_num in
(
select order_num
from OrderItems
where prod_id ='BR01')
1
2
3
4
5
6
7
2
3
4
5
6
7
# 93.返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件(一) (opens new window)
select cust_email from Customers
where cust_id in
(select cust_id from Orders
where order_num in
(select order_num from OrderItems
where prod_id='BR01'))
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 94.返回每个顾客不同订单的总金额 (opens new window)
- 坑点:
OrderItems.order_num = Orders.order_num
需要考虑,2个订单是否完整
select
cust_id,
(
select sum( item_price*quantity ) as total_ordered
from OrderItems
where OrderItems.order_num = Orders.order_num
group by order_num
) as total_ordered
from
Orders
order by total_ordered desc
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 95.从 Products 表中检索所有的产品名称以及对应的销售 (opens new window)
select prod_name,
(
select sum(quantity) as quant_sold
from OrderItems
where Products.prod_id=OrderItems.prod_id
group by prod_name
)as quant_sold
from Products
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 11.联结表
# 96.返回顾客名称和相关订单号
# 97.返回顾客名称和相关订单号以及每个订单的总价
# 98.返回顾客名称和相关订单号以及每个订单的总价
# 99.返回顾客名称和相关订单号以及每个订单的总价
# 100.确定最佳顾客的另一种方式(二)
# 12.创建高级联结
# 101.确定最佳顾客的另一种方式(二)
# 102.检索每个顾客的名称和所有的订单号(二)
# 103.返回产品名称和与之相关的订单号
# 104.返回产品名称和每一项产品的总订单数
# 105.列出供应商及其可供产品的数量
# 13.组合查询
# 106.将两个 SELECT 语句结合起来(一)
select
prod_id,
quantity
from
OrderItems
where
quantity = 100
union
select
prod_id,
quantity
from
OrderItems
where
prod_id like 'BNBG%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 107.将两个 SELECT 语句结合起来(一)
# 108.组合 Products 表中的产品名称和 Customers 表中的顾客名称
# 109.纠错4
# SQL大厂面试真题
# 1.某音短视频
# 156 各个视频的平均完播率
# 2.用户增长场景(某度信息流)
# 3.电商场景(某东商城)
# 4.出行场景(某滴打车)
# 5.某宝店铺分析(电商模式)
# 6.牛客直播课分析(在线教育行业)
# 7.某乎问答(内容行业)
# 190 某乎问答11月份日人均回答量
# 191 某乎问答高质量的回答中用户属于各级别的数量
# 192 某乎问答单日回答问题数大于等于3个的所有用户
# 193 某乎问答回答过教育类问题的用户里有多少用户回答过
# 194.某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级
# 参考资料
- 《SQL必知必会》
- 还有人整理了:CyC2018的SQL语法 (opens new window)
- 《MySQL必知必会》
- 廖雪峰的SQL (opens new window)
- 自学SQL网,站点 (opens new window)『有实践题』
- 知乎上关于SQL练习的平台 (opens new window)
- 阿里巴巴,SQL代码编码原则和规范 (opens new window)
- 恒生电子,SQL编码规范2020