看看我以前做过的经典例题
student(sno,sname,sex,age,deptno) --学生表
department(dno,dname) --系院表
teacher(tno,tname) --教师表
course(cno,cname,teachno) --课程表
score(id,stuno,corsno,score) --成绩表
--一检索学习jsp这门课程的学生,列出学生的名字
select student.sname
from student
where sno in(select stuno from score where corsno =(select cno from course where cname='jsp'))
go
select student.sno
from student inner join score on student.sno=score.stuno
inner join course on corsno=course.cno
where course.cname='jsp'
select sname
from student
where sno in(select score.stuno
from score inner join course on score.corsno=course.cno where course.cname='jsp')
--二检索java课程有多少学生学习.
select COUNT(stuno) as 'JAVA学习人数'
from score
where corsno =(select cno from course where cname='java')
--三检索各科课程各有多少学生学习,
select course.cname,COUNT(score.stuno) as '学习人数'
from score inner join course on score.corsno=course.cno
group by course.cname
--四检索学习了jsp和servlet两门课程的学生有多少
select COUNT(sno) as 'jsp和servlet课程的学生人数'
from student
where sno in(select score.stuno
from score inner join course on score.corsno=course.cno
where course.cname='jsp') and
sno in(select score.stuno
from score inner join course on score.corsno=course.cno
where course.cname='jsp')
--五检索学习了超过两门课程的学生有多少
select corsno,COUNT(stuno) as '学习超过两门课程的学生人数'
from score
group by corsno having COUNT(stuno)>2 order by corsno
--六检索重来没有学生学习过的课程,课程的名字
select cname
from course
where cno not in(select corsno from score )
--七检索老师A有多少学生
select COUNT(stuno) as '张老师的学生人数'
from score
where corsno in(select course.cno
from course inner join teacher on course.teachno=teacher.tno
where teacher.tname='张华' )
--九检索一共有多少老师,每一个老师所授课程是什么
select teacher.tno,course.cname
from course join teacher on course.teachno=teacher.tno
group by teacher.tno,course.cname
--十检索每个老师有多少个学生
select teacher.tno,teacher.tname ,COUNT(score.stuno) as '学生人数'
from teacher inner join course on teacher.tno=course.cno
inner join score on course.cno=score.corsno
group by teacher.tno,teacher.tname order by teacher.tno
--十一检索授课超过两门的老师
select teacher.tno,COUNT(course.cno) as '授课超过两门的老师'
from teacher inner join course on teacher.tno=course.teachno
group by teacher.tno having(COUNT(course.cno)>2) order by teacher.tno
--十三检索A老师所授课程被学生全部学习的学生的名字.
select sname
from student
where sno (select stuno from score inner join )
--查询选修人数超过人的课程的名字，以及每门课的选课总人数，并将结果按照人数的升序排序
select teacher.tno,COUNT(score.stuno) as '选课总人数'
from teacher inner join course on teacher.tno=course.teachno
inner join score on course.cno=score.corsno
group by teacher.tno having(COUNT(score.stuno)>350) order by count(score.stuno) asc
--查询每门课的成绩都比这门课的其他同学高的学生的学号
select stuno
from score
where
group by corsno
--查询每个同学的学号和姓名以及这个同学成绩为优秀的课程的门数。
select student.sno,student.sname,max(score.score)
from student inner join score on student.sno=score.stuno
group by student.sno,student.sname
--查询其他系的同学的年龄比‘软件工程系’的某个学生的年龄小的学生的学号，姓名和系别
--查询每个系中年龄高于这个系的平均年龄的学生的学号和姓名、年龄
select sno,sname,age
from student
group by deptno,sno,sname,age having(age>AVG(age))
--查询没有选课的学生的学号和姓名
select sno,sname
from student
where sno not in(select sno from score)
go
--查询每个同学成绩高于自己选修课程的平均分的学生的学号和选修课程的课号
select stuno,corsno
from score
group by stuno,corsno,score having(score>AVG(score))
--查询每个学生以及选修课程的情况（要求使用做外连接）
select student.sno,student.sname,course.cname
from student inner join score on student.sno=score.stuno
left outer join course on score.corsno=course.cno
order by student.sno
go
--自己举例实现带有ANY和ALL谓词的例子，各举一个例子
--查询其他系中比天文系所有学生年龄都小的学生姓名及年龄。
select sname,age,deptno
from student
where age<any(select student.age
from student inner join department on student.deptno=department.dno
where department.dname='天文系') and
deptno <>(select dno from department where dname='天文系')
go
--查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。
select student.sno,student.sname,department.dname,student.sex,student.age
from student,department
where student.deptno = department.dno
order by department.dname ASC,student.age DESC
go
--查询选修课门数等于或大于门的学生的平均成绩和选课门数。
select stuno,convert(numeric(8,2),AVG(score)) as '平均成绩',COUNT(corsno) as '选课门数'
from score
group by stuno having(COUNT(corsno)>2) order by stuno
go
--查询计算机系修数据结构课程的学生的修课成绩，要求列出学生姓名、课程名和成绩。
select student.sname,course.cname,score.score
from student inner join score on student.sno=score.stuno
inner join course on score.corsno=course.cno
where course.cname='c199' order by student.sname
go
--查询学生的选课状况，包括选了课程的学生和没有选课的学生。
select student.sname,count(score.corsno) as '选课状况'
from student left join score on student.sno=score.stuno
group by student.sname
go
--查询选修了“C06”课程，且成绩高于此课程平均成绩的学生学号和成绩。
select stuno,corsno,score
from score
where corsno=(select cno from course where cname='c100') and
score >
--删除计算机系所有不及格学生的选课记录。
select id,stuno,corsno,score
from score
where score<10
go
--创建计算机系学生的选课视图view001，包括学生号、姓名、性别、年龄、系、课程号、课程名及选课成绩。
create view viewScore
as
select student.sno,student.sname,student.sex,student.age,department.dname,course.cno,course.cname,score.score
from student,department,score,course
where student.deptno = department.dno and student.sno=score.stuno and score.corsno = course.cno order by student.sno
go
--在学生表的年龄列上建立一索引，索引名字为ix_age。
create index ix_age on student(age)
go