广药数据库实验答案参考(共32页).doc

精选优质文档-倾情为你奉上-实验三 参考答案（一）、数据库、表的创建及删除1.将数据库bookdb的相关属性（系统默认设置），填入下表：项目 内容数据库所有者 XP-6Administrator(Windows身份登录SQL server)数据库名称 bookdb数据库逻辑文件名 bookdb数据文件物理名 D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatabookdb.mdf（本机SQL SERVER系统安装目录在D盘）数据库初始大小 3MB数据文件最大值 不限制增长数据文件增长量 1MB日志逻辑文件名 bookdb_log日志文件物理名 D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatabookdb_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 EDUCON 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 EDUCCREATE 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 StudentALTER 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 CourseADD year varchar(4) NULL-（2）用SQL语句在year字段添加约束，year 的属性值在2006-2012之间。ALTER TABLE CourseADD CONSTRAINT CK_year CHECK(year BETWEEN 2006 AND 2012)-3删除列-（1）用SQL语句将Course表中的year字段删除。-先删除year上的各种约束（CHECK约束）ALTER TABLE CourseDROP CONSTRAINT CK_year -再删除yearALTER TABLE CourseDROP COLUMN year -COLUMN不能省略-实验四 参考答案-1. 统计各门课程所选修的人数，并把结果存储在一张新表中。（提示：用SelectInto，group by）SELECT Course_id,COUNT(*) AS 选修人数 INTO Course_numFROM StudentGradeGROUP BY Course_id-2查询男生的资料。SELECT *FROM StudentWHERE Stu_sex='男'-3查询所有计算机系的班级信息。-WHERE连接查询SELECT *FROM Class,DeparmentWHERE Class.depar_id=Deparment.depar_id AND Depar_name='计算机系'-JOIN连接查询SELECT *FROM Class JOIN Deparment ON Class.depar_id=Deparment.depar_idWHERE Depar_name='计算机系'-嵌套查询SELECT * -父查询：根据子查询找出的系别编号，查找该系别编号的班级FROM ClassWHERE Depar_id=(SELECT Depar_id -子查询：找出计算机系的系别编号 FROM Deparment WHERE Depar_name='计算机系')-4查询艾老师所教的课程号。(也可用嵌套查询)SELECT Course_id,*FROM CourseTeacher JOIN Teacher ON CourseTeacher.Teac_id=Teacher.Teac_idWHERE Teac_name like '艾%'-5查询年龄小于30岁的女同学的学号和姓名。-GETDATE()为获取系统时间的函数，YEAR()为获取“年”,MONTH()为获取“月”,DAY()为获取“日”。SELECT *FROM StudentWHERE (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,GradeFROM StudentGradeWHERE Course_id='0511'ORDER BY Grade DESC-9查询选修课程号为“0109”或“0111”的学生学号。SELECT Stu_idFROM StudentGradeWHERE Course_id='0109' OR Course_id='0111'-或者SELECT Stu_idFROM StudentGradeWHERE Course_id IN('0109','0111') -IN表示满足指定集合中的任意一个。-10查询课程“0101”的成绩在80与90之间的同学的学号。SELECT Stu_idFROM StudentGradeWHERE Course_id='0101' AND (Grade BETWEEN 80 AND 90)-11查询平均成绩都在80分以上的学生学号及平均成绩。SELECT Stu_id,AVG(Grade) AS 平均成绩FROM StudentGradeGROUP BY Stu_id HAVING AVG(Grade)>80-实验五 参考答案-1找出所有任教“数据库”的教师的姓名。SELECT teac_nameFROM teacher JOIN courseteacher ON teacher.teac_id=courseteacher.teac_id JOIN course ON courseteacher.course_id=course.course_idWHERE 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取出学号为“”的学生选修的课程号和课程名。SELECT course.course_id,course_nameFROM course JOIN studentgrade ON course.course_id=studentgrade.course_idWHERE stu_id=''-嵌套查询SELECT course_id,course_nameFROM courseWHERE course_id in(SELECT course_id FROM studentgrade WHERE stu_id='')-3“涂杰杰”所选修的全部课程号及成绩。（注意：school中有同名，即有两名学生叫“涂杰杰”。）SELECT course_id,gradeFROM studentgrade JOIN student ON studentgrade.stu_id=student.stu_idWHERE stu_name='涂杰杰'-嵌套查询SELECT course_id,gradeFROM studentgradeWHERE stu_id in(SELECT stu_id FROM student WHERE stu_name='涂杰杰')-4C语言成绩比数据结构成绩好的学生（自身连接）。SELECT *FROM studentgrade AS a JOIN studentgrade AS b ON a.stu_id=b.stu_idWHERE 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.GradeFROM 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_idORDER 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_idGROUP BY course.course_id-6检索选修课程号为“0109”或“0111”的学生学号、姓名和所在班级。SELECT student.stu_id,stu_name,class_idFROM student JOIN studentgrade ON student.stu_id=studentgrade.stu_idWHERE course_id in('0109','0111')-若列出班级名称，则需在于Class表连接SELECT student.stu_id,stu_name,class_nameFROM student JOIN studentgrade ON student.stu_id=studentgrade.stu_id JOIN class ON student.class_id=class.class_idWHERE course_id in('0109','0111')-当多个表（A,B,C）进行连接时，B表同时具有A表和C表中的某一字段，最好将B表作为连接三个表的中介，-如下，将student作为连接studentgrade和class的中介SELECT student.stu_id,stu_name,class_nameFROM studentgrade JOIN student ON studentgrade.stu_id=student.stu_id JOIN class ON student.class_id=class.class_idWHERE course_id in('0109','0111')-嵌套查询SELECT student.stu_id,stu_name,class_idFROM student WHERE stu_id in(SELECT stu_id FROM studentgrade WHERE course_id in('0109','0111') )-7查询“0203”课程的最高分的学生的学号。SELECT stu_idFROM studentgradeWHERE course_id='0203' AND grade=(SELECT MAX(grade) FROM studentgrade -子查询的作用是：找出“0203”课程的最高分 WHERE course_id='0203')-如果最高分的同学有两个以上(分数相同)，此方法无法实现。SELECT TOP 1 Stu_idFROM StudentGradeWHERE Course_id='0203'ORDER BY Grade DESC-8没有选修以“01”开头的课程的学生信息。（用子查询完成。提示not in或not exists）SELECT *FROM StudentWHERE stu_id NOT IN (SELECT stu_id FROM studentgrade WHERE course_id like'01%')SELECT *FROM StudentWHERE NOT EXISTS (SELECT * FROM studentgrade WHERE Student.stu_id=studentgrade.stu_id -EXISTS查询为相关子查询，依赖父表(student),需将两表连接 AND course_id like'01%')-9找出“苏贤兴”同学所学课程的名称和成绩。(请使用连接查询和嵌套查询分别来完成) SELECT course_name,gradeFROM course JOIN studentgrade ON course.course_id=studentgrade.course_id JOIN student ON studentgrade.stu_id=student.stu_idWHERE stu_name='苏贤兴'-嵌套查询SELECT course_name,gradeFROM 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 StudentWHERE (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,CourseWHERE Grade>80 AND StudentGrade.Course_id=Course.Course_id AND Course_name='数据结构'-3统计各系开设班级的数目(系名称、班级数目)，并创建结果表。SELECT Depar_name,COUNT(*) AS '班级数目' INTO Class_CountFROM Deparment,ClassWHERE Class.Depar_id=Deparment.Depar_idGROUP BY Deparment.Depar_name-4找出最多人选修的三门课的课程编号，以及统计选修人数，并按人数降序排列。SELECT TOP 3 Course_id,COUNT(*) AS '选修人数' FROM StudentGradeGROUP 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,CourseWHERE StudentGrade.Course_id=Course.Course_idGROUP BY Course_name-6所有成绩都在70分以上的学生姓名（提示：使用子查询）SELECT Stu_id,Stu_nameFROM StudentWHERE 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_nameFROM Student WHERE (SELECT MIN(Grade) FROM StudentGrade WHERE Student.Stu_id=StudentGrade.Stu_id GROUP BY StudentGrade.Stu_id )>=70 -或SELECT Stu_id,Stu_nameFROM StudentWHERE 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_nameFROM StudentWHERE 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_sexFROM Student JOIN StudentGrade ON Student.Stu_id=StudentGrade.Stu_idWHERE 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_sexFROM StudentWHERE Stu_id IN (SELECT Stu_id FROM StudentGrade GROUP BY Stu_id HAVING COUNT(Course_id)>=2) -9检索至少选修课程“数据结构”和“C语言”的学生学号SELECT Stu_idFROM 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_nameFROM Student JOIN Class ON Monitor=Student.Stu_id JOIN Teacher ON Director=Teacher.Teac_idJOIN Deparment ON Class.Depar_id=Deparment.Depar_id */-或：(此方法的查询结果中还包括Monitor为NULL的记录)SELECT Class_name,Stu_name,Teac_name,Depar_nameFROM 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 monitorSELECT stu_id,stu_name,stu_sex,class_idFROM studentWHERE class_id='0101'-3、更新所有职称为“助教”的教师职称为“助理教师”。UPDATE teacherSET techpost='助理教师'WHERE techpost='助教'-4、在所有经济系班级的名称前加上“经济系”三个字。UPDATE classSET class_name='经济系'+class_nameWHERE class_id=(SELECT class_id FROM deparment WHERE depar_name='经济系')-5、学号为“”的学生的“数据结构”课程成绩改为80分。UPDATE studentgradeSET grade=80WHERE course_id=(SELECT course_id FROM course WHERE course_name='数据结构') AND stu_id=''-6、删除studentgrade表中所有成绩不及格的记录。DELETEFROM studentgradeWHERE grade<60-7、删除studentgrade表中学号以“99”开头的学生选修课程的记录。DELETEFROM studentgradeWHERE stu_id like '99%'-8、删除课程名为“C语言”的课程信息和所有这门课的选课信息/*此题中由于course表与courseteacher和studentgrade存在外键联系，根据各表中的course_id建立。从现实中的逻辑角度考虑，应是：先开设一门课，然后指定讲授这门课的老师、时间和地点，再是学生们根据自身情况选修这门课程。因此，如需删除这门课（撤销此课），先删除学生选课信息，再删除该课的授课信息，最后删除该课的课程信息。*/DELETEFROM studentgradeWHERE course_id=(SELECT course_id FROM course WHERE course_name='C语言')DELETEFROM courseteacherWHERE course_id=(SELECT course_id FROM course WHERE course_name='C语言')DELETEFROM courseWHERE course_name='C语言'-实验八参考答案-（一）、视图的操作-1建立一视图View_CSTeacher，列出计算机系各个老师的资料(姓名、性别、职称)。USE SCHOOLGOCREATE VIEW VIEW_CSTEACHERASSELECT TEAC_NAME,TEAC_SEX,TECHPOSTFROM TEACHERWHERE DEPAR_ID=(SELECT DEPAR_ID FROM DEPARMENT WHERE DEPAR_NAME='计算机系')-2建立一视图View_Class，列出每个班级的名称、系别和班级人数。USE SCHOOLGOCREATE VIEW VIEW_CLASSASSELECT 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_IDGROUP BY CLASS_NAME,DEPAR_NAME-3建立一视图View_Student，列出每个学生的学号、选修课程门数和平均成绩，并按平均成绩从大到小排列。USE SCHOOLGOCREATE VIEW VIEW_STUDENTASSELECT TOP 1000 STU_ID,COUNT(COURSE_ID) AS 选修课程数,AVG(GRADE) AS 平均成绩 -TOP 1000 目的是结合ORDER BY在子查询中使用，并排序FROM STUDENTGRADEGROUP BY STU_IDORDER BY AVG(GRADE) DESC-4查询视图View_Student。-将视图作为基本表一样查询