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 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,(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) 查询选了但还没有登记考试成绩的学生的学号。

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 as 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 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');