家装e站广州:sql
来源:百度文库 编辑:中财网 时间:2024/04/29 11:25:03
实验六
四、实验作业
create database 学生信息
on primary
(name='学生信息_data',
filename='e:\个人目录\学生信息_data.mdf',
size=2mb,
maxsize=10mb,
filegrowth=1mb)
log on
(name='学生信息_log',
filename='e:\个人目录\学生信息_log.ldf',
size=1mb,
maxsize=5mb,
filegrowth=10%)
Use 学生信息
Go
Create table 课程注册
(课程编号 char(2) not null,
课程名称 char(10),
任课教师 char(8),
周学时 int null,
学分 int null)
Go
Use 学生信息
Go
Create table 学生
(学号 int not null,
姓名 char(10),
性别 char(2),
专业 char(20),
系别 char(20),
年级 char(2),
班别 char(2),
出生日期 datetime null,
地区来源 varchar (30),
变动情况 char(10),
政治面貌 char(8),
民族 char(8),
学分 int null)
Go
Use 学生信息
Go
Create table 学期成绩
(学号 int not null,
课程编号 char(2),
成绩 real null,
备注 varchar (50))
Go
1、使用“学生信息”数据库,创建存储过程JSXX_PROC,返回各任课教师姓名及其所代课程名称。
USE 学生信息
GO
CREATE PROCEDURE JSXX_PROC
AS
SELECT 课程名称,任课教师
FROM 课程注册
GROUP BY 课程名称,任课教师
GO
2、使用“学生信息”数据库创建存储过程XM_PROC。该存储过程的作用是:当任意输入一个学生的姓名时,将返回该学生的学号、课程名称和成绩。
USE 学生信息
GO
CREATE PROCEDURE XM_PROC
@xsxm char(8)
AS
SELECT 学号,课程名称,成绩
FROM 课程注册,学期成绩
WHERE 课程注册.课程编号=学期成绩.课程编号and 学期成绩.学号=@xsxm
GO
3、使用“学生信息”数据库,创建存储过程XBNL_PROC。可任意按系汇总各年龄段的学生人数及男、女生人数。
USE 学生信息
GO
CREATE PROCEDURE XBNL_PROC
@xbmc char(8) ,@birth datetime
AS
SELECT 系别,出生日期,性别,COUNT(*) AS 人数
FROM 学生
WHERE 系别=@xbmc and year(出生日期)= @birth
GROUP BY 系别,出生日期,性别
GO
4、执行XM_PROC存储过程,查询“贾慧”的学号、课程名称和成绩。
USE 学生信息
GO
Exec xm_proc '贾慧'
GO
5、执行XBNL_PROC存储过程,查询'软件学院'在1985年出生的学生人数及男、女生人数。
USE 学生信息
GO
Exec XBNL_PROC '软件学院', '1985'
GO
6、分别查看XBNL_PROC存储过程的一般信息、文本信息和依赖关系。
USE 学生信息
GO
EXEC SP_HELP XBNL_PROC
EXEC SP_HELPTEXT XBNL_PROC
EXEC SP_DEPENDS XBNL_PROC
GO
7、删除XM_PROC存储过程。
USE 学生信息
GO
DROP PROCEDURE XM_PROC
GO
四、实验作业
1、在“学生信息”数据库中创建名为INSERT_KCBH的INSERT触发器,存储在“学期成绩”表中。当用户向“学期成绩”表中插入记录时,如果插入了在“课程注册”表中没有的课程编号,则提示用户不能插入记录,否则提示记录插入成功,并输入INSERT语句加以验证两种情况。
USE 学生信息
GO
CREATE TRIGGER INSERT_KCBH ON 学期成绩
FOR INSERT
AS
DECLARE @XH CHAR(3)
SELECT @XH =课程注册. 课程编号
FROM 课程注册 JOIN INSERTED ON 课程注册.课程编号=INSERTED. 课程编号
IF @XH <> ''
PRINT '记录插入成功'
ELSE
BEGIN
PRINT '该课程编号在课程注册表中不存在,插入记录失败!'
ROLLBACK TRANSACTION
END
GO
2、查看“学期成绩”表信息以及触发器的信息。
EXEC Sp_help 学期成绩
sp_helptext INSERT_KCBH
3、在“学生信息”数据库中创建名为UPDATE_RKJS的UPDATE触发器,存储在“课程注册”表中。当用户修改“课程注册”表中的“任课教师”字段值时,提示用户不能修改任课教师姓名,并输入UPDATE语句加以验证。
USE 学生信息
GO
CREATE TRIGGER UPDATE_RKJS ON 课程注册
FOR UPDATE
AS
IF UPDATE(任课教师)
PRINT '不能修改任课教师姓名'
GO
USE 学生信息
GO
update 课程注册
set 任课教师 = '123123'
GO
4、在“学生信息”数据库中创建名为DELETE_KC的DELETE触发器,存储在“课程注册”表中。当用户删除“课程注册”表中的某条记录时,将该课程在“学期成绩”表中相应的成绩信息也随之清除,并输入DELETE语句加以验证。
USE 学生信息
GO
CREATE TRIGGER DELETE_KC ON 课程注册
FOR DELETE
AS
DECLARE @kcmc CHAR(10)
SELECT @kcmc=课程名称
FROM DELETED
DELETE 学期成绩
WHERE 课程名称=@kcmc
GO
DECLARE @XH CHAR(10)
SELECT @XH=学号
FROM DELETED
DELETE 学期成绩
WHERE 学号=@XH
GO
5、查看“课程注册”表信息以及触发器的信息。
EXEC Sp_help 课程注册
sp_helptext DELETE_KC
6、查看触发器UPDATE_RKJS的文本信息、一般信息和依赖关系。
USE 学生信息
GO
EXEC SP_HELP UPDATE_RKJS
EXEC SP_HELPTEXT UPDATE_RKJS
EXEC SP_DEPENDS UPDATE_RKJS
GO
7、将触发器UPDATE_RKJS更名为UPDATE_JSMC。
SP_RENAME UPDATE_RKJS, UPDATE_JSMC
8、自主练习禁止和启用以上所创建的触发器并观察结果。
alter table 课程注册
disable trigger UPDATE_RKJS
alter table 课程注册
enable trigger UPDATE_RKJS
9、删除UPDATE_RKJS触发器。
DROP TRIGGER UPDATE_RKJS
实验七
四、实验作业
1、编写程序,实现查询总分成绩大于300分的学生人数。
USE 学生信息
GO
SELECT 学号,sum(成绩)
from 学期成绩
group by 学号
having sum(成绩)>300
GO
2、显示字符串“China”中每个字符的ASCII码值和字符。
DECLARE @I INT
SET @I=1
WHILE @I<=len('china')
BEGIN
PRINT substring('china',@I,1)
SET @I=@I+1
END
3、求出1—30000之间所有能够被123整除的整数。
DECLARE @I INT,@J INT
SET @I=123
WHILE @I<=30000
BEGIN
SET @J=123
IF @I%@J=0
PRINT CONVERT(VARCHAR,@I)+'是整数'
SET @I=@I+1
END
4、根据学生的年龄范围显示相应信息:小于20岁的显示“年龄较小”,大于或等于20岁且小于24岁的显示“年龄适中”,大于或等于24岁的显示“年龄偏大”。
DECLARE @I INT,@J INT
SET @I=123
USE 学生信息
GO
SELECT 姓名,年龄等级=
CASE
WHEN year(getdate())-year(出生日期)>=24 THEN '年龄偏大'
WHEN year(getdate())-year(出生日期)<24 THEN '年龄适中'
WHEN year(getdate())-year(出生日期)<20 THEN '年龄较小'
END
from 学生
二、实验作业
1、使用游标修改“学生信息”数据库中的数据,将所有学生的02号课程成绩都加上10分。
USE 学生信息
GO
DECLARE @SCORE DECIMAL
--声明一个可更新的游标
DECLARE 学生成绩游标 CURSOR FOR
SELECT 成绩
FROM 学期成绩
WHERE 课程编号= '02'
FOR UPDATE
OPEN 学生成绩游标
--将提取的数据存入局部变量
while @@fetch_status=0
begin
fetch next from 学生成绩游标 INTO @SCORE
PRINT '修改前的成绩:'+CONVERT(VARCHAR, @SCORE)
UPDATE 学期成绩
SET 成绩=成绩+10
WHERE CURRENT OF 学生成绩游标
end
CLOSE 学生成绩游标
DEALLOCATE 学生成绩游标
GO
2、自行定义游标,然后打开该游标,输出其行数。
USE 学生信息
GO
DECLARE @I INT
set @I=0
DECLARE cur_Stu CURSOR FOR
select 学号,姓名 from 学生 order by 学号
OPEN cur_Stu
/*执行第一次取数操作*/
FETCH NEXT FROM cur_Stu
/*检查上一次游标操作所返回的状态值(若成功,该变量值为0)*/
WHILE (@@FETCH_STATUS=0)
BEGIN
FETCH NEXT FROM cur_Stu
set @I=@I+1
END
CLOSE cur_Stu
DEALLOCATE cur_Stu
print @I