SQL面試題 三（單表、多表查詢）

一、給定一張學生成績表tb_grade：學號，姓名，科目號，科目名，分數。（如下表）

 1、查詢不及格科目數大於等於2的學生學號和不及格科目數量：

MySQL> select Sno,count(score) as '不及格科目數' from tb_grade where score<60 group by Sno having count(score)>=2;

+------+--------------+
| Sno  | 不及格科目數 |
+------+--------------+
| 1001 |            3 |
| 1003 |            3 |
| 1004 |            2 |
+------+--------------+

2、查詢不及格科目數大於等於2的學生學號和學生姓名：

MySQL> select Sno,Sname from tb_grade where score<60 group by Sno having count(score)>=2;

+------+-------+

3、查詢不及格科目數大於等於2的學生學號、學生姓名、科目號、科目名稱和分數，並按學號降序、科目號升序排序：

MySQL>  select * from tb_grade where score<60 and Sno in( select  Sno  from tb_grade where score<60  group by  Sno having count(score)>=2 ) order by Sno desc,Cno asc;

+------+-------+-----+-------+-------+
| Sno  | Sname | Cno | Cname | score |
+------+-------+-----+-------+-------+
| 1004 | 和樹  |   2 | 數學  |    56 |
| 1004 | 和樹  |   4 | 化學  |    30 |
| 1003 | 楊陽  |   2 | 數學  |    56 |
| 1003 | 楊陽  |   3 | 物理  |    48 |
| 1003 | 楊陽  |   5 | 英語  |    52 |
| 1001 | 李菲  |   2 | 數學  |    56 |
| 1001 | 李菲  |   3 | 物理  |    48 |
| 1001 | 李菲  |   5 | 英語  |    57 |
+------+-------+-----+-------+-------+

二、有三張表：class、student、score

班級表 class：                                                           

+-------+---------+---------+
| classID | className |
+---------+-----------+
|       1 | 一班      |
|       2 | 二班      |
|       3 | 三班      |
+---------+-----------+

學生表 student：

+-------+---------+---------+
| stuID | classID | stuName |
+-------+---------+---------+
|  1001 |       1 | 張三    |
|  1002 |       1 | 李麗    |
|  1003 |       1 | 錢封    |
|  1004 |       2 | 楊國    |
|  1005 |       2 | 小樣    |
|  1006 |       2 | 區天    |
|  1007 |       3 | 李三宅  |
|  1008 |       3 | 黃武    |
|  1009 |       3 | 趙六    |
+-------+---------+---------+

分數表 score：

+----------+-------+--------+-------+
| courseID | stuID | course | score |
+----------+-------+--------+-------+
|        2 |  1001 | 數學   |    73 |
|        3 |  1001 | 英語   |    79 |
|        1 |  1001 | 語文   |    81 |
|        3 |  1002 | 英語   |    87 |
|        2 |  1002 | 數學   |    83 |
|        1 |  1002 | 語文   |    79 |
|        1 |  1003 | 語文   |    65 |
|        3 |  1003 | 英語   |    65 |
|        2 |  1003 | 數學   |    97 |
|        1 |  1004 | 語文   |    78 |
|        3 |  1004 | 英語   |    78 |
|        2 |  1004 | 數學   |    86 |
|        1 |  1005 | 語文   |    67 |
|        3 |  1005 | 英語   |    88 |
|        2 |  1005 | 數學   |    89 |
|        2 |  1006 | 數學   |    90 |
|        3 |  1006 | 英語   |    92 |
|        1 |  1006 | 語文   |    98 |
|        1 |  1007 | 語文   |    85 |
|        2 |  1007 | 數學   |    78 |
|        3 |  1007 | 英語   |    72 |
|        1 |  1008 | 語文   |    78 |
|        3 |  1008 | 英語   |    77 |
|        2 |  1008 | 數學   |    85 |
|        3 |  1009 | 英語   |    94 |
|        2 |  1009 | 數學   |    91 |
|        1 |  1009 | 語文   |    68 |
+----------+-------+--------+-------+

1、查詢各班各科分數最高的學生學號，姓名，班級名稱，科目名稱，分數：

MySQL>

 select stu.stuID,stu.stuName,c.classID,c.className, sc.score from class c,student stu,
 score sc where c.classID=stu.classID and stu.stuID=sc.stuID order by c.classID,stu.stuID;

 轉載自：https://blog.csdn.net/yangyi22/article/details/7483859