11

1、    创建视图v_student1,使其具有功能：“检索“2012自动化1班”的所有学生学号、姓名、课程号及对应成绩”。

create or replace view v_student1(sno,sname,cno,score) as select student.student_no,student.student_name,choose.course_no,score from student,choose,classes where student.student_no =choose.student_noand student.class_no=classes.class_no and class_name='2012自动化1班' ;

2、    从视图v_student1中查询“成绩大于60分的学生学号、姓名、课程号及对应成绩”。

select sno,sname,cno,score from v_student1 where score>60;

3、    创建视图v_student2,使其具有功能：“检索成绩优秀（>=80）的学生学号、姓名及其选修的课程号和课程名称，分数。”

create or replace view v_student2(sno,sname,cno,cname,score) as

select student.student_no,student_name, choose.course_no, course_name,score from choose,course,student

where student.student_no = choose.student_no and course.course_no =choose.course_no and score >=80;

4、    从视图v_student2中查询“姓张的学生的学号、姓名及其选修的课程号和课程名称及分数。

select * from v_student2 where sname like '张%';

5、    创建视图course_view1,使其具有功能：“统计学生的学号、姓名及平均成绩”。

create or replace view course_view1(sno,sname,avscore) as

select student.student_no,student_name,avg(score)from choose,student

where choose.student_no=student.student_no group bystudent.student_no,student_name;

6、    从视图course_view1中查询“平均成绩>=90分的学生的学号、姓名及平均成绩”

select * from course_view1 where avscore >=90;

7、    创建视图course_view2,使其具有功能：“统计平均成绩高于70分的学生的学号、姓名及平均成绩”；

从视图创建视图：create view course_view2(sno,sname,avscore) as select* from course_view1 where avscore >=70; 

或者：  从表创建视图

create view course_view2(sno,sname,avscore) as

select student.student_no,student_name,avg(score)from choose,student

where choose.student_no = student.student_no groupby student.student_no,student_name

having avg(score)>70;

8、    使用SQL语句查看以上四个视图的结构；

desc v_student1;

desc v_student2;

desc course_view1;

desc course_view2;

9、    使用SQL语句查看以上四个视图的的所有数据；

select * from  v_student1;

select * from v_student2;

select * from course_view1;

select * from course_view2;

10、 删除视图course_view2

drop view course_view2;

实验13  5 -- 9

5. 创建视图available_course_view,使其具有功能：“统计每一门课程已经有多少学生选修，还能供多少学生选修”；

create orreplace view available_course_view(已有人数,剩余名额) as

select count(choose.course_no),up_limit-count(choose.course_no) from choose,course where choose.course_no = course.course_no group bychoose.course_no;

5. 使用SQL语句查看视图available_course_view的结构；

describeavailable_course_view;

6. 使用SQL语句查看视图available_course_view的数据值；

select *from  available_course_view;

7. 使用SQL语句像课程表course中新增字段available，默认值为0；

altertable course add available_test int default 0;（因为给的数据库备份里面已有available字段，请使用其他字段名测试这个命令）

8. 使用SQL语句将每一门课程的available字段值设置为“剩余的学生名额”。 Available字段值从available_course_view视图中获取。

（1）alter table course change available_test 剩余的学生名额 int;

（2）需要重新创建view ，因为缺少参照比对的字段course_no

第1步：create or replace view available_course_view(course_no,已有人数,剩余名额) as select choose.course_no,count(choose.course_no),up_limit-count(choose.course_no) from choose,course wherechoose.course_no=course.course_no group by choose.course_no;

第2步：update course set 剩余的学生名额=(select 剩余名额 from available_course_view where available_course_view.course_no=course.course_no);