【数据库原理及应用教程(第4版|微课版)陈志泊】【SQLServer2012综合练习】

0．本实验环境

①SQL Server 2016
②SSMS 17

1．创建数据库和数据表

（1）利用资源管理器，在 D 盘建立以自己的姓名为名称的文件夹，以便保存数据库。

（2）登录并连接到 SQL Server 2012 服务器。

（3）利用对象资源管理器建立名称为 Study 的数据库文件，主文件名为 Study.mdf，日志文件名
为 Study.ldf，它们的保存路径在第（1）步中建立的文件夹。

（4）利用对象资源管理器在已经建立的 Study 数据库中分别建立以下六个数据表。

① 学生基本情况数据表 Student，结构如下：

use Study;CREATE TABLE Student(
s_no char(6) primary key,--学号
class_no char(6) not null,--班级号
s_name varchar(10) not null,--学生姓名
s_sex char(2) check(s_sex= '男' or s_sex='女'),--性别
s_birthday datetime --出生日期
)

② 班级数据表 Class，结构如下：

CREATE TABLE Class(
class_no char(6) primary key,--班级号
class_name char(20) not null,--班级名称
class_special varchar(20),--所属专业
class_dept char(20)--系别
)

③ 课程数据表 Course，结构如下：

CREATE TABLE Course(
course_no char(5) primary key,--课程号
course_name char(20) not null,--课程名称
course_score numeric(6, 2)--学分
)

④ 选修课程情况数据表 Choice，结构如下：

CREATE TABLE Choice(
s_no char(6),--学号
course_no char(5),--课程号
score numeric(6, 2)--成绩
)

⑤ 教师数据表 Teacher，结构如下：

CREATE TABLE Teacher(
t_no char(6) primary key,--教师号
t_name varchar(10) not null,--教师姓名
t_sex char(2) check(t_sex = '男'or t_sex='女'),--性别
t_birthday datetime,--出生日期
t_title char(10)--职称
)

⑥ 教师任课情况表 Teaching，结构如下：

Create table Teaching(
couse_no char(5),--课程号
t_no char(6)--教师号
)

（5）利用企业管理器，在 Study 数据库中，向以上建立的六个数据表中分别输入以下内容。

① 学生基本情况数据表 Student 的内容如下：

insert into
Student (s_no,class_no,s_name, s_sex,s_birthday)
values('991101', 'js9901', '张彬', '男', '1981-10-1')insert into
Student (s_no,class_no,s_name,s_sex,s_birthday)
values('991102', 'js9901' , '王蕾', '女', '1980-8-8')insert into
Student (s_no,class_no,s_name,s_sex,s_birthday)
values('991103', 'js9901' , '李建国', '男', '1981-4-5')insert into
Student (s_no, class_no, s_name, s_sex,s_birthday)
values('991104', 'js9901' , '李平方', '男', '1981-5-12')insert into
Student (s_no,class_no,s_name,s_sex,s_birthday)
values('991201', 'js9902' , '陈东辉', '男', '1980-2-8')insert into
Student (s_no, class_no, s_name, s_sex,s_birthday)
values('991202', 'js9902' , '葛鹏', '男', '1979-12-23')insert into
Student (s_no,class_no, s_name,s_sex,s_birthday)
values('991203', 'js9902' , '潘桃芝', '女', '1980-2-6')insert into
Student (s_no, class_no, s_name, s_sex, s_birthday)
values('991204', 'js9902' , '姚一峰', '男', '1981-5-7')insert into
Student (s_no,class_no,s_name,s_sex,s_birthday)
values('001101', 'js0001' , '宋大方', '男', '1980-4-9')insert into
Student (s_no,class_no,s_name,s_sex,s_birthday)
values('001102', 'js0001' , '许辉', '女','1978-8-1')insert into
Student (s_no,class_no,s_name,s_sex,s_birthday)
values('001201', 'js0002' , '王一山', '男', '1982-12-14')insert into
Student (s_no,class_no,s_name,s_sex,s_birthday)
values('001202', 'js0002' , '牛莉', '女', '1981-6-9')insert into
Student (s_no, class_no,s_name,s_sex,s_birthday)
values('002101', 'xx0001' , '李丽丽', '女', '1981-9-19')insert into
Student (s_no, class_no, s_name,s_sex,s_birthday)
values('002102', 'xx0001', '李王', '男', '1980-9-23')

② 班级数据表 Class 的内容如下：

insert into Class(class_no,class_name,class_special,class_dept)
values('js9901','计算机99-1','计算机','计算机系')insert into Class(class_no,class_name,class_special,class_dept)
values('js9902','计算机99-2','计算机','计算机系')insert into Class(class_no,class_name,class_special,class_dept)
values('js0001','计算机00-1','计算机','计算机系')insert into Class(class_no,class_name,class_special,class_dept)
values('js0002','计算机00-2','计算机','计算机系')insert into Class(class_no,class_name,class_special,class_dept)
values('xx0001','信息00-1','信息','信息系')insert into Class(class_no,class_name,class_special,class_dept)
values('xx0002','信息00-2','信息','信息系')

③ 课程数据表 Course 的内容如下：

insert into Course(course_no,course_name,course_score)
values('01001','计算机基础','3')insert into Course(course_no,course_name,course_score)
values('01002','程序设计语言','5')insert into Course(course_no,course_name,course_score)
values('01003','数据结构','6')insert into Course(course_no,course_name,course_score)
values('02001','数据库原理与应用','6')insert into Course(course_no,course_name,course_score)
values('02002','计算机网络','6')insert into Course(course_no,course_name,course_score)
values('02003','微机原理与应用','8')

④ 选修课程情况数据表 Choice 的内容如下：

insert into Choice(s_no,course_no,score)
values('991101','01001','88.0')insert into Choice(s_no,course_no,score)
values('991102','01001','0.0')
--这里的score书上是空的的，但是前面设置了NOT NYLL，所以给个初始值0.0insert into Choice(s_no,course_no,score)
values('991103','01001','91.0')insert into Choice(s_no,course_no,score)
values('991104','01001','78.0')insert into Choice(s_no,course_no,score)
values('991201','01001','67.0')insert into Choice(s_no,course_no,score)
values('991101','01002','90.0')insert into Choice(s_no,course_no,score)
values('991102','01002','58.0')insert into Choice(s_no,course_no,score)
values('991103','01002','71.0')insert into Choice(s_no,course_no,score)
values('991104','01002','85.0')

⑤ 教师数据表 Teacher 的内容如下：

insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)
values('000001', '李英','女','1964-11-3','讲师')insert into Teacher(t_no,t_name, t_sex,t_birthday,t_title)
values('000002', '王大山', '男', '1955-3-7','副教授')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)
values('000003','张朋','男','1960-10-5','讲师')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)
values('000004', '陈为军', '男', '1970-3-2','助教')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)
values('000005', '宋浩然', '男','1966-12-4','讲师')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)
values('000006','许红霞','女','1951-5-8','副教授')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)
values('000007', '徐永军', '男', '1948-4-8','教授')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)
values('000008', '李桂箐', '女','1940-11-3','教授')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)
values('000009', '王一凡', '女','1962-5-9','讲师')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)
values('000010','田峰','男','1972-11-5','助教')

⑥ 教师任课情况表 Teaching 的内容如下：

insert into Teaching(couse_no,t_no)
values('01001', '000001')insert into Teaching(couse_no,t_no)
values('01002', '000002')insert into Teaching(couse_no,t_no)
values('01003', '000002')insert into Teaching(couse_no,t_no)
values('02001', '000003')insert into Teaching(couse_no,t_no)
values('02002', '000004')insert into Teaching(couse_no,t_no)
values('01001', '000005')insert into Teaching(couse_no,t_no)
values('01002', '000006')insert into Teaching(couse_no,t_no)
values('01003', '000007')insert into Teaching(couse_no,t_no)
values('02001','000007')insert into Teaching(couse_no,t_no)
values('02002', '000008')

（6）利用对象资源管理器的数据库备份功能，将以上建立的数据库 Study 备份到所建立的文件夹
中，并将备份文件拷贝到 U 盘中，以备下面的题目使用。

2．简单的数据查询

本题中所用的数据库是第 1 题中所建立的 Study 数据库。

（1）查询所有同学的基本信息，包括：学号 s_no、班级号 class_no、姓名 s_name、性别 s_sex、出生日期 s_birthday。

SELECT s_no,class_no,s_name,s_sex,s_birthday
FROM Student

（2）查询所有同学，要求显示其学号 s_no、姓名 s_name。

SELECT s_no,s_name
FROM Student

（3）查询所有男同学，要求显示其学号 s_no、姓名 s_name、出生日期 s_birthday。

SELECT s_no,s_name,s_birthday
FROM Student
WHERE s_sex = '男'

（4）查询所有出生日期在“1980－01－01”前的女同学，要求显示其学号 s_no、姓名 s_name、性别 s_sex、出生日期 s_birthday。

SELECT s_no,s_name,s_sex,s_birthday
FROM Student
WHERE s_sex = '女'
AND s_birthday < '1980-01-01'

（5）查询所有姓“李”的男同学，要求显示其学号 s_no、姓名 s_name、性别 s_sex、出生日期s_birthday。

SELECT s_no,s_name,s_sex,s_birthday
FROM Student
WHERE s_sex = '男'
AND s_name like '李%'

（6）查询所有姓名中含有“一”字的同学，要求显示其学号 s_no、姓名 s_name。

SELECT s_no,s_name
FROM Student
WHERE s_sex = '男'
AND s_name like '%一%'

（7）查询所有职称不是“讲师”的教师，要求显示其教师号 t_no、姓名 t_name、职称 t_title。

SELECT t_no,t_name,t_title
FROM Teacher
WHERE t_title != '讲师'

（8）查询虽选修了课程，但未参加考试的所有同学，要求显示出这些同学的学号 s_no。

SELECT s.s_no
FROM Student As s,Choice as Cho
WHERE s.s_no = cho.s_no
AND cho.score = 0.0

（9）查询所有考试不及格的同学，要求显示出这些同学的学号 s_no、成绩 score，并按成绩降序排列。

SELECT s.s_no,c.score
FROM Student As s inner join Choice As c
ON s.s_no = c.s_no
AND c.score < '60.0'
order by c.score desc

（10）查询出课程号为 01001、02001、02003 的所有课程，要求显示出课程号 course_no、课程名称 course_name。（要求用 in 运算符）。

SELECT course_no,course_name
FROM Course
WHERE course_no in('01001','01002','01003')

（11）查询所有在 1970 年出生的教师，要求显示其教师号 t_no、姓名 t_name、出生日期t_birthday。

SELECT t_no,t_name,t_birthday
FROM Teacher
WHERE t_birthday >= '1970-01-01'
AND t_birthday < '1971-01-01'

（12）查询出各个课程号 course_no 及相应的选课人数。

SELECT Choice.course_no,COUNT(Choice.course_no)
FROM Choice inner join Course
ON Choice.course_no = Course.course_no
group by Choice.course_no

（13）查询出教授两门以上课程的教师号 t_no。

SELECT t_no
FROM Teaching
GROUP BY t_no
Having COUNT(t_no) > 2

（14）查询出选修了 01001 课程的学生平均分数、最低分数及最高分数。

SELECT
SUM(score) AS'平均分数',
MAX(score) AS'最高分数',
MIN(score) AS'最低分数'
FROM Choice

（15）查询 1960 年以后出生的，职称为讲师的教师的姓名 t_name、出生日期 t_birthday，并按出生日期升序排列。

SELECT t_name,t_birthday
FROM Teacher
WHERE t_birthday >= '1960-01-01'
AND t_title = '讲师'
order by t_birthday asc

3．复杂数据查询

本题中所用的数据库是第 1 题中所建立的 Study 数据库。

（1）查询所有同学的选课及成绩情况，要求显示学生的学号 s_no、姓名 s_name、课程号course_no和课程的成绩 score。

--有些同学没有选课，在这里列出的是所有同学的，无论选没选课
SELECT s.s_no,s.s_name,c.course_no,c.score
FROM Student
As s left join Choice As c
On s.s_no = c.s_no

（2）查询所有同学的选课及成绩情况，要求显示学生的姓名 s_name、课程名称 course_ name、课程的成绩 score，并将查询结果存放到一个新的数据表 new_table 中。

--在这里把没有选课的也查出来了，如果想查出只选有课的同学，用内连接
SELECT s.s_name,c.course_name,ch.score
into new_table
FROM(Student As s left join Choice As ch on s.s_no=ch.s_no)
left join Course As c
On ch.course_no=c.course_no

（3）查询“计算机 99-1”班的同学的选课及成绩情况，要求显示学生的学号 s_no、姓名 s_name、课程号 course_no、课程名称 course_name、课程的成绩 score。

--这里用到的方法是把四个表联立
SELECT s.s_no,s.s_name,c.course_no,c.course_name,ch.score
FROM(Student As s inner join Choice As ch ON s.s_no=ch.s_no)
inner join Class As cl ON s.class_no=cl.class_no AND cl.class_name='计算机99-1'
inner join Course As c ON ch.course_no=c.course_no

（4）查询所有同学的学分情况（假设课程成绩≥60 分时可获得该门课程的学分），要求显示学生的学号 s_no、姓名 s_name、总学分（将该列定名为：total_score）。（用 JOIN）

SELECT s.s_no,s.s_name,SUM(course_score)As total_score
FROM Student As s inner join Choice As ch ON s.s_no=ch.s_no
inner join Course As c ON ch.course_no=c.course_no
WHERE ch.score>='60.0'
GROUP BY s.s_no,s.s_name

（5）查询所有同学的平均成绩及选课门数，要求显示学生的学号 s_no、姓名 s_name、平均成绩（将该列定名为 average_score）、选课的门数（将该列定名为：choice_num）。

SELECT s.s_no,s_name,AVG(ch.score)As average_score,COUNT(ch.course_no)As choice_num
FROM Student AS s inner join Choice As ch ON s.s_no=ch.s_no
GROUP BY s.s_no,s.s_name

（6）查询所有选修了课程但未参加考试的所有同学及相应的课程，要求显示学生的学号 s_no、姓名 s_name、课程号 course_no、课程名称 course_name。

SELECT s.s_no,s.s_name,c.course_no,c.course_name
FROM Student As s inner join Choice As ch ON s.s_no=ch.s_no AND ch.score='0.0'
inner join Course As c ON ch.course_no=c.course_no

（7）查询所有选修了课程但考试不及格（假设＜60 分为不及格）的所有同学及相应的课程，要求显示学生的学号 s_no、姓名 s_name、课程号 course_no、课程名称 course_name、学分course_score。

SELECT s.s_no,s.s_name,c.course_name,c.course_score
FROM Student As s inner join Choice As ch
ON s.s_no=ch.s_no
inner join Course As c
ON ch.course_no=c.course_no
WHERE ch.score<'60.0'

一门没考，一门不及格

（8）查询选修了课程名为“程序设计语言”的所有同学及成绩情况，要求显示学生的姓名 s_name、课程的成绩 score。（使用 ANY）

SELECT s.s_name,ch.score
FROM Student As s inner join Choice As ch
ON s.s_no=ch.s_no
AND ch.course_no=ANY(SELECT course_no FROM Course WHERE course_name='程序设计语言')

（9）查询“计算机系”的所有同学及成绩情况，要求显示学生的学号 s_no、姓名 s_name、班级名称 class_name、课程号 course_no、课程名称 course_name、课程的成绩 score。

SELECT s.s_no,s.s_name,cl.class_name,c.course_no,c.course_name,ch.score
FROM Student As s left join Choice As ch
ON s.s_no=ch.s_no
left join Course As c
ON ch.course_no=c.course_no
inner join Class As cl
ON cl.class_dept='计算机系' AND s.class_no=cl.class_no

计算机系的所有同学在这里，但有些可是没有课程的，也就没有成绩

（10）查询所有教师的任课情况，要求显示教师姓名 t_name、担任课程的名称 course_name。

SELECT Teacher.t_name,Course.course_name
FROM Teacher inner join Teaching ON Teacher.t_no=Teaching.t_no
INNER JOIN Course ON Teaching.couse_no=Course.course_no

（11）查询所有教师的任课门数，要求显示教师姓名 t_name、担任课程的门数（将该列定名为course_number）。

SELECT Teacher.t_name,COUNT(Teaching.couse_no)AS course_number
FROM Teacher inner join Teaching
ON Teacher.t_no=Teaching.t_no
AND Teaching.couse_no IN(SELECT couse_no FROM Course WHERE Teaching.couse_no=course_no)
GROUP BY Teacher.t_name

（12）查询和“李建国”是同一班级的同学的姓名。（使用子查询）

SELECT s_name
FROM Student
WHERE s_name!='李建国'
AND class_no in(SELECT class_no
FROM Student
WHERE s_name='李建国')

（13）查询没有选修“计算机基础”课程的学生姓名。（用 NOT EXISTS）

SELECT s_name
FROM Student
WHERE
NOT EXISTS(SELECT 1 FROM Course inner join Choice ON course_name='计算机基础'
AND Choice.course_no=Course.course_no
AND Student.s_no=Choice.s_no)

（14）查询主讲“数据库原理与应用”和主讲“数据结构”的教师姓名。（用 UNION）

SELECT t.t_name
FROM Teacher As t inner join Teaching As ti
ON t.t_no=ti.t_no AND ti.couse_no=(SELECT Course.course_no FROM Course WHERE course_name ='数据库原理与应用')
UNION
SELECT t.t_name
FROM Teacher As t inner join Teaching As ti
ON t.t_no=ti.t_no AND ti.couse_no=(SELECT Course.course_no FROM Course WHERE course_name ='数据结构')

（15）查询讲授了所有课程的教师的姓名。

SELECT t.t_name
FROM Teacher As t
WHERE NOT EXISTS(SELECT*
FROM Course As c WHERE NOT EXISTS(SELECT*FROM Teaching As ti
WHERE t.t_no=ti.t_no AND c.course_no=ti.couse_no))

4．用 Transact-SQL 语句定义存储过程

（1）创建一个能向学生表 Student 中插入一条记录的存储过程 Insert_student，该过程需要五个参数，分别用来传递学号、姓名、班级、性别、出生日期五个值。（书上勘误）

CREATE proc Insert_student
@s_no char(6),
@class_no char(6),
@s_name varchar(10),
@s_sex char(2),
@s_birthday datetime
as
begin
insert into Student(s_no,class_no, s_name, s_sex,s_birthday)
values(@s_no,@class_no,@s_name,@s_sex,@s_birthday)
end

（2）写出执行存储过程 Insert_student 的 SQL 语句，向数据表 Student 中插入一个新同学，并提供相应的实参值（实参值由用户自己给出）。

exec Insert_student 
@s_no = '199800', 
@class_no = 'xx0001', 
@s_name='陈帅',
@s_sex= '男',
@s_birthday = '2020-01-01'

(参数自己随便填，当然要在限制内）

（3）创建一个向课程表 Course 中插入一门新课程的存储过程 Insert_course，该存储过程需要三个参数，分别用来传递课程号、课程名、学分，但允许参数“学分”的默认值为 2，即当执行存储过程 Insert_course 时，未给第三个参数“学分”提供实参值时，存储过程将按默认值 2 进行运算。

CREATE proc Insert_course
@course_no char(5),
@course_name char(20),
@course_score numeric(6,2)=2
as
begin
insert into Course(course_no,course_name,course_score)
values(@course_no,@course_name,@course_score)
end

（4）执行存储过程 Insert_course，向课程数据表 Course 中插入一门新课程。分两种情况写出相应的 SQL 命令。
第一种情况：提供三个实参值执行存储过程 Insert_course（三个实参值由用户提供）。
第二种情况：只提供两个实参值执行存储过程 Insert_course，即不提供与参数“学分”对应的实参值。
执行完毕后，查询两种执行存储过程的结果并比较差别。

第一种情况：

exec Insert_course
@course_no = '00007',
@course_name ='操作系统',
@course_score='6'

第二种情况：

exec Insert_course
@course_no = '00008',
@course_name = '计算机组成原理'

（5）创建一个名称为 query_student 的存储过程，该存储过程的功能是从数据表 Student 中根据学号查询某一同学的姓名 s_name、班级 class_no、性别 s_sex、出生日期 s_birthday。

CREATE proc qurey_student
@s_no char(6)
as
begin
SELECT s_name,class_no,s_sex,s_birthday FROM Student WHERE s_no=@s_no
end

（6）执行存储过程 query_student，查询学号为“001101”的姓名 s_name、班级 class_no、性别s_sex、出生日期 s_birthday。

exec qurey_student
@s_no='001101'

5．Transact-SQL 语句自定义触发器

（1）创建一个向学生表 Student 中插入一新同学时能自动列出全部同学信息的触发器Display_trigger。

CREATE trigger Display_trigger
ON dbo.Student
after insert
AS
begin
SELECT*FROM Student
end

（2）执行存储过程 Insert_student，向学生表中插入一新同学，看触发器 Display_trigger 是否被执行。

insert into Student(s_no,class_no,s_name,s_sex,s_birthday)
values('99999', 'js0002', '帅陈','男','2020-01-01')

PS：仅供参考，因个人能力有限，如有错误，请不吝赐教~

Reference

数据库原理及应用教程（第4版微课版）陈志泊-SQLServer2012综合练习/文百度文库@

数据库原理及安全技术教学实验报告SQL实践（四）/文CSDN@王陈锋