数据库实验

实验一：创建库表

创建 STUDENT 数据库，根据学生信息、课程信息和选修信息设计学生关 系、课程关系 、选课关系，并创建对应的基本表 student 、course 、 SC

create table STUDENT
(
Sno varchar(30) not null,
Sname varchar(30),
Ssex nchar(1),
Sage int,
Sdept varchar(30)
)
 
create table COURSE
(
Cno varchar(30) not null,
Cname varchar(30),
Cpno int,
Ccredit int
)

create table SC
(
Sno varchar(30) not null,
Cno int,
Grade int
)

实验二：SQL 语言使用

1 使用上次实验创建的数据库和表，利用企业管理器或查询分析器向基本表 中插入实验数据。

2 查询全体学生学号和姓名 6

3 查询全体学生学号、姓名、性别、年龄和所在系

4 查询全体学生选课情况，即学号、课程号、成绩

5 显示所有选课学生的学号，并取掉重复行

6 查询成绩大于 80 分的学生的学号、课程号、成绩

7 查询计算机系全体学生的学号

8 查询计算机系年龄在 18 岁以上的学生的学号、姓名

9 查询选修了数据库课程或数据结构课程学生的学号、成绩

10 查询选修了 C1 课程的学生的学号和成绩，查询结果按成绩降序排序

11 查询每个学生的学号、姓名、选修的课程名、成绩

12 查询与‘李勇’在同一个系学习的学生学号、姓名

13 查询选修了 3 门以上课程的学生学号

14 查询没有选修课程的学生的基本信息

15 找出平均成绩在 85 分以上的学生，结果按系分组，并按平均成绩的升序 排列

16 查询选修课程平均成绩在 80 分以上的学生学号

17 查询数据结构课程的先行课的先行课课程名

18 查询同时选修《数据库》和《数据结构》两门课程的学生姓名

19 查询每个系的平均年龄，并建立视图

20 建立课程的先修课程的视图

-1 使用上次实验创建的数据库和表，利用企业管理器或查询分析器向基本表 中插入实验数据。
insert into STUDENT values('201215121','李勇','男',20,'CS');
insert into STUDENT values('201215122','刘晨','女',19,'CS');
insert into STUDENT values('201215123','王敏','女',18,'MA');
insert into STUDENT values('201215125','张立','男',19,'IS');

insert into COURSE values(1,'数据库',5,4);
insert into COURSE(Cno,Cname,Ccredit) values(2,'数学',2)
insert into COURSE values(3,'信息系统',1,4);
insert into COURSE values(4,'操作系统',6,3);
insert into COURSE values(5,'数据结构',7,4);
insert into COURSE(Cno,Cname,Ccredit) values(6,'数据处理',2)
insert into COURSE values(7,'PASCAL语言',6,4);

insert into SC values('201215121',1,92)
insert into SC values('201215121',2,85)
insert into SC values('201215121',3,88)
insert into SC values('201215122',2,90)
insert into SC values('201215122',3,80)

--2 查询全体学生学号和姓名
select Sno,Sname from STUDENT	

--3 查询全体学生学号、姓名、性别、年龄和所在系
select * from STUDENT	

--4 查询全体学生选课情况，即学号、课程号、成绩
select * from SC		

--5 显示所有选课学生的学号，并去掉重复行
select Sno from SC group by Sno	

--6 查询成绩大于80分的学生的学号、课程号、成绩
select Sno,Cno,Grade from SC where Grade > 80	

--7 查询计算机系全体学生的学号
select Sno from STUDENT where Sdept='CS'		

--8 查询计算机系年龄在18岁以上的学生的学号、姓名
select Sno,Sname from STUDENT where	Sdept = 'CS' and Sage > 18	

--9 查询选修了数据库课程或数据结构课程学生的学号、成绩
select Sno,Grade from SC,COURSE where COURSE.Cno=SC.Cno and (Cname = '数据库' or Cname = '数据结构')

--10 查询选修了C1课程的学生的学号和成绩，查询结果按成绩降序排序
select Sno,Grade from SC where Cno=1 order by Grade desc

--11 查询每个学生的学号、姓名、选修的课程名、成绩
select STUDENT.Sno,STUDENT.Sname,Cname,Grade from STUDENT,SC,COURSE where STUDENT.Sno=SC.Sno and COURSE.Cno = SC.Cno

--12 查询与‘李勇’在同一个系学习的学生学号、姓名
select Sno,Sname from STUDENT where Sdept in (select Sdept from STUDENT where Sname='李勇') and Sname<>'李勇'

--13 查询选修了3门以上课程的学生学号
select Sno from SC group by Sno having count(Sno)>3

--14 查询没有选修课程的学生的基本信息
select * from STUDENT where Sno not in (select Sno from SC)

--15 找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列
SELECT STUDENT.Sno,avg(Grade) avg_grade,Sdept
from SC,STUDENT
where SC.Sno=STUDENT.Sno
group by STUDENT.Sno,sdept
having avg(grade)>85
order by avg_grade;
select STUDENT.Sno,avg(grade) avg_grade,STUDENT.sdept
from SC,STUDENT
where SC.Sno=STUDENT.Sno
group by STUDENT.Sno,Sdept
having avg(grade)>=85
order by avg_grade;

--16 查询选修课程平均成绩在80分以上的学生学号
select Sno from SC group by Sno having avg(Grade)>80  

--17 查询数据结构课程的先行课的先行课课程名
select Cname from COURSE where Cno=(select Cpno from COURSE where Cno=(select Cpno from COURSE where Cname = '数据结构'))

--18 查询同时选修《数据库》和《数据结构》两门课程的学生姓名
select Sname from SC,STUDENT,COURSE where SC.Sno = STUDENT.Sno and COURSE.Cno = SC.Cno and Cname='数据结构'
and Sname in (select Sname from SC,STUDENT,COURSE where SC.Sno = STUDENT.Sno and COURSE.Cno = SC.Cno and Cname = '数据库')

--19 查询每个系的平均年龄，并建立视图
create view shitu1(Sdept,Gavg) as select Sdept,avg(Sage) from STUDENT group by Sdept

--20 建立课程的先修课程的视图
create view shitu2(Cname,Cpno) as select Cname,Cpno from COURSE

实验三：数据库更新和视图的基本操作

1 向 S 表中添加一个学生纪录，学号为 S7, 姓名为高大全，性别为男，年龄 为 20，系别为计算机 8

2 将所有学生的成绩加 5

3 将高大全同学所在系改为‘信息’

4 删除姓名为高大全同学的学生纪录

5 李晨选修了 1 号课程，把这个选修关系插入到基本表

6 把没有考试成绩的学生的成绩改为 30 分

7 把李晨 1 号课程的成绩改为 78；

8 把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生的成绩提高 5%；

9 把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉

10 建立一个名为 StudentSumView 的视图，包含计算机系学生所有学生学号 和总成绩

11 建立选修了课程‘程序设计’并且成绩大于 80 分的学生的姓名、成绩

12 将“CS”系全体学生的成绩置零；

13 删除 StudentSumView 视图

14 删除 S 所有记录

15 删除 S 表

16 删除 STUDENT 数据库

--1 向S表中添加一个学生纪录，学号为S7, 姓名为高大全，性别为男，年龄为20，系别为计算机
--insert into STUDENT values('S7','高大全','男',20,'CS')
--select * from STUDENT

--2 将所有学生的成绩加5
--update SC set Grade=Grade + 5
--select * from SC

--3 将高大全同学所在系改为‘信息’
--update STUDENT set Sdept = 'IS' where Sname ='高大全'
--select * from STUDENT

--4 删除姓名为高大全同学的学生纪录
--delete from STUDENT where Sname='高大全'
--select * from STUDENT

--5 李晨选修了1号课程，把这个选修关系插入到基本表
--insert into SC(Sno,Cno) values ((select Sno from STUDENT where Sname='刘晨'),1)
--select * from SC

--6 把没有考试成绩的学生的成绩改为30分
--update SC set Grade = 30 where Grade is NULL
--select * from SC

--7 把李晨1号课程的成绩改为78；
--update SC set Grade = 78 where  Cno = 1 and Sno=(select Sno from STUDENT where Sname='刘晨' )
--select * from SC

--8 把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生的成绩提高5%；
--update SC set Grade =Grade * 1.05 where Cno = 2 and Grade < (select avg (Grade)  from SC where Cno = 2)
--select * from SC

update SC set Grade = 90 where Sno = '201215121' and Cno = 2 

--9 把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉
--delete from SC where Cno = 2 and Grade < (select avg(Grade) from SC where Cno = 2) 
--select * from SC

--10 建立一个名为StudentSumView的视图，包含计算机系学生所有学生学号和总成绩
create view StudentSumView 
as
select SC.Sno   Sno,
	   sum(Grade)	Grade
from SC,STUDENT
where SC.Sno = STUDENT.Sno and STUDENT.Sdept = 'CS'
group by SC.Sno

--select * from StudentSumView

--11 建立选修了课程‘程序设计（数据库）’并且成绩大于80分的学生的姓名、成绩的视图
create view DB
as
select STUDENT.Sname Sname,
		Grade Grade
from STUDENT,SC,COURSE
where STUDENT.Sno = SC.Sno and COURSE.Cno = SC.Cno and COURSE.Cname = '数据库' and Grade > 80

--12  将“CS”系全体学生的成绩置零；
--update SC set Grade = 0 where Sno in (select Sno from STUDENT where Sdept = 'CS')
--select * from SC

--13 删除StudentSumView视图
--drop view StudentSumView

--14 删除S所有记录
--truncate table STUDENT
--select * from STUDENT

--15 删除S表
drop table STUDENT

--16 删除STUDENT数据库
USE master
GO
drop database student;