Oracle数据库操作例题

select *from emp;
select *from dept;
select *from salgrade;

--1.列出至少有一个员工的所有部门
--第1种
select deptno,count(*)
   from emp e
   group by e.deptno;
--第2种
select distinct deptno from emp;
--第3种
select dname,count(empno) from emp,dept where emp.deptno = dept.deptno group by dname;
--2列出薪金比SMITH多的所有员工
select emp.ename,emp.sal
  from emp
  where  sal > (select sal from emp where ename = 'SMITH')
  order by emp.sal;

--3.列出所有员工的姓名及其直接上级的姓名        
select e.ename,d.ename
   from emp e,
        emp d
   where e.mgr = d.empno(+);
  
--4.列出受雇日期早于其它直接上级的所有员工
select e.ename
  from emp e   
  where e.hiredate < (select d.hiredate from emp d where e.mgr = d.empno);
 
--5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select a.*,b.dname
  from emp a,
       dept b
  where a.deptno(+) = b.deptno;
 
--6.列出所有CLERK（办事员）的姓名及其部门名称
select a.ename,b.dname
  from emp a,
       dept b
  where a.job = 'CLERK' and a.deptno = b.deptno; 
 
 
--7.列出最低薪金大于1500的各种工作
select emp.job，min(sal)
  from emp
  group by emp.job
  having min(sal) >1500;
 
--8.列出在部门SALES（销售部）工作的员工的姓名，假定不知道销售部的部门变号
select emp.ename
  from emp ,
       dept
  where emp.deptno = dept.deptno and dept.dname = 'SALES';
 
--9.列出薪金高于公司平均薪金的所以员工
select ename,sal
 from emp
 where sal > (select avg(sal) from emp);

--10.列出与SCOTT从事相同工作的所有员工
--第1种.
select ename
  from emp
  where job = 'ANALYST';
--第2种.
select ename
  from emp     
  where job = (select job from emp where ename = 'SCOTT' );
      
--11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
select ename,sal
  from emp
  where sal = any(select sal from emp where deptno = 30);

--12.列出薪金大于在部门30中员工的薪金的所有员工的姓名和薪金
--第1种
select ename,sal
  from emp
  where sal > all(select sal from emp where deptno = 30);
--第二种
select ename,sal from emp where sal > (select max(sal) from emp  where emp.deptno = 30);
--13.列出在每个部门工作的员工数量、平均工资和平均服务期限
select deptno,count(1),avg(sal),avg((sysdate - hiredate)/365)
  from emp
  group by deptno;

--14.列出所有员工的姓名、部门名称和工作
select emp.ename,dept.dname,emp.sal
  from emp,
       dept
  where emp.deptno = dept.deptno;

--15.列出所有部门的详细信息和部门人数
select dept.deptno,dept.dname,dept.loc,count(emp.ename)
  from emp ,dept
  where emp.deptno(+) = dept.deptno
  group by dept.deptno,dept.dname,dept.loc;

--16.列出各种工作的最低工资
select emp.job,min(sal)
 from emp
 group by emp.job;
 
--17.列出各个部门的MANAGER（经理）的最低薪资
  select dept.deptno,min(sal)
  from emp,dept
  where  job = 'MANAGER'      
  group by dept.deptno ;

--18.列出所有员工的年工资，按年薪从低到高排序
select emp.ename,emp.sal*12
  from emp
  order by emp.sal,emp.ename;

--19.列出工资最高的员工信息
select emp.ename,emp.sal
 from emp
 where sal = (select max(sal) from emp);

--20.l列出每个工作岗位的最低工资和最高工资和平均工资且平均工资大于2000的
select emp.job, min(sal),max(sal),round(avg(sal),2)
  from emp
  group by job
  having avg(sal) > 2000;

--21.列出工资大于同部门平均工资的所以员工信息
--第1种
select e1.*
  from emp e1
  where sal > (select avg(sal) from emp e2 where e1.job = e2.job
  group by job); 

--第2种
select e1.*
  from emp e1,(select job,avg(sal) as  avg1 from emp
  group by job)e2
  where e1.job = e2.job and e1.sal > e2.avg1;

--22.找出各月倒数第二天受雇的员工
SELECT e.ename
FROM emp e
WHERE e.hiredate = (LAST_DAY( e.hiredate)-2);
select *from emp;

--ANY、ALL子查询

--1.查询出 Emp 表中比任意一个销售员(“SALESMAN” )工资低的员工姓名、工作、工资。
SELECT e.ename,e.job,e.sal
FROM emp e
WHERE sal < any(SELECT sal FROM emp WHERE job = 'SALESMAN');

--2.查询出销售部（ SALES）下面的员工姓名，工作，工资。
select emp.ename,emp.job,emp.sal ,a.deptno
FROM emp ,(SELECT deptno FROM dept WHERE dname = 'SALES' ) a
WHERE emp.deptno = a.deptno;

--3.查询出比所有销售员的工资都高的员工姓名，工作，工资。
SELECT e.ename,e.job,e.sal
FROM emp e
WHERE sal > ALL(SELECT sal FROM emp WHERE job = 'SALESMAN');

--ROWID:表中的每一行在数据文件中都有一个物理地址， ROWID 伪列返回的就是该行的物理地址。

select last_day(hiredate)-2 from emp;
SELECT *FROM EMP;