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;