MySQL--子查询相关操作

查询没有选修05203314号课程的学生姓名、学生专业。

查询与‘丁志杰’在同一个专业学习的学生学号、姓名和性别。 

查询选修‘信工’学院开设课程的学生学号及成绩。

select stu_id,grade
from score
where cs_id in
 (select cs_id from course where cs_depart='信工');

查询与‘王芳’老师讲授同一门课的老师的教师编号和教师姓名。

SELECT distinct teacher.tea_id,tea_name
FROM teacher JOIN teaching on  teacher.tea_id=teaching.tea_id
WHERE cs_id in
(SELECT cs_id from teacher JOIN teaching on  teacher.tea_id=teaching.tea_id WHERE tea_name='王芳');

查询‘王芳’老师讲授的课程名和课程类型。
 select cs_name,cs_type
 from course
 where cs_id in
 (select cs_id from teaching join teacher on teacher.tea_id=teaching.tea_id where tea_name='王芳');

查询‘计科’专业学生选修的课程名和课程类型。
 select cs_name 课程名,cs_type 课程类型
 from course
 where cs_id in
 (select cs_id from score join student on student.stu_id=score.stu_id where stu_major='计科');

查询没有选修‘数据库原理及应用’课程的学生学号。
 select distinct stu_id
 from score
 where cs_id in
 (select cs_id from course where cs_name<>'数据库原理及应用');

查询与‘张依婷’在同一个学院学习的学生学号、姓名和专业。
select stu_id,stu_name,stu_major
from student
where stu_college in
 (select stu_college from student where stu_name='张依婷');