西游记猴哥:sql

实验六

四、实验作业

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