Catalog
数据库实验七
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
--使用数据库student,完成以下操作,并写出相应的代码
--1.用函数实现:求某个专业选修了某门课程的学生人数,并调用函数求出计算机系“数据库”课程的选课人数。
create function dept_num(@dept varchar(8),@cname varchar(20))
returns int
as
begin
declare @num int
select @num = count(*) from sc,course,student
where sc.cno = course.cno and sc.sno = student.sno
and course.cname = @cname and student.sdept = @dept
return(@num)
end

select dbo.dept_num('CS','数据库') as 人数

--2.用内嵌表值函数实现:查询某个专业所有学生所选的每门课的平均成绩;调用该函数求出计算机系的所有课程的平均成绩。
create function avg_all(@dept varchar(8))
returns table
as
return(select course.cname,avg(sc.grade) as 平均成绩
from course,sc,student
where sc.cno = course.cno and sc.sno = student.sno
and student.sdept = @dept
group by course.cname)

select *
from dbo.avg_all('CS')

--3.创建多语句表值函数,通过学号作为实参调用该函数,可显示该学生的姓名以及各门课的成绩和学分,调用该函数求出“200515002”的各门课成绩和学分。
create function stu_show(@sno char(9))
returns @stu_table table(sname nchar(6),cname varchar(20),grade int,credit int)
as
begin
insert @stu_table
select sname,cname,grade,credit
from student,sc,course
where sc.cno = course.cno and sc.sno = student.sno and sc.sno = @sno
return
end

select *
from dbo.stu_show('200515002')

--4.编写一个存储过程,统计某门课程的优秀(90-100)人数、良好(80-89)人数、中等(70-79)人数、及格(60-69)人数和及格率,其输入参数是课程号,输出的是各级别人数及及格率,及格率的形式是90.25%,执行存储过程,在消息区显示1号课程的统计信息。
create procedure clevel @cno char(4)
as
declare @great int,@good int,@fair int,@pass int,@all int
select @great = count(*) from sc where sc.cno = @cno and sc.grade >= 90
select @good = count(*) from sc where sc.cno = @cno and sc.grade >= 80 and sc.grade <= 89
select @fair = count(*) from sc where sc.cno = @cno and sc.grade >= 70 and sc.grade <= 79
select @pass = count(*) from sc where sc.cno = @cno and sc.grade >= 60 and sc.grade <= 69
select @all = count(*) from sc where sc.cno = @cno
select @great as 优秀人数,@good as 良好人数,@fair as 中等人数,@pass as 及格人数,convert(varchar,(@great+@good+@fair+@pass)*100/@all)+'%' as 及格率

exec clevel '1'

--5.创建一个带有输入参数的存储过程,该存储过程根据传入的学生名字,查询其选修的课程名和成绩,执行存储过程,在消息区显示赵箐箐的相关信息。
create procedure pc_g @sname nchar(6)
as
select cname,grade
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and student.sname = @sname

exec pc_g '赵菁菁'

--6.以基本表 course为基础,完成如下操作
--生成显示如下报表形式的游标:报表首先列出学生的学号和姓名,然后在此学生下,列出其所选的全部课程的课程号、课程名和学分;依此类推,直到列出全部学生。
declare @sno char(9),@sname nchar(6)
declare @cno char(4),@cname varchar(20),@credit int
declare stu_cursor insensitive cursor for
select student.sno,student.sname from student
open stu_cursor
fetch next from stu_cursor into @sno,@sname
while @@fetch_status = 0
begin
print @sno +' '+@sname
print'----------------------------'
print 'cno cname credit'
declare c_cursor cursor for
select sc.cno,course.cname,course.credit
from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno
and student.sno=@sno
open c_cursor
fetch next from c_cursor into @cno,@cname,@credit
while @@fetch_status = 0
begin
print @cno+' '+@cname+' '+cast(@credit as char(4))
fetch next from c_cursor into @cno,@cname,@credit
end
print '=============================================='
close c_cursor
deallocate c_cursor
fetch next from stu_cursor into @sno,@sname
end
close stu_cursor
deallocate stu_cursor

--7.请设计一个存储过程实现下列功能:判断某个专业某门课程成绩排名为n的学生的成绩是否低于该门课程的平均分,如果低于平均分,则将其成绩改为平均分,否则输出学号、姓名、班号、课程号、课程名、成绩。(提示:可以在存储过程内部使用游标)。
create procedure avg_p @dept char(4),@cno char(10),@n int
as
declare @sno char(10),@sgrade int,@avg_sgrade int,@sname char(6)

declare cj cursor scroll for
select sc.Sno,Grade,Sname
from sc,student
where sc.Cno=@cno and student.Sdept=@dept
order by Grade desc

select @avg_sgrade=(select avg(Grade) from sc where sc.Cno=@cno)

open cj
fetch absolute @n from cj into @sno,@sgrade,@sname
if(@sgrade>@avg_sgrade)
print @sno+' '+@sname+' '+@cno+' '+convert(char(6),@sgrade)
else
begin
update sc
set sc.Grade=@avg_sgrade
where sc.Sno=@sno and sc.Cno=@cno
end
close cj
deallocate cj

exec avg_p 'CS','1',3

--8.对student数据库设计存储过程,设计程序实现更新某个专业某门课成绩排名为第2的学生的成绩改为90分。(提示可以使用存储过程内部使用游标)
create procedure change_p @dept varchar(8),@cno char(10)
as
declare change_c scroll cursor for
select sc.sno,sname
from sc,student
where student.sdept=@dept and sc.cno = @cno
order by grade desc

open change_c
fetch absolute 2 from change_c
update sc
set grade = 90

close change_c
deallocate change_c

exec change_p 'CS','1'

--9.设计存储过程实现如果某个学生某门课程的成绩>=60,那么就把这门课程的学分加到该学生的总学分(student表的total项)上
create procedure add_p @sno char(9)
as
declare @grade int,@credit int
declare add_c scroll cursor for
select sc.grade,course.credit
from sc,course
where sc.sno = @sno and sc.cno = course.cno

open add_c
fetch next from add_c into @grade,@credit
if(@grade >= 60)
begin
update student
set total = total + @credit
end
close add_c
deallocate add_c

exec add_p '200515001'

--10.现有图书管理数据库, 其中包含如下几个表:
--读者表:reader(学号,姓名,性别,余额)
--借书表:lend(学号,书号,借书日期,应还日期,是否续借)
--欠款表:debt(学号,日期,欠款金额)
--还书表:return(学号,书号,还书日期)
--请设计一个存储过程实现续借或还书操作,具体要求如下:
--只有没有超期的书才可以续借(借书和续借时间都为30天),并修改应还日期,否则只能还书;
--还书时删除借书表内的借阅记录,并向还书表中插入一条还书记录,注意还书日期为当前日期,
--并且对超期图书,按照超期的天数计算出罚款金额(每天每本书罚款0.1元),并将罚款信息插入到欠款表中,
--同时将罚款从读者表的余额里扣除。
create procedure book as @sno char(20),@bno char(30)
as
declare @days int,@continue char(10)
select @days = datediff(dd,借书日期,getdate()) from lend
select @continue = 是否续借 from lend

if(@days<=30 and @continue = '否') --未到期续借
begin
update lend
set 应还日期 = dateadd(day,30,应还日期)
set 是否续借 = '是'
end
else if(@continue = '是' and datediff(dd,dateadd(day,@days,借书日期),应还日期)>=0) --续借后还书
begin
delete from lend
where sno = @sno and bno = @bno
insert into return
values(@sno,@bno,getdate())
end
else if(datediff(day,getdate(),应还日期)<0) --逾期后还书
begin
declare @debt money
set @debt=datediff(day,应还日期,getdate())*0.1
insert into debt
values(@sno,getdate(),@debt)
update reader
set 余额=余额-@debt
end
Author: Christopher Shen
Link: https://www.pasxsenger.com/2019/11/14/数据库实验七/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
Donate
  • 微信
  • 支付寶