数据库系统学生成绩管理实验报告

数据库系统上机实验题

一、学生学习系统的基本表

学生：s(sno，sname , sex , age , dept )；表中属性分别为：学号，姓名，性别，年龄和

系名。

课程：c(cno , cname , tname )；表中属性分别为：课号，课名，教师名。

选课：sc(sno , cno , grade )；表中属性分别为：学号，课号和成绩。

要求：利用SQL语句创建上面三个基本表，包括：主键，外键定义。

有针对性地输入一些实验数据。

二、利用SQL语句，在上面三个基本表中，完成如下的功能

1.查找学号2至10号学生的学号、姓名和系名。

2.查找女同学的人数。

3.查找学号2至10号学生中，女同学的人数。

4.查找男、女同学各有多少人（注：输出性别和人数）。

5.查找‘CS’系中，男、女同学各有多少人（注：输出性别和人数）

6.查找年龄在18至20岁之间的所有男同学的学号、姓名和系名。

7.查找所有学生选修课程的门数。

8.查找学号2至15号学生中，英语成绩超过80分的学生学号和姓名。

9.查找没有学习‘物理’课程的学生姓名和系名。

10.查找‘王明’同学所学课程的门数。

11.统计‘王明’同学所学各门课程的平均分数。

12.统计英语成绩高于该课程平均成绩的人数。

13.查找查找至少选修了‘王一’和‘王二’老师所授课程的学生学号与姓

名。

14.查找‘CS’系比‘MA’系的学生年龄都小的学生姓名和系名。

15.统计每个学生的总分，输出学生的学号和总分，总分按降序排列。

16.查找学号比‘王一’同学大，而年龄比他小的学生学号、姓名和年龄。

17.查找年龄大于女同学的平均年龄的男同学的姓名与年龄。

18.查找只选修了一门课程的同学的学号和姓名。

19.查找‘CS’系学生中，没有选修‘王一’老师课程的学号、姓名和系名。

20.查找至少选修了3门课程的学生学号和姓名。

21.查找每门课程的最高分和最低分。（注：输出课号、最高分和最低分）

22.采用查询视图方式完成21题的功能：

利用视图方式的步骤：

建立视图，如：gdf（cno，max，min）属性：课号、最高分和最低分。

利用SELECT语句对视图gdf查询显示。

1.将所有学生的年龄增加1岁。

2.将‘物理’课程不及格的成绩改为0分。

3.删除‘王五’老师的任课信息

4.将一门课的成绩大于等于80分学生的学号、姓名和系名插入到一个已存在（要建

立）基本表stu（sno，sname，dept）中。

分配空间

create database ccjj

创建表s,c,sc

create table s

( sno char(10) primary key ,

sname varchar (10) unique ,

sex char(2) ,

age smallint ,

dept char(4)

);

create table c

( cno char(10) primary key ,

cname varchar (10) ,

tname char (10) ,

);

create table sc

( sno char(10) ,

cno char(10) ,

grade smallint,

primary key(sno,cno),

foreign key(sno) references s(sno),

foreign key(cno) references c(cno),

);

S表

SC表

C表

1

1.查找学号2至10号学生的学号、姓名和系名。

select sno,sname,dept

from s

where sno between 2 and 5;

2.查找女同学的人数。

select sex,count(sno)人数

from s

where sex='女'

group by sex;

3.查找学号2至10号学生中，女同学的人数。

select sex,count(sno)

from s

where sex='女' and sno between 2 and 5

group by sex;

4.查找男、女同学各有多少人（注：输出性别和人数）

select sex,count(sno)人数

from s

group by sex;

5.查找‘CS’系中，男、女同学各有多少人（注：输出性别和人数）

from s

where dept='ce'

group by sex;

6.查找年龄在18至20岁之间的所有男同学的学号、姓名和系名。

select sno,sname,dept

from s

where age between 18 and 20 and sex='男';

7.查找所有学生选修课程的门数。

select sno, count(cno)门数

from sc

group by sno;

8.查找学号2至15号学生中，英语成绩超过80分的学生学号和姓名。

select s.sno ,sname

from s,c,sc

where s.sno=sc.sno

and https://www.wendangku.net/doc/3e8421271.html,o=https://www.wendangku.net/doc/3e8421271.html,o

and s.sno between 2 and 4

and cname='英语'

and grade>80;

9.查找没有学习‘物理’课程的学生姓名和系名。

select sname,dept

from s

where not sno in(select sno

from sc

where cno in(select cno

from c

where not cname<>'物理'))

10.查找‘王明’同学所学课程的门数。

select sname,count(cno)门数

from s,sc

where s.sno=sc.sno and s.sname='王明'

group by sname;

11.统计‘王明’同学所学各门课程的平均分数。

select sname, avg(grade)平均分

from sc ,s

where sc.sno in ( select sno

from s

where sname='王明')

and s.sname='王明'

group by sname;

12.统计英语成绩高于该课程平均成绩的人数。

select count(*)人数

from sc

where grade>(select avg(grade)

from sc

where cno in(select cno

from c

where cname='英语'));

13.查找查找至少选修了‘王一’和‘王二’老师所授课程的学生学号与姓

名。

select sno, sname from s

where sno in (select sno from sc

where cno = (select cno from c

where tname='王一') or cno=(select cno from c

where tname='王二') group by sno having count(cno)>=2 );

14.查找‘CS’系比‘MA’系的学生年龄都小的学生姓名和系名。

select sname,dept

from s

where age <(select min(age)

from s

where dept='ma')

and dept='cs';

15.统计每个学生的总分，输出学生的学号和总分，总分按降序排列。

select sno,sum(grade)总分

from sc

group by sno

order by 2 desc

16.查找学号比‘王一’同学大，而年龄比他小的学生学号、姓名和年龄。

select sno,sname,age

from s

where sno >(select sno

from s

where sname='王一')

and age <(select age

from s

where sname='王一')

17.查找年龄大于女同学的平均年龄的男同学的姓名与年龄。

select sname,age

from s

where age>(select avg(age)

from s

where sex='女')

and sex='男';

18.查找只选修了一门课程的同学的学号和姓名。

select s.sname,s.sno

from sc ,s

where s.sno=sc.sno

group by s.sno,s.sname

having count(*)=1;

19.查找‘CS’系学生中，没有选修‘王一’老师课程的学号、姓名和系名。

select sno,sname,dept

from s

where not sno in( select sno

from sc

where https://www.wendangku.net/doc/3e8421271.html,o in( select cno

from c

where not tname<>'王一'))

and dept='cs';

20.查找至少选修了3门课程的学生学号和姓名。

select s.sname,s.sno

from sc ,s

where s.sno=sc.sno

group by s.sno,s.sname

having count(*)>=3;

21.查找每门课程的最高分和最低分。（注：输出课号、最高分和最低分）

select max(grade)最高分, min(grade)最低分

from sc

group by cno;

22.采用查询视图方式完成21题的功能：

利用视图方式的步骤：

建立视图，如：gdf（cno，max，min）属性：课号、最高分和最低分。利用SELECT语句对视图gdf查询显示。

create view gdf(cno,max,min)

as

select cno, max(grade),min(grade)

from sc

group by cno;

1将所有学生的年龄增加1岁。

update s

set age = age+1;

2将‘物理’课程不及格的成绩改为0分。

update sc

set grade=0

where cno in(select cno

from c

where cname='物理'and grade<60)

3删除‘王五’老师的任课信息

delete

from sc

where cno in(select cno

from c

where tname='王五')

delete from c

where tname='王五'

4将一门课的成绩大于等于80分学生的学号、姓名和系名插入到一个已存在（要建立）基本表stu（sno，sname，dept）中。

create table stu

( sno char(10) primary key ,

sname varchar (10) unique ,

dept char(4)

);

insert into stu(sno,sname,dept)

select sno,sname,dept

from s

where sno in(select sno

from sc

where grade>80)

举一反三

1.查询每个学生及其选修课的情况

select s.*,sc.*

from s ,sc

where s.sno=sc.sno

2.查询选修“政治”的学生姓名与学号

select sname,sno

from s

where sno in(select sno

from sc

where cno in(select cno

from c

where cname='政治'

))

3.找出每个学生超过他选修课平均成绩的课程号

select sno,cno

from sc t1

where grade >=(select avg(grade)

from sc t2

where t1.sno=t2.sno)