oracle湘潭大学数据库数据查询实验报告[教学试题]

1、湘潭大学 实 验 报 告课 程： Oracle数据库 实验题目： 数据查询 学 院： 信息工程学院 专 业： 计算机科学与技术2班 学 号： 2013551417 姓 名： 韩林波 指导教师： 郭云飞 完成日期： 2015.5.25 一上机目的1. 掌握Select语句的运用，2. 掌握一些函数的应用，3. 掌握子查询的运用，4. 掌握连接和分组的应用，5. 掌握视图的创建。二 实验内容常用oracle语句的学习，与相应视图的创建三上机作业写出下列应用对应的SQL语句，并将查询语句定义为视图，视图名根据题号依次命名为V1、V2、，如果一个应用要定义多个视图，则视图名根据题号依次命名为V1_1、

2、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,deptn

3、o 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 sele

4、ct 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%;8) 显示所有员工的名字和各项收入总和。create or replace view v8 as select ename,sal+comm as sal_comm from emp;9) 查询每个部门的平均工资。create or replace view v9 as sele

5、ct job,avg(sal) as avg_sal from emp group by job;10) 查询出每个部门中工资最高的职工。create or replace view v10 as select ename,job,max(sal) as max_sal from emp group by job,ename ;11) 查询出每个部门比本部门平均工资高的职工人数。Create or replace view v11(deptno,count) as select deptno,count(*) from (select a.deptno,a.ename from emp a,(

6、select avg(sal) c,deptno from emp group by deptno) b where a.deptno=b.deptno and a.salb.c) group by deptno;12) 列出至少有一个员工的所有部门。Create or replace view v12 as select job,count(ename) from emp group by job having count(ename) 0;13) 列出薪金比“SMITH”多的所有员工。Create or replace view v13 as select ename from emp w

7、here sal(select sal from emp where ename=SMITH);14) 列出所有员工的姓名及其直接上级的姓名。Create or replace view v14 as select distinct A.ename as work1 ,(select ename from emp where (A.mgr=emp.empno) )as work2 from emp A;15) 列出受雇日期早于其直接上级的所有员工。Create or replace view v15 as select A.ename from emp A where A.hiredate35

8、00;19) 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。Create or replace view view v19 as select ename,dname from emp natural join dept where dname=SALES ;20) 列出薪金高于公司平均薪金的所有员工。Create or replace view v20 as select ename from emp where sal(select avg(sal) from emp );21) 列出与“SCOTT”从事相同工作的所有员工。Create or replac

9、e view v21 as select ename from emp where job=(select job from emp where ename=SCOTT) and ename!=SCOTT;22) 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。Create or replace view v22 as select ename,sal from emp where sal in(select sal from emp where deptno=30);23) 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。Create or replace view v2

10、3 as select ename,sal from emp where sal(select max(sal) from emp where deptno=30);24) 列出在每个部门工作的员工数量、平均工资。Create or replace view 24 as select dname,count(ename),avg(sal) from emp natural join dept group by dname;25) 列出所有员工的姓名、部门名称和工资。Create or replace view v25 as select ename,dname,sal from emp nat

11、ural join dept;26) 列出所有部门的详细信息和部门人数。Create or replace view v26 as select dname,count(ename),avg(sal),loc,deptno from emp natural right outer join dept group by dname,loc,deptno ;27) 列出各种工作的最低工资。Create or replace view v27 as select job,min(sal) from emp group by job;28) 列出各个部门的MANAGER（经理）的最低薪金。Create

12、 or replace view v28 as select dname,min(sal) from emp natural join dept where empno in (select mgr from emp ) group by dname;29) 列出所有员工的年工资，按年薪从低到高排序。Create or replace view v29 as select ename,sal*12 as year_salary from emp order by year_salary;30) 给出有学生的系的名单。create or replace view v30(dept_name,id

13、_num) as select dept_name,count(id) from student group by dept_name;31) 给出有学生的系的名单，按升序排列create or replace view v31(dept_name,id_num) as select dept_name,count(id) from student group by dept_name order by count(id);32) 查询考试成绩有不及格的学生的学号。create or replace view V32 as select distinct id from takes where

14、 grade60;33) 查询选了但还没有登记考试成绩的学生的学号。Create or replace view v33 as select id from takes where grade is null and course_id is not null;34) 列出计算机科学系与物理系的学生。（三种方式）create or replace view v34_1 as select id,dept_name from student where dept_name=Comp .Sci. or dept_name=Physics;create or replace view v34_2 a

15、s select id,dept_name from student where (dept_name)=(Comp .Sci.) or (dept_name)=(Physics) ;create or replace view v34_3 as select id,dept_name from student where dept_name in(select dept_name from student where dept_name=(Comp .Sci.) or (dept_name)=(Physics) );35) 列出除计算机科学系与物理系外其他系的学生。（三种方式）create

16、or replace view v35_1 as select * from student where dept_name!=Comp. Sci. and dept_name!=Physics;create or replace view v35_3 as select * from student where id not in(select id from student where dept_name=Comp. Sci. or dept_name=Physics);36) 列出名称中含有计算机的课程的名称与开课系。create or replace view v36 as selec

17、t title,dept_name from course where title like%计算机% ;37) 列出所有姓名以李开头且只有3个字的学生的学号、姓名与所在系。create or replace view v37 as select ID,name,dept_name from student where name like李_;38) 列出所有姓名以李开头、以军结束且只有3个字的学生的学号、姓名。create or replace view v38 as select ID,name,dept_name from student where name like李_军;39) 查

18、询所有姓名中第2个字为小的学生的姓名与所在系。create or replace view v39 as select ID,name,dept_name from student where name like_小%;40) 列出2010年春季选修了CS013号课程的学生学号及其成绩。create or replace view v40 as select ID,grade from takes where course_id=CS013 and year=2010 and semester=Spring;41) Find the titles of courses in the Comp.

19、 Sci. department that have 3 credits.create or replace view v41 as select title from course where dept_name=Comp. Sci. and credits=3;42) 统计学生总人数。create or replace view v42(id_num) as select count(id) from student;43) 统计选修了CS013号课程的学生人数。create or replace view v43(id_num) as select count(id) from take

20、s where sec_id=CS013;44) 统计每年选修了课程的学生人数。create or replace view v44(id_num) as select count(id) from takes group by year;45) 统计每年选修了课程的学生人数，按年份升序排列。create or replace view v45(id_num) as select count(id) from takes where sec_id=CS013 group by year order by year;46) 统计每年选修了CS013号课程的学生人数。create or repla

21、ce view v46(id_num,year) as select count(id),year from takes where sec_id=CS013 group by year;47) 统计各个学期选修了课程的学生人数。create or replace view v47(semester,id_num) as select semester,count(id) from takes group by semester;48) 统计各个学期选修了CS013号课程的学生人数。create or replace view v48(semester,id_num) as select se

22、mester,count(id) from takes where sec_id=CS013 group by semester;49) 统计每个学期每门课程的选修的学生人数。create or replace view v49(semester,course_id,id_num) as select semester,course_id,count(id) from takes group by semester,course_id;50) 按年、学期、课程与开课号统计选修学生人数。create or replace view v50(year,semester,course_id,sec_

23、id,id_num) as select year,semester,course_id,sec_id,count(id) from takes group by year,semester,course_id, sec_id;51) 统计2008年春季各门课程不及格学生的人数。create or replace view v51(course_id,id_num) as select course_id,count(id) from takes where grade(select avg(salary) from instructor);54) 统计每个系教师的人数、最高工资与最低工资。c

24、reate or replace view v54(dept_name,id_num,max_salary,min_salary) as select dept_name,count(id),max(salary),min(salary) from instructor group by dept_name;55) 统计各个学期每位教师授课门数。create or replace view v55(teaches_id,semester,course) as select id,semester,count(course_id) from teaches group by id,semeste

25、r;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 i

26、d; 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,ma

27、x_grade,min_grade) as select id,avg(grade),max(grade),min(grade) from takes group by id;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) 统计每门课程

28、有成绩的学生人数、平均成绩、最高成绩与最低成绩。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) a

29、s select id,count(course_id),avg(grade) from takes group by id;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

30、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,te

31、acher_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

32、_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=

33、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=Falland year=2010;72) 查询2010年秋季选修课程名为C Programming的学生学号与姓名。create or replace view v72 as sele

34、ct 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

35、where course_id!=CS013;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 joi

36、n student where semester=Spring and year=2010 and tot_cred60;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_cred80;78) 查询CS013号课程成绩超过该课程平均成绩的学生的学号。create or replace v

37、iew 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

38、 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) bwhere a.id=b.id a

39、nd a.gradeb.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;83) 统计选课人数最多的课程有多少人。create or rep

40、lace 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,semest

41、er) group by semester;85) 查询每学期选课人数最多的课程的课程名。Create or replace view v85 as select year,semester,titlefrom (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 number

42、s 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

43、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 gro

44、up 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);9

45、0) 查询选修了化学系开设的全部课程的化学系的学生的姓名。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) 查询至少选修

46、了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

47、;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

48、 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

49、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

50、select max(capacity) from section natural join classroom where semester=Fall and year=2009;97) Find the sections that had the maximum enrollment in Autumn 2009.(with clause)Create or replace view 97 as with max(buiding,capacity) as (select building,max(capacity) from classroom natural join section)

51、select capacity from max;98) Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate Create or replace view v98 as select name from student where id in(select id from takes natural join course group by id,dept_name having dept_name=Comp. Sci. an

52、d count(course_id)1);names in the result.99) Find the IDs and names of all students who have not taken any course offering before Spring 2009.create or replace view v99 (id,name) as select id,name from takes natural join student where course_id not in(select course_id from takes where year2009);100) For each department, find the maximum salary of instructors in that department. You may assume that every department has at least one instructor.Create or replace view v1