数据库原理8个例子sql语句

习题

select s#,score from sc where c#=2

select s.s#,s.sname from s,sc where s.s#=sc.s# and c#=2

select distinct s.s#,s.sname from s,sc,c,t
where s.s#=sc.s# and sc.c#=c.c# and c.t#=t.t# and t.tname='john'

select distinct t.s# from sc t where t.c#=1 or t.c#=2;

select t.s# from sc t,sc s where t.s#=s.s# and t.c#=1 and s.c#=2

--查询不学2号课程的所有学生

select distinct s.s# from sc s where s.s# not in(select distinct t.s# from sc t where t.c#=2);

--检索查询学过所有课程的学生

select distinct(sc.s#),s.sname from sc,s where sc.s#=s.s# group by sc.s#,s.sname having count(sc.c#)=all(select count(t.c#) from c t)

--检索所学课程包含学号为s3 所学课程的学生号
select s.s# from sc s where exists
 (select a.c# from sc a where a.s#=3 and a.c#=s.c#) group by s.s# having count(s.c#)=all(
select count(a.c#) from sc a where a.s#=3)