广药数据库实验答案参考.doc

专业资料
--实验三 参考答案
（一）、数据库、表的创建及删除
1.将数据库bookdb的相关属性（系统默认设置），填入下表：
项目 内容
数据库所有者 XP-201104281226\Administrator(Windows身份登录SQL server)
数据库名称 bookdb

数据库逻辑文件名 bookdb
数据文件物理名 D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\bookdb.mdf（本机SQL SERVER系统安装目录在D盘）
数据库初始大小 3MB
数据文件最大值 不限制增长
数据文件增长量 1MB

日志逻辑文件名 bookdb_log
日志文件物理名 D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\bookdb_log.ldf（本机SQL SERVER系统安装目录在D盘）
日志文件初始大小 1MB
日志文件最大值 2,097,152MB
日志文件增长量 10%
*/

--根据“二、实验内容”的要求，使用T-SQL命令创建/删除数据库EDUC，创建数据表student、course、sc。

--2.删除bookdb数据库。
DROP DATABASE bookdb

--3.创建数据库EDUC。
CREATE DATABASE EDUC
ON PRIMARY
(
NAME='EDUC_data',
FILENAME='C:\EDUC_data.mdf',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=1MB
)

LOG ON
(
NAME='EDUC_log',
FILENAME='C:\EDUC_log.ldf',
SIZE=1MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10%
)

--4.创建数据表Student。
USE EDUC
CREATE TABLE Student
(
Sno varchar(20) NOT NULL CONSTRAINT PK_Student PRIMARY KEY,
Sname varchar(10) NOT NULL,
Sage int,
Ssex varchar(2),
Sdept varchar(30)
)

--5.创建数据表Course。
CREATE TABLE Course
(
Cno varchar(15) NOT NULL CONSTRAINT PK_Course PRIMARY KEY,
Cname varchar(30),
Cpno varchar(15),
Ccredit int
)

--6.创建数据表SC。
CREATE TABLE SC
(
Sno varchar(20) NOT NULL,
Cno varchar(15) NOT NULL,
Grade DECIMAL(9,2),
CONSTRAINT PK_SC PRIMARY KEY(Sno,Cno)
)

--（二）、修改基本表的定义
--1．修改列属性
--（1）用SSMS将Student表中的Sage字段设为不能为空（not null）。
ALTER TABLE Student
ALTER COLUMN Sage int NOT NULL

--（2）用SQL语句将Student表中的属性Sno varchar(20)改成char(8)类型。
--先删除Sno上的各种约束（此例中Sno上的约束有：主键）
ALTER TABLE Student DROP CONSTRAINT PK_Student --CONSTRAINT 也可省略
--再修改Sno列
ALTER TABLE Student ALTER COLUMN Sno CHAR(8) NOT NULL --需设置为不允许空，否则不能在该列上创建主键
--最后，恢复（添加）Sno上的主键约束
ALTER TABLE Student ADD CONSTRAINT PK_Student PRIMARY KEY(Sno)

--2．添加列
--（1）用SSMS在Course表中添加一列year，类型为varchar（4），默认置为空。
ALTER TABLE Course
ADD year varchar(4) NULL

--（2）用SQL语句在year字段添加约束，year 的属性值在2006-2012之间。
ALTER TABLE Course
ADD CONSTRAINT CK_year CHECK(year BETWEEN 2006 AND 2012)

--3．删除列
--（1）用SQL语句将Course表中的year字段删除。
--先删除year上的各种约束（CHECK约束）
ALTER TABLE Course
DROP CONSTRAINT CK_year

--再删除year
ALTER TABLE Course
DROP COLUMN year --COLUMN不能省略

--实验四 参考答案
--1. 统计各门课程所选修的人数，并把结果存储在一张新表中。（提示：用Select……Into……，group by）
SELECT Course_id,COUNT(*) AS 选修人数 INTO Course_num
FROM StudentGrade
GROUP BY Course_id

--2．查询男生的资料。
SELECT *
FROM Student
WHERE Stu_sex='男'

--3．查询所有计算机系的班级信息。
--WHERE连接查询
SELECT *
FROM Class,Deparment
WHERE Class.depar_id=Deparment.depar_id AND Depar_name='计算机系'
--JOIN连接查询
SELECT *
FROM Class JOIN Deparment ON Class.depar_id=Deparment.depar_id
WHERE Depar_name='计算机系'
--嵌套查询
SELECT * --父查询：根据子查询找出的系别编号，查找该系别编号的班级
FROM Class
WHERE Depar_id=(SELECT Depar_id --子查询：找出计算机系的系别编号
FROM Deparment
WHERE Depar_name='计算机系')

--4．查询艾老师所教的课程号。(也可用嵌套查询)
SELECT Course_id,*
FROM CourseTeacher JOIN Teacher ON CourseTeacher.Teac_id=Teacher.Teac_id
WHERE Teac_name like '艾%'

--5．查询年龄小于30岁的女同学的学号和姓名。
--GETDATE()为获取系统时间的函数，YEAR()为获取“年”,MONTH()为获取“月”,DAY()为获取“日”。
SELECT *
FROM Student
WHERE (YEAR(GETDATE())-YEAR(Birthday))<30 AND Stu_sex='女'

--6．查询所有被选修的课程号。
SELECT DISTINCT Course_id --DISTINCT作用是去除重复行
FROM StudentGrade --存储在StudentGrade表中的数据均为选修数据，因此该表中的课程号即为被选修的课程号

--7．在学生基本信息表Student中检索学生的姓名和出生年份，输出的列名为STUDENT_NAME和BIRTH_YEAR。
SELECT Stu_name AS STUDENT_NAME,YEAR(Birthday) AS BIRTH_YEAR --AS作用是起别名
FROM Student

--8．在StudnetGrade中，求选修课程“0511”且分数排名为前10%学生的学号和得分。
SELECT TOP 10 PERCENT Stu_id,Grade
FROM StudentGrade
WHERE Course_id='0511'
ORDER BY Grade DESC

--9．查询选修课程号为“0109”或“0111”的学生学号。
SELECT Stu_id
FROM StudentGrade
WHERE Course_id='0109' OR Course_id='0111'
--或者
SELECT Stu_id
FROM StudentGrade
WHERE Course_id IN('0109','0111') --IN表示满足指定集合中的任意一个。

--10．查询课程“0101”的成绩在80与90之间的同学的学号。
SELECT Stu_id
FROM StudentGrade
WHERE Course_id='0101' AND (Grade BETWEEN 80 AND 90)

--11．查询平均成绩都在80分以上的学生学号及平均成绩。
SELECT Stu_id,AVG(Grade) AS 平均成绩
FROM StudentGrade
GROUP BY Stu_id HAVING AVG(Grade)>80

--实验五 参考答案
--1．找出所有任教“数据库”的教师的姓名。
SELECT teac_name
FROM teacher JOIN courseteacher ON teacher.teac_id=courseteacher.teac_id
JOIN course ON courseteacher.course_id=course.course_id
WHERE course_name='数据库'
--此处用连接查询，三个表连接后形成一个大表，然后从中筛选。
--练菲彰上了两个班级的数据库，因此有两条记录。可用DISTINCT去除重复行

--嵌套查询
SELECT teac_name --根据子查询得到的结果在teacher中找出上“数据库”的老师，上数据库的老师只有一个，故只有一条记录。
FROM teacher
WHERE teac_id in(SELECT teac_id FROM courseteacher
WHERE course_id=(SELECT course_id FROM course
WHERE course_name='数据库'))

--2．取出学号为“980101011”的学生选修的课程号和课程名。
SELECT course.course_id,course_name
FROM course JOIN studentgrade ON course.course_id=studentgrade.course_id
WHERE stu_id='980101001'

--嵌套查询
SELECT course_id,course_name
FROM course
WHERE course_id in(SELECT course_id FROM studentgrade WHERE stu_id='980101001')

--3．“涂杰杰”所选修的全部课程号及成绩。（注意：school中有同名，即有两名学生叫“涂杰杰”。）
SELECT course_id,grade
FROM studentgrade JOIN student ON studentgrade.stu_id=student.stu_id
WHERE stu_name='涂杰杰'

--嵌套查询
SELECT course_id,grade
FROM studentgrade
WHERE stu_id in(SELECT stu_id FROM student WHERE stu_name='涂杰杰')

--4．C语言成绩比数据结构成绩好的学生（自身连接）。
SELECT *
FROM studentgrade AS a JOIN studentgrade AS b ON a.stu_id=b.stu_id
WHERE a.course_id=(SELECT course_id FROM course WHERE course_name='C语言')
AND b.course_id=(SELECT course_id FROM course WHERE course_name='数据结构')
AND a.grade>b.grade

--连接查询，比嵌套查询复杂
SELECT SG1.Stu_id,C1.Course_name,SG1.Grade,C2.Course_name, SG2.Grade
FROM StudentGrade AS SG1 JOIN Course AS C1 ON SG1.Course_id=C1.Course_id --此连接得到:每位学生的选课情况及所选课程信息,作为一个新表
JOIN StudentGrade AS SG2 ON SG1.Stu_id=SG2.Stu_id --此连接得到:将相同内容的表,表进行自身连接
JOIN Course AS C2 ON SG2.Course_id=C2.Course_id --此连接得到:每位学生的选课情况及所选课程信息,作为一个新表
WHERE SG1.Grade>SG2.Grade AND C1.Course_name='C语言' AND C2.Course_name='数据结构'

--5．显示所有课程的选修情况（外连接）。
SELECT *
FROM course LEFT JOIN studentgrade ON course.course_id=studentgrade.course_id
ORDER BY course.course_id --为了便于观察。将每门课程的选修情况列在一起

--或：列出各门课程的选修人数，无人选的课程的选修人数为0。
--COUNT(stu_id)，此处不能为*，COUNT(*)为统计有多少行（记录）。因为course与studentgrade左连接后，每门课程都至少有一条行（记录）。
SELECT course.course_id,COUNT(stu_id) AS 选修人数
FROM course LEFT JOIN studentgrade ON course.course_id=studentgrade.course_id
GROUP BY course.course_id

--6．检索选修课程号为“0109”或“0111”的学生学号、姓名和所在班级。
SELECT student.stu_id,stu_name,class_id
FROM student JOIN studentgrade ON student.stu_id=studentgrade.stu_id
WHERE course_id in('0109','0111')
--若列出班级名称，则需在于Class表连接
SELECT student.stu_id,stu_name,class_name
FROM student JOIN studentgrade ON student.stu_id=studentgrade.stu_id
JOIN class ON student.class_id=class.class_id
WHERE course_id in('0109','0111')
--当多个表（A,B,C）进行连接时，B表同时具有A表和C表中的某一字段，最好将B表作为连接三个表的中介，
--如下，将student作为连接studentgrade和class的中介
SELECT student.stu_id,stu_name,class_name
FROM studentgrade JOIN student ON studentgrade.stu_id=student.stu_id
JOIN class ON student.class_id=class.class_id
WHERE course_id in('0109','0111')

--嵌套查询
SELECT student.stu_id,stu_name,class_id
FROM student
WHERE stu_id in(SELECT stu_id FROM studentgrade
WHERE course_id in('0109','0111')
)

--7．查询“0203”课程的最高分的学生的学号。
SELECT stu_id
FROM studentgrade
WHERE course_id='0203'
AND grade=(SELECT MAX(grade) FROM studentgrade --子查询的作用是：找出“0203”课程的最高分
WHERE course_id='0203')

--如果最高分的同学有两个以上(分数相同)，此方法无法实现。
SELECT TOP 1 Stu_id
FROM StudentGrade
WHERE Course_id='0203'
ORDER BY Grade DESC

--8．没有选修以“01”开头的课程的学生信息。（用子查询完成。提示not in或not exists）
SELECT *
FROM Student
WHERE stu_id NOT IN (SELECT stu_id FROM studentgrade
WHERE course_id like'01%')

SELECT *
FROM Student
WHERE NOT EXISTS (SELECT * FROM studentgrade
WHERE Student.stu_id=studentgrade.stu_id --EXISTS查询为相关子查询，依赖父表(student),需将两表连接
AND course_id like'01%')

--9．找出“苏贤兴”同学所学课程的名称和成绩。(请使用连接查询和嵌套查询分别来完成)
SELECT course_name,grade
FROM course JOIN studentgrade ON course.course_id=studentgrade.course_id
JOIN student ON studentgrade.stu_id=student.stu_id
WHERE stu_name='苏贤兴'

--嵌套查询
SELECT course_name,grade
FROM course JOIN studentgrade ON course.course_id=studentgrade.course_id
WHERE stu_id in(SELECT stu_id FROM student
WHERE stu_name='苏贤兴')

--实验六参考答案

--1．统计年龄大于30岁的学生的人数。
SELECT COUNT(*)
FROM Student
WHERE (year(getdate())-year(Birthday))>30

--2．统计数据结构有多少人80分或以上。
--嵌套查询
-- SELECT COUNT(*) as 人数
-- FROM StudentGrade
-- WHERE Grade>80 AND Course_id=(SELECT Course_id
-- FROM Course
-- WHERE Course_name='数据结构')
--连接查询
SELECT COUNT(*) as 人数
FROM StudentGrade,Course
WHERE Grade>80 AND StudentGrade.Course_id=Course.Course_id
AND Course_name='数据结构'

--3．统计各系开设班级的数目(系名称、班级数目)，并创建结果表。
SELECT Depar_name,COUNT(*) AS '班级数目' INTO Class_Count
FROM Deparment,Class
WHERE Class.Depar_id=Deparment.Depar_id
GROUP BY Deparment.Depar_name

--4．找出最多人选修的三门课的课程编号，以及统计选修人数，并按人数降序排列。
SELECT TOP 3 Course_id,COUNT(*) AS '选修人数'
FROM StudentGrade
GROUP BY Course_id
ORDER BY 选修人数 DESC
--或：
--ORDER BY COUNT(*) DESC

--5．统计每科目的最高分、最低分，平均分、总分，并以中文列名显示。
SELECT Course_name AS '课程名',MAX(Grade) 最高分,MIN(Grade) 最低分,AVG(ALL(Grade)) AS '平均分',SUM(ALL(Grade)) AS '总分'
FROM StudentGrade,Course
WHERE StudentGrade.Course_id=Course.Course_id
GROUP BY Course_name

--6．所有成绩都在70分以上的学生姓名（提示：使用子查询）
SELECT Stu_id,Stu_name
FROM Student
WHERE Stu_id IN
(SELECT Stu_id
FROM StudentGrade
GROUP BY Stu_id HAVING MIN(Grade)>=70) --注意：HAVING MIN(Grade)>=70 是关键。

--注意：MIN(Grade)是关键。此处分组条件不可以是Student.Stu_id。
SELECT Stu_id,Stu_name
FROM Student
WHERE (SELECT MIN(Grade)
FROM StudentGrade WHERE Student.Stu_id=StudentGrade.Stu_id GROUP BY StudentGrade.Stu_id )>=70

--或
SELECT Stu_id,Stu_name
FROM Student
WHERE Stu_id in
(select a.Stu_id from StudentGrade a --关键：Stu_id要来自StudentGrade表，否则就会出现问题：无法剔除没有选课的学生
where 70<=all (SELECT Grade FROM StudentGrade b WHERE a.Stu_id=b.Stu_id ))

--或
SELECT Stu_id,Stu_name
FROM Student
WHERE Stu_id in
(SELECT Stu_id FROM StudentGrade --关键：Stu_id要来自StudentGrade表，否则就会出现问题：无法剔除没有选课的学生
WHERE NOT EXISTS
(SELECT * FROM StudentGrade WHERE Student.Stu_id=StudentGrade.Stu_id AND Grade<70))

--7．“数据库”课程得最高分的学生的姓名、性别（提示：使用子查询）
SELECT Stu_name,Stu_sex
FROM Student JOIN StudentGrade ON Student.Stu_id=StudentGrade.Stu_id
WHERE Grade=(SELECT MAX(Grade) --数据库课程号的最高成绩
FROM StudentGrade JOIN Course ON StudentGrade.Course_id=Course.Course_id
WHERE Course_name='数据库')
AND Course_id=(SELECT Course_id
FROM Course
WHERE Course_name='数据库')

--8．至少选修了两门课及以上的学生姓名和性别
SELECT Stu_name,Stu_sex
FROM Student
WHERE Stu_id IN (SELECT Stu_id FROM StudentGrade
GROUP BY Stu_id HAVING COUNT(Course_id)>=2)

--9．检索至少选修课程“数据结构”和“C语言”的学生学号
SELECT Stu_id
FROM StudentGrade JOIN Course ON StudentGrade.Course_id=Course.Course_id
WHERE Course_name='数据结构' AND Stu_id IN
(SELECT Stu_id
FROM StudentGrade JOIN Course ON StudentGrade.Course_id=Course.Course_id
WHERE Course_name='C语言' )

--10．列出所有班名、班主任、班长、系名。（请使用连接查询；进一步考虑使用外连接，因为很多班级可能是没有班长的，考虑需要显示所有班级的信息）
--列出所有班名，班主任，班长，系名
/*SELECT Class_name,Stu_name,Teac_name,Depar_name
FROM Student JOIN Class ON Monitor=Student.Stu_id
JOIN Teacher ON Director=Teacher.Teac_id
JOIN Deparment ON Class.Depar_id=Deparment.Depar_id */

--或：(此方法的查询结果中还包括Monitor为NULL的记录)
SELECT Class_name,Stu_name,Teac_name,Depar_name
FROM Class LEFT JOIN Student ON Monitor=Student.Stu_id
JOIN Teacher ON Director=Teacher.Teac_id
JOIN Deparment ON Class.Depar_id=Deparment.Depar_id

--实验七参考答案

--l、新开设一门课程，名叫网络安全与防火墙，学时40，编号为“0118”，主要介绍网络的安全与主要的防火墙软件。
INSERT
INTO Course(course_id,course_name,course_hour,introduce)
VALUES ('0118','网络安全与防火墙',40,'主要介绍网络的安全与主要的防火墙软件')

--2、先建立monitor表，其结构与student表大致一样．包含student表的学号、姓名、性别和班级编号，然后把班级编号为“0101”的学生的相应资料插入到monitor表中。
CREATE TABLE monitor
( stu_id varchar(9) constraint pk_monitor primary key,
stu_name varchar(8),
stu_sex varchar(2),
class_id varchar(4)
)

INSERT
INTO monitor
SELECT stu_id,stu_name,stu_sex,class_id
FROM student
WHERE class_id='0101'

--3、更新所有职称为“助教”的教师职称为“助理教师”。
UPDATE teacher
SET techpost='助理教师'
WHERE techpost='助教'

--4、在所有经济系班级的名称前加上“经济系”三个字。
UPDATE class
SET class_name='经济系'+class_name
WHERE class_id=(SELECT class_id
FROM deparment
WHERE depar_name='经济系')

--5、学号为“980101005”的学生的“数据结构”课程成绩改为80分。
UPDATE studentgrade
SET grade=80
WHERE course_id=(SELECT course_id
FROM course
WHERE course_name='数据结构')
AND stu_id='980101005'

--6、删除studentgrade表中所有成绩不及格的记录。
DELETE
FROM studentgrade
WHERE grade<60

--7、删除studentgrade表中学号以“99”开头的学生选修课程的记录。
DELETE
FROM studentgrade
WHERE stu_id like '99%'

--8、删除课程名为“C语言”的课程信息和所有这门课的选课信息
/*此题中由于course表与courseteacher和studentgrade存在外键联系，根据各表中的course_id建立。
从现实中的逻辑角度考虑，应是：先开设一门课，然后指定讲授这门课的老师、时间和地点，再是学生们
根据自身情况选修这门课程。因此，如需删除这门课（撤销此课），先删除学生选课信息，再删除该课的授课信息，
最后删除该课的课程信息。
*/
DELETE
FROM studentgrade
WHERE course_id=(SELECT course_id
FROM course
WHERE course_name='C语言')
DELETE
FROM courseteacher
WHERE course_id=(SELECT course_id
FROM course
WHERE course_name='C语言')
DELETE
FROM course
WHERE course_name='C语言'

--实验八参考答案

--（一）、视图的操作
--1．建立一视图View_CSTeacher，列出计算机系各个老师的资料(姓名、性别、职称)。
USE SCHOOL
GO

CREATE VIEW VIEW_CSTEACHER
AS
SELECT TEAC_NAME,TEAC_SEX,TECHPOST
FROM TEACHER
WHERE DEPAR_ID=(SELECT DEPAR_ID FROM DEPARMENT WHERE DEPAR_NAME='计算机系')

--2．建立一视图View_Class，列出每个班级的名称、系别和班级人数。
USE SCHOOL
GO

CREATE VIEW VIEW_CLASS
AS
SELECT CLASS_NAME,DEPAR_NAME,COUNT(STU_ID)
FROM CLASS JOIN DEPARMENT ON CLASS.DEPAR_ID=DEPARMENT.DEPAR_ID
JOIN STUDENT ON CLASS.CLASS_ID=STUDENT.CLASS_ID
GROUP BY CLASS_NAME,DEPAR_NAME

--3．建立一视图View_Student，列出每个学生的学号、选修课程门数和平均成绩，并按平均成绩从大到小排列。
USE SCHOOL
GO

CREATE VIEW VIEW_STUDENT
AS
SELECT TOP 1000 STU_ID,COUNT(COURSE_ID) AS 选修课程数,AVG(GRADE) AS 平均成绩 --TOP 1000 目的是结合ORDER BY在子查询中使用，并排序
FROM STUDENTGRADE
GROUP BY STU_ID
ORDER BY AVG(GRADE) DES