1、数据库实验三七实验三 数据表的创建与管理第一个表，学生表的创建和插入数据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 IN
2、TO 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,男, 机械工程系
3、)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 CO
4、URSEVALUES (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,计
5、算机网络,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 (090
6、01101,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 S
7、CVALUES (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,GR
8、ADEFROM 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.C
9、NAME,C2.PCNO AS PPCNOFROM COURSE C1,COURSE C2WHERE C1.PCNO=C2.CNO实验五 嵌套查询用 Transact-SQL 语句表示下列操作，在学生选课库中实现其数据嵌套查询操作：(1)查询选修了离散数学的学生学号和姓名SELECT SNO,SNAMEFROM STUDENTWHERE SNO IN(SELECT SNOFROM SCWHERE CNO=(SELECT CNOFROM COURSEWHERE CNAME=离散数学)(2)查询 0101 课程的成绩高于张林的学生学号和成绩SELECT SNO,GRADEFROM SCWHERE
10、CNO=0101 AND GRADE(SELECT GRADEFROM SCWHERE CNO=0101 AND SNO=(SELECT SNOFROM STUDENTWHERE SNAME=张林)(3)查询其他系中年龄小于计算机系年龄最大者的学生SELECT * FROM STUDENTWHERE DEPTB.CNO实验六 组合查询和统计查询在学生选课数据库中实现查询操作：(1)查找选修“计算机基础”课程的学生成绩比此课程平均成绩大的学生学号，成绩。SELECT SC.SNO,GRADEFROM SC,COURSEWHERE SC.CNO=COURSE.CNOAND COURSE.CNAME
11、=计算机基础AND GRADE(SELECT AVG(GRADE)FROM SC,COURSEWHERE SC.CNO=COURSE.CNOAND COURSE.CNAME=计算机基础)(2)查询选修计算机基础课程的学生的平均成绩SELECT AVG(GRADE)FROM SC,COURSEWHERE SC.CNO=COURSE.CNOAND COURSE.CNAME=计算机基础(3)查询年龄大于女同学平均年龄的男同学姓名和年龄SELECT SNAME,AGEFROM STUDENTWHERE SEX=男AND AGE(SELECT AVG(AGE)FROM STUDENTWHERE SEX=
12、女)(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.SNOFROM SC A,SC BWHE
13、RE A.SNO=B.SNO AND A.CNO=(SELECT CNOFROM COURSEWHERE CNAME=离散数学)AND B.CNO=(SELECT CNOFROM COURSEWHERE 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,gradefrom student,sc,coursewhere student.sno=sc.sno and o=o and grade80删除视图drop view view_stu_grade创建索引use 学生选课create index IX_dept on student(dept)create index IX_age on student(age desc)删除索引（部分略）use 学生选课drop index IX_dept（实际情况drop index IX_dept on student）
