最近找工作才發現對於SQL的使用很陌生,因此找了網路上一篇多人引用的經典文章–超经典SQL练习题,做完这些你的SQL就过关了。本篇結合幾位大神的解法,加上自己的想法彙整而成。比較不同的是,原作者們都是使用MSSQL或MySQL,這裡則是使用SQLite來實作。
建立資料表
1 | -- 學生表 |
題目
- 查詢”01”課程比”02”課程成績高的學生的資訊及課程分數
答案
1 | SELECT a.*, b.s_score as score_01, c.s_score as score_02 FROM Student a |
1 | +------+--------+------------+-------+----------+----------+ |
- 查詢”01”課程比”02”課程成績低的學生的資訊及課程分數
答案
1 | SELECT a.*, b.s_score as score_01, c.s_score as score_02 FROM Student a |
1 | +------+--------+------------+-------+----------+----------+ |
- 查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績
答案
1 | SELECT a.s_id, s_name, ROUND(AVG(s_score), 2) AS avg_score FROM Student a |
1 | +------+--------+-----------+ |
- 查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績(包括有成績的和無成績的)
答案
使用ifnull()函式1 | SELECT a.s_id, s_name, ROUND(ifnull(AVG(s_score), 0), 2) AS avg_score FROM Student a |
1 | +------+--------+-----------+ |
- 查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績(包含沒有選課的)
答案
1 | SELECT a.s_id, s_name, COUNT(c_id) as course_count, ifnull(SUM(s_score), 0) as total_score FROM Student a |
1 | +------+--------+--------------+-------------+ |
- 查詢”李”姓老師的數量
答案
1 | SELECT COUNT() FROM Teacher |
1 | +---------+ |
- 查詢學過”張三”老師授課的學生資訊
答案
方法一1 | SELECT * FROM Student |
1 | SELECT a.* FROM Student a |
1 | +------+--------+------------+-------+ |
- 查詢沒學過”張三”老師授課的學生資訊
答案
1 | SELECT * FROM Student |
1 | +------+--------+------------+-------+ |
- 查詢學過編號為01,並且也學過編號為02的課程的學生資訊
答案
1 | SELECT * FROM Student |
1 | +------+--------+------------+-------+ |
- 查詢學過編號為01,但是沒有學過編號為02的課程的學生資訊
答案
1 | SELECT * FROM Student |
1 | +------+--------+------------+-------+ |
- 查詢沒有學全所有課程的學生資訊
答案
1 | SELECT a.*, COUNT(b.c_id) as course_count FROM Student a |
1 | +------+--------+------------+-------+--------------+ |
- 查詢至少有一門課與學號為”01”的同學所學相同的學生資訊
答案
1 | SELECT a.* FROM Student a |
1 | +------+--------+------------+-------+ |
- 查詢和”01”號的同學學習的課程完全相同的其他學生資訊
答案
使用group_concat()函式1 | SELECT a.* FROM Student a |
1 | +------+--------+------------+-------+ |
- 查詢沒學過”張三”老師講授的任一門課程的學生姓名
PS: 與第8題相似
答案
1 | SELECT s_name FROM Student |
1 | +--------+ |
- 查詢兩門及其以上不及格課程的同學的學號,姓名及其平均成績
答案
1 | SELECT a.s_id, s_name, ROUND(AVG(s_score), 2) as avg_score FROM Student a |
1 | +------+--------+-----------+ |
- 檢索”01”課程分數小於60,按分數降序排列的學生資訊
答案
1 | SELECT a.*, s_score FROM Student a |
1 | +------+--------+------------+-------+---------+ |
- 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
答案
1 | SELECT s_id, |
1 | +------+----------+----------+----------+-----------+ |
- 查詢各科成績最高分、最低分和平均分,以如下形式顯示:課程ID,課程name,最高分,最低分,平均分,及格率,中等率,優良率,優秀率(及格為>=60,中等為:70-80,優良為:80-90,優秀為:>=90)。
要求輸出課程號碼和選修人數,查詢結果依人數降序排列,若人數相同,依課程號碼升序排列
答案
1 | SELECT b.c_id as '課程ID', c_name as '課程name', COUNT() as '選修人數', |
1 | +--------+----------+---------+-------+--------+---------+---------+---------+---------+----------+ |
- 按各科成績進行排序,並顯示排名,分數重複時保留名次空缺
答案
1 | SELECT *, ( |
1 | +------+------+---------+------+ |
- 19.1 按各科成績進行行排序,並顯示排名,分數重複時合併名次
答案
方法一1 | SELECT *, ( |
1 | SELECT * |
1 | +------+------+---------+------+ |
- 查詢學生的總成績,並進行排名
答案
使用row_number()函式1 | SELECT a.s_id, s_name, ifnull(SUM(s_score), 0) as total_score, |
1 | +------+--------+-------------+------+ |
- 查詢不同老師所教不同課程平均分從高到低顯示
答案
1 | SELECT c.t_id, t_name, c.c_id, c_name, ROUND(AVG(s_score), 2) as avg_score FROM Score s |
1 | +------+--------+------+--------+-----------+ |
- 查詢所有課程的成績第2名到第3名的學生資訊及該課程成績
答案
1 | SELECT b.*, c_id, s_score, rank |
1 | +------+--------+------------+-------+------+---------+------+ |
- 統計各科成績各分數段人數:課程編號,課程名稱,[100-85],[85-70],[70-60],[60-0] 及所佔百分比
答案
1 | SELECT a.c_id as '課程編號', c_name as '課程名稱', |
1 | +---------+----------+----------+---------------+---------+--------------+---------+---------------+--------+-------------+ |
- 查詢學生平均成績及其名次
PS: 與第20題相似
答案
1 | SELECT a.s_id, s_name, ROUND(ifnull(AVG(s_score), 0), 2) as avg_score, |
1 | +------+--------+-----------+------+ |
- 查詢各科成績前三名的記錄
答案
使用rank()函式1 | SELECT * |
1 | +------+------+---------+------+ |
- 查詢每門課程被選修的學生數
答案
1 | SELECT c_id, COUNT(s_id) as num FROM Score |
1 | +------+-----+ |
- 查詢出只選修兩門課程的學生學號和姓名
答案
1 | SELECT a.s_id, s_name FROM Score a |
1 | +------+--------+ |
- 查詢男生、女生人數
答案
1 | SELECT s_sex, COUNT(s_id) as num FROM Student |
1 | +-------+-----+ |
- 查詢名字中含有”風”字的學生資訊
答案
1 | SELECT * FROM Student |
1 | +------+--------+------------+-------+ |
- 查詢同名同性學生名單,並統計同名人數
答案
1 | SELECT s1.s_name, s1.s_sex, COUNT() FROM Student s1 |
- 查詢1990年出生的學生資訊
答案
1 | SELECT * FROM Student |
1 | +------+--------+------------+-------+ |
- 查詢每門課程的平均成績,結果按平均成績降序排列,平均成績相同時,按課程編號升序排列
答案
1 | SELECT c_id, ROUND(AVG(s_score), 2) as avg_score |
1 | +------+-----------+ |
- 查詢平均成績大於等於85的所有學生的學號、姓名和平均成績
PS: 與第3題相似
答案
1 | SELECT a.s_id, s_name, ROUND(AVG(s_score), 2) AS avg_score FROM Student a |
1 | +------+--------+-----------+ |
- 查詢課程名稱為”數學”,且分數低於60的學生姓名和分數
答案
1 | SELECT s_name, s_score FROM Score a |
1 | +--------+---------+ |
- 查詢所有學生的課程及分數狀況(有學生沒成績,沒選課的狀況)
答案
1 | SELECT a.s_id, s_name, |
1 | +------+--------+------+------+------+ |
- 查詢任何一門課程成績在70分以上的姓名、課程名稱和分數
答案
1 | SELECT s_name, c_name, s_score FROM Score a |
1 | +--------+--------+---------+ |
- 查詢不及格的課程
答案
1 | SELECT s_name, c_name, s_score FROM Score a |
1 | +--------+--------+---------+ |
- 查詢課程編號為01且課程成績在80分以上的學生的學號和姓名
答案
1 | SELECT a.s_id, s_name FROM Score a |
求每門課程的學生人數
PS: 與第26題相同成績不重複的情況下,查詢選修”張三”老師所授課程的學生中,成績最高的學生資訊及其成績
答案
1 | SELECT b.*, MAX(s_score) FROM Score a |
1 | +------+--------+------------+-------+--------------+ |
- 40.1 成績重複的情況下,查詢選修”張三”老師所授課程的學生中,成績最高的學生資訊及其成績
答案
1 | SELECT b.*, s_score |
1 | +------+--------+------------+-------+---------+ |
- 查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
答案
1 | SELECT DISTINCT a.* FROM Score a |
1 | +------+------+---------+ |
- 查詢每門成績最好的前兩名
PS: 與第25題相似
答案
1 | SELECT * |
1 | +------+------+---------+------+ |
- 統計每門課程的學生選修人數(超過5人的課程才統計)
PS: 與第26題相似
答案
1 | SELECT c_id, COUNT(s_id) as num FROM Score |
1 | +------+-----+ |
- 檢索至少選修兩門課程的學生學號
PS: 與第27題相似
答案
1 | SELECT s_id FROM Score |
1 | +------+ |
- 查詢選修了全部課程的學生資訊
答案
1 | SELECT a.* FROM Student a |
1 | +------+--------+------------+-------+ |
- 查詢各學生的年齡,只按年份來算
答案
1 | SELECT s_name, strftime('%Y', 'now') - strftime('%Y', s_birth) as age FROM Student |
1 | +--------+------+ |
- 46.1 按照出生日期來算,當前月日 < 出生年月的月日則,年齡減一
答案
1 | SELECT s_name, |
1 | +--------+-----+ |
- 查詢本週過生日的學生
答案
1 | SELECT * FROM Student |
- 查詢下週過生日的學生
答案
使用strftime()函式會得到字串結果,做加減運算後,會變成數字,要小心兩邊的型態是否一致。1 | SELECT * FROM Student |
- 查詢本月過生日的學生
答案
1 | SELECT * FROM Student |
- 查詢下月過生日的學生
答案
使用strftime()函式會得到字串結果,做加減運算後,會變成數字,要小心兩邊的型態是否一致。1 | SELECT * FROM Student |
其它
- 查詢在成績表存在成績的學生資訊
答案
1 | SELECT DISTINCT b.* FROM Score a, Student b |
1 | +------+--------+------------+-------+ |
參考資料