2023年6月21日发(作者：)
数据库原理与应用（SQL Server）练习题 - 1 -
实验1 数据库操作
1．创建数据库：
操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_，初始大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为test_，初始大小为1MB，最大尺寸为5MB，增长速度为10%。
参考答案：
create database Test
ON primary
(
name = test_data,
filename = 'd:testtest_',
size = 5MB,
maxsize = unlimited,
filegrowth = 1MB
)
LOG ON
(
name = test_log,
filename = 'd:testtest_',
size = 1MB,
maxsize = 5MB, 数据库原理与应用（SQL Server）练习题 - 2 -
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 说明：学生基本信息表
列约束
PK
说明 属性列 数据类型 长度 空值
Not
st_id
st_nm
nVarChar
nVarChar
9
8
Null
Not
学生学号
学生姓名 数据库原理与应用（SQL Server）练习题 - 3 -
Null
st_sex nVarChar 2
Null
Null
Null
Null
学生性别
出生日期
入学成绩
入学日期
学生来源
所在系编号
学生职务
st_birth datetime
st_score int
st_date datetime
st_from nChar
st_dpid nVarChar
st_mnt tinyint
20 Null
2
Null
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 数据库原理与应用（SQL Server）练习题 - 4 -
)
GO
操作2.2：创建课程信息表：
表名：couse 说明：课程信息表
列约束
PK
说明 属性列 数据类型 长度 空值
Not
cs_id nVarChar 4
Null
Not
cs_nm nVarChar 20
cs_tm int
cs_sc int
参考答案：
USE test
GO
CREATE TABLE couse
(
Null
Null
Null
课程编号
课程名称
课程学时
课程学分
cs_id nVarChar(4) primary key NOT NULL ,
cs_nm nVarChar(20) NOT NULL ,
cs_tm int NULL ,
cs_sc int NULL
) 数据库原理与应用（SQL Server）练习题 - 5 -
GO
操作2.3：创建选课表：
表名：slt_couse
属性列 数据类型 长度
说明：选课表
空值
Not
Null
Not
Null
Null
Null
列约束
cs_id nVarChar 4 FK 课程编号
说明
st_id
score
nVarChar
int
9
FK
学生编号
课程成绩
选课日期 sltdate datetime
参考答案：
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 数据库原理与应用（SQL Server）练习题 - 6 -
操作2.4：创建院系信息表：
表名：dept 说明：院系信息表
列约束
说明 属性列 数据类型 长度 空值
Not
Null
Not
dp_nm nVarChar 20
dp_drt nVarChar 8
Null
Null
dp_id nVarChar 2 系编号
院系名称
院系主任
联系电话 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 数据库原理与应用（SQL Server）练习题 - 7 -
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”表中输入数据记录 数据库原理与应用（SQL Server）练习题 - 8 -
实验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_e'
GO
EXEC sp_unbindefault 'slt_e'
GO 数据库原理与应用（SQL Server）练习题 - 9 -
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, '_sex'
GO
EXEC sp_unbindrule '_sex'
GO
DROP RULE rl_sex
GO 数据库原理与应用（SQL Server）练习题 - 10 -
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)
数据库原理与应用（SQL Server）练习题 - 11 -
实验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 数据库原理与应用（SQL Server）练习题 - 12 -
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：查询所有的课程号与课程名称 数据库原理与应用（SQL Server）练习题 - 13 -
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：显示所有学生的学号、姓名、性别和入学年份 数据库原理与应用（SQL Server）练习题 - 14 -
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的院系信息 数据库原理与应用（SQL Server）练习题 - 15 -
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='男' 数据库原理与应用（SQL Server）练习题 - 16 -
操作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之间的学数据库原理与应用（SQL Server）练习题 - 17 -
生选课信息
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号课程成绩非空的学生学号和成绩，数据库原理与应用（SQL Server）练习题 - 18 -
并按成绩降序排序
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 数据库原理与应用（SQL Server）练习题 - 19 -
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' 数据库原理与应用（SQL Server）练习题 - 20 -
操作8.3：查询被选修课程的数量
SELECT COUNT( DISTINCT 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 人数 数据库原理与应用（SQL Server）练习题 - 21 -
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 数据库原理与应用（SQL Server）练习题 - 22 -
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 _id, st_nm, st_sex, cs_id
FROM student a, slt_couse b
WHERE _id = _id
ORDER BY _id
操作9.2：用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号
SELECT _id, st_nm, st_sex, cs_id
FROM student a INNER JOIN slt_couse b
ON _id = _id 数据库原理与应用（SQL Server）练习题 - 23 -
ORDER BY _id
操作9.3：用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩
SELECT _id, st_nm, cs_nm, score
FROM student a, slt_couse b, couse c
WHERE _id = _id AND _id = _id
ORDER BY _id
操作9.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩
SELECT _id, st_nm, cs_nm, score
FROM slt_couse a INNER JOIN student b ON _id = _id
INNER JOIN couse c ON _id = _id
ORDER BY _id
操作9.5：查询选修了1002课程的学生学号、姓名及1001课程成绩
SELECT _id, st_nm, score
FROM student a，slt_couse b
WHERE _id = _id AND _id = '1002'
ORDER BY _id
操作9.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩
SELECT _id, st_nm, score
FROM student a, slt_couse b, couse c 数据库原理与应用（SQL Server）练习题 - 24 -
WHERE _id=_id AND _id=_id AND _nm='数据结构'
ORDER BY _id
操作9.7：用左外连接查询没有选修任何课程的学生学号、姓名
SELECT _id, st_nm, score
FROM student a LEFT OUTER JOIN slt_couse b ON _id =
_id
WHERE _id IS NULL
ORDER BY _id
操作9.8：用右外连接查询选修各个课程的学生学号
SELECT _id, _id
FROM slt_couse a Right OUTER JOIN couse b ON _id =
_id
ORDER BY _id
实验10 数据查询（6）——子查询
操作10.1：用子查询对各班人数进行查询（新增列）
SELECT DISTINCT LEFT(_id,6) AS 班级, 人数 =
( SELECT COUNT(st_id) FROM student b
WHERE LEFT(_id,6) = LEFT(_id,6)
)
FROM student a ORDER BY LEFT(_id,6) ASC 数据库原理与应用（SQL Server）练习题 - 25 -
操作10.2：用子查询对各课程的选课人数进行查询（新增列）
SELECT DISTINCT _id, 人数 =
( SELECT COUNT(st_id) FROM slt_couse b
WHERE _id = _id
)
FROM slt_couse a ORDER BY _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 _id = _id AND cs_id = '1002' ) < 60
ORDER BY st_nm
操作10.4：查询“东方红”同学所在班的学生信息，并按姓名降序排序 数据库原理与应用（SQL Server）练习题 - 26 -
通过子查询实现：——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 _id, _nm, _sex
FROM student s1 JOIN student s2
ON LEFT(_dpid,6) = LEFT(_dpid,6)
WHERE _nm='东方红'
ORDER BY _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 数据库原理与应用（SQL Server）练习题 - 27 -
( 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 BETWEEN 60 AND
( SELECT AVG() FROM slt_couse b
WHERE _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班所有学生信息插入到表数据库原理与应用（SQL Server）练习题 - 28 -
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 _id,st_nm,score FROM student a, slt_couse b
WHERE _id=_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的选课信息 数据库原理与应用（SQL Server）练习题 - 29 -
DELETE FROM slt_couse
WHERE cs_id IN
( SELECT DISTINCT cs_id FROM slt_couse WHERE cs_id=0 )
实验12 事务和锁
实验13 索引
实验14 视图
实验15 游标
实验16 存储过程
实验17 自定义函数
实验18 触发器
2023年6月21日发(作者：)
数据库原理与应用（SQL Server）练习题 - 1 -
实验1 数据库操作
1．创建数据库：
操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_，初始大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为test_，初始大小为1MB，最大尺寸为5MB，增长速度为10%。
参考答案：
create database Test
ON primary
(
name = test_data,
filename = 'd:testtest_',
size = 5MB,
maxsize = unlimited,
filegrowth = 1MB
)
LOG ON
(
name = test_log,
filename = 'd:testtest_',
size = 1MB,
maxsize = 5MB, 数据库原理与应用（SQL Server）练习题 - 2 -
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 说明：学生基本信息表
列约束
PK
说明 属性列 数据类型 长度 空值
Not
st_id
st_nm
nVarChar
nVarChar
9
8
Null
Not
学生学号
学生姓名 数据库原理与应用（SQL Server）练习题 - 3 -
Null
st_sex nVarChar 2
Null
Null
Null
Null
学生性别
出生日期
入学成绩
入学日期
学生来源
所在系编号
学生职务
st_birth datetime
st_score int
st_date datetime
st_from nChar
st_dpid nVarChar
st_mnt tinyint
20 Null
2
Null
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 数据库原理与应用（SQL Server）练习题 - 4 -
)
GO
操作2.2：创建课程信息表：
表名：couse 说明：课程信息表
列约束
PK
说明 属性列 数据类型 长度 空值
Not
cs_id nVarChar 4
Null
Not
cs_nm nVarChar 20
cs_tm int
cs_sc int
参考答案：
USE test
GO
CREATE TABLE couse
(
Null
Null
Null
课程编号
课程名称
课程学时
课程学分
cs_id nVarChar(4) primary key NOT NULL ,
cs_nm nVarChar(20) NOT NULL ,
cs_tm int NULL ,
cs_sc int NULL
) 数据库原理与应用（SQL Server）练习题 - 5 -
GO
操作2.3：创建选课表：
表名：slt_couse
属性列 数据类型 长度
说明：选课表
空值
Not
Null
Not
Null
Null
Null
列约束
cs_id nVarChar 4 FK 课程编号
说明
st_id
score
nVarChar
int
9
FK
学生编号
课程成绩
选课日期 sltdate datetime
参考答案：
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 数据库原理与应用（SQL Server）练习题 - 6 -
操作2.4：创建院系信息表：
表名：dept 说明：院系信息表
列约束
说明 属性列 数据类型 长度 空值
Not
Null
Not
dp_nm nVarChar 20
dp_drt nVarChar 8
Null
Null
dp_id nVarChar 2 系编号
院系名称
院系主任
联系电话 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 数据库原理与应用（SQL Server）练习题 - 7 -
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”表中输入数据记录 数据库原理与应用（SQL Server）练习题 - 8 -
实验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_e'
GO
EXEC sp_unbindefault 'slt_e'
GO 数据库原理与应用（SQL Server）练习题 - 9 -
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, '_sex'
GO
EXEC sp_unbindrule '_sex'
GO
DROP RULE rl_sex
GO 数据库原理与应用（SQL Server）练习题 - 10 -
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)
数据库原理与应用（SQL Server）练习题 - 11 -
实验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 数据库原理与应用（SQL Server）练习题 - 12 -
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：查询所有的课程号与课程名称 数据库原理与应用（SQL Server）练习题 - 13 -
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：显示所有学生的学号、姓名、性别和入学年份 数据库原理与应用（SQL Server）练习题 - 14 -
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的院系信息 数据库原理与应用（SQL Server）练习题 - 15 -
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='男' 数据库原理与应用（SQL Server）练习题 - 16 -
操作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之间的学数据库原理与应用（SQL Server）练习题 - 17 -
生选课信息
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号课程成绩非空的学生学号和成绩，数据库原理与应用（SQL Server）练习题 - 18 -
并按成绩降序排序
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 数据库原理与应用（SQL Server）练习题 - 19 -
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' 数据库原理与应用（SQL Server）练习题 - 20 -
操作8.3：查询被选修课程的数量
SELECT COUNT( DISTINCT 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 人数 数据库原理与应用（SQL Server）练习题 - 21 -
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 数据库原理与应用（SQL Server）练习题 - 22 -
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 _id, st_nm, st_sex, cs_id
FROM student a, slt_couse b
WHERE _id = _id
ORDER BY _id
操作9.2：用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号
SELECT _id, st_nm, st_sex, cs_id
FROM student a INNER JOIN slt_couse b
ON _id = _id 数据库原理与应用（SQL Server）练习题 - 23 -
ORDER BY _id
操作9.3：用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩
SELECT _id, st_nm, cs_nm, score
FROM student a, slt_couse b, couse c
WHERE _id = _id AND _id = _id
ORDER BY _id
操作9.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩
SELECT _id, st_nm, cs_nm, score
FROM slt_couse a INNER JOIN student b ON _id = _id
INNER JOIN couse c ON _id = _id
ORDER BY _id
操作9.5：查询选修了1002课程的学生学号、姓名及1001课程成绩
SELECT _id, st_nm, score
FROM student a，slt_couse b
WHERE _id = _id AND _id = '1002'
ORDER BY _id
操作9.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩
SELECT _id, st_nm, score
FROM student a, slt_couse b, couse c 数据库原理与应用（SQL Server）练习题 - 24 -
WHERE _id=_id AND _id=_id AND _nm='数据结构'
ORDER BY _id
操作9.7：用左外连接查询没有选修任何课程的学生学号、姓名
SELECT _id, st_nm, score
FROM student a LEFT OUTER JOIN slt_couse b ON _id =
_id
WHERE _id IS NULL
ORDER BY _id
操作9.8：用右外连接查询选修各个课程的学生学号
SELECT _id, _id
FROM slt_couse a Right OUTER JOIN couse b ON _id =
_id
ORDER BY _id
实验10 数据查询（6）——子查询
操作10.1：用子查询对各班人数进行查询（新增列）
SELECT DISTINCT LEFT(_id,6) AS 班级, 人数 =
( SELECT COUNT(st_id) FROM student b
WHERE LEFT(_id,6) = LEFT(_id,6)
)
FROM student a ORDER BY LEFT(_id,6) ASC 数据库原理与应用（SQL Server）练习题 - 25 -
操作10.2：用子查询对各课程的选课人数进行查询（新增列）
SELECT DISTINCT _id, 人数 =
( SELECT COUNT(st_id) FROM slt_couse b
WHERE _id = _id
)
FROM slt_couse a ORDER BY _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 _id = _id AND cs_id = '1002' ) < 60
ORDER BY st_nm
操作10.4：查询“东方红”同学所在班的学生信息，并按姓名降序排序 数据库原理与应用（SQL Server）练习题 - 26 -
通过子查询实现：——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 _id, _nm, _sex
FROM student s1 JOIN student s2
ON LEFT(_dpid,6) = LEFT(_dpid,6)
WHERE _nm='东方红'
ORDER BY _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 数据库原理与应用（SQL Server）练习题 - 27 -
( 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 BETWEEN 60 AND
( SELECT AVG() FROM slt_couse b
WHERE _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班所有学生信息插入到表数据库原理与应用（SQL Server）练习题 - 28 -
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 _id,st_nm,score FROM student a, slt_couse b
WHERE _id=_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的选课信息 数据库原理与应用（SQL Server）练习题 - 29 -
DELETE FROM slt_couse
WHERE cs_id IN
( SELECT DISTINCT cs_id FROM slt_couse WHERE cs_id=0 )
实验12 事务和锁
实验13 索引
实验14 视图
实验15 游标
实验16 存储过程
实验17 自定义函数
实验18 触发器
发布评论