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

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

(1) 查询计算机工程系女学生的学生学号、姓名及考试成绩。

SELECT s.sno, sname, degree
FROM student s
LEFT OUTER JOIN sc c on s.sno = c.sno
WHERE ssex = '女'
AND sdept = '计算机工程系';

(2) 查询“李勇”同学所选课程的成绩。(不考虑重名)

SELECT sname, c.cname, sc.degree
FROM sc
         LEFT JOIN student s on sc.sno = s.sno
         LEFT OUTER JOIN course c on c.cno = sc.cno
WHERE sname = '李勇';

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

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

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

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

(5) 查询至少选修一门课程的女学生姓名。

SELECT sname
FROM student
LEFT JOIN sc s on student.sno = s.sno
WHERE ssex = '女'
GROUP BY s.sno
HAVING COUNT(cno) > 1;

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

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

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

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

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

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

(9) 查询课程成绩及格的男同学的学生信息及课程号与成绩。

SELECT d.*, cno, degree
FROM student d
         LEFT JOIN sc s on d.sno = s.sno
WHERE degree >= 60;

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

SELECT AVG(age)
FROM (
         SELECT(YEAR(NOW()) - YEAR(sbirthday)) age
         FROM student
         LEFT JOIN sc s on student.sno = s.sno
         WHERE cno = 'C02'
     )  avs;

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

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

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

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

(13)查询“钱军”教师任课的课程号，选修其课程的学生的姓名。

SELECT d.cno, s2.sname
FROM course d
         LEFT JOIN sc s on d.cno = s.cno
         LEFT JOIN teaching t on d.cno = t.cno
         LEFT JOIN teacher t2 on t2.tno = t.tno
         LEFT JOIN student s2 on s2.sno = s.sno
WHERE t2.tname = '钱军';

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

SELECT cname, degree
FROM course
         LEFT JOIN sc ON course.cno = sc.cno
         LEFT JOIN teaching t on course.cno = t.cno
WHERE cterm = 3
GROUP BY sno;;

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

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

(16)查询软件工程系成绩在90分以上的学生姓名、性别和课程名称。

SELECT sname , ssex , cname FROM
    student d LEFT JOIN sc s on d.sno = s.sno
              LEFT JOIN course c on s.cno = c.cno
WHERE degree > 90 AND d.sdept = '软件工程系';

(17) 查询同时选修了“C04”和“C02”课程的学生姓名和成绩。

SELECT sname,b.degree ,c.degree
FROM student a,sc b,sc c
WHERE a.sno=b.sno AND b.sno=c.sno
  AND b.cno='C01' AND c.cno='C02'
GROUP BY sname;