Mysql多表查询面试题

题目素材：

查询要求：

(1)

mysql> select st.s_id 学号,st.s_name 姓名,count(sc.s_id) 选课数,sum(s_score) 总成绩 from student st inner join score sc on(st.s_id=sc.s_id) group by st.s_id;
+--------+-----------+-----------+-----------+
| 学号   | 姓名      | 选课数    | 总成绩    |
+--------+-----------+-----------+-----------+
|      1 | 王宇      |         3 |       288 |
|      2 | 小二      |         3 |       192 |
|      3 | 谢金富    |         3 |       238 |
|      4 | 肖勇      |         2 |       200 |
+--------+-----------+-----------+-----------+
4 rows in set (0.00 sec)

(2)
1.先联合课程表和教师表，查出张三老师的任课科目的c_id，查出第一张表b1；

mysql> select co.c_id,te.t_name from course co inner join teacher te on(co.t_id=te.t_id) where te.t_name='张三';
+------+--------+
| c_id | t_name |
+------+--------+
|    6 | 张三   |
+------+--------+
1 row in set (0.00 sec)

2.将成绩表和b1联合查询，查出张三老师的任课科目(c_id)对应的学生s_id，查出第二张表b2;

mysql> select sc.s_id from score sc inner join (select co.c_id,te.t_name from course co inner join teacher te on(co.t_id=te.t_id) where te.t_name='张三') b1 on(sc.c_id=b1.c_id);
+------+
| s_id |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

3.将b2表和student表联合查询，查出的是张三老师的学生的s_id（学号），和s_name（姓名）

mysql> select st.s_id 学号,st.s_name 姓名 from student st inner join (select sc.s_id from score sc inner join (select co.c_id,te.t_name from course co inner join teacher te on(co.t_id=te.t_id) where te.t_name='张三') b1 on(sc.c_id=b1.c_id)) b2 on(st.s_id=b2.s_id);
+--------+-----------+
| 学号   | 姓名      |
+--------+-----------+
|      1 | 王宇      |
|      2 | 小二      |
|      3 | 谢金富    |
+--------+-----------+
3 rows in set (0.00 sec)

(3)

mysql> select st.s_id 学号,st.s_name 姓名 from student st inner join (select s_id  from score where c_id in(select c_id from score where s_id='02') group by s_id having count(*) = (select count(*) from score where s_id = '02')) b1 on(st.s_id=b1.s_id);
+--------+-----------+
| 学号   | 姓名      |
+--------+-----------+
|      1 | 王宇      |
|      2 | 小二      |
|      3 | 谢金富    |
+--------+-----------+
3 rows in set (0.01 sec)

(4)目的未达到，代码错误。

select sc.s_id as 学号,sc.c_id='04' as 数据库,sc.c_id='01' as 企业管理,sc.c_id='06' as 英语,(select count(distinct sc.c_id) from score sc student st where st.s_id = sc.s_id) as 有效课程数, (select avg(sc.s_score) from score sc student st where st.s_id=sc.s_id ) as 有效平均分 from score sc order by 平均成绩 desc;

(5)

mysql> select course.c_name, course.c_id,
    -> sum(case when sc.s_score<=100 and sc.s_score>85 then 1 else 0 end) as "[100-85]",
    -> sum(case when sc.s_score<=85 and sc.s_score>70 then 1 else 0 end) as "[85-70]",
    -> sum(case when sc.s_score<=70 and sc.s_score>60 then 1 else 0 end) as "[70-60]",
    -> sum(case when sc.s_score<=60 and sc.s_score>0 then 1 else 0 end) as "[60-0]"
    -> from score sc left join course
    -> on sc.c_id = course.c_id
    -> group by sc.c_id;
+--------------+------+----------+---------+---------+--------+
| c_name       | c_id | [100-85] | [85-70] | [70-60] | [60-0] |
+--------------+------+----------+---------+---------+--------+
| 企业管理     |    1 |        2 |       1 |       1 |      0 |
| 数据库       |    4 |        2 |       0 |       1 |      1 |
| 英语         |    6 |        2 |       0 |       1 |      0 |
+--------------+------+----------+---------+---------+--------+
3 rows in set (0.02 sec)