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

《广药数据库实验答案参考.doc》由会员分享，可在线阅读，更多相关《广药数据库实验答案参考.doc（22页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、如有侵权，请联系网站删除，仅供学习与交流广药数据库实验答案参考【精品文档】第 22 页-实验三 参考答案（一）、数据库、表的创建及删除1.将数据库bookdb的相关属性（系统默认设置），填入下表：项目 内容数据库所有者 XP-201104281226Administrator(Windows身份登录SQL server)数据库名称 bookdb数据库逻辑文件名 bookdb数据文件物理名 D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatabookdb.mdf（本机SQL SERVER系统安装目录在D盘）数据库初始大小 3MB数据文件最大值 不

2、限制增长数据文件增长量 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 EDUCO

3、N PRIMARY NAME=EDUC_data,FILENAME=C:EDUC_data.mdf,SIZE=4MB,MAXSIZE=10MB,FILEGROWTH=1MBLOG ON NAME=EDUC_log,FILENAME=C:EDUC_log.ldf,SIZE=1MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%-4.创建数据表Student。USE EDUCCREATE TABLE StudentSno varchar(20) NOT NULL CONSTRAINT PK_Student PRIMARY KEY,Sname varchar(10) NOT NUL

4、L,Sage int,Ssex varchar(2),Sdept varchar(30)-5.创建数据表Course。CREATE TABLE CourseCno varchar(15) NOT NULL CONSTRAINT PK_Course PRIMARY KEY,Cname varchar(30),Cpno varchar(15),Ccredit int-6.创建数据表SC。CREATE TABLE SCSno varchar(20) NOT NULL,Cno varchar(15) NOT NULL,Grade DECIMAL(9,2),CONSTRAINT PK_SC PRIMAR

5、Y 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 ALTE

6、R 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 CONSTRA

7、INT 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

8、 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=

9、计算机系-嵌套查询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岁的女同学的学号和姓名。

10、-GETDATE()为获取系统时间的函数，YEAR()为获取“年”,MONTH()为获取“月”,DAY()为获取“日”。SELECT *FROM StudentWHERE (YEAR(GETDATE()-YEAR(Birthday)80-实验五 参考答案-1找出所有任教“数据库”的教师的姓名。SELECT teac_nameFROM teacher JOIN courseteacher ON teacher.teac_id=courseteacher.teac_id JOIN course ON courseteacher.course_id=course.course_idWHERE cou

11、rse_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取出学号为“98010

12、1011”的学生选修的课程号和课程名。SELECT course.course_id,course_nameFROM course JOIN studentgrade ON course.course_id=studentgrade.course_idWHERE stu_id=980101001-嵌套查询SELECT course_id,course_nameFROM courseWHERE course_id in(SELECT course_id FROM studentgrade WHERE stu_id=980101001)-3“涂杰杰”所选修的全部课程号及成绩。（注意：school中

13、有同名，即有两名学生叫“涂杰杰”。）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 student

14、grade 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.gradeb.grade-连接查询，比嵌套查询复杂SELECT SG1.Stu_id,C1.Course_name,SG1.Grade,C2.Course_name, SG2.GradeFROM StudentGrade AS SG1 JOIN

15、 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.GradeSG2.Grade AND C1.Course_name=C语言 AND C2.Course_name=数据结构-5显

16、示所有课程的选修情况（外连接）。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)

17、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)-若列出班级名称，则需在于Clas

18、s表连接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,s

19、tu_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查询“

20、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=0203ORDER BY Grade DESC-8没有选修以“01”开头的课程的学生信息。（用子查询完成。提示not

21、 in或not exists）SELECT *FROM StudentWHERE stu_id NOT IN (SELECT stu_id FROM studentgrade WHERE course_id like01%)SELECT *FROM StudentWHERE NOT EXISTS (SELECT * FROM studentgrade WHERE Student.stu_id=studentgrade.stu_id -EXISTS查询为相关子查询，依赖父表(student),需将两表连接 AND course_id like01%)-9找出“苏贤兴”同学所学课程的名称和成绩。(

22、请使用连接查询和嵌套查询分别来完成) 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 i

23、n(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 Grade80 AND Course_id=(SELECT Course_id- FROM Course- WHERE Course_name=数据结构)-连接查询SELEC

24、T COUNT(*) as 人数FROM StudentGrade,CourseWHERE Grade80 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找出最多人选修的三门

25、课的课程编号，以及统计选修人数，并按人数降序排列。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,CourseW

26、HERE 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,St

27、u_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

28、 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=2) -9检索至少选修课程“数据结构”和“C语言”

29、的学生学号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列出所有班名、班主任、班长、系名。（请使用连接查询；进一步考虑使用外连接，因为很多班级可能是没有班长的，考虑需要显示

30、所有班级的信息）-列出所有班名，班主任，班长，系名/*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_nameFRO

31、M 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,主要介

32、绍网络的安全与主要的防火墙软件)-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_id

33、FROM 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、学号为“980101005”的学生的“数据结构”课程成绩改为80分。UPDATE studentgradeSET

34、 grade=80WHERE course_id=(SELECT course_id FROM course WHERE course_name=数据结构) AND stu_id=980101005-6、删除studentgrade表中所有成绩不及格的记录。DELETEFROM studentgradeWHERE grade60-7、删除studentgrade表中学号以“99”开头的学生选修课程的记录。DELETEFROM studentgradeWHERE stu_id like 99%-8、删除课程名为“C语言”的课程信息和所有这门课的选课信息/*此题中由于course表与courset

35、eacher和studentgrade存在外键联系，根据各表中的course_id建立。从现实中的逻辑角度考虑，应是：先开设一门课，然后指定讲授这门课的老师、时间和地点，再是学生们根据自身情况选修这门课程。因此，如需删除这门课（撤销此课），先删除学生选课信息，再删除该课的授课信息，最后删除该课的课程信息。DELETEFROM studentgradeWHERE course_id=(SELECT course_id FROM course WHERE course_name=C语言)DELETEFROM courseteacherWHERE course_id=(SELECT course_i

36、d 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建立一视图

37、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，列出每个学生的学号、选修课程门数和平均成绩，并按平均成绩从大到小排

38、列。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。-将视图作为基本表一样查询USE SCHOOLSELECT *FROM VIEW_STUDENT-5修改视图View_Student，列出每个学生的学号、姓名、选修课

39、程门数和平均成绩。-此题要求实质是：先删除该视图（View_Student），然后在创建视图（View_Student）USE SCHOOLGODROP VIEW VIEW_STUDENTCREATE VIEW VIEW_STUDENTASSELECT STUDENT.STU_ID,STU_NAME,COUNT(COURSE_ID) AS 选修课程数,AVG(GRADE) AS 平均成绩 FROM STUDENT JOIN STUDENTGRADE ON STUDENT.STU_ID=STUDENTGRADE.STU_IDGROUP BY STUDENT.STU_ID,STU_NAME-6要通过视图View_Student，将学号为“000503002”的平均成绩