【SQL进阶04】综合练习题-10道经典SQL题目，配套数据与解答

82年的黎叔

已于 2023-12-28 19:02:33 修改

阅读量896

点赞数 23

文章标签： sql 数据库笔记学习

于 2023-12-27 14:07:05 首次发布

本文链接：https://blog.csdn.net/qq_34018898/article/details/135244372

版权

下载相关创建表格和插入数据脚本

这里为了方便大家，数据脚本已经给大家准备好啦。
点击下方链接直接下载
创建数据表脚本：http://tianchi-media.oss-cn-beijing.aliyuncs.com/dragonball/SQL/create_table.sql
插入数据脚本：https://tianchi-media.oss-cn-beijing.aliyuncs.com/dragonball/SQL/data.zip

大家下载好脚本后，会得到1个创建数据表的脚本create_table.sql和1个插入数据脚本的压缩文件data.zip，解压后有9个脚本文件目录如下：

enter image description here

脚本文件名前面的序号表示用到该数据集的题目序号，例如1-7market data.sql表示第1题和第7题用到了该数据集。

由于部分sql脚本文件较大，无法在MySQL Workbench中打开后执行。需要在CMD命令行终端SQL环境下使用source命令执行sql脚本文件。

第一步：在WINDOWS开始菜单中找到并运行MySQL 8.0 Command Line Client

enter image description here

第二步：输入数据库登陆密码
第三步：创建练习用的数据库CREATE DATABASE <数据库名>，比如 task06

CREATE DATABASE task06;

第四步：指定数据库 USE <数据库名>

USE task06

enter image description here

第五步：创建数据表create_table.sql，执行脚本的命令SOURCE <绝对路径>。

-- 假设绝对路径为"D:\Download\",命令行如下：
SOURCE D:\Download\create_table.sql

enter image description here

出现这样的运行结果就说明数据表创建成功

enter image description here

第六步：逐一运行9个插入数据的sql脚本

注意：

绝对路径请替换成你自己的路径
有几个脚本文件较大（2个300MB以上和1个2.2GB），运行时间较长，笔者导入2.2GB的数据5-8-10ccf_online_stage1_train.sql用了4个多小时。

-- 数据文件大小：*487KB*
source D:\Download\data\2-winequality-red.sql

-- 数据文件大小：*759KB*
source D:\Download\data\1-9company operating.sql

-- 数据文件大小：*1507KB*
source D:\Download\data\6-winequality-white.sql

-- 数据文件大小：*9,567KB*
source D:\Download\data\4-macro industry.sql

-- 数据文件大小：*21,819KB*
source D:\Download\data\3-ccf_offline_stage1_test_revised.sql

-- 数据文件大小：*104,479KB*
source D:\Download\data\1-7market data.sql

-- 数据文件大小：*330,888KB*
source D:\Download\data\1-9income statement.sql

-- 数据文件大小：*343,290KB*
source D:\Download\data\8-10ccf_offline_stage1_train.sql

-- 数据文件大小：*2,217,025KB*
source D:\Download\data\5-8-10ccf_online_stage1_train.sql

练习题1：

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=1074

请使用A股上市公司季度营收预测数据集《Income Statement.xls》和《Company Operating.xlsx》和《Market Data.xlsx》，以Market Data为主表，将三张表中的TICKER_SYMBOL为 600383 和 600048 的信息合并在一起。只需要显示以下字段。

表名	字段名
Income Statement	TICKER_SYMBOL
Income Statement	END_DATE
Income Statement	T_REVENUE
Income Statement	T_COGS
Income Statement	N_INCOME
Market Data	TICKER_SYMBOL
Market Data	END_DATE
Market Data	CLOSE_PRICE
Company Operating	TICKER_SYMBOL
Company Operating	INDIC_NAME_EN
Company Operating	END_DATE
Company Operating	VALUE

参考答案

-- 三张表使用TICKER_SYMBOL和END_DATE进行关联
-- income statement表的数据需要DISTINCT去重

SELECT M.*, O.INDIC_NAME_EN, O.`VALUE`,	I.T_REVENUE, I.T_COGS, I.N_INCOME
  FROM ( SELECT TICKER_SYMBOL, END_DATE, CLOSE_PRICE 
           FROM `market data` 
          WHERE TICKER_SYMBOL IN ( '600383', '600048' )) AS M

LEFT JOIN ( SELECT TICKER_SYMBOL, END_DATE,	INDIC_NAME_EN, `VALUE` 
              FROM `company operating` 
             WHERE TICKER_SYMBOL IN ( '600383', '600048' )) AS O 
       ON M.TICKER_SYMBOL = O.TICKER_SYMBOL 
      AND M.END_DATE = O.END_DATE 

LEFT JOIN ( SELECT DISTINCT TICKER_SYMBOL, END_DATE, T_REVENUE, T_COGS, N_INCOME 
              FROM `income statement` 
             WHERE TICKER_SYMBOL IN ( '600383', '600048' )) AS I 
       ON M.TICKER_SYMBOL = I.TICKER_SYMBOL 
      AND M.END_DATE = I.END_DATE

ORDER BY M.TICKER_SYMBOL, M.END_DATE

练习题2：

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=44

请使用 Wine Quality Data 数据集《winequality-red.csv》，找出 pH=3.03的所有红葡萄酒，然后，对其 citric acid 进行中式排名（相同排名的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”）

参考答案

SELECT
	*,
	DENSE_RANK() OVER ( ORDER BY `citric acid` ) AS dense_ranking 
FROM
	`winequality-red` 
WHERE
	`winequality-red`.pH = 3.03;

运行结果：
在这里插入图片描述

练习题3：

数据来源：https://tianchi.aliyun.com/competition/entrance/231593/information

使用Coupon Usage Data for O2O中的数据集《ccf_offline_stage1_test_revised.csv》，试分别找出在2016年7月期间，发放优惠券总金额最多和发放优惠券张数最多的商家。

这里只考虑满减的金额，不考虑打几折的优惠券。

参考答案

-- 发放优惠券满减金额最多的商家
SELECT Merchant_id,
       SUM(discount) AS sum_discount
  FROM ( SELECT Merchant_id, Discount_rate,
                SUBSTRING_INDEX( Discount_rate, ':', - 1 ) AS discount 
           FROM ccf_offline_stage1_test_revised 
          WHERE Date_received BETWEEN '2016-07-01' AND '2016-07-31' ) AS D 
 WHERE discount > 1
 GROUP BY Merchant_id 
 ORDER BY sum_discount DESC LIMIT 1;

-- 发放优惠券张数最多的商家
SELECT Merchant_id,
       COUNT(*) AS num_issued
  FROM ccf_offline_stage1_test_revised 
 WHERE Date_received BETWEEN '2016-07-01' AND '2016-07-31' 
 GROUP BY Merchant_id 
 ORDER BY num_issued DESC LIMIT 1;

练习题4：

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=1074

请使用A股上市公司季度营收预测中的数据集《Macro&Industry.xlsx》中的sheet-INDIC_DATA，请计算全社会用电量:第一产业:当月值在2015年用电最高峰是发生在哪月？并且相比去年同期增长/减少了多少个百分比？

参考答案

-- 2015年用电最高峰是发生在哪月？
SELECT PERIOD_DATE, DATA_VALUE
  FROM `macro industry` 
 WHERE INDIC_ID = '2020101522'
   AND YEAR(PERIOD_DATE) = 2015
 ORDER BY DATA_VALUE DESC LIMIT 1;

-- 相比去年同期增长/减少了多少个百分比？
SELECT Y.PERIOD_DATE, Y.DATA_VALUE, Y.YoY_VALUE,
       ( Y.DATA_VALUE - Y.YoY_VALUE ) / Y.YoY_VALUE AS YoY
  FROM ( SELECT Max.PERIOD_DATE, Max.DATA_VALUE,
                ( SELECT DATA_VALUE 
                    FROM `macro industry` 
                   WHERE INDIC_ID = '2020101522'
                     AND PERIOD_DATE = DATE_SUB( Max.PERIOD_DATE, INTERVAL 1 YEAR )) AS YoY_VALUE
           FROM ( SELECT PERIOD_DATE, DATA_VALUE
                    FROM `macro industry` 
                   WHERE INDIC_ID = '2020101522'
                     AND YEAR(PERIOD_DATE) = 2015
                   ORDER BY DATA_VALUE DESC LIMIT 1 ) AS Max ) AS Y;

练习题5：

数据来源：https://tianchi.aliyun.com/competition/entrance/231593/information

使用Coupon Usage Data for O2O中的数据集《ccf_online_stage1_train.csv》，试统计在2016年6月期间，线上总体优惠券弃用率为多少？并找出优惠券弃用率最高的商家。

弃用率 = 被领券但未使用的优惠券张数 / 总的被领取优惠券张数

参考答案

-- 统计在2016年6月期间，线上总体优惠券弃用率为多少？
-- 观察数据结构得知，Action为1表示已使用，Action为2表示已领取
SELECT SUM( CASE WHEN Action = 2 THEN 1 END ) AS `get`,
       COUNT( * ) AS `use`,
       SUM( CASE WHEN Action = 2 THEN 1 END ) / COUNT( * ) AS discard_rate
  FROM ccf_online_stage1_train
 WHERE Date_received BETWEEN '2016-06-01' AND '2016-06-30'

-- 找出优惠券弃用率最高的商家
SELECT Merchant_id,
       SUM( CASE WHEN Action = 2 THEN 1 END ) AS `get`,
       COUNT( * ) AS `use`,
       SUM( CASE WHEN Action = 2 THEN 1 END ) / COUNT( * ) AS discard_rate
  FROM ccf_online_stage1_train
 WHERE Date_received BETWEEN '2016-06-01' AND '2016-06-30'
 GROUP BY Merchant_id
 ORDER BY discard_rate DESC, Merchant_id

练习题6：

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=44

请使用 Wine Quality Data 数据集《winequality-white.csv》，找出 pH=3.63的所有白葡萄酒，然后，对其 residual sugar 量进行英式排名（非连续的排名）

参考答案

-- 找出 pH=3.63的所有白葡萄酒，对其 residual sugar 量进行英式排名（非连续的排名）
SELECT *, RANK() OVER ( ORDER BY `residual sugar` ) AS rankn 
  FROM `winequality-white` 
 WHERE pH = 3.63

练习题7：

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=1074

请使用A股上市公司季度营收预测中的数据集《Market Data.xlsx》中的sheet-DATA，

计算截止到2018年底，市值最大的三个行业是哪些？以及这三个行业里市值最大的三个公司是哪些？（每个行业找出前三大的公司，即一共要找出9个）

参考答案

-- 市值最大的三个行业是哪些？
-- 按行业进行分组，统计行业总市值，并排序
SELECT TYPE_NAME_CN, SUM( MARKET_VALUE )
  FROM ( -- 找出最后一天的市值
         SELECT T.*, M.MARKET_VALUE
           FROM ( -- 按股票代码和行业分组，找出2018年最后一天的日期
                  SELECT TICKER_SYMBOL, TYPE_NAME_CN, MAX( END_DATE ) AS END_DATE
                    FROM `market data` 
                   WHERE YEAR ( END_DATE ) = '2018'
                   GROUP BY TICKER_SYMBOL, TYPE_NAME_CN ) AS T
           LEFT JOIN `market data` AS M
             ON T.TICKER_SYMBOL = M.TICKER_SYMBOL
            AND T.END_DATE = M.END_DATE ) AS B
 GROUP BY TYPE_NAME_CN
 ORDER BY SUM( MARKET_VALUE ) DESC LIMIT 3

-- 这三个行业里市值最大的三个公司是哪些？
SELECT *
  FROM ( -- 找出三大行业中截至2018年底所有股票的市值
         SELECT TICKER_SYMBOL, TYPE_NAME_CN, END_DATE, MARKET_VALUE,
                RANK() OVER ( PARTITION BY TYPE_NAME_CN ORDER BY MARKET_VALUE DESC ) AS rankn
           FROM `market data`
          WHERE END_DATE = '2018-05-31' AND TYPE_NAME_CN IN ( '银行', '医药生物', '非银金融' ) ) AS B
 WHERE rankn IN (1,2,3)

练习题8：

数据来源：https://tianchi.aliyun.com/competition/entrance/231593/information

使用Coupon Usage Data for O2O中的数据集《ccf_online_stage1_train.csv》和《ccf_offline_stage1_train.csv》，试找出在2016年6月期间，线上线下累计优惠券使用次数最多的顾客。

参考答案

-- 线上线下累计优惠券使用次数最多的顾客
SELECT User_id, SUM( couponCount )
  FROM ( -- 找出线上所有用户使用优惠券的次数
         SELECT User_id, COUNT(*) AS couponCount
           FROM ccf_online_stage1_train
          WHERE Date_received BETWEEN '2016-06-01' AND '2016-06-30'
            AND Date IS NOT NULL
          GROUP BY User_id

          UNION ALL
         -- 找出线下所有用户使用优惠券的次数
         SELECT User_id, COUNT(*) AS couponCount
           FROM ccf_offline_stage1_train
          WHERE Date_received BETWEEN '20160601' AND '20160630'
            AND Date IS NOT NULL
          GROUP BY User_id ) AS B 
 GROUP BY User_id
 ORDER BY SUM( couponCount ) DESC LIMIT 1

练习题9：

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=1074

请使用A股上市公司季度营收预测数据集《Income Statement.xls》中的sheet-General Business和《Company Operating.xlsx》中的sheet-EN。

找出在数据集所有年份中，按季度统计，白云机场旅客吞吐量最高的那一季度对应的净利润是多少？（注意，是单季度对应的净利润，非累计净利润。）

参考答案

-- 找出所有年份中，按季度统计，白云机场旅客吞吐量最高的那一季度对应的净利润是多少？
SELECT Base.*, Income.income_Amount
  FROM ( -- 找出白云机场旅客吞吐量最高的那一季度
         SELECT TICKER_SYMBOL, YEAR(END_DATE) AS Year, QUARTER(END_DATE) AS Quarter, SUM(VALUE) AS throughput
           FROM `company operating`
          WHERE INDIC_NAME_EN = 'Baiyun Airport:Passenger throughput'
          GROUP BY TICKER_SYMBOL, YEAR(END_DATE), QUARTER(END_DATE)
          ORDER BY throughput DESC LIMIT 1) AS Base
  LEFT JOIN
       ( -- 找出白云机场所有年份每个季度的净利润
         SELECT TICKER_SYMBOL, YEAR(END_DATE) AS Year, QUARTER(END_DATE) AS Quarter, SUM(N_INCOME) AS income_Amount
           FROM `income statement`
          WHERE TICKER_SYMBOL = '600004'
          GROUP BY TICKER_SYMBOL, YEAR(END_DATE), QUARTER(END_DATE) ) AS Income
    ON Base.TICKER_SYMBOL = Income.TICKER_SYMBOL
   AND Base.Year = Income.Year
   AND Base.Quarter = Income.Quarter

练习题10：

数据来源：https://tianchi.aliyun.com/competition/entrance/231593/information

使用Coupon Usage Data for O2O中的数据集《ccf_online_stage1_train.csv》和《ccf_offline_stage1_train.csv》，试找出在2016年6月期间，线上线下累计被使用优惠券满减最多的前3名商家。

比如商家A，消费者A在其中使用了一张200减50的，消费者B使用了一张30减1的，那么商家A累计被使用优惠券满减51元。

参考答案

-- 找出在2016年6月期间，线上线下累计被使用优惠券满减最多的前3名商家
SELECT Merchant_id, SUM(discount) AS discount
  FROM ( -- 找出线下所有商店使用满减的记录
         SELECT Merchant_id,SUBSTRING_INDEX( Discount_rate, ':', - 1 ) AS discount 
           FROM ccf_offline_stage1_train
          WHERE Date_received BETWEEN '20160601' AND '20160630'
            AND Date IS NOT NULL AND Discount_rate > 1

          UNION ALL 
         -- 找出线上所有商店使用满减的记录
         SELECT Merchant_id,SUBSTRING_INDEX( Discount_rate, ':', - 1 ) AS discount 
           FROM ccf_online_stage1_train
          WHERE Date_received BETWEEN '2016-06-01' AND '2016-06-30'
            AND Date IS NOT NULL AND Discount_rate != 'fixed') AS B
 GROUP BY Merchant_id
 ORDER BY discount DESC  LIMIT 3