oracle湘潭大学数据库数据查询实验报告

83) 统计选课人数最多的课程有多少人。

create or replace view V83(max) as SELECT max(id_sum) from (select course_id,count(id) as id_sum from takes group by course_id);

84) 查询每学期选课人数最多的课程的编号。

create or replace view v84(semester,max_id) as select semester,max(id_sum) from (select course_id,semester,count(id) as id_sum from takes group by course_id,semester) group by semester;

85) 查询每学期选课人数最多的课程的课程名。

Create or replace view v85 as select year,semester,title

from (select year,semester,title,count(id) as numbers from takes natural join course group by year,semester,title) where (year,semester,numbers) in (select year,semester,course_id,count(id) as numbers from takes group by year,semester,course_id) group by year,semester);

86) 查询至少同时选修了CS013号和CS021号两门课程的学生的学号。 create or replace view v86 as select distinct id from takes where course_id='CS013' and id in( select id from takes where course_id='CS021');

87) 查询至少同时选修了CS013号和CS021号两门课程的计算机系、姓刘的学生的姓名。 Create or replace view v87 as select distinct id from takes natural join student where course_id='CS013' and dept_name='Comp. Sci.' and name like '刘%' and id in( select id from takes where course_id='CS021');

88) 查询选修了化学系开设的全部课程的学生的学号。

Create or replace view v88(id,count_id) as select id,count(course_id) from takes group by id having count(course_id)=(select count(course_id) from course where dept_name='Chemistry');

89) 查询选修了化学系开设的全部课程的学生的姓名。

Create or replace view v89 as select name from takes natural join student group by name having count(course_id)=(select count(course_id) from course where dept_name='Chemistry');

90) 查询选修了化学系开设的全部课程的化学系的学生的姓名。 Create or replace view v90(name,count_id,deptname) as select name,count(course_id),dept_name from takes natural join student group by name,dept_name having dept_name='Chemistry' and count(course_id)=(select count(course_id) from course where dept_name='Chemistry');

91) 查询至少选修了02405（学号）选修的全部课程的学生学号。

Create or replace view v91 as select takes.id from takes,(select id,count(course_id) t from takes group by id) A,(select count(course_id) T from takes where id='02405' group by id) B where takes.course_id=(select course_id from takes where id='02405') and A.t=B.T and takes.id=A.id;

93) Find the IDs of all students who were taught by an instructor named Einstein;make sure there are no duplicates in the result.

Create or replace view v93 as select id from takes where id in(select course_id from instructor natural join teaches where name='Einstein');

94) Find all instructors earning the highest salary (there may be more than one with the same salary).

Create or replace view v94 as select id from instructor group by id,salary having salary=(select max(salary) from instructor);

95) Find the enrollment of each section that was offered in Autumn 2009.Note that if a section does not have any students taking it, it would not appear in the result.

Create or replace view v95 as select * from section where semester='Fall' and year='2009' and course_id in (select course_id from takes);

96) Find the maximum enrollment, across all sections, in Autumn 2009.

Create or replace view v96(max) as select max(capacity) from section natural join classroom where semester='Fall' and year='2009';

湘潭大学 实验报告

课程： Oracle数据库

实验题目： 数据查询

学院： 信息工程学院 专业： 计算机科学与技术2班 学号：2013551417

姓名： 韩林波 指导教师： 郭云飞

完成日期：2015.5.25

一．上机目的

1. 掌握Select语句的运用， 2. 掌握一些函数的应用， 3. 掌握子查询的运用， 4. 掌握连接和分组的应用， 5. 掌握视图的创建。

二． 实验内容

常用oracle语句的学习，与相应视图的创建

三．上机作业

写出下列应用对应的SQL语句，并将查询语句定义为视图，视图名根据题号依次命名为V1、V2、?，如果一个应用要定义多个视图，则视图名根据题号依次命名为V1_1、V1_2、?。

针对基本表EMP和DEPT完成下列查询 1) 检索EMP中所有的记录。

create or replace view v1 as select * from emp;

2) 列出工资在1000到2000之间的所有员工的ENAME，DEPTNO，SAL。

create or replace view v2 as select ename,deptno,sal from emp where sal between 1000 and 2000;

3) 显示DEPT表中的部门号和部门名称，并按部门名称排序。

create or replace view v3 as select dname,deptno from dept group by DNAME,deptno;

4) 显示所有不同的工作类型。

create or replace view v4 as select distinct job from emp;

5) 列出部门号在10到20之间的所有员工，并按名字的字母排序。

create or replace view v5 as select ename from emp where deptno between 10 and 20 order by ename;

6) 列出部门号是20，工作是“CLERK”（办事员）的员工。

create or replace view v6 as select ename from emp where deptno='20' and job='CLERK';

7) 显示名字中包含TH和LL的员工名字。

create or replace view v7 as select ename from emp where ename like'%TH%' or ename like '%LL%';

74) 查询2010年没有选修CS013号课程的计算机科学系的学生姓名。

create or replace view v74 as select name,dept_name from student natural left outer join takes where course_id!='CS013' and dept_name='Comp. Sci.';

75) 查询2010年春季考试成绩有不及格的学生的学号与姓名。

create or replace view v75 as select id,name from takes natural join student where semester='Spring' and year='2010' and tot_cred<60;

76) 查询2010年春季考试成绩有不及格的学生的学号、姓名与课程名

create or replace view v76 as select id,name,title from takes natural join student natural join course where semester='Spring' and year='2010' and tot_cred<60;

77) 查询平均成绩大于80的学生学号与姓名。

create or replace view v77(id,name,avg_tot_cred) as select id,name,avg(tot_cred) from student natural left outer join student group by id,name having avg(tot_cred)>80;

78) 查询CS013号课程成绩超过该课程平均成绩的学生的学号。

create or replace view v78 as select id,name from takes natural join student where course_id='CS013' and tot_cred>(select avg(tot_cred) from takes natural join student );

79) 查询2010年CS013号课程成绩超过该课程平均成绩的学生的学号与姓名。

create or replace view v79 as select id,name from takes natural join student where course_id='CS013' and year='2010' and tot_cred>(select avg(tot_cred) from takes natural join student );

80) 查询每个学生考试成绩超过他选修课程平均成绩的课程号。

create or replace view v80(ID,course_id) as select a.id,a.course_id from (select id,course_id,grade from takes natural join student) a,(select avg(grade) c,id from takes group by id) b

where a.id=b.id and a.grade>b.c;

81) 查询2010年春季选修人数多于120人的课程号与课程名。

create or replace view v81 as select course_id,title from takes natural join course group by course_id,title having count(id)>120;

82) 统计每个学生已经取得的学分（假设60分及格）。

Create or replace view v82 as select name, tot_cred from student;

56) 统计每个系任课教师的人数。

create or replace view v56(dept_name,id_num) as select dept_name,count(id) from instructor group by dept_name;

57) 统计计算机科学系每个学生有成绩的课程门数和平均成绩。 create or replace view v57(id,avg_grade,course_id_num) as select id,avg(grade),count(course_id) from takes where grade is not null group by id;

58) 统计每门课程的平均成绩。

create or replace view v58(course_id,avg_grade) as select course_id,avg(grade) from takes group by course_id;

59) 统计每个学生的平均成绩。

create or replace view v59(id,avg_grade) as select id,avg(grade) from takes group by id;

60) 统计每门课程的平均成绩、最高成绩与最低成绩。

create or replace view v60(id,avg_grade,max_grade,min_grade) id,avg(grade),max(grade),min(grade) from takes group by id;

as

select

61) 统计每门课程的选修人数、平均成绩、最高成绩与最低成绩。

create or replace view v61(course_id,count_id,avg_agrade,max_grade,min_grade) as select course_id,count(id),avg(grade),max(grade),min(grade) from takes group by course_id;

62) 统计每门课程有成绩的学生人数、平均成绩、最高成绩与最低成绩。

create or replace view v62(course_id,count_id,avg_agrade,max_grade,min_grade) as select course_id,count(id),avg(grade),max(grade),min(grade) from takes where grade is not null group by course_id ;

63) 计算每个学生有成绩的课程门数和平均成绩。 create or replace view v63(id,course_id_num,avg_grade) id,count(course_id),avg(grade) from takes group by id;

as

select

64) 查询选修了3 门课程以上的学生的学号和姓名。

create or replace view v64 as select id,name from takes natural join student group by id,name having count(course_id)>=3 ;

65) 查询平均成绩大于90的学生学号。

create or replace view v65 as select id from takes group by id having avg(grade) >90;

66) 查询选修人数多于198人的开课。

create or replace view v66 as select course_id from takes group by course_id having count(course_id)>198;

67) 如果某年某学期同一开课号的课程由多位教师分段讲授，列出这样的开课与授课教师人数。假如 2014 年秋季只开设了一门“数据库系统”课程，由教师 A 讲授前 10 章（第 1 至 6 周）、由教师 B 讲授后 10 章（第 9至 12 周）。 create or replace view v67(course_id,teacher_number) as select

course_id,count(id) from teachers group by

year,semester,sec_id,course_id having count(id)>=2;

68) 找出选课人数大于教室容量的开课

create or replace view v68(course_id,id_num,capacity) as select course_id,count(id), capacity from section natural join classroom natural join takes group by course_id, capacity having count(id)> capacity;

69) 查询选修了CS013号课程的学生学号与姓名。

create or replace view v69 as select id,name from takes natural join student where course_id='CS013';

70) 查询2010年秋季选修了CS013号课程的学生学号、姓名。

create or replace view v70 as select id,name,semester from takes natural join student where course_id='CS013' and semester='Fall' and year='2010';

71) 查询2010年秋季选修了CS013号课程的学生学号、姓名、课程名称及成绩。

create or replace view v71 as select id,name,dept_name,grade from takes natural join student where course_id='CS013' and semester='Fall'and year='2010';

72) 查询2010年秋季选修课程名为\的学生学号与姓名。

create or replace view v72 as select id,name from takes natural join student where course_id in (select course_id from section natural join course where semester='Fall' and year='2010' and title='C Programming');

73) 查询2010年没有选修CS013号课程的学生姓名与所在系。

create or replace view v73 as select name,dept_name from student natural left outer join takes where course_id!='CS013';

百度搜索“yundocx”或“云文档网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，云文档网，提供经典综合文库oracle湘潭大学数据库数据查询实验报告在线全文阅读。