数据库实验三~七.doc

数据库实验三七实验三 数据表的创建与管理第一个表，学生表的创建和插入数据CREATE TABLE STUDENT( SNO CHAR(8) PRIMARY KEY, SNAME CHAR(8) NOT NULL, AGE INT, SEX CHAR(2), DEPT VARCHAR(50);INSERT INTO STUDENTVALUES (09001101,张林,18,男,计算机系)INSERT INTO STUDENTVALUES (09001102,程明,18,男,计算机系)INSERT INTO STUDENTVALUES (09001103,王艳,19,女,计算机系)INSERT INTO STUDENTVALUES (09001104,严平平,20,女,计算机系)INSERT INTO STUDENTVALUES (09001105,王洪敏,19,女,信息管理系)INSERT INTO STUDENTVALUES (09001106,孙祥新,18,男,信息管理系)INSERT INTO STUDENTVALUES (09001107,吕占英,19,女,信息管理系)INSERT INTO STUDENTVALUES (09001108,李义,19,男,机械工程系)INSERT INTO STUDENTVALUES (09001109,牟万里,18,男,机械工程系)INSERT INTO STUDENTVALUES (09001110,刘丽霞,20,女,机械工程系)第二个表，课程表的创建和插入数据CREATE TABLE COURSE( CNO CHAR(4) PRIMARY KEY, CNAME CHAR(20) NOT NULL, CREDIT FLOAT, PCNO VARCHAR(20), DESCRIBE VARCHAR(100);INSERT INTO COURSEVALUES (0101,计算机基础,5,0101,可自学)INSERT INTO COURSEVALUES (0102,C+程序设计,4,NULL,可自学)INSERT INTO COURSEVALUES (0206,离散数学,4,0102,可自学)INSERT INTO COURSEVALUES (0208,数据结构,4,0101,可自学)INSERT INTO COURSEVALUES (0209,操作系统,4,0101,可自学)INSERT INTO COURSEVALUES (0210,微机原理,5,0101,可自学)INSERT INTO COURSEVALUES (0211,图形学,3,0102,可自学)INSERT INTO COURSEVALUES (0212,数据库原理,4,0102,可自学)INSERT INTO COURSEVALUES (0301,计算机网络,3,0102,可自学)INSERT INTO COURSEVALUES (0302,软件工程,3,0102,可自学)第三个表，选课表的创建和插入数据CREATE TABLE SC( SNO CHAR(8), CNO CHAR(4), GRADE FLOAT, PRIMARY KEY (SNO,CNO), FOREIGN KEY (SNO) REFERENCES STUDENT(SNO), FOREIGN KEY (CNO) REFERENCES COURSE(CNO);INSERT INTO SCVALUES (09001101,0101,68)INSERT INTO SCVALUES (09001101,0206,76)INSERT INTO SCVALUES (09001103,0101,62)INSERT INTO SCVALUES (09001106,0209,75)INSERT INTO SCVALUES (09001106,0210,77)INSERT INTO SCVALUES (09001106,0212,75)INSERT INTO SCVALUES (09001106,0301,87)INSERT INTO SCVALUES (09001108,0101,68)INSERT INTO SCVALUES (09001109,0212,88)INSERT INTO SCVALUES (09001109,0302,76)INSERT INTO SCVALUES (09001110,0101,66)实验四 简单查询和连接查询1）简单查询实验用Transact-SQL语句表示下列操作，在“学生选课”数据库中实现数据库查询操作：（1）查询数学系学生的学号和姓名SELECT SNO,SNAMEFROM STUDENTWHERE DEPT=数学系;（2）查询选修了课程的学生学号SELECT DISTINCT SNOFROM SC;（3）查询选修课程号为0101的学生学号成绩，并要求对查询结果按成绩降序排列。如果成绩相同则按学号升序排列SELECT SNO,GRADEFROM SCWHERE CNO=0101ORDER BY GRADE,SNO ASC;（4）查询选修课程号为0101的成绩在8090分之间的学生学号和成绩，并将成绩乘以系数0.8输出SELECT SNO,GRADE*0.8 AS NEW_GRADEFROM SCWHERE CNO=0101 AND (GRADE BETWEEN 80 AND 90)或者SELECT SNO,GRADE*0.8 AS NEW_GRADEFROM SCWHERE CNO=0101 AND GRADE 80 AND GRADE 90（4）查询每一门课的间接先行课SELECT C1.CNO,C1.CNAME,C2.PCNO AS PPCNOFROM COURSE C1,COURSE C2WHERE C1.PCNO=C2.CNO实验五 嵌套查询用Transact-SQL语句表示下列操作，在学生选课库中实现其数据嵌套查询操作：(1)查询选修了离散数学的学生学号和姓名SELECT SNO,SNAMEFROM STUDENTWHERE SNO IN (SELECT SNO FROM SC WHERE CNO=(SELECT CNO FROM COURSE WHERE CNAME=离散数学) )(2)查询0101课程的成绩高于张林的学生学号和成绩SELECT SNO,GRADEFROM SCWHERE CNO=0101 AND GRADE (SELECT GRADE FROM SC WHERE CNO=0101 AND SNO=(SELECT SNO FROM STUDENT WHERE SNAME=张林) )(3)查询其他系中年龄小于计算机系年龄最大者的学生SELECT * FROM STUDENTWHERE DEPT计算机系 AND AGEANY(SELECT AGE FROM STUDENT WHERE DEPT=计算机系)或SELECT * FROM STUDENTWHERE DEPT计算机系 AND AGE(SELECT MAX(AGE) FROM STUDENT WHERE DEPT=计算机系)(4)查询其他系中比计算机系学生年龄都小的学生SELECT * FROM STUDENTWHERE DEPT计算机系 AND AGEALL(SELECT AGE FROM STUDENT WHERE DEPT=计算机系)或SELECT * FROM STUDENTWHERE DEPT计算机系 AND AGE(SELECT MIN(AGE) FROM STUDENT WHERE DEPT=计算机系)(5)查询同王洪敏数据库原理课程分数相同的学生的学号SELECT SNOFROM SCWHERE SNO(SELECT SNO FROM STUDENT WHERE SNAME=王洪敏) AND CNO IN (SELECT CNO FROM COURSE WHERE CNAME=数据库原理) AND GRADE=(SELECT GRADE FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND SNAME=王洪敏 AND CNAME=数据库原理)(6) 查询选修了0206课程的学生姓名SELECT SNAMEFROM STUDENTWHERE SNO IN (SELECT SNO FROM SC WHERE CNO=0206)或SELECT SNAMEFROM STUDENT S,SCWHERE S.SNO=SC.SNO AND CNO IN (SELECT CNO FROM SC WHERE CNO=0206)(7)查询没有选修0206课程的学生姓名SELECT SNAMEFROM STUDENTWHERE SNO NOT IN (SELECT SNO FROM SC WHERE CNO=0206)(8)查询选修了全部课程的学生的姓名SELECT SNAMEFROM STUDENTWHERE NOT EXISTS (SELECT * FROM COURSE WHERE NOT EXISTS (SELECT * FROM SC WHERE SNO=STUDENT.SNO AND CNO=COURSE.CNO) )逻辑描述：没有一门课是学生没有选的例子：查询选修了两门课的学生SELECT DISTINCT A.SNOFROM SC A,SC BWHERE A.SNO=B.SNO AND A.CNOB.CNO实验六 组合查询和统计查询在学生选课数据库中实现查询操作：(1)查找选修“计算机基础”课程的学生成绩比此课程平均成绩大的学生学号，成绩。SELECT SC.SNO,GRADEFROM SC,COURSEWHERE SC.CNO=COURSE.CNO AND COURSE.CNAME=计算机基础 AND GRADE(SELECT AVG(GRADE) FROM SC,COURSE WHERE SC.CNO=COURSE.CNO AND COURSE.CNAME=计算机基础)(2)查询选修计算机基础课程的学生的平均成绩SELECT AVG(GRADE)FROM SC,COURSEWHERE SC.CNO=COURSE.CNO AND COURSE.CNAME=计算机基础(3)查询年龄大于女同学平均年龄的男同学姓名和年龄SELECT SNAME,AGEFROM STUDENTWHERE SEX=男 AND AGE(SELECT AVG(AGE) FROM STUDENT WHERE SEX=女)(4)列出各系学生总人数，并按人数进行降序排列SELECT DEPT,COUNT(*) AS 人数FROM STUDENTGROUP BY DEPTORDER BY 2 DESC(5)统计各系各门课程的平均成绩SELECT SC.CNO,CNAME,AVG(GRADE) AS 平均成绩FROM SC,COURSEWHERE SC.CNO=COURSE.CNOGROUP BY SC.CNO,CNAME（6）查询选修计算机基础和离散数学的学生学号和平均成绩SELECT SNO,AVG(GRADE)FROM SCWHERE SNO IN (SELECT A.SNO FROM SC A,SC B WHERE A.SNO=B.SNO AND A.CNO=(SELECT CNO FROM COURSE WHERE CNAME=离散数学) AND B.CNO=(SELECT CNO FROM COURSE WHERE CNAME=计算机基础) )GROUP BY SNO实验七 视图、索引与数据库关系图创建视图create view view_stu_gradeas select student.sno,sname,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and o=o修改视图alter view view_stu_gradeas select student.sno,sname,cname,grad