SQL训练营TASK06学习笔记-SQL的常用练习题

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

一、学习知识点

sql常用练习题联系

二、学习内容

因平台重复审核原因，不放题目，只写过程或截图
练习一
SELECT IcS.TICKER_SYMBOL
,IcS.END_DATE
,IcS.T_REVENUE
,IcS.T_COGS
,IcS.N_INCOME
,MD.*
,CoO.TICKER_SYMBOL
,CoO.INDIC_NAME_EN
,CoO.END_DATE
,CoO.VALUE
FROM Market Data AS MD
INNER JOIN Income Statement AS IcS
ON MD.TICKER_SYMBOL = IcS.TICKER_SYMBOL
INNER JOIN Company Operating AS CoO
ON MD.TICKER_SYMBOL = CoO.TICKER_SYMBOL
WHERE MD.TICKER_SYMBOL = ‘600383’
OR MD.TICKER_SYMBOL = ‘600048’

练习二
SELECT PH
,citric acid
,DENSE_RANK() OVER (ORDER BY citric acid) AS dense_ranking
FROM winequality-red
WHERE pH = 3.03;
练习三
发放优惠券总金额最多的商家
SELECT Merchant_id
,SUM(SUBSTRING_INDEX(Discount_rate,’:’,-1)) AS discount_amount
FROM ccf_offline_stage1_test_revised
WHERE Date_received BETWEEN ‘2016-07-01’ AND ‘2016-07-31’
GROUP BY Merchant_id
ORDER BY discount_amount DESC
LIMIT 1;

– 发放优惠券张数最多的商家
SELECT Merchant_id
,COUNT(1) AS cnt_coupon
FROM ccf_offline_stage1_test_revised
WHERE YEAR(Date_received) = ‘2016’
AND MONTH(Date_received) = ‘07’
GROUP BY Merchant_id
ORDER BY cnt_coupon DESC
LIMIT 1;

练习八

三、学习问题与解答

如何能做对题目
多看几遍题目，确定好题目中的需求到底是什么，如果思路不清晰的情况下，可自己使用sql进行分步查询后再创建高级查询；

四、学习思考与总结

sql语句应越简洁越好，不应太过繁琐，让人能看的明白就行。
多设置简单易懂的别名
应多做联系题，最好系统的做一整套联系题。