作业模板.docx

1、作业模板实验1、数据定义1.1 实验目的熟悉SQL的数据定义语言，能够熟练地使用SQL语句来创建和更改基本表，创建和取消索引。1.2 实验内容 用企业管理器创建数据库Hongjuey_Mis 使用CREATE语句创建基本表。 更改基本表的定义: 增加列，删除列，修改列的数据类型。 创建表的升、降序索引。 删除基本表的约束、基本表的索引或基本表。1.3 实验步骤(1) 用企业管理器创建数据库Hongjuey_Mis启动SQL Server 企业管理器和服务管理器，进入企业管理器的控制台窗口，选择右键单击“数据库”，在弹出的菜单中选择“新建数据库”命令，创建名为Hongjuey_Mis的数据库。(

2、2) 在查询分析器中用SQL语句创建关系数据库基本表：学生表Hongjy_Students(Hjy_Sno,Hjy_Sname, Hjy_Semail,Hjy_Scredit,Hjy_Sroom)；教师表Hongjy_Teachers(Hjy_Tno,Hjy_Tname,Hjy_Temail,Hjy_Tsalary)；课程表Hongjy_Courses(Hjy_Cno,Hjy_Cname,Hjy_Ccredit);成绩表Hongjy_Reports(Hjy_Sno,Hjy_Tno,Hjy_Cno, Hjy_Score);其中：Hjy_Sno、Hjy_Tno、Hjy_Cno分别是表Hongjy_

3、Students、表Hongjy_Teachers、表Hongjy_Courses的主键，具有唯一性约束，Hjy_Scredit具有约束“大于等于0”； Hongjy_Reports中的Hjy_Sno,Hjy_Tno,Hjy_Cno是外键，它们共同组成Hongjy_Reports的主键。CREATE TABLE Hongjy_Students (Hjy_Sno CHAR(8) NOT NULL UNIQUE, Hjy_Sname CHAR(20) NOT NULL, Hjy_Semail CHAR(50), Hjy_Scredit FLOAT, Hjy_Sroom CHAR(20), CONS

4、TRAINT PK_Hongjy_Students PRIMARY KEY(Hjy_Sno), CONSTRAINT CK_Hongjy_Students_Hjy_Scredit CHECK(Hjy_Scredit=0)CREATE TABLE Hongjy_Teachers (Hjy_Tno CHAR(8) NOT NULL UNIQUE, Hjy_Tname CHAR(20) NOT NULL, Hjy_Temail CHAR(50), Hjy_Tsalary FLOAT, CONSTRAINT PK_Hongjy_Teachers PRIMARY KEY(Hjy_Tno)CREATE T

5、ABLE Hongjy_Courses (Hjy_Cno CHAR(8) NOT NULL UNIQUE, Hjy_Cname CHAR(20), Hjy_Ccredit FLOAT, CONSTRAINT PK_Hongjy_Courses PRIMARY KEY(Hjy_Cno)CREATE TABLE Hongjy_Reports (Hjy_Sno CHAR(8) NOT NULL, Hjy_Tno CHAR(8) NOT NULL, Hjy_Cno CHAR(8) NOT NULL, Hjy_Score FLOAT, CONSTRAINT PK_Hongjy_Reports PRIMA

6、RY KEY(Hjy_Sno,Hjy_Tno,Hjy_Cno), CONSTRAINT FK_Hongjy_Reports_Hjy_Sno FOREIGN KEY(Hjy_Sno) REFERENCES Hongjy_Students, CONSTRAINT FK_Hongjy_Reports_Hjy_Tno FOREIGN KEY(Hjy_Tno) REFERENCES Hongjy_Teachers, CONSTRAINT FK_Hongjy_Reports_Hjy_Cno FOREIGN KEY(Hjy_Cno) REFERENCES Hongjy_Courses)(3) 更改表Hong

7、jy_Students：增加属性Hjy_Ssex(类型是CHAR，长度为2)，取消Hjy_Scredit“大于等于0”约束。把表Hongjy_Courses中的属性Hjy_Cname的数据类型改成长度为30。ALTER TABLE Hongjy_Students ADD Hjy_Hjy_Ssex CHAR(2)ALTER TABLE Hongjy_Students DROP CONSTRAINT CK_Hongjy_Students_Hjy_ScreditALTER TABLE Hongjy_Courses ALTER COLUMN Hjy_Cname CHAR(30)/*注：在取消约束的时候

8、，应该写出约束的名称。这个名称可以由用户在创建表时定义。若用户在创建表时没有定义名称，则当定义约束时，数据库会为这个约束起一个名称（不同的时候创建名称可能不同）。要查看这个名称，可以通过在企业分析器中，查看设计表中的约束选项，可以看到这个约束的名称。本例中的约束名称在创建表时由用户自定义*/(4) 删除表Hongjy_Students的一个属性Hjy_Sroom。ALTER TABLE Hongjy_Students DROP COLUMN Hjy_Sroom(5) 删除表Hongjy_Reports。DROP TABLE Hongjy_Reports(6) 为Hongjy_Courses表创

9、建按Hjy_Cno降序排列的索引。CREATE INDEX XHJY_CNO ON Hongjy_Courses(Hjy_Cno DESC)/*注：XHJY_CNO为创建的索引名称*/(7) 为Hongjy_Students表创建按Hjy_Sno升序排列的索引。CREATE INDEX XHJY_SNO ON Hongjy_Students(Hjy_Sno)(8) 创建表Hongjy_Students的按Hjy_Sname升序排列的唯一性索引。CREATE UNIQUE INDEX XSNA ON Hongjy_Students(Hjy_Sname ASC)(9) 删除Hongjy_Stude

10、nts表Hjy_Sno的升序索引。DROP INDEX Hongjy_Students.XHJY_SNO实验2、SQL的数据查询2.1 实验目的熟悉SQL语句的数据查询语言，能够SQL语句对数据库进行单表查询、连接查询、嵌套查询、集合查询和统计查询。2.2 实验内容实验内容主要是对数据库进行查询操作，包括如下四类查询方式：(1) 单表查询 查询的目标表达式为所有列、指定的列或指定的列的运算三种不同。 使用DISTINCT保留字消除重复行。 对查询结果排序和分组。 集合分组使用集函数进行各项统计。(2) 连接查询 笛卡儿连接和等值连接。 自连接。 外连接 复合条件连接。 多表连接。(3) 嵌套查

11、询 通过实验验证对子查询的两个限制条件。 体会相关子查询和不相关子查询的不同。 考察四类谓词的用法，包括：第一类，IN、NOT IN；第二类，带有比较运算符的子查询；第三类，SOME、ANY或ALL谓词的子查询，查询最大值和最小值；第四类，带有EXISTS谓词的子查询，实现“所有”等情况（如王宏的“所有”课程，“所有”女生选修的课程）(4) 集合运算 使用保留字UNION进行集合或运算。 采用逻辑运算符AND或OR来实现集合交和减运算。2.3 实验步骤以Hongjuey_Mis数据库为例，该数据库中有四张表如实验1，其中Hjy_Score是每门课的考试成绩，Hjy_Scredit是学生所有考试

12、合格课程所获得的积分总数，Hjy_Ccredit每门课程的学分数。在数据库中，存在这样的联系：学生可以选择课程，一个课程对应一个教师。在表Hongjy_Reports中保存学生的选课记录和考试成绩。请先根据以下查询需求输入若干符合条件的元组后，再对数据库进行有关的查询操作：（我们构造好，给出如下表）图1.1、Hongjy_Students表图1.2、Hongjy_Teachers表图1.3、Hongjy_Courses表图1.4、Hongjy_Reports表(1) 查询性别为“男”的所有学生的名称并按学号升序排列。SELECT Hjy_SnameFROM Hongjy_StudentsWHE

13、RE Hjy_Ssex=男ORDER BY Hjy_Sno/*注：当属性列的数值类型是CHAR时，与指定的值比较，指定的值要加上单引号，如本例中的男就必须加上单引号*/(2) 查询学生的选课成绩合格的课程成绩，并把成绩换算为积分。积分的计算公式为：1+(考试成绩-60)*0.1*Hjy_Ccredit。考试成绩=60 否则=0SELECT Hjy_Sno, Hjy_Tno, Hongjy_Reports.Hjy_Cno, Hjy_Score, Point of Hjy_Score, (Hjy_Score-60)*0.1*Hjy_Ccredit+Hjy_CcreditFROM Hongjy_Co

14、urses,Hongjy_ReportsWHERE Hjy_Score=60 And Hongjy_Reports.Hjy_Cno=Hongjy_Courses.Hjy_Cno/*注:为增加可读性，在结果集中，每行的积分值前加上提示“Point of Hjy_Score”*/(3) 查询学分是3或4的课程的名称。 SELECT Hjy_CnameFROM Hongjy_CoursesWHERE Hjy_Ccredit IN(3,4)(4) 查询所有课程名称中含有“算法”的课程编号。SELECT Hjy_CnameFROM Hongjy_CoursesWHERE Hjy_Cname LIKE %

15、算法% /*查询得到算法分析与设计、数据结构与算法分析*/(5) 查询所有选课记录的课程号（不重复显示）。SELECT DISTINCT Hjy_Cno FROM Hongjy_Reports(6) 统计所有老师的平均工资。SELECT AVG(Hjy_Tsalary) FROM Hongjy_Teachers(7) 查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列。SELECT Hjy_Tno,AVG(Hjy_Score)FROM Hongjy_ReportsGROUP BY Hjy_TnoORDER BY AVG(Hjy_Score) DESC(8) 统计各个课程的选课人

16、数和平均成绩。SELECT Hjy_Cno,COUNT(Hjy_Sno),AVG(Hjy_Score)FROM Hongjy_ReportsGROUP BY Hjy_Cno(9) 查询至少选修了三门课程的学生编号和姓名。SELECT Hjy_Sno,Hjy_SnameFROM Hongjy_StudentsWHERE Hjy_Sno IN(SELECT Hjy_SnoFROM Hongjy_ReportsGROUP BY Hjy_SnoHAVING COUNT(*)=3)(10) 查询编号S26的学生所选的全部课程的课程名和成绩。SELECT Hongjy_Courses.Hjy_Cname,

17、Hongjy_Reports.Hjy_ScoreFROM Hongjy_Courses,Hongjy_ReportsWHERE Hongjy_Reports.Hjy_Sno=S26 AND Hongjy_Courses.Hjy_Cno=Hongjy_Reports.Hjy_Cno(11) 查询所有选了“数据库原理及其应用”课程的学生编号和姓名。SELECT Hjy_Sno,Hjy_SnameFROM Hongjy_StudentsWHERE Hjy_Sno IN(SELECT Hongjy_Reports.Hjy_SnoFROM Hongjy_Reports,Hongjy_CoursesWHE

18、RE Hongjy_Reports.Hjy_Cno=Hongjy_Courses.Hjy_Cno AND Hongjy_Courses.Hjy_Cname=数据库原理及其应用)(12) 求出选择了同一个课程的学生对。SELECT X.Hjy_Sno,Y.Hjy_Sno,X.Hjy_CnoFROM Hongjy_Reports X,Hongjy_Reports YWHERE X.Hjy_Cno=Y.Hjy_Cno AND X.Hjy_SnoY.Hjy_Sno/*注：这里的学生对指的是选择了相同的某门课程的两个学生，由于两个学生可能选择了多门相同的课程，因此查询结果会出现重复的学生对，为增加可读性

19、，在每个学生对后显示其选修的课程。本例中使用条件X.Hjy_SnoY.Hjy_Sno以确保每个学生对中，不存在学生自己与自己组成的对。*/(13) 求出至少被两名学生选修的课程编号。SELECT DISTINCT X.Hjy_CnoFROM Hongjy_Reports X,Hongjy_Reports YWHERE X.Hjy_Cno=Y.Hjy_Cno AND X.Hjy_SnoY.Hjy_Sno(14) 查询选修了编号S26的学生所选的某个课程的学生编号。SELECT Y.Hjy_SnoFROM Hongjy_Reports X,Hongjy_Reports YWHERE X.Hjy_C

20、no=Y.Hjy_Cno AND X.Hjy_Sno=S26/*注：在本题中，如果要求是“查询选修了编号S26的学生所选的某个课程的其他学生编号”时，也就是不包含编号S26学生自身的情况时，在查询条件WHERE中，还需要加上条件X.Hjy_SnoY.Hjy_Sno*/(15) 查询学生的基本信息及选修课程编号和成绩。SELECT Hongjy_Students.Hjy_Sno, Hongjy_Students.Hjy_Sname, Hongjy_Students.Hjy_Semail, Hongjy_Students.Hjy_Scredit, Hongjy_Students.Hjy_Ssex,

21、 Hongjy_Reports.Hjy_Cno, Hongjy_Reports.Hjy_ScoreFROM Hongjy_Students JOIN Hongjy_Reports ON Hongjy_Students.Hjy_Sno=Hongjy_Reports.Hjy_Sno(16) 查询学号S52的学生的姓名和选修的课程名称及成绩。SELECT Hongjy_Students.Hjy_Sname,Hongjy_Courses.Hjy_Cname,Hongjy_Reports.Hjy_ScoreFROM Hongjy_Students,Hongjy_Courses,Hongjy_Report

22、sWHERE Hongjy_Students.Hjy_Sno=Hongjy_Reports.Hjy_Sno AND Hongjy_Courses.Hjy_Cno=Hongjy_Reports.Hjy_Cno AND Hongjy_Students.Hjy_Sno=S52(17) 查询和学号S52的学生同性别的所有学生资料。SELECT *FROM Hongjy_StudentsWHERE Hjy_Ssex=( SELECT Hjy_Ssex FROM Hongjy_Students WHERE Hjy_Sno=S52 )(18) 查询所有选课的学生的详细信息。SELECT *FROM Hong

23、jy_StudentsWHERE Hjy_Sno IN(SELECT Hjy_SnoFROM Hongjy_Reports)(19) 查询没有学生选的课程的编号和名称。SELECT Hjy_Cno,Hjy_CnameFROM Hongjy_CoursesWHERE Hjy_Cno NOT IN(SELECT Hjy_CnoFROM Hongjy_Reports)(20) 查询选修了课程名为C+的学生学号和姓名。SELECT Hjy_Sno,Hjy_SnameFROM Hongjy_StudentsWHERE Hjy_Sno IN ( SELECT Hjy_Sno FROM Hongjy_Rep

24、orts WHERE Hjy_Cno IN ( SELECT Hjy_Cno FROM Hongjy_Courses WHERE Hjy_Cname=C+ ) )(21) 找出选修课程UML或者课程C+的学生学号和姓名。SELECT Hjy_Sno,Hjy_SnameFROM Hongjy_StudentsWHERE Hjy_Sno IN ( SELECT Hjy_Sno FROM Hongjy_Reports WHERE Hjy_Cno IN ( SELECT Hjy_Cno FROM Hongjy_Courses WHERE Hjy_Cname=C+ OR Hjy_Cname=UML )

25、)(22) 找出和课程UML或课程C+的学分一样课程名称。SELECT Hjy_CnameFROM Hongjy_CoursesWHERE Hjy_Ccredit=SOME(SELECT Hjy_CcreditFROM Hongjy_CoursesWHERE Hjy_Cname=UML OR Hjy_Cname=C+) /*注：在本题中，将=SOME换成=ANY或IN都可行。*/(23) 查询所有选修编号C01的课程的学生的姓名。SELECT Hjy_SnameFROM Hongjy_StudentsWHERE EXISTS(SELECT *FROM Hongjy_ReportsWHERE H

26、ongjy_Reports.Hjy_Cno=C01 AND Hongjy_Reports.Hjy_Sno=Hongjy_Students.Hjy_Sno)(24) 查询选修了所有课程的学生姓名。SELECT Hjy_SnameFROM Hongjy_StudentsWHERE NOT EXISTS ( SELECT * FROM Hongjy_Reports X WHERE NOT EXISTS ( SELECT * FROM Hongjy_Reports Y WHERE Y.Hjy_Sno=Hongjy_Students.Hjy_Sno AND Y.Hjy_Cno=X.Hjy_Cno ) )

27、/*注：在本题中，将问题分成三个查询来实现：1、最里层的子查询表示存在课程X，学生S没有选修，即在选课记录表Hongjy_Reports中，没有学生S选修课程X的记录。2、第二层子查询表示不存在最里层子查询所表示的这种反例，即在选课记录表Hongjy_Reports中不存在学生S没有选的课程。3、最外层查询是在表Hongjy_Students中取出符合条件的学生姓名*/(25) 利用集合查询方式，查询选修课程C+或选择课程JAVA的学生的编号、姓名和积分。SELECT Hjy_Sno,Hjy_Sname,Hjy_ScreditFROM Hongjy_StudentsWHERE Hjy_Sno

28、IN ( SELECT Hjy_Sno FROM Hongjy_Reports WHERE Hongjy_Reports.Hjy_Cno= ( SELECT Hongjy_Courses.Hjy_Cno FROM Hongjy_Courses WHERE Hongjy_Courses.Hjy_Cname=C+ ) )UNIONSELECT Hjy_Sno,Hjy_Sname,Hjy_ScreditFROM Hongjy_StudentsWHERE Hjy_Sno IN ( SELECT Hjy_Sno FROM Hongjy_Reports WHERE Hongjy_Reports.Hjy_C

29、no= ( SELECT Hongjy_Courses.Hjy_Cno FROM Hongjy_Courses WHERE Hongjy_Courses.Hjy_Cname=JAVA ) )(26) 实现集合交运算，查询既选修课程C+又选修课程JAVA的学生的编号、姓名和积分。SELECT Hjy_Sno,Hjy_Sname,Hjy_ScreditFROM Hongjy_StudentsWHERE Hjy_Sno IN ( SELECT X.Hjy_Sno FROM Hongjy_Reports X,Hongjy_Reports Y WHERE(X.Hjy_Cno=( SELECT Hjy_C

30、no FROM Hongjy_Courses WHERE Hjy_Cname=C+ )AND Y.Hjy_Cno=( SELECT Hjy_Cno FROM Hongjy_Courses WHERE Hjy_Cname=JAVA ) )AND X.Hjy_Sno=Y.Hjy_Sno )(27) 实现集合减运算，查询选修课程C+而没有选修课程JAVA的学生的编号。SQL1：select Hjy_Sno,Hjy_Snamefrom Hongjy_Students, Hongjy_Courses where Hjy_Sno= Hjy_Sno and Hjy_Cno= Hjy_Cno and Hjy_Cname=C+and not exists (select *from Hongjy_Courses where Hjy_Sno= Hjy_Sno and Hjy_Cno= Hjy_Cno and Hjy_Cname=Java)SQL2：select S.Hjy_Sno,Hjy_Snamefrom Hongjy_Students S,SC, Hongjy_Courses Cwhere S.Hjy_Sno=SC.Hjy_Sno and SC.Hjy_Cno=C.Hjy_Cno and C.Hjy_Cna