作业8

[3.60]查询所有选修了1号课程的学生姓名。
select sname
from student,sc
where student.sno = sc.sno
and sc.cno=‘1’;

[3.61] 查询没有选修1号课程的学生姓名。
select sname
from student
where not exists
(select *
from sc
where sno=student.sno and cno=‘1’);

[3.62]查询与“刘晨”在同一个系学习的学生。
select sno,sname,sdept
from student s1
where exists
(select *
from student s2
where s2.sdept=s1.sdept
and s2.sname=‘刘晨’);

[3.63]查询选修了全部课程的学生姓名
select sno,sname,sdept
from student
where not exists //若下层返回F，则该层返回T
(select *
from course
where not exists //对于该学号来说是否选了所有课程，若是，则返回F
(select *
from sc
where sno=student.sno
and cno = course.cno)
);
[3.64]查询计算机科学系的学生及年龄不大于19岁的学生。

[3.65]查询选修了课程1或者选修了课程2的学生。
select sno
from sc
where cno=‘1’
union select sno
from sc
where cno=‘2’;
[3.66]查询计算机科学系的学生与年龄不大于19岁的学生的交集。
select sno,sname,sage,sdept
from student
where sdept='cs’and sage<=19;
[3.67]查询既选修了课程1又选修了课程2的学生。
select sno
from sc
where cno=‘1’
intersect
select sno
from sc
where cno=‘2’;
[3.68]查询计算机科学系的学生与年龄不大于19岁的学生的差集。
select *
from student
where sdept=‘cs’
except select *
from student
where sage<=19;
[3.57]找出每个学生超过他自己选修课程平均成绩的课程号。
select sno,cno
from sc,(select sno,avg(grade)
from sc
group by sno)
as avg_sc(avg_sno,avg_grade)
where sc.sno=avg_sc.avg_sno
and sc.grade>=avg_sc.avg_grade

[3.60]查询所有选修了1号课程的学生姓名。
select sname
from student
where exists(
select *
from sc
where sno=student.sno
and cno=‘1’);

总结：
SELECT [ALL|DISTINCT] <目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名] [ ,<表名或视图名> [别名]] …
|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];