【MySQL】多表链接查询

(1)查询计算机工程系女生的学生学号、姓名及考试成绩:
SELECT A.sno,B.cno,sname,degree FROM student A,sc B WHERE A.sno=B.sno AND A.sdept='电子工程系';

(2)查询“自己”所选课程的名称、成绩。
SELECT b.cno,b.degree,sname FROM student a,sc b WHERE a.sno=b.sno AND  a.sname='于田田';

(3)查询“李新”教师所授课程的课程名称。
SELECT b.cno,tname FROM teacher a,teaching b WHERE a.tno=b.tno AND a.tname='李新'; 

(4)查询女教师所授课程的课程号及课程名称:
SELECT b.cno,c.cname,tname,tsex FROM teacher a,teaching b,course c WHERE c.cno=b.cno  AND a.tno=b.tno AND a.tsex='女';

(5)查询至少选修了一门课程的女生姓名，
SELECT x.sname FROM student X,sc Y WHERE x.ssex='女' AND x.sno=y.sno GROUP BY x.sno HAVING COUNT(cno)>=1;

(6)查询姓“王”的学生所学的课称名称。
SELECT a.sname,c.cname FROM student a,sc b,course c WHERE a.sname LIKE '王%' AND a.sno=b.sno AND b.cno=c.cno;

(7)查询选修“数据库”课程且成绩在80-90分的学生学号及成绩
SELECT a.sno,a.sname,c.cname,degree FROM student a,sc b, course c WHERE a.sno=b.sno AND b.cno=c.cno AND degree BETWEEN 80 AND 90 AND c.cname='数据库原理及应用';

(8)查询课程成绩及格的男生的学生信息、课程号与成绩。
SELECT b.cno,degree ,a.* FROM student a,sc b WHERE a.sno=b.sno AND degree>=60;

(9)查询选修“C04”课程的学生的平均年龄。
SELECT b.cno,AVG(YEAR(CURDATE())-YEAR(sbirthday))  FROM student a,sc b WHERE a.sno=b.sno AND b.cno='c04';

(10)查询选修课程名为“数学”的学生学号和姓名。
SELECT a.sno,sname FROM  student a,sc b, course c WHERE a.sno=b.sno AND b.cno=c.cno AND c.cname='高等数学';

(11)查询“钱军”教师任课的课程号、选修其课程的学生的学号和和成绩。
SELECT a.sname,a.sno,b.degree,b.cno FROM student a, sc b,teacher c,teaching d WHERE a.sno=b.sno AND b.cno=d.cno AND d.tno=c.tno AND c.tname='钱军';

(12)查询在第3学期所开课程的课程名称及学生的成绩。
SELECT cname,degree FROM sc,course a,teaching b WHERE b.cterm=3 AND b.cno=a.cno AND sc.cno=a.cno;

(13)查询“C02”课程不及格的学生信息。
SELECT degree,a.*  FROM student a,sc b WHERE a.sno=b.sno AND b.cno='c02'AND degree<60;

(14)查询信息工程系成绩在90分以上的学生姓名、性别和课程名称。
SELECT a.sname,a.ssex,b.cname FROM student a,course b,sc WHERE sdept=' 信息工程系' AND a.sno=sc.sno AND degree>90 AND sc.cno=b.cno;

(15)查询同时选修了“C04”和“C02”课程的学生姓名和成绩。
SELECT student.sname,a.degree,b.degree FROM sc a, sc b,student WHERE student.sno=a.sno AND student.sno=b.sno AND a.sno=b.sno AND a.cno='c04' AND b.cno='c02';