当前位置:文档之家› SQL实验与练习题参考答案

SQL实验与练习题参考答案

实验1 数据库操作
1.创建数据库:
操作1.1:创建一个test数据库,其主数据文件逻辑名test_data,物理文件名test_data.mdf,初始大小10MB,最大尺寸为无限大,增长速度1MB;数据库日志文件逻辑名称为test_log,物理文件名为test_log.ldf,初始大小为1MB,最大尺寸为5MB,增长速度为10%。
参考答案:
create database Test
ON primary
(
name = test_data,
filename = 'd:\test\test_data.mdf',
size = 5MB,
maxsize = unlimited,
filegrowth = 1MB
)
LOG ON
(
name = test_log,
filename = 'd:\test\test_log.ldf',
size = 1MB,
maxsize = 5MB,
filegrowth = 10%
)
GO
2.查看数据库属性:
操作1.2:使用T-SQL语句查看数据库test属性
参考答案:
EXEC sp_helpdb test
3.删除数据库:
操作1.3:使用T-SQL语句删除数据库test
参考答案:
drop database Test

实验2 表操作
1.创建表:
操作2.1:创建学生表:
表名:student 说明:学生基本信息表 属性列 数据类型 长度 空值 列约束 说明 st_id nVarChar 9 Not Null PK 学生学号 st_nm nVarChar 8 Not Null 学生姓名 st_sex nVarChar 2 Null 学生性别 st_birth datetime Null 出生日期 st_score int Null 入学成绩 st_date datetime Null 入学日期 st_from nChar 20 Null 学生来源 st_dpid nVarChar 2 Null 所在系编号 st_mnt tinyint Null 学生职务 参考答案:
USE test
GO
CREATE TABLE student
(
st_id nVarChar(9) primary key NOT NULL ,
st_nm nVarChar(8) NOT NULL ,
st_sex nVarChar(2) NULL ,
st_birth datetime NULL ,
st_score int NULL ,
st_date datetime NULL ,
st_ from nVarChar(20) NULL ,
st_dpid nVarChar(2) NULL ,
st_ mnt tinyint NULL
)
GO
操作2.2:创建课程信息表:
表名:couse 说明:课程信息表 属性列 数据类型 长度 空值 列约束 说明 cs_id nVarChar 4 Not Null PK 课程编号 cs_nm nVarChar 20 Not Null 课程名称 cs_tm int Null 课程学时 cs_sc int Null 课程学分 参考答案:
USE test
GO
CREATE TABLE couse
(
cs_id nVarChar(4) primary key NOT NULL ,
cs_nm nVarChar(20) NOT NULL ,
cs_tm int NULL ,
cs_sc int NULL
)
GO
操作2.3:创建选课表:
表名:slt_couse 说明:选课表 属性列 数据类型 长度 空值 列约束 说明 cs_id nVarChar 4 Not Null FK 课程编号 st_id nVarChar 9 Not Null FK 学生编号 score int Null 课程成绩 sltdate datetime Null 选课日期 参考答案:
USE test
GO
CREATE TABLE couse
(
cs_id nVarChar(4) NOT NULL ,
st_id nVarChar(9) NOT NULL ,
score int NULL ,
sltdate datetime NULL
)
GO
操作2.4:创建院系信息表:
表名:dept 说明:院系信息表 属性列 数据类型 长度 空值 列约束 说明 dp_id nVarChar 2 Not Null 系编号 dp_nm nVarChar 20 Not Null 院系名

称 dp_drt nVarChar 8 Null 院系主任 dt
_tel nVarChar 12 Null 联系电话 参考答案:
USE test
GO
CREATE TABLE dept
(
dp_id nVarChar(2) NOT NULL ,
dp_nm nVarChar(20) NOT NULL ,
dp_drt nVarChar(8) NULL ,
dp_tel nVarChar(12) NULL
)
GO
2.修改表结构:
 (1)向表中添加列:
操作2.5:为“dept”表添加“dp_count”列(数据类型为nvarchar,长度为3,允许为空)
参考答案:
ALTER TABLE dept ADD dp_count nvarchar(3) NULL
 (2)修改列数据类型:
操作2.6:修改“dept”表的“dp_count”列数据类型为int
参考答案:
ALTER TABLE dept ALTER COLUMN dp_count int NULL
 (3)删除表中指定列:
操作2.7:删除“dept”表的“dp_count”列
参考答案:
ALTER TABLE dept DROP COLUMN dp_count
3.删除表
操作2.8:删除“dept”表
参考答案:
DROP TABLE student
4.向表中输入数据记录
操作2.9:分别向“student”表、“couse”表、“slt_couse”表、“dept”表中输入数据记录

实验3 数据完整性
1.空值约束( NULL )
操作3.1:将student表中的st_sex列属性更改为NOT NULL
参考答案:
ALTER TABLE student ALTER COLUME st_nm nVarChar(8) NOT NULL
2.默认值约束( DEFAULT )
操作3.2:将student表中的st_from列默认值设置为“陕西省”
参考答案:
ALTER TABLE student ADD DEFAULT '陕西省' FOR st_from
3.默认值对象
操作3.3:创建默认值对象df_today为当前日期,并将其绑定到slt_couse表中的sltdate列,然后取消绑定,最后删除默认值对象df_today。
参考答案:
CREATE DEFAULT df_today AS Getdate( )
GO
EXEC sp_bindefault df_today, 'slt_couse.sltdate'
GO
EXEC sp_unbindefault 'slt_couse.sltdate'
GO
DROP DEFAULT df_today
GO
4.检查约束( CHECK )
操作3.4:将slt_couse表中的score列的检查约束设置为>=0且<=100
参考答案:
ALTER TABLE slt_couse ADD CHECK (score>=0 AND score<=100)
5.规则约束对象
操作3.5:创建规则约束对象rl_sex,用于检查性别的取值仅限于“男”和“女”,并将其绑定到student表中的st_sex列,然后取消绑定,最后删除规则约束对象rl_sex。
参考答案:
CREATE RULE rl_sex AS @chksex ’男’ OR @chksex=’女’

CREATE RULE rl_sex AS @chksex IN (’男’, ’女’)
GO
EXEC sp_bindrule rl_sex, 'student.st_sex'
GO
EXEC sp_unbindrule 'student.st_sex'
GO
DROP RULE rl_sex
GO
6.主键
操作3.6:将dept表中的dp_id列设置为主键
参考答案:
ALTER TABLE dept ADD PRIMARY KEY (dp_id)
7.唯一性约束( UNIQUE )
操作3.7:将dept表中的dp_nm列设置为唯一性约束
参考答案:
ALTER TABLE dept ADD UNIQUE (dp_nm)
8

.标识列
操作3.8:向slt_couse表中添加标识列id,第1行默认值为1,相邻两个标识列间的增量为1
参考
答案:
ALTER TABLE slt_couse ADD id INT IDENTITY(1,1) NOT NULL
9.外键( FOREIGN KEY )
操作3.9:被参照表为dept,参照表为student
参考答案:
ALTER TABLE student
ADD FOREIGN KEY (st_dpid) REFERENCES dept(dp_id)

实验4 数据更新
1.表中插入数据
操作4.1:向dept表插入一条记录,系号11,系名自动控制系,系主任为李其余,电话81234567
INSERT INTO dept VALUES('11', '自动控制系', '李其余', '81234567')
操作4.2:向student表插入一条记录,学号070201001,姓名为王小五,性别为男,出生日期为1990年9月9日,系号为11,其余字段为NULL或默认值
INSERT INTO student(st_id, st_nm, st_sex, st_birth, st_dpid)
VALUES ('070201001', '王小五', '男', '1990.9.9', '11' )
操作4.3:向couse表插入一条记录,课程号1234,课程名为操作系统,其余字段为NULL或默认值
INSERT INTO couse(cs_id, cs_nm) VALUES ('1234', '操作系统')
操作4.4:向slt_couse表插入一条记录,课程号1234,学名070201001,其余字段为NULL或默认值
INSERT INTO slt_couse(cs_id, st_id) VALUES ('1234', '070201001')
2.修改表中数据
操作4.5:修改student表记录,将王小五的入学成绩改为88
UPDATE student SET st_score=88 WHERE st_nm='王小五'
操作4.6:修改couse表记录,将所有记录的学分改为4,学时改为64
UPDATE couse SET cs_tm=64, cs_sc=4
操作4.7:修改slt_couse表记录,将课程号为1234,学名为070201001的记录的成绩改为77
UPDATE slt_couse SET score=77 WHERE cs_id='1234' AND st_id='070201001'
3.删除表中数据
操作4.8:删除slt_couse表记录,将课程号为1234,学名为070201001的记录删除
DELETE FROM slt_couse WHERE cs_id='1234' AND st_id='070201001'
操作4.9:删除couse表记录,将课程号为1234的记录删除
DELETE FROM couse WHERE cs_id='1234'

实验5 数据查询(1)——简单查询
(1)查询表中所有的列
操作5.1:查询所有系的信息
SELECT * FROM dept
(2)查询表中指定列的信息
操作5.2:查询所有的课程号与课程名称
SELECT cs_id, cs_nm FROM couse
(3)在查询列表中使用列表达式
操作5.3:在查询student表时使用列表达式:入学成绩+400
SELECT st_id, st_nm, st_score, st_score+400 AS new_score
FROM student
(4)重新命名查询结果
操作5.4:使用AS关键字为dept表中属性指定列名:系号、系名、系主任、联系电话
SELECT dp_id AS 系号, dp_nm AS 系名, dp

_drt AS 系主任, dp_tel AS联系电话
FROM dept
操作5.5:使用"="号为couse表中属性指定列名:课程号、课程名、学时(=cs_sc*16)、学分
SELECT 课程号=cs_id, 课程名=cs_nm, 学分=cs_sc, 学时=cs_sc*16
FROM couse
(5)增加说明列
操作5.6:查询dept表的系号、系名和系主任,
向查询结果中插入说明列:系号、系名和系主任
SELECT '系号:', st_id, '系名:', st_nm, '系主任:', st_drt
FROM dept
(6)查询列表中使用系统函数
操作5.7:显示所有学生的学号、姓名、性别和入学年份
SELECT st_id, st_nm, st_sex, DATEPART(yy,st_birth) AS 入学年份
FROM student
操作5.8:显示所有学生学号、姓名、性别和班级(学号前6位)
SELECT st_id, st_nm, st_sex, LEFT(st_id, 6) AS 班级
FROM student
(7)消除查询结果中的重复项
操作5.9:显示所有学生班级
SELECT DISTINCT LEFT(st_id,6) AS 班级 FROM student
(8)取得查询结果的部分行集
操作5.10:显示前5条学生记录信息
SELECT TOP 5 * FROM student
操作5.11:显示前25%条学生记录信息
SELECT TOP 25 PERCENT * FROM student
操作5.12:显示前n条学生记录信息,n为局部变量
DECLARE @n INT
SET @n = 4
SELECT TOP @n * FROM student

实验6 数据查询(2)——条件查询
1.使用关系表达式表示查询条件
操作6.1:查询dept表中系号为11的院系信息
SELECT * FROM dept WHERE dp_id = '11'
操作6.2:查询student表中11系的学生学号、姓名、性别和所在系编号
SELECT st_id, st_nm, st_sex, st_dpid FROM student
WHERE st_dpid = '11'
操作6.3:查询student表中2008年及以后入学的学生信息
SELECT * FROM student
WHERE DATEPART( yy, st_date )>= 2008
操作6.4:在查询student表080808班学生的学号、姓名、性别和入学成绩
SELECT st_id, st_nm, st_sex, st_score FROM student
WHERE Left(st_id,6)='080808'
2.使用逻辑表达式表示查询条件
操作6.5:查询student表中非11系的学生信息
SELECT * FROM student WHERE NOT (st_dpid = '11')
操作6.6:查询选修了1002号课程且成绩在60以下的学生学号
SELECT st_id FROM slt_couse
WHERE (cs_id='1002') AND (score<60)
操作6.7:查询2007年入学的11系所有男生信息
SELECT * FROM student
WHERE DATEPART(yy,st_date)=2007 AND st_dpid='11' AND st_sex='男'
操作6.8:查询11系和12系的学生信息
SELECT * FROM student
WHERE st_dpid='11' OR st_dpid='12'
操作6.9:查询11系和12系所有2007年入学的学生信息
SELECT * FROM student
WHERE (st_dpid='11' OR st_dpid='12') AND DATEPART(yy,st_date)=2007
3.使用LIKE关键字进行模糊查询

操作6.10:查询所有“计算机”开头的课程信息
SELECT * FROM couse WHERE cs_nm LIKE '计算机%'
操作6.11:查询所有由三个字组成的“王”姓学生信息
SELECT * FROM student WHERE st_nm LIKE '王__'
操作6.12:查询所有课程名中包含“信息”的课程信息
SELECT * FROM couse WHERE cs_nm LIKE '%信息%'
操作6.13:查询学生姓名介于王姓到张姓的信息
SELECT * FROM student
WHERE st_nm LIKE '[王-张]%'
4.使用Between…And关键字进行查

操作6.14:查询在1989.7.1到1990.6.30之间出生的学生信息
SELECT st_id, st_nm, st_sex, st_birth FROM student
WHERE st_birth BETWEEN '1981.7.1' AND '1999.6.30'
操作6.15:查询选修了1001号课程且成绩在60到80之间的学生选课信息
SELECT * FROM slt_couse
WHERE cs_id='1001' AND (score BETWEEN 60 AND 80)
5.使用IN关键字进行查询
操作6.16:查询11系、12系、13系的学生信息
SELECT * FROM student WHERE st_dpid IN ('11', '12','13')
操作6.17:查询所有张,王,李,赵姓的学生的学号、姓名、性别
SELECT st_id, st_nm, st_sex FROM student
WHERE Left(st_nm,1) IN ('张','王','李','赵')
6.使用[NOT] NULL关键字进行查询
操作6.18:查询所有生源为非空的学生信息
SELECT * FROM student WHERE st_from IS NOT NULL
操作6.19:查询选修了1001号课程且成绩为空的学生选课信息
SELECT * FROM slt_couse
WHERE cs_id='1001' AND score IS NULL

实验7 数据查询(3)——查询排序与查询结果存储
操作7.1:查询课程信息,按课程名称降序排序
SELECT * FROM couse ORDER BY cs_nm DESC
操作7.2:查询选修了1001号课程成绩非空的学生学号和成绩,并按成绩降序排序
SELECT st_id, score FROM slt_corse
WHERE cs_id='1001' AND score IS NOT NULL
ORDER BY score DESC
操作7.3:查询11系学生学号、姓名和年龄,按年龄升序排序
SELECT st_id,st_nm,DATEPART(yy,GETDATE( ))-DATEPART(yy,st_birth) AS age
FROM student
ORDER BY age ASC
操作7.4:查询学生信息,按姓名升序排序,再按系号降序排序
SELECT * FROM student ORDER BY st_nm, st_dpid DESC
操作7.5:创建学生表副本student01,仅保留学生学号、姓名和性别
SELECT st_id, st_nm, st_sex INTO student01 FROM student
操作7.6:查询陕西籍学生,将结果保存在新表st_shanxi
SELECT * INTO st_shanxi
FROM student
WHERE st_from='陕西省'
操作7.7:查询选修了1001号课程学生的选课信息,按学号升序排序,将结果保存在新表slt1001
SELECT * INTO slt1001 FROM slt_corse
WHERE cs_id='1001' ORDER BY st_id
操作7.8:用局部变量@stage保存学生

张三的年龄
DECLARE @stage int
SELECT @stage = DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)
FROM student
WHERE st_nm='张三'
操作7.9:用局部变量@name和@stscore保存070101班按学号排序后最后一个学生的姓名和入学成绩
DECLARE @name nVarChar(8), @stscore int
SELECT @name = st_nm, @stscore = st_score
FROM student
WHERE LEFT(st_id,6)='070101'
ORDER BY st_id

实验8 数据查询(4)——查询统计与汇总
操作8.1:查询课程总数
SELECT COUNT( * ) FROM couse
操作8.2:查询选修1001号课程的学生人数
SELECT COUNT(st_id)
FROM slt_couse
Where cs_id = '1001'
操作8.3:查询被选修课程的数量
SELECT COUNT( DIS
TINCT cs_id ) FROM slt_couse
操作8.4:查询选修070101班学生的平均入学成绩
SELECT AVG(st_score)
FROM student
WHERE LEFT(st_id,6)='070101'
操作8.5:查询070101001号学生选修课程的数量、总分以及平均分
SELECT COUNT(cs_id) AS 课程数量,SUM(score) AS 总分,AVG(score) AS 平均分
FROM slt_couse
WHERE st_id='070101001'
操作8.6:查询选修1001号课程的学生人数、最高分、最低分和平均分
SELECT COUNT(*) AS 学生人数, MAX(score) AS 最高分,
MIN(score) AS 最低分, AVG (score) AS 平均分
FROM slt_couse
WHERE cs_id='1001'
操作8.7:求各个课程号和相应的选课人数
SELECT cs_id, COUNT(st_id)
FROM slt_couse GROUP BY cs_id
操作8.8:统计各班人数
SELECT LEFT(st_id,6) AS 班级, COUNT(st_id) AS 人数
FROM student
GROUP BY LEFT(st_id,6)
操作8.9:依次按班级、系号对学生进行分类统计人数、入学平均分
SELECT st_dpid AS 系号, LEFT(st_id,6) AS 班级,
COUNT(st_nm) AS 人数, AVG(st_score) AS 均分
FROM student
GROUP BY LEFT(st_id,6), st_dpid
操作8.10:查询选修了均分在75以上的课程号及均分
SELECT cs_id AS 课程编号, AVG(score) AS 均分
FROM slt_couse
GROUP BY cs_id HAVING AVG(score)>75
操作8.11:查询选修了2门以上课程的学生学号
SELECT st_id FROM slt_couse
GROUP BY st_id HAVING COUNT(*)>2
操作8.12:明细汇总年龄<20的学生,并汇总学生数量、平均年龄
SELECT st_nm,DATEPART(yy,GETDATE( ))-DATEPART(yy,st_birth) AS 年龄
FROM student
WHERE DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)<20
COMPUTE COUNT(st_nm),AVG(DATEPART(yy,GETDATE())-DATEPART(yy,st_birth))
操作8.13:按班级明细汇总成绩<85分的学生,汇总学生数、均分
SELECT st_nm, LEFT(st_id,6) AS 班级, st_score
FROM student
WHERE st_score<85
ORDER BY 班级
COMPUTE COUNT(st_nm), AVG(st_score) BY 班级

实验9 数据查询(5)——连接查询
操作9.1:用SQL Server形式连接查询学生学号、姓名、性别及其所选课程

编号
SELECT a.st_id, st_nm, st_sex, cs_id
FROM student a, slt_couse b
WHERE a.st_id = b.st_id
ORDER BY a.st_id
操作9.2:用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号
SELECT a.st_id, st_nm, st_sex, cs_id
FROM student a INNER JOIN slt_couse b
ON a.st_id = b.st_id
ORDER BY a.st_id
操作9.3:用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩
SELECT a.st_id, st_nm, cs_nm, score
FROM student a, slt_couse b, couse c
WHERE a.st_id = b.st_id AND b.cs_id = c.cs_id
ORDER BY a.st_id
操作9.4:用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩
SELECT a.st_id, st_nm, cs_nm, score
FROM slt_couse a INNER JOIN student b ON a.st_id = b.st_id
INNER JOIN couse c ON a.cs_id = c.cs_id
ORDER BY b.
st_id
操作9.5:查询选修了1002课程的学生学号、姓名及1001课程成绩
SELECT a.st_id, st_nm, score
FROM student a,slt_couse b
WHERE a.st_id = b.st_id AND b.cs_id = '1002'
ORDER BY b.st_id
操作9.6:查询选修了“数据结构”课程的学生学号、姓名及课程成绩
SELECT a.st_id, st_nm, score
FROM student a, slt_couse b, couse c
WHERE a.st_id=b.st_id AND b.cs_id=c.cs_id AND c.cs_nm='数据结构'
ORDER BY a.st_id
操作9.7:用左外连接查询没有选修任何课程的学生学号、姓名
SELECT a.st_id, st_nm, score
FROM student a LEFT OUTER JOIN slt_couse b ON a.st_id = b.st_id
WHERE b.cs_id IS NULL
ORDER BY b.st_id
操作9.8:用右外连接查询选修各个课程的学生学号
SELECT b.cs_id, a.st_id
FROM slt_couse a Right OUTER JOIN couse b ON a.cs_id = b.cs_id
ORDER BY b.cs_id

实验10 数据查询(6)——子查询
操作10.1:用子查询对各班人数进行查询(新增列)
SELECT DISTINCT LEFT(a.st_id,6) AS 班级, 人数 =
( SELECT COUNT(st_id) FROM student b
WHERE LEFT(a.st_id,6) = LEFT(b.st_id,6)
)
FROM student a ORDER BY LEFT(a.st_id,6) ASC
操作10.2:用子查询对各课程的选课人数进行查询(新增列)
SELECT DISTINCT a.cs_id, 人数 =
( SELECT COUNT(st_id) FROM slt_couse b
WHERE a.cs_id = b.cs_id
)
FROM slt_couse a ORDER BY a.cs_id ASC
操作10.3:查询选修了1002课程成绩不及格的学生的学号、姓名和性别,并按姓名升序排序
通过子查询实现:——使用IN关键字
SELECT st_id, st_nm, st_sex FROM student
WHERE st_id IN
( SELECT st_id FROM slt_couse
WHERE cs_id='1002' AND score < 60 )
ORDER BY st_nm
通过子查询实现:——使用比较运算符
SELECT st_id, st_nm, st_sex
FROM student a
WHERE
( SELECT score FROM slt_couse b
WHERE a.st_id = b.st_id AND cs_id = '1002' ) < 60
ORDER BY st_nm
操作10.4:查询“东方红”同学所在班的学生信息,并按姓名降序排


通过子查询实现:——IN运算符
SELECT st_id, st_nm, st_sex FROM student
WHERE LEFT(st_dpid,6) IN
( SELECT Left(st_dpid,6) FROM student WHERE st_nm='东方红' )
ORDER BY st_nm DESC
通过自连接查询实现:——JOIN
SELECT s1.st_id, s1.st_nm, s1.st_sex
FROM student s1 JOIN student s2
ON LEFT(s1.st_dpid,6) = LEFT(s2.st_dpid,6)
WHERE s2.st_nm='东方红'
ORDER BY s2.st_nm
操作10.5:查询其它班比070511班某一学生的1002号课程成绩高的学生信息(ANY/ALL)
SELECT * FROM slt_couse
WHERE score > ANY
( SELECT score FROM slt_couse
WHERE cs_id = '1002' AND LEFT(st_id,6)='070511' )
AND LEFT(st_id,6) <> '070511' AND cs_id = '1002'
操作10.6:查询其它班比070511班任一学生的1002号课程成绩高的学生信息(ANY/ALL)
SELECT * FROM slt_couse
WHERE score > ALL
( SELECT score FROM slt_couse
WHERE cs_id = '1002' AND LEFT(st_id
,6)='070511' )
AND LEFT(st_id,6) <> '070511' AND cs_id = '1002'
操作10.7:查询大于等于60分且且比1003课程平均成绩低的学生课程信息(Between…And)
SELECT * FROM slt_couse a
WHERE a.score BETWEEN 60 AND
( SELECT AVG(b.score) FROM slt_couse b
WHERE b.cs_id='1003' )
操作10.8:查询系主任为“赵虎”的系的所有学生信息
通过子查询实现:——IN运算符
SELECT * FROM student
WHERE EXISTS
( SELECT * FROM dept
WHERE st_dpid = dp_id AND dp_drt='赵虎' )
通过子查询实现:——=运算符
SELECT * FROM student
WHERE st_dpid =
( SELECT dp_id FROM dept WHERE dp_drt='赵虎' )

实验11 数据查询(7)——数据更新与子查询
操作11.1:将070511班所有学生信息插入到表student01(st_id,st_nm,st_sex)
INSERT INTO student01
SELECT st_id,st_nm,st_sex FROM student
WHERE LEFT(st_id,6)='070511'
操作11.2:生成1002号课程的成绩单student02(st_id,st_nm, score)
INSERT INTO student02
SELECT a.st_id,st_nm,score FROM student a, slt_couse b
WHERE a.st_id=b.st_id AND cs_id='1002'
操作11.3:将有不及格成绩的学生的st_mnt值更改为3
UPDATE student SET st_mnt=3
WHERE st_id IN
( SELECT DISTINCT st_id FROM slt_couse WHERE score < 60 )
操作11.4:将没有被选修的课程的学分更改为0
UPDATE couse SET cs_sc=0
WHERE cs_id NOT IN
( SELECT DISTINCT cs_id FROM slt_couse )
操作11.5:删除5系学生的选课信息
DELETE FROM slt_couse
WHERE st_id IN
( SELECT st_id FROM student WHERE st_dpid = 5 )
操作11.6:删除学分为0的选课信息
DELETE FROM slt_couse
WHERE cs_id IN
( SELECT DISTINCT cs_id FROM slt_couse WHERE cs_id=0 )

实验12 事务和锁


实验13 索引
例6.1 在student表创建非聚集索引
CREATE INDEX idx_s

td ON student( st_nm )
例6.2 在student表创建复合索引
CREATE INDEX idx_std
ON student( st_id , st_nm )
WITH DROP_EXISTING
例6.3 在student表创建唯一非聚集索引
CREATE UNIQUE NONCLUSTERED INDEX idx_std
ON student( st_id )
WITH DROP_EXISTING
例6.4 使用填充因子创建唯一非聚集索引
CREATE UNIQUE NONCLUSTERED INDEX idx_std
ON student( st_id )
WITH PAD_INDEX , FILLFACTOR = 50 , DROP_EXISTING
例6.5 查看student表的索引
Exec sp_helpindex 'student'
例6.6 更改student表的索引名
Exec sp_rename 'student.idx_stut', 'idx_st00', 'index'
例6.7 删除student表的索引
DROP INDEX student.idx_stid


实验14 视图
例6.8 创建学生情况视图
CREATE VIEW view_student
AS
SELECT st_id, st_nm, st_sex, st_dpid
FROM student
例6.9 创建院系情况视图
CREATE VIEW view_dept
AS
SELECT dp_id, dp_nm
F
ROM dept
例6.10 创建课程情况视图
CREATE VIEW view_couse
AS
SELECT cs_id, cs_nm
FROM couse
WHERE a.st_id = c.st_id AND b.cs_id = c.cs_id
例6.11 创建学生选课情况视图
CREATE VIEW std_slt
AS
SELECT a.st_id, a.st_nm, a.st_sex, b.cs_id, b.score
FROM student a, slt_couse b
WHERE a.st_id = b.st_id
例6.12 由view_student视图创建01系学生情况视图
CREATE VIEW view_student01
AS
SELECT st_id, st_nm, st_sex, st_dpid
WHERE st_dpid = '01'
FROM view_student
◆在创建视图时,使用SELECT *子句,则当基表中的列发生变化时,此变化不会显示在视图中
例6.13 创建视图时使用SELECT *子句
CREATE VIEW vw_student AS
SELECT * FROM student
GO
SELECT * FROM vw_student
GO
ALTER TABLE student ADD st_tel varChar(12) NULL
GO
SELECT * FROM vw_student
GO
例6.14 修改std_slt视图
ALTER VIEW std_slt AS
SELECT a.st_id, a.st_nm, a.st_sex, b.cs_id, b.cs_nm, c.score
FROM student a, couse b, slt_couse c
WHERE a.st_id = c.st_id AND b.cs_id = c.cs_id
GO
例6.15 把视图std_slt重命名为view_std
sp_rename std_slt, view_std
例6.16 删除std_slt视图
DROP VIEW std_slt
例6.17 查询view_dept视图中所有院系的编号及其名称
SELECT * FROM view_dept
例6.18 查询view_student视图中所有01系的学生
SELECT * FROM view_student
WHERE st_dpid = '01'
例6.19 查询std_slt视图中所有选修1001号课程的学生

SELECT * FROM view_student
WHERE cs_id = '1001'
例6.20 向view_student视图中插入学生记录
INSERT INTO view_student
VALUES ( '070511121', ’李丽’, ’女’, '01')
例6.21 向view_student01视图中插入其他系所有女生记录
INSERT INTO view_student01
( SELECT * FROM view_student WHERE st_sex = '女' AND st_id <>'01' )
例6.22 将view_dept视图中01系的系名更改为"信息管理系"
UPDATE view_dept SET dp_nm ='信息管理系' WHERE dp_id = '01'
例6.23 将std_slt视图中学生"张三"的操作系统课程成绩更改为75
UPDATE std_slt SET score = 75
WHERE st_nm = '张三' AND cs_nm = '操作系统'
例6.24 删除view_student视图中学生’李丽’的信息
DELETE FROM view_student WHERE st_nm = ’李丽’
例6.25 删除std_slt视图中学生"张三"对操作系统课程的选课信息
DELETE FROM std_slt
WHERE st_nm = '张三' AND cs_nm = '操作系统'

实验15 游标
统计“多媒体技术”考试成绩的各分数段的分布情况。
DECLARE course_cursor CURSOR FOR
--声明游标
SELECT score FROM slt_couse
WHERE cs_id = ( SELECT cs_id FROM couse WHERE cs_nm = '多媒体技术' )
DECLARE @p_100 SMALLINT, @p_90 SMALLINT, @p_80 SMALLINT
DECLARE @p_70 SMALLINT, @p_60 SMALLINT, @p_others SMALLINT
DECLARE @p_grade SMALLINT
SET @p_100 = 0
SET @p_90 = 0
SET @p_80 = 0
SET @p_70 = 0
SET @p_60 = 0
SET @p_others = 0
SET @p_grade = 0
OPEN course_cursor -- 打开游标
LOOP :
FETCH NEXT FROM course_cursor INTO @p_grade --使用游标提取数据
IF ( @p_grade = 100 ) SET @p_100 = @p_100+1
ELSE IF ( @p_grade >= 90 ) SET @p_90 = @p_90+1
ELSE IF ( @p_grade >= 80 ) SET @p_80 = @p_80+1
ELSE IF ( @p_grade >= 70 ) SET @p_70 = @p_70+1
ELSE IF ( @p_grade >= 60 ) SET @p_60 = @p_60+1
ELSE SET @p_others = @p_others+1
IF ( @@FETCH_STATUS = 0 ) GOTO LOOP
PRINT STR( @p_100 ) + ’,’ + STR( @p_90 ) + ’,’ + STR( @p_80 ) + ’,’
PRINT STR( @p_70 ) + ’,’ + STR( @p_60 ) + ’,’ + STR( @p_others )
CLOSE course_cursor -- 关闭游标
DEALLOCATE course_cursor -- 释放游标

实验16 存储过程
【示例7.1】针对学生表student,创建一个名称为student_proc的存储过程,该存储过程的功能是从数据表student中查询所有男学生的信息。
USE test
GO
CREATE PROCEDURE p

roc_student
AS
SELECT * FROM student WHERE st_sex = '男'
GO
【示例7.2】创建一个带参数的存储过程
USE test
GO
CREATE PROCEDURE proc_stu_class
@classnm nvarchar(6) = ‘070511’
AS
SELECT * FROM student WHERE left(st_id,6) = @classnm
GO
【示例7.3】创建一个带输入输出参数的存储过程
USE test
GO
CREATE PROCEDURE proc_stuinfor
@stuid nvarchar(9) ,
@stunm nvarchar(8) OUTPUT ,
@stuscore int OUTPUT
AS
SELECT @stunm=st_nm, @stuscore=st_score FROM student
WHERE st_id = @stuid
GO
【示例7.4】存储过程参数中使用通配符
USE test
GO
CREATE PROCEDURE proc_stuwang
@stunm nvarchar(8) = ‘王%’
AS
SELECT * FROM student WHERE st_nm LIKE @stunm
GO
【示例7.5】执行不带参数的存储过程
EXEC proc_student
【示例7.6】执行带一个参数的存储过程
EXEC proc_stu_class '070512'
【示例7.7】执行一个带输入输出参数的存储过程
DECLARE @stu_name NVARCHAR(8) , @stu_score INT
EXEC proc_stuinfor '070511001' , @stu_name OUTPUT , @stu_score OUTPUT
SELECT @stu_name , @stu_score
【示例7.8】执行参数中使
用通配符的存储过程
EXEC proc_stuwang '张%'
【示例7.9】查看存储过程proc_student的详细信息
EXEC sp_helptext proc_student

实验17 自定义函数
【示例7.10】自定义标量函数,实现对学生成绩的评价
CREATE FUNCTION fc_grade
( @score INT )
RETURNS nVarChar(3)
BEGIN
DECLARE @grade nVarChar(3)
IF @score >= 90
SET @grade = ‘优秀’
ELSE IF @score >= 80
SET @grade = ‘良好’
ELSE IF @score >= 70
SET @grade = ‘中等’
ELSE IF @score >= 60
SET @grade = ‘及格’
ELSE
SET @grade = ‘不及格’
RETURN @grade
END
函数的使用:
SELECT a.st_id , a.st_nm , b.cs_id , b.cs_nm , c.score , dbo.fc_grade( c.score )
FROM student a , couse b , slt_couse c
WHERE a.st_id = c.st_id AND b.cs_id = c.cs_id

【示例7.11】自定义内嵌表值函数,根据指定的班级查询此班所有的学生信息
CREATE FUNCTION fc_class
( @class nVarChar(6) )
RETURNS table
AS
RETURN
( SELECT * FROM student WHERE LEFT( st_id,6 ) = @class )
函数的使用:
SELECT * FROM dbo.fc_class( ‘070512’ )

【示例7.12】自定义多语句表值函数,根据输入的学生学号返回选修该课程的学生姓名和

成绩
CREATE FUNCTION fc_chengji
( @stuid nVarChar(9) )
RETURNS @chji TABLE
(
st_id nVarChar(9),
st_nm nVarChar(8),
cs_nm nVarChar(20),
score INT
)
AS
BEGIN
INSERT @chji
SELECT a.st_id , a.st_nm , b.cs_nm , c.score
FROM student a INNER JOIN slt_couse c ON a.st_id = c.st_id
INNER JOIN couse b ON b.cs_id = c.cs_id
WHERE a.st_id = @stuid
RETURN
END
函数的使用:
SELECT * FROM fc_chengji ('070501101')

实验18 触发器
【示例8.1】 创建INSERT触发器,在插入学生信息数据时检查入学日期是否超过当前日期,若超过当前日期,则给出警告信息
USE test
GO
CREATE TRIGGER tri_stuInsert
ON student
FOR INSERT
AS
DECLARE @lsdate DATETIME
SELECT @lsdate = student.st_date FROM student , inserted
WHERE student.st_date = inserted.st_date

IF @lsdate > getdate( )
BEGIN
RAISERROR( '入学日期错误,请删除该行数据!' , 16 , 10 )
END
执行以下语句:
INSERT INTO student( st_id , st_nm , st_sex , st_date )
VALUES( '070511023' , '李小四' , '男' , '2011-9-1' )

【示例8.2】 创建UPDATE触发器,在对学生入学成绩修改时,显示修改前后的成绩
USE te
st
GO
CREATE TRIGGER tri_stuUpdate
ON student
FOR UPDATE
AS
IF UPDATE(st_score)
BEGIN
SELECT Inserted.st_nm,Inserted.st_score AS new_score, Deleted.st_score AS old_score
FROM Inserted , Deleted
WHERE Deleted.st_id = Inserted.st_id
END
GO
执行以下语句:
UPDATE student SET st_score = 99 WHERE st_id = '070511003'

【示例8.3】 创建DELETE触发器,当删除某学生记录时,同时删除选课表中的相关记录
USE test
GO
CREATE TRIGGER tri_stuDelete
ON student
FOR DELETE
AS
DELETE slt_couse WHERE st_id IN ( SELECT st_id FROM deleted )
GO
执行以下语句:
DELETE student WHERE st_id = '070511003'
【示例8.4】 在数据库上创建DDL触发器,实现删除和修改表时显示警告信息
CREATE TRIGGER safety
ON DATABASE
FOR drop_table , alter_table
AS
PRINT '要删除或修改表之前,必须先禁用触发器safety '
ROLLBACK ;

【示例8.5】 查看学生表上存在的触发器的信息
EXEC sp_helptrigger student
GO
【示例7.24】禁止或启用在数据库test中表student上创建的所有

触发器
ALTER TABLE student DISABLE TRIGGER ALL
ALTER TABLE student ENABLE TRIGGER ALL



数据库原理与应用(SQL Server)练习题 - 1 -



相关主题
相关文档 最新文档