Orcle中emp及dept常用命令.doc

select ename 姓名，job 工作 from EMP t
where ename like 'S%'
--在em表中，按DEPTNO统计各部门的职员数和各部门的平均工资，要求不统计没有分配职员的部门。
select deptno,count(*),avg(sal)
from emp
where deptno is not null
group by deptno
--登录scott用户，从DEPT表中，查找DEPTNO 字段小于等于30，且DNAME以“A”开头的所有记录，
--要求查询结果只显示DEPTNO和DNAME两个字段，DEPTNO和DNAME分别用“部门号”、“部门名称”
--来显示。请写出详细SQL语句。
select deptno 部门号,dname 部门名称
from dept
where deptno<30 and dname like 'A%'
--登录SCOTT用户，在emp表中，按JOB统计职员数和平均工资，要求不统计没有分配职员的 job。
select job 职位,count(job) 人数,avg(sal) 平均工资,sum(sal) 总工资
from emp
group by job
having count(job)>0
--选择部门30中的雇员
select * from tb_emp
where deptno = 30
--列出所有经理的姓名、编号和部门
select ename 姓名,empno 编号,dname 部门
from emp, dept
where emp.deptno=dept.deptno and
job = 'MANAGER' --小写manager不行
--列出佣金高于薪金的雇员.
select e1.* from emp e1,emp e2
where e1.empno=e2.empno and
m>e1.sal
--找出佣金高于或等于薪金50%的雇员
select e1.* from emp e1,emp e2
where e1.empno=e2.empno and
m>=e1.sal*0.5
--找出部门10中所有经理和部门20中所有办事员的详细信息
select * from emp
where (deptno=10 and job='MANAGER') or
(deptno=20 and job='CLERK')
--找出部门10中所有经理和部门20中所有办事员以及即不是经理又不是办事员但薪金
--大于或等于2000的所有雇员的详细资料.
select * from emp
where (deptno=10 and job='MANAGER') or
(deptno=20 and (job='CLERK'or
(job not in('MANAGER','CLERK') and sal>=2000)))
--找出收取佣金的雇员的不同工作.
select ename 姓名,job 工作,comm 佣金
from emp
where comm is not null
--找出不收取佣金或收取的佣金低于是200的雇员
select * from emp
where comm is null or
comm is not null and comm<200
--找出各月最后一天受雇的所有雇员.
select * from emp
where hiredate=last_day(hiredate)
--找出早于31年之前受雇的所有雇员.
select emp.*,floor(months_between(sysdate,hiredate)/12) as HireYears from emp
where floor(months_between(sysdate,hiredate)/12)>31
--查询每个工种的人数.并按工种的人数排序
select job, count(*) from emp
group by job
order by count(*)
--查询MANAGER的平均工资。
select job,avg(sal) from emp
group by job
having job='MANAGER'
--查询每个工种的人数,和平均工资,并按一均工资进行排序.
select job, count(job), avg(sal)
from emp
group by job
order by avg(sal)
--查询部门名称是SALES的所有员工.
select emp.* from emp, dept
where emp.deptno=dept.deptno
and dname='SALES'
--查询部门名称是SALES的所有员工的平均工资.
select dname, avg(sal) from emp, dept
where emp.deptno=dept.deptno and sal in(
select sal from emp, dept
where emp.deptno=dept.deptno
and dname='SALES')
group by dname
//select * from emp
--查询每个工种中雇员的最高薪金;
select job, max(sal) from emp
group by job
--查询职务是经理的雇员的最低薪金;
select job, min(sal) 最低薪金 from emp
group by job
having job='MANAGER'
--查询平均工资高于1500的所有职务的名称和平均工资金额.
select job, avg(sal) from emp
group by job
having avg(sal)>1500
--查询所有雇员的实发工资(薪金+佣金)
select ename, nvl(sal+comm,sal) as 实发工资 from emp
--找出同名的后，进行删除，每个重名只留一个
delete from (
select a.*,row_number() over(ORDER BY empno DESC) AS rn from tb_emp a
where ename in(
select ename from tb_emp a
group by ename
having count(*)>1))
where rn>1

--查询每个员工的详细信息.包括部门编号,部门名称及部门所在城市.
select * from emp,dept
where emp.deptno=dept.deptno
--按部门分组,查询出部门名称,及部门中员工的平均工资.
select dname, avg(sal) from emp,dept
where emp.deptno=dept.deptno
group by dname
--查询所有员工中工资数在前五的员工详细信息。并从高到低进行排序。
select * from
(select a.*,row_number() over(order by sal desc) as rn from emp a)
where rn<=5
order by sal desc;
--查询所有经理中工资第二高的员工的详细信息。
select * from
(select a.*,row_number() over(order by sal desc) as rn from emp a where job='MANAGER')
where rn=3;
--找出所有员工中薪金第五高到第八高的员工信息。
select * from
(select a.*,row_number() over(order by sal desc) as rn from emp a)
where rn in (5,8)
--查询出SCOTT的上级领导的详细信息。
select * from emp where empno=(select mgr from emp where ename='SCOTT')
--查询出员工的领导层次结构
select
case
when job='PRESIDENT' then 'A'
when job='MANAGER' then 'B'
when job='ANALYST' then 'C'
when job='SALESMAN' then 'D'
else 'E'
end Grade, empno, ename, job
from emp
order by grade
--查询出没有员工的部门。
select * from dept
where not exists(
select * from emp where emp.deptno=dept.deptno)
--或者
select * from dept
where deptno not in (select deptno from emp)
--查询出平均高于2000的部门信息。
select * from dept
where deptno in(
select deptno from emp
group by deptno having avg(sal)>2000)
--找到同名的所有员工
select * from emp
where exists(
select ename from emp a
group by ename
having count(*)>1)
--查询出工次级别是4级的所有员工。（salgrade为员工级别表）
select * from salgrade
select ename,sal,
case
when 700<sal and sal<1200 then '1'
when 1201<sal and sal<1400 then '2'
when 1401<sal and sal<2000 then '3'
when 2001<sal and sal<3000 then '4'
else '5'
end salgrade
from emp
order by salgrade

select ename,sal,
case
when losal<sal and sal<hisal then grade
end gra
from emp,salgrade
order by gra