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

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

2、1_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,d

3、eptno 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

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

5、select 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 , from emp a,(select avg(

6、sal) c,deptno from emp group by deptno) b where = and 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 where sal(select sal from emp where en

7、ame=SMITH);14) 列出所有员工的姓名及其直接上级的姓名。Create or replace view v14 as select distinct as work1 ,(select ename from emp where = )as work2 from emp A;15) 列出受雇日期早于其直接上级的所有员工。Create or replace view v15 as select from emp A where 3500;19) 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。Create or replace view view v19

8、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 replace view v21 as select ename from emp where job=(select job from emp where ename=

9、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 v23 as select ename,sal from emp where sal(select max(sal) from emp where deptno=

10、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 natural join dept;26) 列出所有部门的详细信息和部门人数。Create or replace view v26 as select dname,

11、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 or replace view v28 as select dname,min(sal) from emp natural join dept where

12、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_num) as select dept_name,count(id) from student group by dept_name;31) 给出有学生的系

13、的名单，按升序排列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 grade60;33) 查询选了但还没有登记考试成绩的学生的学号。Create or replace view v33 as select id from

14、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 as select id,dept_name from student where (dept_name)=(Comp .Sci.) or (dept_name

15、)=(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 or replace view v35_1 as select * from student where dept_name!=Comp. Sci. and

16、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 select title,dept_name from course where title like%计算机% ;37) 列出所有姓名以李开头且只有3个字的学生的学号

17、、姓名与所在系。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) 查询所有姓名中第2个字为小的学生的姓名与所在系。create or replace view v39 as select ID,name,dept_name f

18、rom 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. Sci. department that have 3 credits.create or replace view v41 as select title

19、 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 takes where sec_id=CS013;44) 统计每年选修了课程的学生人数。create or replace view v44(id_num) as s

20、elect 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 replace view v46(id_num,year) as select count(id),year from takes where sec_id=CS013

21、 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 semester,count(id) from takes where sec_id=CS013 group by semester;49) 统计每个学期每门课程

22、的选修的学生人数。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_id,id_num) as select year,semester,course_id,sec_id,count(id) from takes group

23、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) 统计每个系教师的人数、最高工资与最低工资。create or replace view v54(dept_name,id_num,max_salary,min_salary) as select dep

24、t_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,semester;56) 统计每个系任课教师的人数。create or replace view v56(dept_name,id_num) as select dept_

25、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 co

26、urse_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) as select id,avg(grade),max(grade),min(grade) from takes gro

27、up 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) 统计每门课程有成绩的学生人数、平均成绩、最高成绩与最低成绩。create or replace view v62(course_id,count_id,avg_agrad

28、e,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) as 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 avg(grade) 90;66) 查询选修人数多于198人的开课。create