SQL Server 练习

练习：《数据库原理》（第四版）

创建数据库 GradeManager(成绩管理)

create database GradeManager;

创建Student、Course、Class、Grade表

切换数据库

use GradeManager;

创建 Studnet 表

CREATE TABLE Student(
Sno CHAR(7)NOT NULL UNIQUE,  /*学号唯一*/
Sname VarChar(20) NOT NULL,  /*学生姓名*/
Ssex CHAR(2) NOT NULL,       /*性别*/
Sage Smallint NULL,          /*年龄*/
Clno CHAR(5) NOT NULL        /*学生所在班级*/
);

创建 Course 表

CREATE TABLE Course(
Cno CHAR(1) NOT NULL UNIQUE,  /*课程号唯一*/
Cname VarChar(20) NOT NULL,  /*课程名称*/
Credit Smallint NULL         /*学分*/
);

创建 Class 表

CREATE TABLE Class(
Clno CHAR(5) NOT NULL UNIQUE,     /*班级号唯一*/
Speciality VarChar(20) NOT NULL,  /*班级所在专业*/
Inyear Char(4) NOT NULL,          /*入校年份*/
Number Integer NULL,              /*班级人数*/
Monitor Char(7) NULL              /*班长学号*/
);

创建 Grade 表

CREATE TABLE Grade(
Sno CHAR(7) NOT NULL,          /*学号*/
Cno Char(1) NOT NULL,          /*课程号*/
Gmark Numeric(4, 1) NULL       /*成绩*/
);

对 Studnet 表添加数据

INSERT INTO Student
VALUES('2000101', '李勇', '男', 20, '00311'),
('2000102', '刘诗晨', '女', 19, '00311'),
('2000103', '王一鸣', '男', 20, '00312'),
('2000104', '张婷婷', '女', 21, '00312'),
('2001101', '李勇敏', '女', 19, '01311'),
('2001102', '贾向东', '男', 22, '01311'),
('2001103', '陈宝玉', '男', 20, '01311'),
('2001104', '张逸凡', '男', 21, '01311');

	Sno	Sname	Ssex	Sage	Clno
1	2000101	李勇	男	20	00311
2	2000102	刘诗晨	女	19	00311
3	2000103	王一鸣	男	20	00312
4	2000104	张婷婷	女	21	00312
5	2001101	李勇敏	女	19	01311
6	2001102	贾向东	男	22	01311
7	2001103	陈宝玉	男	20	01311
8	2001104	张逸凡	男	21	01311

对 Course 表添加数据

INSERT INTO Course
VALUES('1', '数据库', 4),
('2', '离散数学', 3),
('3', '管理信息系统', 2),
('4', '操作系统', 4),
('5', '数据结构', 4),
('6', '数据处理', 2),
('7', 'C语言', 4);

	Cno	Cname	Creditc
1	1	数据库	4
2	2	离散数学	3
3	3	管理信息系统	2
4	4	操作系统	4
5	5	数据结构	4
6	6	数据处理	2
7	7	C语言	4

对 Class 表添加数据

INSERT INTO Class
VALUES('00311', '计算机软件', '2000', 120, '2000101'),
('00312', '计算机应用', '2000', 140, '2000103'),
('01311', '计算机软件', '2001', 220, '2001103');

	Clno	Speciality	Inyear	Number	Monitor
1	00311	计算机软件	2000	120	2000101
2	00312	计算机应用	2000	140	2000103
3	01311	计算机软件	2001	220	2001103

对 Grade 表添加数据

INSERT INTO Grade
VALUES('2000101', '1', 92),
('2000101', '3', NULL),
('2000101', '5', 86),
('2000102', '1', 78),
('2000102', '6', 55),
('2000103', '3', 65),
('2000103', '6', 78),
('2000103', '5', 66),
('2000104', '1', 54),
('2000104', '6', 83),
('2001101', '2', 70),
('2001101', '4', 65),
('2001102', '2', 80),
('2001102', '4', NULL),
('2000103', '1', 83),
('2000103', '2', 76),
('2000103', '4', 56),
('2000103', '7', 88);

Sno	Cno	Gmark
2000101	1	92.0
2000101	3	NULL
2000101	5	86.0
2000102	1	78.0
2000102	6	55.0
2000103	3	65.0
2000103	6	78.0
2000103	5	66.0
2000104	1	54.0
2000104	6	83.0
2001101	2	70.0
2001101	4	65.0
2001102	2	80.0
2001102	4	NULL
2000103	1	83.0
2000103	2	76.0
2000103	4	56.0
2000103	7	88.0

（1）给 Studnet 表增加属性 Nation，数据类型为 Varchar(20);

ALTER TABLE Student
ADD Nation Varchar(20) NULL;

	Sno	Sname	Ssex	Sage	Clno	Nation
1	2000101	李勇	男	20	00311	NULL
2	2000102	刘诗晨	女	19	00311	NULL
3	2000103	王一鸣	男	20	00312	NULL
4	2000104	张婷婷	女	21	00312	NULL
5	2001101	李勇敏	女	19	01311	NULL
6	2001102	贾向东	男	22	01311	NULL
7	2001103	陈宝玉	男	20	01311	NULL
8	2001104	张逸凡	男	21	01311	NULL

（2）删除 Studnet 表中新增的属性Nation;

ALTER TABLE Student
DROP COLUMN Nation;

	Sno	Sname	Ssex	Sage	Clno
1	2000101	李勇	男	20	00311
2	2000102	刘诗晨	女	19	00311
3	2000103	王一鸣	男	20	00312
4	2000104	张婷婷	女	21	00312
5	2001101	李勇敏	女	19	01311
6	2001102	贾向东	男	22	01311
7	2001103	陈宝玉	男	20	01311
8	2001104	张逸凡	男	21	01311

（3）向成绩表中插入记录(“2001110”, “3”, 80);

INSERT INTO Grade
VALUES('2001110', '3', 80);

Sno	Cno	Gmark
2000101	1	92.0
2000101	3	NULL
2000101	5	86.0
2000102	1	78.0
2000102	6	55.0
2000103	3	65.0
2000103	6	78.0
2000103	5	66.0
2000104	1	54.0
2000104	6	83.0
2001101	2	70.0
2001101	4	65.0
2001102	2	80.0
2001102	4	NULL
2000103	1	83.0
2000103	2	76.0
2000103	4	56.0
2000103	7	88.0
2001110	3	80.0

（4）将学号为 “2001110” 的学生的成绩修改为70分;

UPDATE Grade
SET Gmark = 70
WHERE Sno = '2001110';

Sno	Cno	Gmark
2000101	1	92.0
2000101	3	NULL
2000101	5	86.0
2000102	1	78.0
2000102	6	55.0
2000103	3	65.0
2000103	6	78.0
2000103	5	66.0
2000104	1	54.0
2000104	6	83.0
2001101	2	70.0
2001101	4	65.0
2001102	2	80.0
2001102	4	NULL
2000103	1	83.0
2000103	2	76.0
2000103	4	56.0
2000103	7	88.0
2001110	3	70.0

（5）删除学号为 “2001110” 的学生的成绩记录;

DELETE FROM Grade
WHERE Sno= '2001110';

Sno	Cno	Gmark
2000101	1	92.0
2000101	3	NULL
2000101	5	86.0
2000102	1	78.0
2000102	6	55.0
2000103	3	65.0
2000103	6	78.0
2000103	5	66.0
2000104	1	54.0
2000104	6	83.0
2001101	2	70.0
2001101	4	65.0
2001102	2	80.0
2001102	4	NULL
2000103	1	83.0
2000103	2	76.0
2000103	4	56.0
2000103	7	88.0

（6）在学生表的Cno属性上创建一个名为IX_Class的索引，以班级号的升序排序;

CREATE INDEX IX_Class
ON student(clno);

（7）删除 IX_Class 索引。

DROP INDEX student.IX_Class;

（8）找出所有被学生选修了的课程号;

SELECT DISTINCT Cno 课程号 FROM Grade;

	课程号
1	1
2	2
3	3
4	4
5	5
6	6
7	7

（9）找出01311班女学生的个人信息

SELECT *
FROM Student
WHERE Clno='01311' AND Ssex='女'

	Sno	Sname	Ssex	Sage	Clno
1	2001101	李勇敏	女	19	01311

（10）找出 01311 班和 01312 班的学生姓名、性别、出生年份;

SELECT sname 学生姓名, ssex 性别, 2021-sage 出生年份
FROM Student
WHERE Clno='01311' or Clno='01312'

	学生姓名	性别	出生年份
1	李勇敏	女	2002
2	贾向东	男	1999
3	陈宝玉	男	2001
4	张逸凡	男	2000

（11）找出所有姓李的学生的个人信息

SELECT * FROM Student
WHERE Sname like '李%';

	Sno	Sname	Ssex	Sage	Clno
1	2000101	李勇	男	20	00311
2	2001101	李勇敏	女	19	01311

（12）找出学生李勇所在班级的学生人数

SELECT COUNT(*) 班级人数
FROM Student
WHERE Clno in
(SELECT Clno 
FROM Student
WHERE Sname='李勇')

	班级人数
1	2

（13）找出课程名为操作系统的平均成绩、最高分、最低分;

 SELECT AVG(Gmark) 平均成绩, MAX(Gmark) 最高分, MIN(Gmark) 最低分
 FROM Grade
 WHERE Cno in
 (SELECT Cno 
 From Course
 WHERE Cname='操作系统')

	平均成绩	最高分	最低分
1	60.500000	65.0	56.0

（14）找出选修了课程的学生人数;

SELECT COUNT(distinct sno) 学生人数
FROM Grade

	学生人数
1	6

（14）找出选修了课程操作系统的学生人数;

SELECT COUNT(distinct sno) 学生人数
FROM Grade
WHERE Cno in
(SELECT Cno 
FROM Course
WHERE Cname='操作系统')

	学生人数
1	3

（15）找出 2000 级计算机软件班的成绩为空的学生姓名。

SELECT Sname '姓名'
FROM Student,Class
WHERE Student.Clno=Class.Clno 
	  and Inyear='2000'
      and Speciality='计算机软件'
      and sno NOT IN(SELECT sno 
                     FROM Grade)
UNION
SELECT Sname '姓名'
FROM Student,Class
WHERE Student.Clno=Class.Clno 
	  and Inyear='2000'
      and Speciality='计算机软件'
      and sno IN(SELECT sno 
                     FROM Grade 
					 where Gmark is NULL);

	姓名
1	李勇

（16）找出与李勇在同一个班级的学生信息;

SELECT * FROM Student
WHERE Sname='李勇' OR Clno IN (
SELECT Clno
FROM Student
WHERE Sname='李勇')

	Sno	Sname	Ssex	Sage	Clno
1	2000101	李勇	男	20	00311
2	2000102	刘诗晨	女	19	00311

（17）找出所有与学生李勇有相同选修课程的学生信息;

SELECT * 
FROM Student
WHERE Sname='李勇' OR exists(
SELECT * 
FROM Grade
WHERE Student.Sno=Grade.Sno AND Grade.Cno IN(
SELECT Cno from Grade
WHERE Sno IN(
SELECT Sno 
FROM Student
WHERE Sname='李勇')))

	Sno	Sname	Ssex	Sage	Clno
1	2000101	李勇	男	20	00311
2	2000102	刘诗晨	女	19	00311
3	2000103	王一鸣	男	20	00312
4	2000104	张婷婷	女	21	00312

（18）找出年龄介于学生李勇和25岁之间的学生信息(已知李勇的年龄小于25岁);

SELECT * 
FROM Student 
WHERE Sage >  (
SELECT Sage 
FROM Student 
WHERE Sname='李勇') AND Sage < 25

	Sno	Sname	Ssex	Sage	Clno
1	2000104	张婷婷	女	21	00312
2	2001102	贾向东	男	22	01311
3	2001104	张逸凡	男	21	01311

（19）找出选修了课程操作系统的学生学号和姓名;

SELECT Sno 学号,Sname 姓名 
FROM Student 
WHERE exists (
SELECT * 
FROM Grade  
WHERE Cno IN(
SELECT Cno 
FROM Course 
WHERE Cname='操作系统') AND Student.Sno=Grade.Sno)

	学号	姓名
1	2000103	王一鸣
2	2001101	李勇敏
3	2001102	贾向东

（20）找出没有选修1号课程的所有学生姓名;

SELECT Sname 姓名 
FROM Student 
WHERE NOT exists (
SELECT * 
FROM Grade  
WHERE Student.Sno=Grade.Sno AND Cno=1)

	姓名
1	李勇敏
2	贾向东
3	陈宝玉
4	张逸凡

（21）找出选修了全部课程的学生姓名。

SELECT Sname 姓名 
FROM Student 
WHERE NOT EXISTS (
SELECT Cno 
FROM Course EXCEPT 
SELECT Cno 
FROM Grade 
WHERE Student.Sno=Grade.Sno)

或者

SELECT Sname 姓名 
FROM Student 
WHERE Student.Sno IN(
SELECT Grade.Sno
FROM Grade 
GROUP BY Grade.Sno
HAVING COUNT(Grade.Cno) = (
SELECT COUNT(Course.Cno)
FROM Course
)) 

	姓名
1	王一鸣

（22）查询选修了3号课程的学生学号及成绩，并按成绩的降序排列;

SELECT Sno 学号, Gmark 成绩 
FROM Grade 
WHERE Cno=3 ORDER BY Gmark DESC

	学号	成绩
1	2000103	65.0
2	2000101	NULL

（23）查询全体学生信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列;

SELECT * 
FROM Student 
ORDER BY Clno ASC, Sage DESC

	Sno	Sname	Ssex	Sage	Clno
1	2000101	李勇	男	20	00311
2	2000102	刘诗晨	女	19	00311
3	2000104	张婷婷	女	21	00312
4	2000103	王一鸣	男	20	00312
5	2001102	贾向东	男	22	01311
6	2001104	张逸凡	男	21	01311
7	2001103	陈宝玉	男	20	01311
8	2001101	李勇敏	女	19	01311

（24）求每个课程号及相应的选课人数;

SELECT Grade.Cno 课程号, COUNT(Grade.Cno) 选课人数  
FROM Grade JOIN Course ON Grade.Cno=Course.Cno GROUP BY Grade.Cno

	课程号	选课人数
1	1	4
2	2	3
3	3	2
4	4	3
5	5	2
6	6	3
7	7	1

（25）查询选修了3门以上课程的学生学号;

SELECT Sno 学号 
FROM Grade GROUP BY Sno HAVING COUNT(Sno)>3 

	学号
1	2000103

（26）将01311班的全体学生的成绩置零;

UPDATE Grade 
SET Gmark=0 
WHERE Sno in (
SELECT Sno 
FROM Student 
WHERE Clno='01311')

	Sno	Cno	Gmark
1	2000101	1	92.0
2	2000101	3	NULL
3	2000101	5	86.0
4	2000102	1	78.0
5	2000102	6	55.0
6	2000103	3	65.0
7	2000103	6	78.0
8	2000103	5	66.0
9	2000104	1	54.0
10	2000104	6	83.0
11	2001101	2	0.0
12	2001101	4	0.0
13	2001102	2	0.0
14	2001102	4	0.0
15	2000103	1	83.0
16	2000103	2	76.0
17	2000103	4	56.0
18	2000103	7	88.0

（27）删除2001级计算机软件的全体学生的选课记录;

DELETE
FROM Grade 
WHERE Sno IN (
SELECT Sno 
FROM Student 
WHERE Clno IN (
SELECT Clno 
FROM Class 
WHERE Speciality='计算机软件' AND Inyear='2001'))

	Sno	Cno	Gmark
1	2000101	1	92.0
2	2000101	3	NULL
3	2000101	5	86.0
4	2000102	1	78.0
5	2000102	6	55.0
6	2000103	3	65.0
7	2000103	6	78.0
8	2000103	5	66.0
9	2000104	1	54.0
10	2000104	6	83.0
11	2000103	1	83.0
12	2000103	2	76.0
13	2000103	4	56.0
14	2000103	7	88.0

（28）学生李勇已退学,从数据库中删除有关他的记录;

DELETE 
FROM Grade 
WHERE Sno IN (
SELECT Sno 
FROM Student 
WHERE Sname='李勇')  

	Sno	Cno	Gmark
1	2000102	1	78.0
2	2000102	6	55.0
3	2000103	3	65.0
4	2000103	6	78.0
5	2000103	5	66.0
6	2000104	1	54.0
7	2000104	6	83.0
8	2000103	1	83.0
9	2000103	2	76.0
10	2000103	4	56.0
11	2000103	7	88.0

UPDATE Class 
SET Number=Number-1 
WHERE Clno IN (
SELECT Clno 
FROM Student 
WHERE Sname='李勇')  

	Clno	Speciality	Inyear	Number	Monitor
1	00311	计算机软件	2000	119	2000101
2	00312	计算机应用	2000	140	2000103
3	01311	计算机软件	2001	220	2001103

UPDATE Class 
SET Monitor=CASE  WHEN Monitor=(
SELECT Sno FROM Student WHERE Sname='李勇')
THEN ''
END 
FROM Class 
WHERE Clno IN(
SELECT Clno 
FROM Student 
WHERE Sname='李勇')  

	Clno	Speciality	Inyear	Number	Monitor
1	00311	计算机软件	2000	119	NULL
2	00312	计算机应用	2000	140	2000103
3	01311	计算机软件	2001	220	2001103

DELETE FROM 
Student WHERE Sname='李勇'

	Sno	Sname	Ssex	Sage	Clno
1	2000102	刘诗晨	女	19	00311
2	2000103	王一鸣	男	20	00312
3	2000104	张婷婷	女	21	00312
4	2001101	李勇敏	女	19	01311
5	2001102	贾向东	男	22	01311
6	2001103	陈宝玉	男	20	01311
7	2001104	张逸凡	男	21	01311

（29）对每个班,求学生的平均年龄,并把结果存入数据库;

ALTER TABLE Class   
ADD Cage SMALLINT NULL

	Clno	Speciality	Inyear	Number	Monitor	Cage
1	00311	计算机软件	2000	119	NULL	NULL
2	00312	计算机应用	2000	140	2000103	NULL
3	01311	计算机软件	2001	220	2001103	NULL

UPDATE Class 
SET Cage=CASE WHEN Clno='00311' THEN (
SELECT AVG(Sage) 
FROM Student 
WHERE Clno='00311') WHEN Clno='00312' THEN (
SELECT AVG(Sage) 
FROM Student 
WHERE Clno='00312') WHEN Clno='01311' THEN (
SELECT AVG(Sage) 
FROM Student WHERE Clno='01311') END 
FROM Class 

	Clno	Speciality	Inyear	Number	Monitor	Cage
1	00311	计算机软件	2000	119	NULL	19
2	00312	计算机应用	2000	140	2000103	20
3	01311	计算机软件	2001	220	2001103	20

（30）建立01311班选修了1号课程的学生视图Stu_01311_1;

CREATE VIEW Stu_01311_1  
AS SELECT *    
FROM Student    
WHERE Sno IN(
SELECT Sno                 
FROM Grade                 
WHERE Cno = 1) AND Clno = '01311';

	Sno	Sname	Ssex	Sage	Clno

（31）建立01311班选修了1号课程并且不及格的学生视图Stu_01311_2;

CREATE VIEW Stu_01311_2 
AS SELECT *    
FROM Student    
WHERE Sno IN(
SELECT Sno                
FROM Grade                 
WHERE Cno = 1 AND Gmark < 60) AND Clno = '01311'; 

	Sno	Sname	Ssex	Sage	Clno

（32）建立视图Stu_year，由学生学号、姓名、出生年份组成；

  CREATE VIEW Stu_year 
  AS SELECT Sno AS '学号',Sname AS '姓名',(2014-Sage) AS '出生年份'    
  FROM Student;

	学号	姓名	出生年份
1	2000102	刘诗晨	1995
2	2000103	王一鸣	1994
3	2000104	张婷婷	1993
4	2001101	李勇敏	1995
5	2001102	贾向东	1992
6	2001103	陈宝玉	1994
7	2001104	张逸凡	1993

（33）查询1990年以后出生的学生姓名;

SELECT 姓名 
FROM dbo.Stu_year 
WHERE '出生年份' > '1990';

	姓名
1	刘诗晨
2	王一鸣
3	张婷婷
4	李勇敏
5	贾向东
6	陈宝玉
7	张逸凡

（34）查询01311班选修了1号课程并且成绩不及格的学生的学号、姓名、出生年份;

SELECT * 
FROM dbo.Stu_year 
WHERE 学号 IN (
SELECT Sno               
FROM Stu_01311_2); 

	学号	姓名	出生年份

创建Course表

CREATE TABLE Coures
(
	Cno Char(1) PRIMARY KEY,         /*主码*/
	Cname VarChar(20) NOT NULL,
	Credit Smallint Check(Credit IN('1', '2', '3', '4', '5', '6'))
);

或

CREATE TABLE Coures
(
	Cno Char(1),         /*主码*/
	Cname VarChar(20) NOT NULL,
	Credit Smallint Check(Credit IN('1', '2', '3', '4', '5', '6')),
	PRIMARY KEY(Cno)
);

创建Class表

CREATE TABLE Class
(
	Clno Char(5) PRIMARY KEY,    /*主码*/
	Speciality VarChar(20) Not Null,
	Inyear Char(4) Not Null,
	Number Integer Check(Number > 1 And Number < 100),
	Monitor Char(7)
);

或

CREATE TABLE Class
(
	Clno Char(5),    /*主码*/
	Speciality VarChar(20) Not Null,
	Inyear Char(4) Not Null,
	Number Integer Check(Number > 1 And Number < 100),
	Monitor Char(7),
	PRIMARY KEY(Clno)
)

创建Student表

CREATE TABLE Student(
	Sno Char(7) NOT NULL PRIMARY KEY,                                  /*学号唯一  主码*/
	Sname VarChar(20) NOT NULL,                                        /*学生姓名*/
	Ssex Char(2) NOT NULL CHECK(Ssex In('男', '女')) DEFAULT '男',     /*性别*/
	Sage Smallint NULL CHECK(Sage > 14 AND Sage < 65),                /*年龄*/
	Clno Char(5) NOT NULL,
	FOREIGN KEY(Clno) REFERENCES Class(Clno) ON UPDATE CASCADE
)

为 Class表 的 Monitor 属性添加 外部码

ALTER TABLE Class
ADD CONSTRAINT Monitor FOREIGN KEY (Monitor) REFERENCES Student(Sno)

注：使用 PRIMARY KEY关键字定义主码有两种方式：

• 1、在属性后添加关键字
• 2、在属性表中加入额外的定义主码的字句

使用 PRIMARY KEY关键字定义主码需要注意的地方：

• 1、主码的属性值不能重复出现
• 2、主码的属性值不能为空
• 3、一个表中只能有一个主码

例如下图

当主码的属性值重复时，会出现重复键值错误。