create table student(
sno char(10) primary key, --学号
sname nvarchar(20), --姓名
ssex bit, --性别
birthdate smalldatetime, --出生日期
sdept varchar(10) --所属院系
)
create table course
( cno char(6) primary key, --课程号
cname nvarchar(20), --课程名
ccredit int, --学分
pcno char(6) --先行课课程号
)
create table sc
(
sno char(10), --学号
cno char(6), --课程号
grade int, --课程成绩
primary key (sno,cno)
)
1.查询数据库课程的最高分、最低分、和平均分
use X
go
select max(grade) 最高分,min(grade) 最低分,avg(grade) 平均分
from course,sc
where course.cno=sc.cno and cname='数据库'
2.查询数据库课程的最高分的学生的情况,要求列出学号、姓名、该课程的成绩
use X
go
select student.sno 学号,sname 姓名,grade 课程分数
from course,sc,student
where course.cno=sc.cno and student.sno=sc.sno and course.cname='数据库'
and grade =(select max(grade) from sc,course where course.cno=sc.cno and cname='数据库')
3.查询各门课程的最高分的的学生情况,要求列出学号、姓名、课程号、课程名、该课程的最高分的成绩
use X
go
select a.sno 学号,a.sname 姓名,b.cno 课程号,b.grade 最高分
from student a
inner join sc b on
a.sno=b.sno
inner join
(select cno,max(grade) agrade
from sc
group by cno) c
on b.cno=c.cno
where b.grade =c.agrade
4.查询所有选修该课程的学生的成绩均及格的课程信息(至少两种方法)
(1)
use X
go
select course.* from course where not exists
(select * from sc where grade<60 and sc.cno=course.cno or grade is not null)
(2)
use X
go
select * from course
where not exists(select 1 from sc where sc.cno=course.cno and (grade<60 or grade is null))
5.查询“计算机”系学生选修“数据库”课程的情况,要求显示:学号、姓名、成绩(至少两种方法)
(1)
use X
go
select student.sno,sname,grade
from student left join sc on student.sno=sc.sno and
cno in (select cno from course where cname='数据库' )
where sdept='计算机学院'
(2)
use X
go
select student.sno,sname,grade
from student, sc,course
where student.sno=sc.sno and sc.cno=course.cno and
sdept='计算机学院' and cname='数据库'
union
select student.sno,sname,null
from student
where sno not in(select sno from sc where cno in(select cno from course where cname='数据库' ))
and sdept='计算机学院'
6.查询每个学生的选课门数
use X
go
select sc.sno ,count(*)
from student,sc where student.sno=sc.sno group by sc.sno
7.查询没有选修数据库课程、或选修了该课程但成绩不合格的学生的学号(至少用3种方法)
(1)
use X
go
select sno from student
where sno not in
(select sno from sc where cno in
(select cno from course where cname='数据库'))
or sno in(select sno from sc where cno in
(select cno from course where cname='数据库')
and (grade <60 or grade is null))
(2)
use X
select student.sno from student
left join sc on student.sno=sc.sno and
cno in(select cno from course where cname='数据库')
where (grade <60 or grade is null)
(3)
use X
go
select sno from sc where cno in
(select cno from course where cname='数据库')
and (grade <60 or grade is null)
union
select sno from student where sno not in
(select sno from sc where cno in
(select cno from course where cname='数据库'))
8.查询各院系的“数据库”课程及格以上学生人数超过3人的人数(3人以下计为0)。
use X
go
select sdept,case when count(cno)<3
then 0 else count(cno) end
from student
left join sc on
student.sno=sc.sno and grade>=60 and cno in
(select cno from course where cname='数据库')
group by sdept
9.查询某位学生的某门课程成绩比该门课程的平均成绩高10分的该学生的学号、姓名、课程号及该课程的成绩
use X
go
select a.sno,a.sname,b.cno,b.grade
from student a
inner join sc b on
a.sno=b.sno
inner join
(select cno,avg(grade) agrade
from sc
group by cno) c
on b.cno=c.cno
where b.grade =c.agrade+10