数据库实验集合

datastructure

0 建表代码

create database Students
on
primary(name=Students,
   filename='D:\test\test.mdf',
size=4mb,
maxsize=10mb,
filegrowth=2mb
  )
log on
(name=Studentslog,
filename='D:\test\testlog.ldf',
size=1mb,
maxsize=5mb,
filegrowth=1mb
)
use Students;
create table Student
(
sno char(8) not null primary key,
Sname char(15) null,
Ssex char(4) null,
Sage int null,
Sdept char(10) null
)
create table Course
(
Cno char(6) not null,
Cname char(10),
Cpno char(6),
Ccredit int null,
primary key(cno),
)
go 
create table SC
(
Sno char(8) not null foreign key references Student(sno),
Cno char(6) not null foreign key references course(cno),
primary key(Sno,Cno),
Grade int  null
)
insert into Student values('19130201','丁鹏','男',20,'Software')
insert into Student values('19130202','王韵婷','女',20,'Software')
insert into Student values('19130203','尹嘉琪','男',18,'Software')
insert into Student values('19130204','卢冬冬','男',20,'Software')
insert into Student values('19130205','史逸凡','男',19,'Software')
insert into Course values('1','数据库',5,4)
insert into Course values('2','数学',null,2)
insert into Course values('3','信息系统',1,4)
insert into Course values('4','操作系统',6,3)
insert into Course values('5','数据结构',7,5)
insert into Course values('6','数据处理',null,2)
insert into Course values('7','PASCAL语言',6,4)
insert into SC values('19130201',1,99)
insert into SC values('19130202',5,95)
insert into SC values('19130203',3,100)
insert into SC values('19130205',1,93)
insert into SC values('19130204',5,92)
alter table course add  foreign key(cpno) references course(cno)
select * from Course;
select * from SC;
select * from Student;

数据库10.8实验

1 给表student列Sno增加检查长度为8位的约束并测试。

alter table Student add constraint ck_tudent_ssex check (len(Sno)=8) 

2 给表student列ssex的输入限定为男、女两个值并测试。

use Students;
create table Student
(
Sno char(8) not null primary key,
Sname char(8) null,
Ssex char(4) null check(ssex='男' or ssex='女'),
Sage int null,
Sdept char(10) null
)

3 给表sc列grade的输入限定为0到100并测试。

use Students;
create table SC
(
Sno char(8) not null,
Cno int null,
Grade int  null check(Grade >=0 and Grade<=100),
//或采用constraint ck_sc_grade check(Grade >=0 and Grade<=100)
)

4 给表sc的列增加外键约束并测试。

alter table SC add constraint pk_sc_fk foreign key(Sno) references Student(Sno);

5 给表student增加列idcard表示身份证号并限定输入长度为18位，且最后一位奇数表示男，偶数表示女，这个值必须与ssex一致，并请测试。

alter table Student add idcard char(18) null;
alter table Student add  constraint pk_id_ck check(len(idcard)=18 and((CAST(right(idcard,1) AS INT )%2=1 and Ssex ='男') or  (CAST(right(idcard,1) AS INT )%2=0 and Ssex ='女')));

数据库10.15实验

1.查询姓名为“丁鹏”的学号、性别、年龄、院系；

select sno,Ssex,Sage,Sdept from Student where Sname='丁鹏';

2.查询选修课程名为“数据库”课程的同学的学号、姓名、院系；

select sno,Sname,Sdept from Student where sno in 
(select Sno from SC where Cno in 
(select Cno from Course where Cname = '数据库'));

3.查询没被任何同学选的课程号及课程名；

select Cno,Cname from Course where Cno not in
(select distinct Cno from SC);

4查询与“丁鹏”在同一院系的同学的学号、姓名、性别、院系；

select sno,Sname,Ssex,Sdept from Student where Sdept in(
select Sdept from Student where Sname='丁鹏');

数据库10.22实验

1. 查询所有被选的课程号及课程名，并将查询结果的列名用中文表示

select Cno as '课程编号',Cname as '课程名称' from Course where Cno in
(select distinct Cno from SC);

1. 查询年龄在19到21岁之间的同学的学号、姓名、性别，并按学号降序显示

select sno,sname,ssex from Student where Sage>=19 and Sage<=21 order by sno desc

1. 查询既不是英语系、数学系，也不是计算机科学系的学生的学号、姓名和性别

select sno,sname,ssex from Student where Sdept!='English' and 
Sdept!='Math' and Sdept!='Computer'

1. 查询所有姓王的学生的学号、姓名和性别，并按学号升序显示

select sno,sname,ssex from Student where sname like '王%' order by sno

1. 查询所有不姓刘的学生姓名

select sname from Student where sname not like '刘%'

1. 查询名字中第2个字为"敏"字的学生的学号和姓名

select sno,sname from Student where sname like '_敏%'

1. 查所有有成绩的学生学号和课程号

select sno,cno from SC where Grade is not null

数据库10.29实验

1. 创建登录名log1,口令为123456，缺省数据库为 student

exec sp_addlogin 'log1','123456','Students'

1. 创建用户wangyong，登录名为log1

exec sp_grantdbaccess 'log1','wangyong'

1. 创建角色student_role

EXEC sp_addrole 'student_role'

1. 为角色studen_role增加成员wangyong

EXEC sp_addrolemember 'student_role', 'wangyong'

1. 授予角色studen_role在表student上的查询权限、在sc表上的插入权限

GRANT  select on Student  TO student_role 
GRANT  insert on SC TO student_role 

1. 撤销角色studen_role在表student上的查询权限

REVOKE SELECT   ON  Student  FROM  student_role

1. 删除角色、用户、登录名

exec sp_droprolemember 'student_role','wangyong'
exec sp_droprole 'student_role'
exec sp_droplogin 'log1'

1. 创建视图v_1查询计算机系的所有学生学号、姓名、性别、年龄

create view CS_Student
    AS 
    select  sno as '学号',Sname as '姓名',Ssex as '性别',Sage as '年龄'
    FROM   Student
    WHERE  Sdept='Software'

数据库11.05实验

1. 查询选修了课程的学生人数

select count(distinct Sno) from SC

1. 计算1号课程的学生平均成绩

select AVG(Grade) as 平均成绩 from SC where Cno='1'

1. 查询选修1号课程的学生最高分数、最低分数

select MAX(Grade)as 最高成绩,MIN(Grade)as 最低成绩 from SC where Cno='1'

1. 列出每个课程号及相应的选课人数

select Cno ,COUNT(Sno) as 选课人数 from SC group by Cno

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

select Sno from SC group by Sno having COUNT(*)>3

1. 列出每门课程的最高分数、最低分数

select cno,MAX(Grade)as 最高成绩,MIN(Grade)as 最低成绩 from SC group by Cno

数据库11.19实验

1.查询每一门课的间接先修课（即先修课的先修课）

select a.cno as '课程号' , a.cname as '课程名', b.cpno as '间接先修课' 
from course a,course b 
where b.cno = a.cpno

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

select a.sno as '学号',a.sname as '姓名',cno as '选修科目',grade as '成绩'
from Student a,sc b
where a.sno=b.sno

1. 查询与“刘晨”在同一个系学习的学生。

select a.* 
from Student a,Student b
where b.sname='丁鹏'
and a.sdept=b.sdept

1. 查询选修了课程名为“信息系统”的学生学号和姓名

select a.sno,a.sname 
from Student a,course b,sc c
where b.cname='数据库'
and c.cno=b.cno
and a.sno=c.sno

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

select b.sno,a.cno ,a.cname from 
course a,
sc b,
(select sno,AVG(grade) as 'avge' from sc group by sno)c
where a.cno=b.cno
and b.sno=c.sno
and b.grade >= c.avge

数据库11.26实验

1. 列出每个系的男生人数、女生人数

select Sdept,ssex,count(ssex)as '人数' from Student group by Sdept,ssex

1. 查询选修了课程名为“信息系统”的学生学号和姓名

select b.cname,a.sname,a.sno
from Student a,course b,sc c
where a.sno=c.sno
and b.cno=c.cno
and b.cname='数据库'

1. 删除“王兰”所选的全部课程；

delete from sc
where sno in
(select sno from Student where sname='丁鹏')

1. 将“计算机”系的学生成绩全部清零；

update sc 
set grade = null
where sno in
(select sno from student where sdept ='software')

1. 查询没有选修1号课程的学生姓名。

select sname  from Student 
where sno not in
(select sno from sc where cno =1)

1. 查询选修了课程1或者选修了课程2的学生姓名。

select sname from Student
where sno in
(select sno from sc where cno ='1')
union
select sname from Student
where sno in
(select sno from sc where cno ='2')

1. 查询既选修了课程1又选修了课程2的学生姓名

select sname from Student
where sno in
(select sno from sc where cno ='1')
intersect
select sname from Student
where sno in
(select sno from sc where cno ='2')

1. 查询选修了课程1但没有又选修了课程2的学生姓名

select sname from Student
where sno in
(select sno from sc where cno ='1')
except
select sname from Student
where sno in
(select sno from sc where cno ='2')

数据库12.03实验

1. 创建一个视图显示学号、姓名、数据结构和数据库原理课程的成绩

create view Is_S1(sno,sname,grade1,grade2)
as
select a.sno,a.sname,b.Grade,c.Grade
from Student a
left outer join
(select sno,grade from sc,course where sc.cno=cource.cno and course.cname='数据结构') b on b.sno=a.sno
left outer join
(select sno,grade from sc,course where sc.cno=cource.cno and course.cname='数据库') c on c.sno=a.sno

1. 查询选修了全部课程的同学的学号、姓名

select a.sname,a.sno
from Student a
where not exists
(select *
 from Course
 where not exists
  (select *
   from SC
   where Sno= a.Sno
   and Cno= Course.Cno
   )
)

1. 查询平均成绩在60分以上的同学的学号、姓名

select a.sno, b.sname,a.平均成绩
from Student b,
(
select sno,AVG(grade) as '平均成绩'
from sc 
group by sno 
having AVG(grade)>60
) a 
where a.sno = b.sno

1. 查询平均成绩在全系平均成绩之上的同学的学号、姓名

go 
create view S_G(sno,sname,Gavg)
as
select student.sno,sname,AVG(grade)
from sc,student
where student.sno=sc.sno
group by student.sno,student.sname
go
select *
from S_G
where Gavg>(select AVG(grade) from sc)

数据库12.10实验

1. 列出每门课程成绩都在90分以上的学生学号、姓名

//拿出每条记录，若存在一门课小于90分，则不要
select sno,sname
from Student a
where  not exists
(
select grade from sc
where a.sno=sc.sno
and sc.grade<=90
)

1. 查询没有选修数据结构课程的学生学号与姓名

//拿出每一条记录，对比是否选择了数据结构
select a.sno,a.sname
from Student a
where not exists
(
select a.* from course b,sc c
where a.sno=c.sno
and b.cname='数据结构'
and c.cno=b.cno
)

1. 查询既没有选修数据结构又没有选修数据库课程的学生学号与姓名

//利用交集，结合上题
select a.sno,a.sname
from Student a
where not exists
(
select a.* from course b,sc c
where a.sno=c.sno
and b.cname='数据结构'
and c.cno=b.cno
)
intersect
select a.sno,a.sname
from Student a
where not exists
(
select a.* from course b,sc c
where a.sno=c.sno
and b.cname='数据库'
and c.cno=b.cno
)

1. 将所有选择“数据结构”课程的“计算机系”的学生成绩置为0分

//结合第二题
update sc 
set grade = 98
where sno not in
(
select a.sno from student a ,course b,sc c
where a.sno=c.sno
and b.cname='数据结构'
and c.cno=b.cno
and a.sdept='software'
)

数据库12.17实验

0 上课示例

create procedure sp_1(@sdept char(4))
as
begin
    select a.sno,a.sname,b.grade 
    from student a,sc b
    where sdept =  @sdept
    and a.sno=b.sno
end
//调用
execute sp_1 'software'

create proc sp_2(@cno1 char(4),@avge int output)
as
begin
    select @avge = avg(grade) 
    from sc 
    where cno=@cno1
end

declare @avge1 int
declare @cno2 char(4)
set @cno2='001'
select  @cno2='001'
exec sp_2 @cno2,@avge1 output
select  @avge1

if @x1>1 and @x1<10
begin
end
else
begin
end

（1）编写一个存储过程，可以查询指定系的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。调用该存储过程，测试执行结果。

create procedure sp_2(@sdept char(20))
as
begin
select a.sno,a.sname,a.sdept,b.cname,c.grade
from Student a,course b,sc c
where a.sno=c.sno
and b.cno=c.cno
and a.sdept=@sdept
end
execute sp_2 'software'

（2）编写一个存储过程，返回指定课程的平均分。调用该存储过程，测试执行结果。

create proc sp_3(@cno1 char(4),@avge int output)
as
begin
    select @avge = avg(grade) 
    from sc 
    where cno=@cno1
end
declare @avge1 int
declare @cno2 char(4)
set  @cno2='3'
exec sp_3 @cno2,@avge1 output
select  @avge1

（3）编写一个存储过程可以查询指定系指定成绩等级的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。调用该存储过程，测试执行结果。（成绩等级为优、良、中、及格、不及格，其中成绩在90分到100分之间为‘优’，在80分到89分之间为‘良’，在70分到79分之间为‘中’，在60分到69分之间为‘及格’，在0分到59分之间为‘不及格’。）

create  procedure sp_6(@sdept varchar(20),@GradeLevel varchar(6))
as
Declare @SQLText varchar(200),@GradeStr varchar(30)
Set @SQLText=
'Select S.sno, S.sname, S.Sdept, C.Cname, SC.grade 
From Student S 
Left Join SC 
on S.sno=SC.sno 
Left Join Course C 
on SC.Cno=C.cno'
Set @GradeStr= Case 
    When @GradeLevel='优' then 'between 90 And 100'
    When @GradeLevel='良' then 'between 80 And 89'
    When @GradeLevel='中' then 'between 70 And 79'
    When @GradeLevel='及格' then 'between 60 And 69'
    When @GradeLevel='不及格' then 'between 0 And 59'
    When @GradeLevel IS NULL then 'IS NULL'
    Else  'LevelError' 
        end
IF @GradeStr='LevelError' 
      print '错误：输入的成绩等级不符合要求！'
Else
       Execute(@SQLText+' where Sdept='''+@sdept +''' And Grade '+@GradeStr)
Execute sp_6  'software','优'

数据库12.24实验

实验十五：T-SQL(13)
一个简化的图书馆信息管理系统，系统需求如下：
1.图书馆有若干管理员librarian，各自有员工号empid、姓名name、身份证号idno等属性。
2.图书馆有若干种图书booktype，每种图书有ISBN、名称title、出版社publisher、作者writers、价格price等属性，每种图书有唯一的ISBN号，同种图书可购入多本。
3.每一本图书book有唯一标记bookid和种类booktype。
4.读者reader在办理借书证后方可借阅，一个读者有唯一的借书证号cardno，还有姓名name、身份证号idno、住址address、注销标记logoff等。每个读者最多可借20本书，读者在注销前，须归还所有已借图书或报失。
5.需处理以下基本业务：
①借书：在某时刻某读者通过某管理员借阅某一本书。
②还书：在某时刻通过某管理员归还某一本书，读者可以在借阅历史表中查阅自己以前所借的书。
③报失：在某时刻某读者向某管理员报失某一本书；报失之后该书不能再借；每一次还书和报失记录都须对应某一次借书记录，且可由不同管理员处理。
用E/R图建立该系统的概念模型如下：

请按要求完成如下工作：
1. 参考以上E-R图，设计关系模式，并确定各关系模式的属性应满足的数据完整性约束，然后定义表的参照完整性约束
2. 根据借还书流程设计相应的触发器.

0建库

create database LIS
on
primary(name=LIS,
   filename='D:\test\test1.mdf',
size=4mb,
maxsize=10mb,
filegrowth=2mb
  )
log on
(name=LISlog,
filename='D:\test\testlog1.ldf',
size=1mb,
maxsize=5mb,
filegrowth=1mb
)
use LIS;

1 librarian表

--1.图书馆有若干管理员librarian，各自有员工号empid、姓名name、身份证号idno等属性。
create table librarian
(
Lempid char(12) not null primary key,
Lname char(15) null,
Lidno char(18) null
)

2 booktype表

--2.图书馆有若干种图书booktype，每种图书有ISBN、名称title、出版社publisher、
--作者writers、价格price等属性，每种图书有唯一的ISBN号，同种图书可购入多本。
create table booktype
(
ISBN char(20) not null primary key,
title char(15),
publisher char(15),
writers char(15),
price int
)

3 book表

--3.每一本图书book有唯一标记bookid和种类booktype。
create table book
(
bookid char(8) not null primary key,
ISBN char(20) foreign key references booktype(ISBN),
)

4 reader表

--4.读者reader在办理借书证后方可借阅，一个读者有唯一的借书证号cardno，
--还有姓名name、身份证号idno、住址address、注销标记logoff等。
--每个读者最多可借20本书，读者在注销前，须归还所有已借图书或报失。
create table reader
(
cardno char(20) not null primary key,
name char(15) null,
idno char(18) null,
address char(18) null,
logoff char(1) not null,
)

5 Record表

--5.需处理以下基本业务：
--①借书：在某时刻某读者通过某管理员借阅某一本书。
--②还书：在某时刻通过某管理员归还某一本书，读者可以在借阅历史表中查阅自己以前所借的书。
--③报失：在某时刻某读者向某管理员报失某一本书；报失之后该书不能再借；
--每一次还书和报失记录都须对应某一次借书记录，且可由不同管理员处理。
--用E/R图建立该系统的概念模型如下：
create table Record
(
recid char(12) not null primary key,
brwLempid char(12) foreign key references librarian(Lempid),
cardno char(20) foreign key references reader(cardno),
bookid char(8)foreign key references book(bookid),
borrowdate DATETIME,
status char(8) null,
enddate DATETIME,
endLempid char(12) foreign key references librarian(Lempid),
)

6 根据借还书流程设计相应的触发器

CREATE trigger borrow_record on book_record after insert
  as begin
    declare @bookid1 char(10)
    declare @empid1 int
    declare @borrow_time char(10)
   select @bookid1 = bookid ,@empid1 = empid ,@borrow_time = borrow_time
    from inserted
     insert into borrow_record values(@bookid1,@empid1,@borrow_time) 
  end
CREATE trigger return_record on book_record after insert
  as begin
    declare @bookid1 char(10)
    declare @empid1 int
    declare @return_time1 char(10)
    declare @return_flag1 char(4)
   select @bookid1 = bookid ,@empid1 = empid ,@return_time1 = return_time,@return_flag1 = return_flag
    from inserted
     insert into return_record values(@bookid1,@empid1,@return_time1,@return_flag1) 
  end
CREATE trigger lost_record on book_record after insert
  as begin
    declare @bookid1 char(10)
    declare @empid1 int
    declare @lost_time1 char(10)
    declare @lost_flag1 char(4)
   select @bookid1 = bookid ,@empid1 = empid ,@lost_time1 = lost_time,@lost_flag1 = lost_flag
    from inserted
     insert into lost_record values(@bookid1,@empid1,@lost_time1,@lost_flag1) 
  end