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

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

（select * from tb_grade order by Sno,Cno;）
+------+-------+-----+-------+-------+
| Sno | Sname | Cno | Cname | score |
+------+-------+-----+-------+-------+
| 1001 | 李菲 | 1 | 語文 | 86 |
| 1001 | 李菲 | 2 | 數學 | 56 |
| 1001 | 李菲 | 3 | 物理 | 48 |
| 1001 | 李菲 | 4 | 化學 | 90 |
| 1001 | 李菲 | 5 | 英語 | 57 |
| 1002 | 王琪 | 1 | 語文 | 90 |
| 1002 | 王琪 | 2 | 數學 | 68 |
| 1002 | 王琪 | 3 | 物理 | 87 |
| 1002 | 王琪 | 4 | 化學 | 68 |
| 1002 | 王琪 | 5 | 英語 | 45 |
| 1003 | 楊陽 | 1 | 語文 | 86 |
| 1003 | 楊陽 | 2 | 數學 | 56 |
| 1003 | 楊陽 | 3 | 物理 | 48 |
| 1003 | 楊陽 | 4 | 化學 | 90 |
| 1003 | 楊陽 | 5 | 英語 | 52 |
| 1004 | 和樹 | 1 | 語文 | 82 |
| 1004 | 和樹 | 2 | 數學 | 56 |
| 1004 | 和樹 | 3 | 物理 | 67 |
| 1004 | 和樹 | 4 | 化學 | 30 |
| 1004 | 和樹 | 5 | 英語 | 68 |
+------+-------+-----+-------+-------+

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;

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

| Sno | Sname |
+------+-------+
| 1001 | 李菲 |
| 1003 | 楊陽 |
| 1004 | 和樹 |
+------+-------+

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

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

相關文章

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