广药数据库实验答案参考

专业资料word完美格式--实验三参考答案（一）、数据库、表的创建及删除1.将数据库bookdb的相关属性（系统默认设置），填入下表：项目内容数据库所有者XP-201104281226\Administrator(Windows身份登录SQLserver)数据库名称bookdb数据库逻辑文件名bookdb数据文件物理名D:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\bookdb.mdf（本机SQLSERVER系统安装目录在D盘）数据库初始大小3MB数据文件最大值不限制增长数据文件增长量1MB日志逻辑文件名bookdb_log日志文件物理名D:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\bookdb_log.ldf（本机SQLSERVER系统安装目录在D盘）日志文件初始大小1MB日志文件最大值2,097,152MB日志文件增长量10%*/--根据“二、实验内容”的要求，使用T-SQL命令创建/删除数据库EDUC，创建数据表student、course、sc。--2.删除bookdb数据库。DROPDATABASEbookdb--3.创建数据库EDUC。CREATEDATABASEEDUCONPRIMARY(NAME='EDUC_data',FILENAME='C:\EDUC_data.mdf',SIZE=4MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOGON(NAME='EDUC_log',FILENAME='C:\EDUC_log.ldf',SIZE=1MB,专业资料word完美格式MAXSIZE=UNLIMITED,FILEGROWTH=10%)--4.创建数据表Student。USEEDUCCREATETABLEStudent(Snovarchar(20)NOTNULLCONSTRAINTPK_StudentPRIMARYKEY,Snamevarchar(10)NOTNULL,Sageint,Ssexvarchar(2),Sdeptvarchar(30))--5.创建数据表Course。CREATETABLECourse(Cnovarchar(15)NOTNULLCONSTRAINTPK_CoursePRIMARYKEY,Cnamevarchar(30),Cpnovarchar(15),Ccreditint)--6.创建数据表SC。CREATETABLESC(Snovarchar(20)NOTNULL,Cnovarchar(15)NOTNULL,GradeDECIMAL(9,2),CONSTRAINTPK_SCPRIMARYKEY(Sno,Cno))--（二）、修改基本表的定义--1．修改列属性--（1）用SSMS将Student表中的Sage字段设为不能为空（notnull）。ALTERTABLEStudentALTERCOLUMNSageintNOTNULL--（2）用SQL语句将Student表中的属性Snovarchar(20)改成char(8)类型。--先删除Sno上的各种约束（此例中Sno上的约束有：主键）ALTERTABLEStudentDROPCONSTRAINTPK_Student--CONSTRAINT也可省略--再修改Sno列专业资料word完美格式ALTERTABLEStudentALTERCOLUMNSnoCHAR(8)NOTNULL--需设置为不允许空，否则不能在该列上创建主键--最后，恢复（添加）Sno上的主键约束ALTERTABLEStudentADDCONSTRAINTPK_StudentPRIMARYKEY(Sno)--2．添加列--（1）用SSMS在Course表中添加一列year，类型为varchar（4），默认置为空。ALTERTABLECourseADDyearvarchar(4)NULL--（2）用SQL语句在year字段添加约束，year的属性值在2006-2012之间。ALTERTABLECourseADDCONSTRAINTCK_yearCHECK(yearBETWEEN2006AND2012)--3．删除列--（1）用SQL语句将Course表中的year字段删除。--先删除year上的各种约束（CHECK约束）ALTERTABLECourseDROPCONSTRAINTCK_year--再删除yearALTERTABLECourseDROPCOLUMNyear--COLUMN不能省略专业资料word完美格式--实验四参考答案--1.统计各门课程所选修的人数，并把结果存储在一张新表中。（提示：用Select……Into……，groupby）SELECTCourse_id,COUNT(*)AS选修人数INTOCourse_numFROMStudentGradeGROUPBYCourse_id--2．查询男生的资料。SELECT*FROMStudentWHEREStu_sex='男'--3．查询所有计算机系的班级信息。--WHERE连接查询SELECT*FROMClass,DeparmentWHEREClass.depar_id=Deparment.depar_idANDDepar_name='计算机系'--JOIN连接查询SELECT*FROMClassJOINDeparmentONClass.depar_id=Deparment.depar_idWHEREDepar_name='计算机系'专业资料word完美格式--嵌套查询SELECT*--父查询：根据子查询找出的系别编号，查找该系别编号的班级FROMClassWHEREDepar_id=(SELECTDepar_id--子查询：找出计算机系的系别编号FROMDeparmentWHEREDepar_name='计算机系')--4．查询艾老师所教的课程号。(也可用嵌套查询)SELECTCourse_id,*FROMCourseTeacherJOINTeacherONCourseTeacher.Teac_id=Teacher.Teac_idWHERETeac_namelike'艾%'--5．查询年龄小于30岁的女同学的学号和姓名。--GETDATE()为获取系统时间的函数，YEAR()为获取“年”,MONTH()为获取“月”,DAY()为获取“日”。SELECT*FROMStudentWHERE(YEAR(GETDATE())-YEAR(Birthday))30ANDStu_sex='女'--6．查询所有被选修的课程号。SELECTDISTINCTCourse_id--DISTINCT作用是去除重复行FROMStudentGrade--存储在StudentGrade表中的数据均为选修数据，因此该表中的课程号即为被选修的课程号--7．在学生基本信息表Student中检索学生的姓名和出生年份，输出的列名为STUDENT_NAME和BIRTH_YEAR。SELECTStu_nameASSTUDENT_NAME,YEAR(Birthday)ASBIRTH_YEAR--AS作用是起别名FROMStudent--8．在StudnetGrade中，求选修课程“0511”且分数排名为前10%学生的学号和得分。SELECTTOP10PERCENTStu_id,GradeFROMStudentGradeWHERECourse_id='0511'ORDERBYGradeDESC--9．查询选修课程号为“0109”或“0111”的学生学号。SELECTStu_idFROMStudentGradeWHERECourse_id='0109'ORCourse_id='0111'--或者SELECTStu_idFROMStudentGradeWHERECourse_idIN('0109','0111')--IN表示满足指定集合中的任意一个。专业资料word完美格式--10．查询课程“0101”的成绩在80与90之间的同学的学号。SELECTStu_idFROMStudentGradeWHERECourse_id='0101'AND(GradeBETWEEN80AND90)--11．查询平均成绩都在80分以上的学生学号及平均成绩。SELECTStu_id,AVG(Grade)AS平均成绩FROMStudentGradeGROUPBYStu_idHAVINGAVG(Grade)80--实验五参考答案--1．找出所有任教“数据库”的教师的姓名。SELECTteac_nameFROMteacherJOINcourseteacherONteacher.teac_id=courseteacher.teac_idJOINcourseONcourseteacher.course_id=course.course_idWHEREcourse_name='数据库'--此处用连接查询，三个表连接后形成一个大表，然后从中筛选。--练菲彰上了两个班级的数据库，因此有两条记录。可用DISTINCT去除重复行专业资料word完美格式--嵌套查询SELECTteac_name--根据子查询得到的结果在teacher中找出上“数据库”的老师，上数据库的老师只有一个，故只有一条记录。FROMteacherWHEREteac_idin(SELECTteac_idFROMcourseteacherWHEREcourse_id=(SELECTcourse_idFROMcourseWHEREcourse_name='数据库'))--2．取出学号为“980101011”的学生选修的课程号和课程名。SELECTcourse.course_id,course_nameFROMcourseJOINstudentgradeONcourse.course_id=studentgrade.course_idWHEREstu_id='980101001'--嵌套查询SELECTcourse_id,course_nameFROMcourseWHEREcourse_idin(SELECTcourse_idFROMstudentgradeWHEREstu_id='980101001')--3．“涂杰杰”所选修的全部课程号及成绩。（注意：school中有同名，即有两名学生叫“涂杰杰”。）SELECTcourse_id,gradeFROMstudentgradeJOINstudentONstudentgrade.stu_id=student.stu_idWHEREstu_name='涂杰杰'--嵌套查询SELECTcourse_id,gradeFROMstudentgradeWHEREstu_idin(SELECTstu_idFROMstudentWHEREstu_name='涂杰杰')--4．C语言成绩比数据结构成绩好的学生（自身连接）。SELECT*FROMstudentgradeASaJOINstudentgradeASbONa.stu_id=b.stu_idWHEREa.course_id=(SELECTcourse_idFROMcourseWHEREcourse_name='C语言')ANDb.course_id=(SELECTcourse_idFROMcourseWHEREcourse_name='数据结构')ANDa.gradeb.grade--连接查询，比嵌套查询复杂SELECTSG1.Stu_id,C1.Course_name,SG1.Grade,C2.Course_name,SG2.GradeFROMStudentGradeASSG1JOINCourseASC1ONSG1.Course_id=C1.Course_id--此连接得到:每位学生的选课情况及所选课程信息,作为一个新表JOINStudentGradeASSG2ONSG1.Stu_id