1查询刘伟选的课程名。(t,tc,c)
select distinct cn from t,tc,c
where t.tno=tc.tno and tc.cno=c.cno and
c.cno in(select tc.cno from tc
where tc.tno in(select t.tno from t where tn='刘伟'))
2查询不学数据库的学生学号。(sc,c)
select sno from s
where not exists (select * from c
where cn='数据库' and
exists (select * from sc
where sc.cno=c.cno and s.sno=sc.sno))
3查询计算机系成绩不及格的学生信息。(s,sc)
select * from s where dept='计算机' and
exists (select sno from sc where s.sno=sc.sno and score<60)
4查询选修了跟李思一样课程的学生姓名及成绩
select sn,score from s,sc
where s.sno=sc.sno and sn!='李思' and
cno in(select cno from sc
where sc.sno=(select s.sno from s where sn='李思'))
5查询有3人以上选修的课程名。
select cn from c
where exists (select cno,count(sno) from sc
where sc.cno=c.cno
group by cno having count(sno)>3)
6查询每个学生的选课情况。(要求列出学号,姓名,课程号,课程名)
select s.sno,sn,sc.cno,cn from c,s,sc
where s.sno=sc.sno and sc.cno=c.cno
7查询既选修了c1号课又选修了c3号课的学生学号(用自身连接)。
select x.sno from sc x
where x.cno='c1'and exists(select * from sc y
where y.cno='c3'and x.sno=y.sno )
8查询选修c2号课且成绩在80分以上的学生学号和姓名。
select s.sno,sn from sc,s
where cno='c2' and score>80 and s.sno=sc.sno
9查询所有学生的信息和对应的选课信息,没有选课的学生选课记录为NULL。。
select * from s left join sc on s.sno=sc.sno
10查询没有选修任何课程的学生姓名、所在院系。
select sn,dept from s
where not exists( select distinct sno from sc where s.sno=sc.sno)
11查询没有选修"编译原理"的学生姓名。
select sn from s where sno in(select sno from sc
where cno=(select cno from c where cn='编译原理'))
12)查询“数据库”课程的选课人数和总成绩。
select count(distinct sno),sum(score) from sc
where cno=( select cno from c where cn='数据库')
13查询其他系中比信息系某一学生年龄小的学生姓名和年龄。
select sn,age from s
where dept!='信息'and age<( select min(age) from s where dept='信息')
14查询选了所有课程的学生信息
select * from s
where not exists( select * from c
where not exists (select * from sc where sc.cno=c.cno and s.sno=sc.sno))
表在文件里面!!!!!!!!!!!!!!