[MySQL]学生表、教师表、课程表、授课表、成绩表的多表查询案例(2)

多表查询环境：已有5个表，student为学生表，teacher为教师表,teaching为授课表,course为课程表，sc为成绩表。


(1) 查询“李勇”同学所选课程的成绩。

SELECT degree
FROM sc
LEFT JOIN student s on s.sno = sc.sno
WHERE sname = '李勇';

(2) 查询“李新”老师所授课程的课程名称。

SELECT cname
FROM course
         LEFT OUTER JOIN teaching t on course.cno = t.cno
         LEFT OUTER JOIN teacher t2 on t2.tno = t.tno
WHERE t2.tname = '李新';

(3) 查询女教师所授课程的课程号及课程名称。

SELECT course.cno, cname
FROM course
         LEFT JOIN teaching t on course.cno = t.cno
         LEFT OUTER JOIN teacher t2 on t2.tno = t.tno
WHERE tsex = '女';

(4) 查询姓“王”的学生所学的课程名称

SELECT cname
FROM course
         LEFT JOIN sc s on course.cno = s.cno
         LEFT OUTER JOIN student s2 on s2.sno = s.sno
WHERE sname LIKE '王%';

(5) 查询选修“数据库”课程且成绩在80～90分之间的学生学号及成绩。

SELECT student.sno, degree
FROM student
         LEFT JOIN sc s on student.sno = s.sno
         LEFT JOIN course c on c.cno = s.cno
WHERE c.cname = '数据库'
HAVING degree BETWEEN 80 AND 90;

(6) 查询学习课程名为“数学”的学生学号和姓名。

SELECT student.sno, sname
FROM student
         LEFT JOIN sc s on student.sno = s.sno
         LEFT JOIN course c on c.cno = s.cno
WHERE cname = '数学';

(7) 查询选修“C04”课程的学生的平均年龄。

SELECT AVG(DISTINCT YEAR(NOW()) - YEAR(student.sbirthday)) AS '平均年龄'
FROM student
         LEFT JOIN sc s on student.sno = s.sno
         LEFT OUTER JOIN course c on c.cno = s.cno
WHERE s.cno = 'C04';

(8) 查询在第3学期所开课程的课程名称及成绩。

SELECT cname, degree
FROM course
         LEFT JOIN teaching t on course.cno = t.cno
         LEFT JOIN sc s on course.cno = s.cno
WHERE t.cterm = 3;

(9) 查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩。

SELECT cname, student.sno, degree
FROM student
         LEFT JOIN sc s on student.sno = s.sno
         LEFT OUTER JOIN course c on c.cno = s.cno
         LEFT JOIN teaching t on s.cno = t.cno
         LEFT OUTER JOIN teacher t2 on t2.tno = t.tno
WHERE tname = '钱军';

(10) 查询与“李勇”同一个系的同学姓名。

SELECT sname
FROM student
WHERE sdept = (SELECT sdept FROM student WHERE sname = '李勇')
  AND sname != '李勇';

(11) 查询学号比“刘晨”同学大，而出生日期比他小的学生姓名。

SELECT sname
FROM student
WHERE sno > (SELECT sno FROM student WHERE sname = '刘晨')
  AND sname != '刘晨'
  AND (YEAR(NOW()) - YEAR(sbirthday)) <
      (SELECT (YEAR(NOW()) - YEAR(sbirthday)) FROM student
       WHERE sname = '刘晨');

(12) 查询出生日期大于所有女同学出生日期的男同学的姓名及系别。

SELECT sname, sdept
FROm student
WHERE sno > (SELECT sno FROM student WHERE sname = '刘晨')
  AND (YEAR(NOW()) - YEAR(sbirthday)) >
      ALL (SELECT (YEAR(NOW()) - YEAR(sbirthday)) FROM student WHERE ssex = '女')
  AND ssex = '男';

(13) 查询成绩比该课程平均成绩高的学生的学号及成绩。

SELECT sc.sno, degree
FROM sc
         LEFT OUTER JOIN course c on c.cno = sc.cno
         LEFT JOIN (SELECT cname, AVG(degree) avgd
                    FROM sc
                    LEFT OUTER JOIN course c on c.cno = sc.cno
                    GROUP BY c.cname) t on c.cname = t.cname
WHERE degree > t.avgd;

(14) 查询不讲授“C01”课的教师姓名。

SELECT tname
FROM teacher
         LEFT JOIN teaching t on teacher.tno = t.tno
WHERE cno != 'C01';

(15) 查询“C02”号课程不及格的学生信息。

SELECT student.*
FROM student
         LEFT JOIN sc s on student.sno = s.sno
WHERE s.cno = 'C02'
  AND degree < 60;

(16) 查询没有选修“C02”课程的学生学号及姓名。

SELECT sno , sname FROM student
WHERE sno NOT IN
(
SELECT s.sno FROM student s
LEFT JOIN
(SELECT sno , cno FROM sc WHERE sc.cno = 'C02')
t ON t.sno = s.sno WHERE s.sno = t.sno
);

(17) 查询选修了“数据库”课程的学生学号、姓名及系别。

SELECT student.sno, sname, sdept
FROM student
         LEFT JOIN sc s on student.sno = s.sno
         LEFT OUTER JOIN course c on c.cno = s.cno
WHERE cname = '数据库';