《数据库系统原理实验》上机考试题答案 原创

《数据库系统原理实验》上机考试题答案

-- 《数据库系统原理实验》上机考试题

-- 、模式结构的构建
-- 1.	使用命令方式创建一个学生课程数据库，数据库包含多个文件（不少于3个），至少指定一个日志文件。这些文件存放在两个文件组中；（10分）

CREATE DATABASE stu_2017_4
ON 
primary
( NAME = 'stu_dat1',
    FILENAME = 'c:\db_2017\stu_dat1.mdf',
    SIZE = 5,
    MAXSIZE = 50,
    FILEGROWTH = 10% ),
filegroup group1
( NAME = 'stu_dat2',
    FILENAME = 'c:\db_2017\stu_dat2.ndf',
    SIZE = 5,
    MAXSIZE = 50,
    FILEGROWTH = 10% ),
( NAME = 'stu_dat3',
    FILENAME = 'c:\db_2017\stu_dat3.mdf',
    SIZE = 5,
    MAXSIZE = 50,
    FILEGROWTH = 10% )
LOG ON
( NAME = Stulog,
    FILENAME = 'c:\db_2017\stu_log.ldf',
    SIZE = 5MB,
    FILEGROWTH = 3) 
GO


-- 2.	创建三个表，分别是学生表S，课程表C，学生选课表SC。这些表的参数如下：（10分）

create table S (		--学生表
	sno char(11)  not null,			--学号
 sname varchar(10)  not null,	--学生姓名
	ssex  char(2),		    	--性别
	sage  tinyint,		    	--年龄
	sdept  char(10))			--系名


create table C (		--课程表
	cno  char(5)   not null,	   --课程号
	cname varchar(20)  not null,	   --课程名
	ccredit  tinyint )				--学分


create table SC (		--选课表
	sno char(11) not null,	--学号
	cno char(5) not null,	--课程号
	grade  tinyint	)	    --成绩
	
	
-- 3．修改表的结构（15分）
-- （1）为课程表C增加授课教师属性列cteacher ，数据类型为char（10）；（3分）
alter table c add cteacher char(10)

-- （2）为学生表S、课程表C和学生选课表SC定义主键   （6分）
alter table s  add constraint s_pk  primary key(sno)
alter table c  add constraint c_pk  primary key(cno)
alter table sc add constraint sc_pk primary key(sno,cno)

--（3）使用约束名子句为学生选课表SC设置约束，当课程表的课程号变化时，学生选课表SC的课程号随之变化（3分）
alter  table  sc  add  constraint   sc_fk_cno  
foreign key(cno)  references c(cno)  on update cascade

--（4）使用约束名子句为S表的的ssex设置检查约束，使得学生性别只能取“男”或	“女”两种属性值；（3分）
alter table s  add  constraint  s_check_cons  check(ssex in ('男','女'))
go

-- 二、数据准备：
-- 见数据源或附加数据库stu_j 在文档最后面

-- 三、数据查询（35分）
-- 1.查询每个学生的姓名和出生年份，为出生年份指定别名s_year; （2分）
select sname,year(getdate())-sage as s_year
from s
 
-- 2.	查询选修了“00002”课程的学生姓名和院系名称；（3分）
select sname,sdept
from s inner join sc on s.sno=sc.sno
		where sc.cno='00002'
 
-- 3.	使用嵌套查询查询选修了“数据库系统原理”的学生姓名；（5分）
select sname
from s
where sno in (
	select sno 
	from sc
	where cno in (
		select cno
		from c
		where cname='数据库系统原理'))
 
-- 4.	查询选修了全部课程的学生姓名和院系名称；（5分）
select sname,sdept
from s
where not exists (
		select *
		from c
		where not exists (
			select *
			from sc
				where sc.sno=s.sno  and sc.cno=c.cno))
 
-- 5.	使用over子句和开窗函数，查询每个学生的姓名、课程名称、成绩，对每门课程的成绩由高到低排序，并且列出每个学生在每门课程中名次；（10分）
select s.sname,c.cname,grade,
    rank() over(partition by sc.cno order by grade desc) 
                   as cno_grade_num,
    dense_rank()  over(partition by sc.cno order by grade desc) 
                  as cno_grade_dense_num
 from s inner join sc on sc.sno=s.sno  inner join c on sc.cno=c.cno 
     order by sc.cno
 
-- 6.	使用透视技术按如下方式输出每个学生的选课情况；（10分）
 
Select   sno as [学号],[计算机导论],[高级语言程序设计],[离算数学],
             [数据结构],[操作系统],[数据库系统原理]
from (select sno,cname,grade from sc inner join c on 
         sc.cno=c.cno)  as d  
pivot(max(grade) for cname in([计算机导论],[高级语言程序设计],[离算数学],[数据结构],[操作系统],[数据库系统原理]) ) as p

-- 四、创建一个用户定义的标量函数，实现计算全体学生某门功课平均成绩的功能，并调用此函数。（10分）
create function average(@cnum char(5)) returns int
	as
	begin
		declare @aver int
		select @aver=
		( 
			select  avg(grade) 
		    from  sc
		    where  cno=@cnum 
			group by cno
		)
		return @aver
	end
 
-- 计算课程号为00001的计算机导论的平均成绩
 

-- 五、创建一个带参数的存储过程，实现查询每个指定学生成绩的功能。（5分）
create procedure s_info_参数 @sname char(20)
    as
        select s.sno,sname,cno,grade
        from  s inner  join sc  on s.sno=sc.sno
        where sname=@sname
 
 

-- 六、创建一个触发器，实现不可更新视图的数据更新。

-- 1.	数据准备
Create database stu_2019
Go
use stu_2019
go

create table s(
			sno char(5) not null,
			sname char(10),
			ssex char(2),
			sage int,
			sdept char(10),
			ssum_credit tinyint,
primary key(sno))
go

create table c(
			cno char(5) not null  primary key,
			cname char(10),
			ccredit tinyint)
go

create table sc(
			sno char(5) not null,
			cno char(5) not null,
			grade tinyint,
primary key(sno,cno))
go
--(2)依次输入多个学生的记录
insert into s 
values 
	('95001','李莹','女',21,'计科',0),
	('95002','章张','男',22,'计科',0),
	('95003','陈华','女',22,'计科',0),
	('95004','吴华义','男',23,'计科',0),
	('95005','刘冠章','男',22,'计科',0),
	('95006','蒋婷','女',22,'网络',0),
	('95007','邱蔚六','男',21,'网络',0),
	('95008','王德柱','男',22,'网络',0),
	('95009','秦频','女',21,'网络',0),
	('95010','程少根','男',21,'网络',0)
--在C表中录入数据
insert into c
values
	('00001','计算机导论',2),
	('00002','高级语言',2),
	('00003','离算数学',3),
	('00004','数据结构',3),
	('00005','c#',2),
	('00006','面向对象',2),
	('00007','数据库原理',3),
	('00008','操作系统',3)
--在SC表中录入数据
insert into sc
	values
	('95001','00002',86),
	('95001','00003',92),
	('95001','00004',76),
	('95001','00005',86),
	('95002','00001',86),
	('95002','00002',80),
	('95002','00003',81),
	('95003','00001',88),
	('95003','00002',68),
	('95004','00001',96),
			('95005','00001',69)
-- 2.	首先创建视图：
create view stu_view
as
	select s.sno,sdept,cno,grade
	from s inner join sc  on s.sno=sc.sno
-- 3.	向视图中插入数据
Insert into  stu_view  values('98100','software','00001',90)
	这说明，该视图是不可更新视图，不能向该视图中直接插入数据。
-- 4.	创建触发器
CREATE TRIGGER InsteadTrig
	ON stu_view
	 INSTEAD OF INSERT
	AS
	BEGIN
		DECLARE @XH char(5), @XM char(10),
				@ZY char(10), @KCH char(5), @CJ tinyint
		SET @XM='佚名'
		SELECT @XH=sno, @ZY=sdept, @KCH=cno, @CJ=grade
			FROM inserted
		INSERT INTO s(sno, sname, sdept) 
			VALUES(@XH, @XM, @ZY)
		INSERT INTO sc VALUES(@XH, @KCH, @CJ)
	END
-- 5.	再次向视图中插入数据
Insert into  stu_view  values('98100','software','00001',90)

-- 说明该操作向学生表、学生选课表插入了数据，从而间接向该视图中插入了数据。
-- 1）	查询学生表得到：插入了学号为“98100”的学生
-- 2)查询学生选课表：插入了学号为“98100”的学生的选课记录
-- 3）查询视图：插入了该学生的记录
-- 由此可以得出：instead of视图实现了不可更新视图的更新