Oracle实验指导书和实验报告(6)

3.1 count：

select count(*) from emp;

? 公司中有多少员工由经理管理（即不属于高级管理成层）；

? ? ? ?

select count(mgr) from emp; 查看部门号为10的员工数

select count(*) from emp where deptno=10; 查看有多少个岗位

select count(distict job) from emp; 3.2 avg

? select avg(sal) from emp; 平均工资 3.3 sum

? 查看这个月工人的工资支出

? select sum(sal+nvl(comm,0)) from emp; 3.4min

? select min(sal) from emp;最少工资 3.5max

? select max(sal) from emp;最多工资 ? 查找雇佣第一员工和最迟雇佣员工

? Select min(hiredate),max(hiredate) from emp; 3.6 group by：一般与分组函数一起使用

? Select deptno,avg(sal) from emp group by deptno;

? select job,avg(sal) from emp having avg(sal) >2000 group by job; 3.8分组函数嵌套

? 查找工作不是president的员工中按工作分类不同工作的最低平均工资和最高平均

工资 select min(avg(sal)),max(avg(sal)) from emp where job not like 'PRE%' group by job;

课后练习：

? 分组统计各部门下工资>600 的员工的平均工资 ? 统计各部门下平均工资大于1600 的部门 ? 算出部门 30 中得到最多奖金的员工奖金 ? 算出每个职位的员工数和最低工资 ? 显示每个部门的平均工资和最高工资 ? 查询最高工资的员工姓名、岗位、工资 ? 查询出高于平均工资的员工的信息

? 查出高于本部门员工平均工资的员工信息

4.多表查询 4.1 相等连接

? 查询每个员工所属部门和所在的具体地点

?

select emp.ename,dept.dname,dept.loc from emp.dept where emp.deptno=dept.deptno; ?

?

? ?

select a.ename,b.dname,b.loc from emp a,dept b where a.deptno=b.deptno;

公司高级管理层想知道工资为1600元或以上的员工所属的部门和地点

select emp.ename,dept.dname,dept.loc from emp.dept where emp.deptno=dept.deptno and emp.sal>=1600

4.2 自连接

老板要找每个分析员的上司谈话，要查询每个分析员及上司的姓名

? select w.ename \雇员名\雇员工作\经理姓名\

m.job \经理工作\w.job='ANALYST';

4.3 不等连接

查询显示工资级别在3-5级之间的所有员工

?

select e.empno, e.ename, e.job, e.sal, s.grade from emp e, salgrade s where( e.sal between s.losal and s.hisal) and( s.grade>2 and s.grade<6);

查询员工姓名、工资、工资级别

? ?

select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

4.4 外连接

? select * from dept 发现有4个部门，编号为40的部门在emp表中并没有见

过，想查出所有部门的名称、地点和员工信息，怎么办 select a.*,b.* from emp a, dept b where a.deptno(+)=b.deptno;

Select emp.empno,emp.ename,emp.sal,dept.deptno,loc from dept left outer join emp on (emp.deptno=dept.deptno);

? Select emp.empno,emp.ename,dept.deptno,dept.loc from emp right

outer join dept on (emp.deptno=dept.deptno);

4.5 表连接特殊语法 using子句

Select e.empno,e.ename,e.sal,d.loc from emp e join dept d using (deptno);

On 子句

Select e.empno,e.ename,e.sal,e.deptno,d.loc from emp e join dept d on (e.deptno=d.deptno) order by d.loc;

左外连接

Select emp.empno,emp.ename,emp.sal,dept.deptno,loc from dept left outer join emp on (emp.deptno=dept.deptno);

右外连接

?

Select emp.empno,emp.ename,dept.deptno,dept.loc from emp right outer join dept on (emp.deptno=dept.deptno); ? ? ? ? ?

5.子查询

5.1 单行子查询

? ?

查询与SMITH相同职位的人有谁

select ename from emp where job=(select job from emp where ename='SMITH');

查询职位与'SMITH'相同，而工资不超过ADAMS的员工

select ename, job, sal from emp where job=(select job from emp where ename='SMITH') and sal<= (select sal from emp where ename='ADAMS');

? 查询最高工资的员工姓名、岗位、工资

查出高于本部门员工平均工资的员工姓名、部门号、工资、部门平均工资

select e.ename,e.deptno,e.sal,avg.mysal from emp e,(select deptno,avg(sal) mysal from emp group by deptno) avg where (e.deptno=avg.deptno) and (sal>avg.mysal); 5.2 多行子查询

1、使用in 操作符的多行子查询：select ename, job, sal from emp where sal in (select max(sal) from emp group by job);

查询与部门号10相同的工作

思路：查部门号为10的有哪些工作 再查属于这些工作的人

select * from emp where job in (select distinct job from emp where deptno=10); 2、使用all操作符的多行子查询

查询比10号部门工资低的员工的姓名、工作、工资

select ename, job, sal from emp where sal

select ename, job, sal from emp where sal<(select min(sal) from emp where deptno=10); 3、使用any操作符的多行子查询:

查询比每个部门平均工资都高的员工姓名、工作、薪水

select ename, job, sal from emp where sal >any (select avg(sal) from emp group by job); select ename, job, sal from emp where sal >(select min(avg(sal)) from emp group by job); 4、多列子查询

查询与smith工作、部门相同的员工

select ename,job,deptno from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

练习：如何显示高于自己部门平均工资的员工的信息

select a1.ename,a1.sal,a1.deptno,a2.agsal from emp a1,(select deptno,avg(sal) agsal from emp group by deptno) a2where a1.deptno=a2.deptno and a1.sal>a2.agsal;

当在from子句中使用子查询时，该子查询被当做一个视图来对待，因此也叫内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名，否则没办法查处上述例子中的agsal 5、分页

select * from emp;--查出所有，将其作为内嵌视图

select a1.*,rownum rn from (select * from emp) a1;--产生行编号

select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;--取前10

select a1.*,rownum rn from (select * from emp) a1 where rownum<=10 and rownum>=6 --错误的，oracle中rownum只能用一次--取6-10

select a1.*,rownum rn from (select * from emp) a1 where rownum<=10 and rn>=6;--错误 select a1.*,rownum rn from (select * from emp) a1 where rn>=6 and rn<=10;--错误

?

只能将select a1.*,rownum rn from (select * from emp) a1 where rownum<=10作为一个视 …… 此处隐藏：3843字，全部文档内容请下载后查看。喜欢就下载吧 ……