SQL練習及解答

第一題：SQL語言允許使用通配符進行字元串匹配的操作，其中『%』可以表示

A.零個字元

B.1個字元

C.多個字元

D.以上都是

答案:D

第二題:如果有兩張表employees和departments如下：

（1）寫出SQL得出每個部門的平均工資

答案：

SELECT department_id, AVG(salary) FROM employeeGROUP BY department_id;

（2）查詢量表得出如下結果：

答案：

SELECT first_name, salary, department_name FROM employeeJOIN department ON (employee.department_id = department.department_id);

第三題:如有有一張表player，有id、name兩個列，我想知道這張表有多少行數據，id最大的前10位的name是什麼，請問這兩個SQL語句如何寫？

答案:

SELECT COUNT(*) FROM player;

－－－－－－－－－－－－－－－－－－－－

SELECT name FROM playerORDER BY id DESCLIMIT 10;

第四題：查詢一張數據表（tb），基本欄位：日期，訂單，要求用SQL實現：

周次（week），訂單總和，日均訂單，極大值訂單，極小值訂單

答案:

SELECT COUNT(DISTINCT date)/7 FROM table;SELECT COUNT(order) FROM table;SELECT COUNT(order)/COUNT(DISTINCT date) FROM table;SELECT MAX(order) FROM table;SELECT Min(order) FROM table;

第五題：使用SQL實現以下數據錶行轉列及總分，平均分（數據表：table）

答案:

SELECT name,SUM(CASE WHEN subject = chinese THEN score END) AS chinese,SUM(CASE WHEN subject = math THEN score END) AS math,SUM(CASE WHEN subject = english THEN score END) AS english,SUM(score) AS total,AVG(score) AS averageFROM studentsGROUP BY name;

第六題:查詢數據表（order），基本欄位如下：

省，市，店鋪名稱，訂單數

要按」市」排序，需要看到每一個市的訂單排名前三名的門店及訂單數。

答案：

SELECT x.city, x.shop_name FROM order AS xWHERE x.shop_name = (SELECT TOP 2 y.shop_name FROM order AS yWHERE x.city = y.cityORDER BY x.order_num DESCLIMIT 3);

第七題：用戶登陸日誌表為user_id，log_time，session_id，plat

1.用sql查詢近30天，每天平均登陸用戶數量？

2.寫sql查詢出近30天，連續訪問7天以上的用戶數量？

答案：SELECT AVG(t1.ct) FROM(SELECT DATE_FORMAT(t.log_time, %Y-%m-%d),COUNT(DISTINCT t.user_id) AS ct FROM tWHERE DATE_FORMAT(t.log_time, %Y-%m-%d) >= DATE_SUB(CURDATE(),INTERVAL 30 DAY)GROUP BY DATE_FORMAT(t.log_time, %Y-%m-%d)) AS t1;

－－－－－－－－－－－－－－－－－－－－－－－－－－

（遇到的困難：不明白連續７天如何實現）

第八題:交易表結構user_id（用戶ID），order_id（訂單），pay_time（付款時間），order_amount（金額）。

1.寫ql查詢過去一個月付款用戶量（提示：用戶量需去重）最高的3天分別是哪幾天？

2.寫sql查詢昨天每個用戶最後付款的訂單ID及金額？

答案:

SELECT pay_time, COUNT(DISTINCT user_id) FROM tableWHERE MONTH(DATE_FORMAT(pay_time, %Y-%m-%d)) = MONTH(now()) -1GROUP BY pay_timeORDER BY COUNT(DISTINCT user_id) DESCLIMIT 3;－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－SELECT user_id, order_id, order_amount FROM tableWHERE DATE_FORMAT(pay_time, %Y-%m-%d) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)AND DATE_FORMAT(pay_time, %Y-%m-%d) = MAX(DATE_FORMAT(pay_time, %Y-%m-%d))ORDER BY user_id;

第九題：有PV表a（表結構為user_id（用戶名），goods_id（商品ID）），點擊表b（表結構為user_id（用戶名），goods_id（商品ID））兩個表，各存放40億條user_id的goods_id訪問記錄，在防止數據傾斜的情況下，寫一句sql找出a、b兩個表共同的user_id與相應的goods_id？

答案：

SELECT * FROM aJOIN bON a.user_id IS NOT NULLAND a.user_id = b.user_idUNION ALLSELECT * FROM aWHERE a.user_id IS null;

第十題：對於通過不同渠道拉新進來的用戶，經過一段時間許多用戶可能流水，而留下來的用戶稱之為留存用戶。分析用戶留存是拉新和用戶運營的重要指標。假如我們有一張用戶訪問表：person_visit，記錄了所有用戶的訪問信息，包含欄位：

用戶id：user_id，訪問時間：visit_date，訪問頁面：page_name，訪問渠道（android,ios）：plat等

1.請統計近7天每天到訪的新用戶數。

SELECT t.d, COUNT(DISTINCT t.user_id) FROM(SELECT user_id, MIN(visit_date) as d FROM person_visitWHERE DATE_FORMAT(person_visit, %Y-%m-%d) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)GROUP BY user_id) AS tGROUP BY t.d

2.請統計每個訪問渠道7天前（D-7）的新用戶的3日留存和7日留存率。

先求出７天前的新用戶總數SELECT plat, COUNT(DISTINCT user_id) FROM person_visitWHERE MIN(DATE_FORMAT(visit_date, %Y-%m-%d)) = (CURDATE()-INTERVAL 7 DAY)GROUP BY plat;然後求出３日留存的用戶數SELECT plat, COUNT(DISTINCT user_id) FROM person_visitWHERE DATE_FORMAT(visit_date, %Y-%m-%d) = (CURDATE()-INTERVAL 4 DAY) ANDuser_id IN (SELECT user_id FROM person_visitWHERE MIN(DATE_FORMAT(visit_date, %Y-%m-%d)) = (CURDATE()-INTERVAL 7 DAY))GROUP BY plat;再求出７日留存的用戶數SELECT plat, COUNT(DISTINCT user_id) FROM person_visitWHERE DATE_FORMAT(visit_date, %Y-%m-%d) = CURDATE() ANDuser_id IN (SELECT user_id FROM person_visitWHERE MIN(DATE_FORMAT(visit_date, %Y-%m-%d)) = (CURDATE()-INTERVAL 7 DAY))GROUP BY plat;最後分別計算即可

第十一題：需要對用戶抽樣用以分析，用戶表結構為user_id（用戶ID），reg_time（註冊時間），age（年齡）。

1.寫一句SQL按user_id尾數隨機抽樣2000個用戶？

2.寫一句SQL取出按各年齡段（每10歲一個分段，如：[0,10),[10,20),[20,30)...）分別抽取1%的用戶？

答案：

SELECT user_id FROM tableWHERE user_id % 10 = ROUND(10*RAND())ORDER BY user_idLIMIT 2000;

－－－－－－－－－－－－－－－－－－－－－－－

SELECT TOP 1 PERCENT age, user_id FROM users WHERE age >= 0 AND age <10UNION SELECT TOP 1 PERCENT age, user_id FROM users WHERE age >= 10 AND age <20UNION SELECT TOP 1 PERCENT age, user_id FROM users WHERE age >= 20 AND age <30UNION SELECT TOP 1 PERCENT age, user_id FROM users WHERE age >= 30 AND age <40UNION SELECT TOP 1 PERCENT age, user_id FROM users WHERE age >= 40 AND age <50UNION SELECT TOP 1 PERCENT age, user_id FROM users WHERE age >= 50 AND age <60UNION SELECT TOP 1 PERCENT age, user_id FROM users WHERE age >= 60;

推薦閱讀：