SQL数据库实验和参考答案

数据库实验和参考答案

上机实验七

1．声明一个字符串型的局部变量，并对其赋值：‘我的变量’，然后显示出此值。

declare @a char(10)

set @a='我的变量'

--select @a as 变量的值

print @a

2．编程实现如下功能：

1）声明两个整形的局部变量：@i1和@i2,对@i1赋初值：10，@i2的值为：@i1乘以5，再显示@i2

的结果值。

declare @i1 int,@i2 int

set @i1=10

set @i2=@i1*5

print @i2

2）用While语句实现5000减1，

减2，……一直减到50的计算，并显示最终的结果。

declare @sum int,@i int

set @sum=5000

set @i=1

while(@i<=50)

begin

set @i=@i+1

end

print @sum

3）输出100以内的素数。

declare @i smallint,@j

smallint,@k smallint

set @i=2

while(@i<=100)

begin

set @k=0

set @j=2

while(@j<@i)

begin

if(@i%@j=0)

begin

set @j=@I

end

set @j=@j+1

end

if @k=0

print @I

set @i=@i+1

end

4）将字符数在20以内的字符串变量C的值逆序输出。要求输出界面为：

declare @i varchar(20),@j int,@k varchar(20)

set @j=1

set @k=''

while @j<=len(@i)

begin

set @k=substring(@i,@j,1) set @j=@j+1

end

print '字符串C的值:'+@i

print 'C的逆序字符串:'+@k

5）从SC表中查询所有学生的选课成

绩情况，分别统计各分数段人数，

并输出统计结果。

输出格式为：

缺考：人

<60：人

60-69：人

70-79：人

80-89：人

90-100：人

select 成绩=

case

when 成绩is null then '缺考:' when 成绩<60 then '<60'

when 成绩>=60 and 成绩<70 then '60-69:'

when 成绩between 70 and 79 then '70-79:'

when 成绩between 80 and 89 then '80-89:'

when 成绩between 90 and 100 then '90-100:'

end

into ss1

from sc

select 成绩分数段,count(*) 人数from ss1

group by 成绩

上机实验八

3．创建满足下述条件的存储过程：1）创建查询每个学生的修课学分

的存储过程，要求列出学生学号

及总学分。

create procedure t1_1

as

select 学号,sum(学分) 总学分

from sc ,course where sc.课程号=course.课程号group by 学号

go

exec t1_1

2）创建查询个学生的学号、姓名、课程号、课程名、课程学分的存

储过程，将学生所在的系作为输

入参数，执行此存储过程，并分

别指定一些不同的输入参数值。

create proc t1_2

@szx char(20)

as

select student.学号,姓名,course.课程号,课程名,学分

from student, sc ,course where sc.学号=student.学号

and course.课程号=sc.课程号and 所在系=@szx

exec t1_2 '计算机系'

exec t1_2 '信息系'

3）创建计算1+2+3+……一直加到

指定值的结果的存储过程，要

求：计算的终止值由输入参数决

定，计算结果由输出参数表示。

实验如下数据并记下结果：

1+2+3+ (150)

1+2+3+ (500)

create proc t1_3

@in1 int,@out int output

as

declare @sum int

declare @n int

set @n=1

beg:

if(@n<=@in1)

begin

set @sum=@sum+@n

set @n=@n+1

goto beg

end

set @out=@sum

declare @i1 int

exec t1_3 150,@i1 out

print @i1

或：

create proc t1_3

@in1 int,@in2 int ,@out int output

as

declare @sum int

declare @n int

set @n=@in1

set @sum=0

while(@n<=@in2)

begin

n

set @n=@n+1

end

set @out=@sum

go

declare @i1 int,@i2

int,@i3 int

set @i1=1

set @i2=500

exec t1_3 @i1,@i2 ,@i3 out

--print @i3

select @i2 自然数n,@i3 自然数之前n项和

4)创建“输出任意区间内的素数

并统计区间内素数的个数”的存

储过程,区间的上、下界由输入参

数决定。

create proc ssgc

@in1 int,@in2 int

as

DECLARE @S SMALLINT,@I SMALLINT,@j SMALLINT,@n smallint

SET @I=@in1

WHILE (@I<=@in2) BEGIN

SET @S=0

set @j=2

while (@j<@i)

begin

if @i%@j=0

BEGIN

set @s=1

set @j=@i

END

end

if @s=0 and @i!=1

begin

print @i

set @n=@n+1

end

SET @I=@I+1

END

print '素数的个数为：'+str(@n)

exec ssgc 20,30

4．在student表中定义一个更新和插入触发器，在此触发器中保证成绩在0~100范围内。

create trigger rig1

on sc

as

if exists(select * from sc where 成绩>100 or 成绩<0)

rollback

else

begin

select * from sc

end

--insert into sc(学号,课程号,成绩) values('0412101','c04',90)

update sc set 成绩=成绩+10

--update sc set 成绩=成绩+12

--insert into sc(学号,课程号,成绩) values('0531102','c01',110) 5．删除sc 表中对课程号的外码引用约束，然后在此表上定义一个保证课程号在course表的课程号范围内的插入触发器。

--alter table sc

--drop constraint fk_sc_course

create trigger rig4

on sc

for insert

as

if exists(select 课程号from inserted

--where 课程号not like'c0[123456]')

where 课程号not in (select 课程号 from course))

rollback

--insert into sc(学号,课程号,成绩) values('0531102','c06',70)

--insert into sc(学号,课

程号,成绩) values('0531102','c9',70)

--update sc set 课程号='c09' where 学号='0531102'

上机实验三

1．在“学生管理数据库”中用查询分析器创建p82页的课程数据表（表名为kcb），并输入数据内容。2．用查询分析器在student表中增加一个字段：“电话号码”，字符型，并设置默认值为“1111111”。3．用查询分析器删除kcb表的主键4．用企业管理器或查询分析器修改course表，设置约束：学时>=学分*16。

5．用查询分析器修改学生管理数据库中的students_dat文件增容方式

为一次增加3MB.

6．将“学生管理数据库”更名为“学生管理”。

7．将“学生管理”数据库备份到外存中。

8．将“学生管理”数据库的数据导出为excel文件，并保存在外存中。9．为course表中的课程名建立唯一性索引。

10．将“学生管理”数据库从系统中分离出来。

11. 将“学生管理”数据库附加到企业管理器中。

上机实验四

1．分别查询学生表和学生修课表中的全部数据。

2．查询计算机系的学生的姓名、年龄。

3．查询选修了c01号课程的学生的学号和成绩。

4．查询成绩在70到80分之间的学生的学号、课程号和成绩。

5．查询计算机系年龄在18到20之间且性别为“男”的学生的姓名、年龄。

6．查询0412101号学生的修课情况。7．查询c01号课程成绩最高的分数。8．查询学生都选修了哪些课程，要求列出课程号。

9．查询Northwind数据库中orders 表的OrderlD、CustomerID和OrderDate，并将最新的定购日期(OrderDate)列在前边。

10．查询Northwind数据库中orders 表的ShipCountry列以B，C，D，F 开始且第三个字符为“a”的

OrderlD、CustomerID和ShipCountry的信息。

11．查询Northwind数据库中orders 表的ShipCountry列不以A，B，C，D，E，F开始且最后一个字母是“a”的OrderlD、CustomerlD和ShipCountry的信息。

12．查询学生数据库中学生的最大年龄和最小年龄。

13．查询修了c02号课程的所有学生的平均成绩、最高成绩和最低成绩。14．统计每个系的学生人数。

15．统计每门课程的修课人数和最高分

16．统计每个学生的选课门数，并按选课门数的递增顺序显示结果。17. 查询哪些课程尚未考试。

18．列出计算机系年龄最大者及次大

者的姓名。

上机实验六

32．查询哪些课程没有人选，要求列出课程号和课程名。

33. 查询有考试成绩的所有学生的姓名、修课名称及考试成绩，要求将查询结果放在一张新的永久表(假设新表名为new sc)中，新表的列名分别为：Student_Name，Course_ Name，Grade。

34．分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩，并要求将这两个查询结果合并成一个结果集，并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。

35．用子查询实现如下查询：．(1)查询选修了c01号课程的学生的