每周 · 面試題 | SQL面試必會50題(下)

https://zhuanlan.zhihu.com/p/72223558或者直接找職場老師1對1解答哦！！

20.查詢每門課程被選修的學生數

解題思路：就是很簡單的按照cid分組，然後計算每個組裡面的學生人數，由於前面幾題都是想了很久，遇到這題，我竟然不敢相信這麼簡單

知識點：groupby

SELECT cid,COUNT(sid) AS num
FROM  sc
GROUP BY cid;

21.查詢出只選修兩門課程的學生學號和姓名

解題思路：查詢選擇了兩門課的學生，然後連接student表，還算是比較基礎的題

知識點：groupby having join

SELECT sc.sid,sname
FROM sc INNER JOIN student
ON sc.sid = student.sid
GROUP BY sc.sid
HAVING COUNT(sc.cid) = 2;

22. 查詢男生、女生人數

解題思路：男女生人數這個毫無疑問考的是groupby 還有就是分組以後的count，或者可以使用case when

知識點：groupby，或者 case when

SELECT ssex,COUNT(ssex) as 
FROM student
GROUP BY ssex;

SELECT SUM(CASE WHEN ssex='男' THEN 1 ELSE 0 END)AS 男生人數,
       SUM(CASE WHEN ssex='女' THEN 1 ELSE 0 END) AS 女生人數
FROM student;

當然這裡不完美的，可以使用count（sid）-男生人數，有很多的方式。

23. 查詢名字中含有「風」字的學生信息

解題思路：很簡單的一題，就是考察通配符，而考察點是是否有風

知識點：通配符%

SELECT *
FROM student
WHERE sname LIKE '%風%';

24.查詢同名同性學生名單，並統計同名人數

解題思路：同名同性，我們需要的是count（sname）>1，還要做一步篩選。這裡有一個小坑，開始的時候我以為是同名同姓，中國文化博大精深啊

知識點：having，groupby

SELECT sname,COUNT(sname) AS 同名人數
FROM student
GROUP BY sname
HAVING 同名人數>1;

由於這個數據表中沒有同名的人數，所以顯示結果如上。

25.查詢 1990 年出生的學生名單

解題思路：很簡單的題，但是我還是做錯了

知識點：通配符

SELECT sname
FROM student
WHERE sage LIKE '1990%';

本以為可以很快的執行並產生結果，但是沒想到報錯了，看一下報錯信息，是datetime error，這個是什麼原因呢，我們看一下，之前創表的語句：

原來原本的建表的時候就是設置了datetime類型，那這裡就要使用date函數了，這裡對date函數做一些介紹

網上搜的圖

我覺得上面的圖能夠很好的說明常用的時間函數處理，無非就是hour（小時），minute（分鐘），second（秒），day（天），week（周），month（月），quarter（季度），year（年度）

那麼這一題，顯然就是要使用year了

SELECT *
FROM student
WHERE YEAR(sage) LIKE '1990%';

好的，很完美的解決了問題

26.查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列

解題思路：按照課程分組，然後求分組以後的平均值。然後外加一個排序，排序順序按照課程id升序排列

知識點：group by ，order by

SELECT cid,AVG(score)
FROM sc
GROUP BY cid
ORDER BY AVG(score) DESC,cid;

由於這個表裡面只有3門課，所以這裡顯示不出來，如果有很多數據，然後又恰好有相同的id的時候應該就可以看出不同了

27.查詢平均成績大於等於 85 的所有學生的學號、姓名和平均成績

解題思路：按照學生id分組，然後求平均分，然後用having語句判斷平均分大於等於85，又因為要查詢學生姓名因此可以使用join，或者直接使用笛卡爾積，但是笛卡爾積對於數據量比較大的時候不宜使用

知識點：having，groupby，join

SELECT sc.sid,sname,AVG(score) AS 平均成績
FROM sc,student
WHERE sc.sid = student.sid
GROUP BY sc.sid
HAVING 平均成績>= 85;

或者

SELECT sc.sid,sname,AVG(score) AS 平均成績
FROM sc INNER JOIN student ON sc.sid = student.sid
GROUP BY sc.sid
HAVING 平均成績>= 85;

結果都是第一樣的，但是數據裡大的時候我會使用第二種解法

28.查詢課程名稱為「數學」，且分數低於 60 的學生姓名和分數

解題思路：和上一題一樣，可以使用join，也可以直接使用笛卡爾積，但是要使用三張表，所以最好還是使用join

知識點：多重join的使用

SELECT 
  sname,
  score
FROM
  course AS c
  INNER JOIN sc
    ON c.cid = sc.cid
    AND c.cname = '數學' 
    AND sc.score < 60 
  INNER JOIN student AS s
    ON sc.sid = s.sid ;

29. 查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況）

解題思路：要把學生的姓名展示出來，所以使用join

知識點：join的使用

SELECT 
  sname,
  sc.cid,
  score
FROM
  sc
  INNER JOIN student AS s
    ON sc.sid = s.sid ;

30.查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數

解題思路:就是在上一題的基礎上，加上一個where條件判斷，或者直接在join on後邊判斷

知識點：join加上條件判斷

SELECT 
  sname,
  sc.cid,
  score
FROM
  sc
  INNER JOIN student AS s
    ON sc.sid = s.sid
    AND score > 70 ;

或者

SELECT 
  sname,
  sc.cid,
  score
FROM
  sc
  INNER JOIN student AS s
    ON sc.sid = s.sid
WHERE score > 70 ;

結果是一樣的，至於兩者的區別，總結起來就是inner join後邊時兩個做法是沒有區別的，但是如果是left join這種就會有多餘的出來了。

這是因為使用join以後會生成一個虛擬表，而使用where是在這張虛擬表的基礎上進行篩選，所以結果可能不一樣。這個值得注意。

31.查詢不及格的課程

解題思路：這個其實和上面一題是同一個考法，不詳細展開，唯一的區別就是只查詢課程，所以就是要distinct，或者groupby

知識點：join

SELECT 
sc.cid
FROM
  sc
  INNER JOIN student AS s
    ON sc.sid = s.sid
WHERE score < 60 
GROUP BY sc.cid

第二種使用distinct

SELECT 
DISTINCT sc.cid
FROM
  sc
  INNER JOIN student AS s
    ON sc.sid = s.sid
WHERE score < 60 ;

32.查詢課程編號為 01 且課程成績在 80 分以上的學生的學號和姓名

解題思路：首先肯定需要join把兩個表連起來，然後使用條件查詢課程id為01且成績大於80

知識點：join where

SELECT sc.sid,sname
FROM sc INNER JOIN student AS s
ON sc.sid = s.sid
WHERE sc.cid = 01 AND score >= 80;

33.求每門課程的學生人數

解題思路：就是簡單的按照課程分組然後求sid的人數

知識點：group by ，count

SELECT cid,COUNT(sid) AS 人數
FROM sc
GROUP BY cid;

34.成績不重複，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績

解題思路：這題首先要找到張三老師所授課程，然後找到選擇這門課程的學生，在成績按照從大到小的順序排列，然後只取最高的成績，當然還需要使用子查詢

知識點：join，limit

SELECT 
  s.*,
  score
FROM
  student AS s
  INNER JOIN sc
    ON s.sid = sc.sid
WHERE sc.cid =
  (SELECT 
    cid
  FROM
    teacher AS t
    INNER JOIN course AS c
      ON t.tid = c.cid
      AND tname = '張三')
ORDER BY score DESC 
LIMIT 1 ;

現在慢慢有點感覺了，能夠很快寫出來，果然這種語言相關的還是要多敲代碼，不要怕錯，就是烏龜的速度，只要一直往前也有機會超過兔子

35.成績有重複的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績

解題思路：有重複成績，我們就選擇一個最大成績然後使用子查詢，讓成績在各科成績的最大值裡面做篩選

知識點：多重join，以及子查詢的應用

SELECT 
  student.*,
  sc.cid,
  score
FROM
  student
  INNER JOIN sc
    ON student.sid = sc.sid
  JOIN course
    ON sc.cid = course.cid
  JOIN teacher
    ON course.tid = teacher.tid
WHERE tname = '張三' 
  AND score IN 
  (SELECT 
    MAX(score)
  FROM
    sc
    INNER JOIN course
      ON sc.cid = course.cid
    JOIN teacher
      ON course.tid = teacher.tid
  WHERE tname = '張三') ;

由於我創建的表格裡面沒有重複的成績，所以返回結果沒有發生變化

36.查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

解題思路：首先join兩張表，然後做篩選1成績相同，2課程id不相同

知識點：join，以及where的使用

SELECT 
  DISTINCT a.*
FROM
  sc AS a
  INNER JOIN sc AS b
WHERE a.score = b.score
  AND a.cid != b.cid ;

由於可能使用的是inner join，所以可能會有很多重複值，因此我用了一個distinct的做法。

37. 查詢每門成績最好的前兩名

解題思路：這題和前面有一題有很相似的地方，就是找到大於某個成績只有兩人就是前兩名

知識點：關聯子查詢

SELECT 
  *
FROM
  sc
WHERE 
  (SELECT 
    COUNT(*)
  FROM
    sc AS a
  WHERE sc.cid = a.cid
    AND sc.score < a.score) < 2 
ORDER BY cid ASC,
  sc.score DESC ;

我覺得這個題還是相對來說好理解的，同樣的查詢前3名等等，只需改一個數字。

38. 統計每門課程的學生選修人數（超過 5 人的課程才統計）。

解題思路：按照課程分組，然後count人數，使用having做一個篩選

知識點：groupby having

SELECT 
  cid,
  COUNT(sid)
FROM
  sc
GROUP BY cid
HAVING COUNT(sid) > 5 ;

39.檢索至少選修兩門課程的學生學號

解題思路：和上一題基本類似按照學生id分組然後count cid，篩選出大於等於2的學生

知識點：groupby having

SELECT 
  sid,
  COUNT(cid)
FROM
  sc
GROUP BY sid 
HAVING COUNT(cid) >= 2 ;

40.查詢選修了全部課程的學生信息

解題思路：同樣的我們要知道總共有多少門課，不光是為了回答這一道題，可能真實情況會有很多的課，那麼就需要把course表中所有的課程計數，還要求學生信息，那就需要使用join了

知識點：join，where，子查詢

SELECT 
  s.*
FROM
  sc
  INNER JOIN student AS s
    ON sc.sid = s.sid
WHERE cid =
  (SELECT 
    COUNT(*)
  FROM
    course) ;

41.查詢各學生的年齡，只按年份來算

解題思路：這題開始就要使用時間函數了，不難，還算比較簡單

知識點：date函數的使用

SELECT 
  sname,
  YEAR(NOW()) - YEAR(sage) AS 年紀
FROM
  student ;

42. 按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一

解題思路：這個可以使用case when語句，然後使用時間戳求差值

知識點：case when ，year，date_format

SELECT 
  sname,
  CASE
    WHEN (
      DATE_FORMAT(NOW(), '%m-%d') - DATE_FORMAT(sage, '%m-%d')
    ) < 0 
    THEN YEAR(NOW()) - YEAR(sage) + 1 
    ELSE YEAR(NOW()) - YEAR(sage)
  END AS 年齡
FROM
  student ;

43.查詢本周過生日的學生

解題思路：使用week函數

知識點：week，now

SELECT sname
FROM student
WHERE WEEK(sage) = WEEK(NOW());

可以發現沒有結果，為了驗證準確性，我們把student表格單獨拉出來

SELECT 
  *
FROM
  student ;

可以看到果然沒有，那麼為了進一步驗證，我們向student表中新增一行數據

INSERT INTO Student VALUES('09' , '關羽' , '1990-07-15' , '男');
SELECT 
  *
FROM
  student ;

看一下執行結果

ok看來語句是沒有毛病的

44. 查詢下周過生日的學生

解題思路：好的嘛，下周過生日，代表著在上題的基礎上再加1

知識點：week，now

SELECT 
  sname
FROM
  student
WHERE WEEK(sage) = WEEK(NOW()) + 1 ;

當然還是沒有結果的，不過這裡就不做測試了

45.查詢本月過生日的學生

解題思路：基本類似的操作，使用month函數

知識點：month，now

SELECT 
  sname,
  sage
FROM
  student
WHERE MONTH(sage) = MONTH(NOW());

46.查詢下月過生日的學生

解題思路：同樣的套路，同樣的解法

知識點：month，now

SELECT 
  sname,
  sage
FROM
  student
WHERE MONTH(sage) = MONTH(NOW()) + 1 ;

哇哦，不知不覺間就到了最後一題了，這裡給我印象最深的是那幾個需要排序的題，我覺得排序的題，要好好琢磨，爭取能夠很快的寫出來。

本文為轉載分享&推薦閱讀，若侵權請聯繫後臺刪除