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

湘潭大学实验报告课程： 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%';8) 显示所有员工的名字和各项收入总和。

create or replace view v8 as select ename,sal+comm as sal_comm from emp;9) 查询每个部门的平均工资。

create or replace view v9 as select job,avg(sal) as avg_sal from emp group by job;10) 查询出每个部门中工资最高的职工。

create or replace view v10 asselect 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,(select avg(sal) c,deptno from emp group by deptno) b where a.deptno=b.deptno and a.sal>b.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 where 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.hiredate<(select B.hiredate from emp B where A.mgr=B.empno);16) 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门Create or replace view v16 as select dname,empno,ename,job,mgr, hiredate, comm, deptno,sal,deptno from emp natural right outer join dept ;17) 列出所有“CLERK”（办事员）的姓名及其部门名称。

Create or replace view v17 as select ename,dname from emp natural join dept where JOB='CLERK' ;18) 列出最低薪金大于3500的各种工作。

Create or replace view v17 as select distinct job from emp where sal>3500;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 replace 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 v23 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 natural 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 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_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 grade<60;33) 查询选了但还没有登记考试成绩的学生的学号。