数据库多表查询

# 查询计算机工程系女学生的学生学号、姓名及考试成绩。
SELECT student.sno, sname, degree
FROM student,
     sc
WHERE ssex = '女'
  AND sdept = '计算机工程系';
#查询李勇同学所选课程的成绩（不考虑重名）
SELECT student.SNO, degree
FROM student,
     sc
WHERE sname = '李勇';
#  查询“李新”老师所授课程的课程名称。
select cname, teaching.cno
from course,
     teaching
where teaching.cno = 'C01';
# 查询女老师所教授课程的课程号及课程名称
select teacher.tno, cno, cname
from teacher,
     course
where tsex = '女';
# 查询至少选修一门课程的女学生姓名。
select cno, sno, sname
from course,
     student
where ssex = '女';
# 查询姓“王”的学生所学的课程名称。
select sname, course.cname, cno
from course,
     student
where sname like '%王%'
  and cno = '20050301';
# 查询选修“数据库”课程且成绩在80～90分之间的学生学号及成绩。
select sno, course.cno, degree
from sc,
     course
where degree > 80
  and degree < 90;
# 查询选修“数据库”课程且成绩在80～90分之间的学生姓名及成绩。
select sname, course.cno, sc.degree
from student,
     course,
     sc
where degree > 80 and degree < 90
   or sc.sno = student.sno;
# 查询课程成绩及格的男同学的学生信息及课程号与成绩。
select student.sno, course.cno, sc.degree
from student,
     course,
     sc
where degree > 60
  and ssex = '男';
# 查询选修“c04”课程的学生的平均年龄。
select avg(sbirthday), cno
from student,
     course
where cno = 'C04';
# 查询学习课程名为“数学”的学生学号和姓名。
select sno, sname, course.cname
from student,
     course
where cname = '数学';
# 查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩。
select teacher.tno, teaching.cno, sno, degree
from sc,
     teacher,
     teaching
where teacher.tno = '102';

# 	查询“钱军”教师任课的课程号，选修其课程的学生的姓名。
SELECT teacher.tno, teaching.cno, sname
from student,
     teacher,
     teaching
where teacher.tno = '102';

# 查询在第3学期所开课程的课程名称及成绩
SELECT cno, degree
FROM sc
WHERE cno = any (SELECT cno
                 FROM teaching
                 WHERE cterm = '3');


# 查询“c02”号课程不及格的学生信息。

SELECT *
FROM student
WHERE sno IN (SELECT sno
              FROM sc
              WHERE cno = 'c02'
                AND degree < 60);

# 查询软件工程系成绩在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 = '软件工程系';


# 查询同时选修了“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;