MySQL練習

05-03

把自做的經典SQL練習題答案記錄下來，方便日後複習。

1、查詢Student表中的所有記錄的Sname、Ssex和Class列。

SELECT sname, ssex, class FROM student;

2、查詢教師所有的單位即不重複的Depart列。

SELECT DISTINCT depart FROM teacher;

3、查詢Student表的所有記錄。

SELECT * FROM student;

4、查詢Score表中成績在60到80之間的所有記錄。

SELECT * FROM score WHERE degree BETWEEN 60 AND 80;

5、查詢Score表中成績為85，86或88的記錄。

SELECT * FROM score WHERE degree IN (85, 86, 88);

6、查詢Student表中「95031」班或性別為「女」的同學記錄。

SELECT * FROM student WHERE class = 95031 OR ssex = 女;

7、以Class降序查詢Student表的所有記錄。

SELECT * FROM student ORDER BY class DESC;

8、以Cno升序、Degree降序查詢Score表的所有記錄。

SELECT * FROM score ORDER BY cno, degree DESC;

9、查詢「95031」班的學生人數。

SELECT COUNT(sno) FROM student WHERE class = 95031;

10、查詢Score表中的最高分的學生學號和課程號。

SELECT sno, cno FROM score HAVING degree = MAX(degree);

錯了

SELECT sno, cno FROM score WHERE degree = MAX(degree);

錯了

SELECT FIRST(sno), FIRST(cno) FROM score ORDER BY degree DESC;

錯了

SELECT FIRST(sno, cno) FROM score ORDER BY degree DESC;

錯了...看答案

法1：

SELECT sno, cno FROM score WHERE degree = (SELECT MAX(degree) FROM score);

法2：

SELECT sno, cno FROM score ORDER BY degree DESC LIMIT 1;

11、查詢『3-105』號課程的平均分。

SELECT AVG(degree) FROM score WHERE cno = 3-105;

12、查詢Score表中至少有5名學生選修的並以3開頭的課程的平均分數。

SELECT AVG(degree) FROM score WHERE cno LIKE 3% ANDCOUNT(sno) >= 5GROUP BY cno;

錯了看答案

SELECT AVG(degree),cnoFROM scoreWHERE cno LIKE 3%GROUP BY cnoHAVING COUNT(sno) >= 5;

*計數函數不能和WHERE一起用，要和HAVING一起

13、查詢最低分大於70，最高分小於90的Sno列。

SELECT sno FROM scoreWHERE MIN(degree) >70 AND MAX(degree) <90GROUP BY sno;

錯了

SELECT sno FROM scoreGROUP BY snoHAVING MIN(degree) >70 AND MAX(degree) <90;

對了

*找最大/小值的函數也要和HAVING一起

14、查詢所有學生的Sname、Cno和Degree列。

SELECT a.sname, b.cno, b.degreeFROM student a JOIN score bON a.sno = b.sno;

15、查詢所有學生的Sno、Cname和Degree列。

SELECT b.sno, a.cname, b.degreeFROM course a JOIN score bON a.cno = b.cno;

16、查詢所有學生的Sname、Cname和Degree列。

SELECT a.sname, b.cname, c.degreeFROM student a JOIN (course b, score c)ON a.sno = c.sno AND b.cno = c.cno;

17、查詢「95033」班所選課程的平均分。

SELECT AVG(b.degree)FROM student a JOIN score bON a.sno = b.snoWHERE a.class = 95033;

19、查詢選修「3-105」課程的成績高於「109」號同學成績的所有同學的記錄。

SELECT * FROM scoreWHERE cno = 3-105 ANDdegree > (SELECT degree FROM score WHERE sno = 109 AND cno = 3-105);

20、查詢score中選學一門以上課程的同學中分數為非最高分成績的記錄。

SELECT * FROM scoreWHERE degree < (SELECT MAX(degree) FROM score)GROUP BY snoHAVING COUNT(sno) > 1ORDER BY degree;

21、查詢成績高於學號為「109」、課程號為「3-105」的成績的所有記錄。

SELECT * FROM scoreWHERE cno = 3-105 ANDdegree > (SELECT degree FROM score WHERE sno = 109 AND cno = 3-105);

22、查詢和學號為108的同學同年出生的所有學生的Sno、Sname和Sbirthday列。

SELECT sno, sname, sbirthday FROM studentWHERE YEAR(sbirthday) = (SELECT YEAR(sbirthday) FROM student WHERE sno = 108);

23、查詢「張旭「教師任課的學生成績。

SELECT b.degreeFROM course a JOIN (score b, teacher c)ON a.tno = c.tno AND a.cno = b.cnoWHERE c.tname = 張旭;

24、查詢選修某課程的同學人數多於5人的教師姓名。

SELECT c.tnameFROM course a JOIN (score b, teacher c)ON a.cno = b.cno AND a.tno = c.tnoGROUP BY b.cnoHAVING COUNT(b.cno) > 5;

25、查詢95033班和95031班全體學生的記錄。

SELECT * FROM student WHERE class IN (95033, 95031);

26、查詢存在有85分以上成績的課程Cno。

SELECT cno FROM scoreGROUP BY cnoHAVING MAX(degree) > 85;

27、查詢出「計算機系「教師所教課程的成績表。

SELECT b.cno, b.degreeFROM course a JOIN (score b, teacher c)ON a.cno = b.cno AND a.tno = c.tnoWHERE c.depart = 計算機系;

28、查詢「計算機系」與「電子工程系「不同職稱的教師的Tname和Prof。

SELECT tname, prof FROM teacherWHERE depart = 計算機系AND prof NOT IN (SELECT prof FROM teacher WHERE depart = 電子工程系);

29、查詢選修編號為「3-105「課程且成績至少高於選修編號為「3-245」的同學的Cno、Sno和Degree,並按Degree從高到低次序排序。

SELECT * FROM scoreWHERE cno = 3-105AND degree > (SELECT degree FROM score WHERE cno = 3-245)ORDER BY degree DESC;

錯了

SELECT * FROM scoreWHERE cno = 3-105AND degree > (SELECT degree FROM score WHERE cno = 3-245)ORDER BY degree DESCGROUP BY sno;

錯了看答案

SELECT * FROM scoreWHERE cno = 3-105 ANDdegree > ANY(SELECT degree FROM score WHERE cno =3-245)ORDER BY degree DESC;

*ANY()函數

30、查詢選修編號為「3-105」且成績高於選修編號為「3-245」課程的同學的Cno、Sno和Degree。

SELECT * FROM scoreWHERE cno = 3-105 ANDdegree > ALL(SELECT degree FROM score WHERE cno =3-245)ORDER BY degree DESC;

*ALL()函數

31、查詢所有教師和同學的name、sex和birthday。

SELECT tname AS name, tsex AS sex, tbirthday AS birthday FROM teacherUNIONSELECT sname AS name, ssex AS sex, sbirthday AS birthday FROM student;

32、查詢所有「女」教師和「女」同學的name、sex和birthday。

SELECT tname AS name, tsex AS sex, tbirthday AS birthday FROM teacherWHERE tsex = 女UNIONSELECT sname AS name, ssex AS sex, sbirthday AS birthday FROM studentWHERE ssex = 女;

33、查詢成績比該課程平均成績低的同學的成績表。

SELECT * FROM scoreGROUP BY cnoHAVING degree < AVG(degree);

錯了看答案

SELECT a.* FROM score aWHERE degree < (SELECT AVG(degree) FROM score b WHERE a.cno = b.cno);

34、查詢所有任課教師的Tname和Depart。

SELECT b.tname, b.departFROM course a JOIN teacher bON a.tno = b.tno;

35 、查詢所有未講課的教師的Tname和Depart。

SELECT tname, depart FROM teacherWHERE tname NOT IN (SELECT b.tname FROM course a JOIN teacher b ON a.tno = b.tno);

36、查詢至少有2名男生的班號。

SELECT class FROM student GROUP BY classHAVING COUNT(ssex) >= 2;

37、查詢Student表中不姓「王」的同學記錄。

SELECT * FROM studentWHERE sname NOT LIKE 王%;

38、查詢Student表中每個學生的姓名和年齡。

SELECT sname, (YEAR(NOW())-YEAR(sbirthday)) AS age FROM student;

39、查詢Student表中最大和最小的Sbirthday日期值。

SELECT MAX(sbirthday), MIN(sbirthday) FROM student;

40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。

SELECT * FROM student ORDER BY class DESC, sbirthday;

41、查詢「男」教師及其所上的課程。

SELECT b.tname, a.cnameFROM course a JOIN teacher bON a.tno = b.tnoWHERE b.tsex = 男;

42、查詢最高分同學的Sno、Cno和Degree列。

SELECT * FROM score ORDER BY degree DESC LIMIT 1;

43、查詢和「李軍」同性別的所有同學的Sname。

SELECT sname FROM studentWHERE ssex = (SELECT ssex FROM student WHERE sname = 李軍);

44、查詢和「李軍」同性別並同班的同學Sname。

SELECT sname FROM studentWHERE ssex = (SELECT ssex FROM student WHERE sname = 李軍)AND class = (SELECT class FROM student WHERE sname = 李軍);

45、查詢所有選修「計算機導論」課程的「男」同學的成績表。

SELECT c.*FROM student a JOIN (course b, score c)ON a.sno = c.sno AND b.cno = c.cnoWHERE b.cname = 計算機導論 AND a.ssex = 男;