数据库实验三~七.doc

数据库实验三~七
实验三 数据表的创建与管理
第一个表，学生表的创建和插入数据

CREATE TABLE STUDENT
(
SNO CHAR(8) PRIMARY KEY,
SNAME CHAR(8) NOT NULL,
AGE INT,
SEX CHAR(2),
DEPT VARCHAR(50)
);

INSERT INTO STUDENT
VALUES ('09001101','张林',18,'男','计算机系')
INSERT INTO STUDENT
VALUES ('09001102','程明',18,'男','计算机系')
INSERT INTO STUDENT
VALUES ('09001103','王艳',19,'女','计算机系')
INSERT INTO STUDENT
VALUES ('09001104','严平平',20,'女','计算机系')
INSERT INTO STUDENT
VALUES ('09001105','王洪敏',19,'女','信息管理系')
INSERT INTO STUDENT
VALUES ('09001106','孙祥新',18,'男','信息管理系')
INSERT INTO STUDENT
VALUES ('09001107','吕占英',19,'女','信息管理系')
INSERT INTO STUDENT
VALUES ('09001108','李义',19,'男','机械工程系')
INSERT INTO STUDENT
VALUES ('09001109','牟万里',18,'男','机械工程系')
INSERT INTO STUDENT
VALUES ('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 COURSE
VALUES ('0101','计算机基础',5,'0101','可自学')
INSERT INTO COURSE
VALUES ('0102','C++程序设计',4,NULL,'可自学')
INSERT INTO COURSE
VALUES ('0206','离散数学',4,'0102','可自学')
INSERT INTO COURSE
VALUES ('0208','数据结构',4,'0101','可自学')
INSERT INTO COURSE
VALUES ('0209','操作系统',4,'0101','可自学')
INSERT INTO COURSE
VALUES ('0210','微机原理',5,'0101','可自学')
INSERT INTO COURSE
VALUES ('0211','图形学',3,'0102','可自学')
INSERT INTO COURSE
VALUES ('0212','数据库原理',4,'0102','可自学')
INSERT INTO COURSE
VALUES ('0301','计算机网络',3,'0102','可自学')
INSERT INTO COURSE
VALUES ('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 SC
VALUES ('09001101','0101',68)
INSERT INTO SC
VALUES ('09001101','0206',76)
INSERT INTO SC
VALUES ('09001103','0101',62)
INSERT INTO SC
VALUES ('09001106','0209',75)
INSERT INTO SC
VALUES ('09001106','0210',77)
INSERT INTO SC
VALUES ('09001106','0212',75)
INSERT INTO SC
VALUES ('09001106','0301',87)
INSERT INTO SC
VALUES ('09001108','0101',68)
INSERT INTO SC
VALUES ('09001109','0212',88)
INSERT INTO SC
VALUES ('09001109','0302',76)
INSERT INTO SC
VALUES ('09001110','0101',66)
实验四 简单查询和连接查询
1）简单查询实验
用Transact-SQL语句表示下列操作，在“学生选课”数据库中实现数据库查询操作：
（1）查询数学系学生的学号和姓名
SELECT SNO,SNAME
FROM STUDENT
WHERE DEPT='数学系';
（2）查询选修了课程的学生学号
SELECT DISTINCT SNO
FROM SC;
（3）查询选修课程号为0101的学生学号成绩，并要求对查询结果按成绩降序排列。如果成绩相同则按学号升序排列
SELECT SNO,GRADE
FROM SC
WHERE CNO='0101'
ORDER BY GRADE,SNO ASC;
（4）查询选修课程号为0101的成绩在80~90分之间的学生学号和成绩，并将成绩乘以系数0.8输出
SELECT SNO,GRADE*0.8 AS NEW_GRADE
FROM SC
WHERE CNO='0101' AND (GRADE BETWEEN 80 AND 90)
或者
SELECT SNO,GRADE*0.8 AS NEW_GRADE
FROM SC
WHERE CNO='0101' AND GRADE > 80 AND GRADE < 90
（5）查询数学系或计算机系姓张的学生的信息
SELECT *
FROM STUDENT
WHERE DEPT IN ('数学系','计算机系') AND SNAME LIKE '张%'
（6）查询缺少了成绩的学生的学号和课程号
SELECT SNO,CNO
FROM SC
WHERE GRADE IS NULL
2）连接查询实验
用Transact-SQL语句表示下列操作，在“学生选课”数据库中实现数据库查询操作：
（1）查询每个学生的情况以及他（她）所选修的课程
SELECT S.SNO,S.SNAME,S.AGE,S.SEX,S.DEPT,SC.CNO,C.CNAME
FROM STUDENT S,SC,COURSE C
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO
（2）查询学生的学号、姓名、选修的课程名及成绩
SELECT S.SNO,S.SNAME,C.CNAME,SC.GRADE
FROM STUDENT S,SC,COURSE C
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO
（3）查询选修离散数学课程且成绩为90分以上的学生学号、姓名及成绩
SELECT S.SNO,S.SNAME,SC.GRADE
FROM STUDENT S,SC,COURSE C
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND C.CNAME='离散数学' AND GRADE > 90
（4）查询每一门课的间接先行课
SELECT C1.CNO,C1.CNAME,C2.PCNO AS PPCNO
FROM COURSE C1,COURSE C2
WHERE C1.PCNO=C2.CNO
实验五 嵌套查询
用Transact-SQL语句表示下列操作，在学生选课库中实现其数据嵌套查询操作：
(1)查询选修了离散数学的学生学号和姓名
SELECT SNO,SNAME
FROM STUDENT
WHERE SNO IN
(SELECT SNO
FROM SC
WHERE CNO=(SELECT CNO
FROM COURSE
WHERE CNAME='离散数学')
)
(2)查询0101课程的成绩高于张林的学生学号和成绩
SELECT SNO,GRADE
FROM SC
WHERE CNO='0101' AND GRADE>
(SELECT GRADE
FROM SC
WHERE CNO='0101' AND SNO=(SELECT SNO
FROM STUDENT
WHERE SNAME='张林')
)
(3)查询其他系中年龄小于计算机系年龄最大者的学生
SELECT *
FROM STUDENT
WHERE DEPT<>'计算机系' AND AGE<ANY(SELECT AGE
FROM STUDENT
WHERE DEPT='计算机系')
或
SELECT *
FROM STUDENT
WHERE DEPT<>'计算机系' AND AGE<(SELECT MAX(AGE)
FROM STUDENT
WHERE DEPT='计算机系')
(4)查询其他系中比计算机系学生年龄都小的学生
SELECT *
FROM STUDENT
WHERE DEPT<>'计算机系' AND AGE<ALL(SELECT AGE
FROM STUDENT
WHERE DEPT='计算机系')
或
SELECT *
FROM STUDENT
WHERE DEPT<>'计算机系' AND AGE<(SELECT MIN(AGE)
FROM STUDENT
WHERE DEPT='计算机系')
(5)查询同王洪敏数据库原理课程分数相同的学生的学号
SELECT SNO
FROM SC
WHERE 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 SNAME
FROM STUDENT
WHERE SNO IN (SELECT SNO
FROM SC
WHERE CNO='0206')
或
SELECT SNAME
FROM STUDENT S,SC
WHERE S.SNO=SC.SNO AND CNO IN (SELECT CNO
FROM SC
WHERE CNO='0206')
(7)查询没有选修0206课程的学生姓名
SELECT SNAME
FROM STUDENT
WHERE SNO NOT IN (SELECT SNO
FROM SC
WHERE CNO='0206')
(8)查询选修了全部课程的学生的姓名
SELECT SNAME
FROM STUDENT
WHERE NOT EXISTS
(SELECT *
FROM COURSE
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE SNO=STUDENT.SNO AND CNO=COURSE.CNO)
)
逻辑描述：没有一门课是学生没有选的

例子：查询选修了两门课的学生
SELECT DISTINCT A.SNO
FROM SC A,SC B
WHERE A.SNO=B.SNO AND A.CNO<>B.CNO
实验六 组合查询和统计查询
在学生选课数据库中实现查询操作：
(1)查找选修“计算机基础”课程的学生成绩比此课程平均成绩大的学生学号，成绩。
SELECT SC.SNO,GRADE
FROM SC,COURSE
WHERE 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,COURSE
WHERE SC.CNO=COURSE.CNO
AND COURSE.CNAME='计算机基础'
(3)查询年龄大于女同学平均年龄的男同学姓名和年龄
SELECT SNAME,AGE
FROM STUDENT
WHERE SEX='男'
AND AGE>(SELECT AVG(AGE)
FROM STUDENT
WHERE SEX='女')
(4)列出各系学生总人数，并按人数进行降序排列
SELECT DEPT,COUNT(*) AS '人数'
FROM STUDENT
GROUP BY DEPT
ORDER BY 2 DESC
(5)统计各系各门课程的平均成绩
SELECT SC.CNO,CNAME,AVG(GRADE) AS '平均成绩'
FROM SC,COURSE
WHERE SC.CNO=COURSE.CNO
GROUP BY SC.CNO,CNAME
（6）查询选修计算机基础和离散数学的学生学号和平均成绩
SELECT SNO,AVG(GRADE)
FROM SC
WHERE 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_grade
as select student.sno,sname,cname,grade
from student,sc,course
where student.sno=sc.sno and o=o

修改视图
alter view view_stu_grade
as select student.sno,sname,cname,grade
from student,sc,course
where student.sno=sc.sno and o=o and grade>80

删除视图
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
）