ORACLE经典查询语句

aini

　　scott/tiger登录数据库，
　　sqlplus
　　conn scott/scott
　　1、显示所有部门名
　　select dept.dname from dept ;
　　2、显示所有雇员名及其全年收入(工资+补助)，并指定列别名“年收入”
　　select emp.ename as 雇员名,nvl2(comm,comm+sal,sal)*12 as 年收入from emp;
　　3、显示存在雇员的所有部门号
　　select emp.deptno from emp;
　　4、显示工资超过2850的雇员名和工资
　　select emp.ename as 雇员名,emp.sal as 工资 from emp where emp.sal>2850;
　　5、显示工资不在1500到2850之间的所有雇员名及工资
　　select emp.ename as 雇员名,emp.sal as 工资
　　from emp where emp.sal not between 1500 and 2850;
　　6、显示雇员代码为7566的雇员名及所在部门号
　　Select empno,ename from emp where empno=7566;
　　7、显示部门代码为10和30中工资超过1500的雇员名及工资
　　select emp.ename as 雇员名,emp.sal as 工资 from emp where emp.deptno
　　in(10,30) and emp.sal>1500;
　　8、显示无管理者的雇员名及岗位
　　select emp.ename,emp.job from emp where mgr is null;
　　9、显示所有雇员的平均工资、总计工资、最高工资、最低工资
　　select round(avg(emp.sal),2) as 平均工资,count(emp.sal) as 总计工资,
　　max(emp.sal) as 最高工资,min(emp.sal) as 最低工资,
　　from emp;
　　10、显示每种岗位的雇员总数、平均工资
　　select count(empno) as 总人数,round(avg(sal),2) as 平均工资
　　from emp group by job;
　　11、显示雇员总数，以及获得补助的雇员数
　　select count(emp.empno) as 总人数,count(bonus.ename) as 补助总人数
　　from emp left join bonus on emp.ename=bonus.ename;
　　12、显示管理者的总人数
　　select count(distinct mgr) as 管理者总人数 from emp;
　　13、显示雇员工资的最大差额
　　select max(sal)-min(sal) as 最大差额 from emp;
　　14、显示部门代码为20的部门号，以及该部门的所有雇员名、雇员工资及岗位
　　select emp.ename,emp.sal,emp.job from emp where emp.deptno=20;
　　15、显示获得补助的所有雇员名、补助额以及所在部门号
　　select emp.ename,bonus.sal,emp.deptno
　　from emp inner join bonus on emp.ename=bonus.ename;
　　16、显示所有雇员的姓名、部门编号、工资，并且列名要显示为中文
　　select ename as 姓名,deptno as 部门编号,sal as 工资 from emp;
　　17、显示每个部门每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资
　　select a.deptno,a.job,a.deptno_job_avg,b.deptno_avg,c.job_avg
　　from (select deptno,job,avg(sal) as deptno_job_avg
　　from emp group by deptno,job) a left join (select deptno,avg(sal)
　　as deptno_avg from emp group by deptno) b on a.deptno=b.deptno
　　left join (select job,avg(sal) as job_avg from emp group by job) c
　　on a.job=c.job
　　order by deptno;
　　18、显示工资大于1500的雇员名和工资，并且按照工资的降序排列
　　select emp.ename as 雇员名,emp.sal as 工资
　　from emp where emp.sal>1500 order by emp.sal desc;
　　19、显示雇员部门编号为10或20的信息（要求使用IN关键字）
　　select * from emp where emp.deptno in(10,20);
　　20、显示雇员名的第二个字母为A的信息
　　select * from emp where emp.ename like '_A%';
　　21、显示没有发放补助的雇员信息
　　select * from emp where ename not in(select ename from bonus);
　　22、显示雇员表中记录总数
　　select count(*) from emp;
　　23、显示所有雇员名、雇员工资及所在部门名
　　select emp.ename 雇员名,emp.sal 雇员工资,dept.deptno 部门
　　from emp,dept where emp.deptno=dept.deptno;
　　24、显示部门代码为20的部门名，以及该部门的所有雇员名、雇员工资及岗位
　　select dept.dname,emp.ename,emp.sal,emp.job
　　from emp,dept
　　where emp.deptno=dept.deptno and emp.deptno=20;
　　25、显示所有雇员名、雇员工资及工资级别
　　select emp.ename,emp.sal,salgrade.grade
　　from emp,salgrade
　　where emp.sal>salgrade.losal and emp.salsalgrade.losal and emp.sal1500;
　　34、按以下格式显示下面信息，条件是此人工资在所有人中最高。
　　部门  姓名  工资
　　select dept.dname as 部门,emp.ename as 姓名,emp.sal as 工资
　　from emp,dept where emp.deptno=dept.deptno and emp.sal=(select max(emp.sal) from emp);
　　35、按以下格式显示下面信息
　　某人 为 某人 工作
　　select a.ename as 某人为 ,b.ename as 某人 ,a.job as 工作
　　from emp a,emp b where a.empno = b.mgr;
　　36、为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%（要求用DECODE函数）
　　select round(decode(deptno,'10',sal*(1+0.1), '20',sal*(1+0.15), '30',sal*(1+0.2),sal*(1+0.18)),2) as 工资 from emp
　　update emp
　　set sal=decode(deptno,'10',sal*(1+0.1), '20',sal*(1+0.15), '30',sal*(1+0.2),sal*(1+0.18));
　　37、根据工作年限长工资，标准是：为公司工作了几个月就长几个百分点。
　　select round(sal * (1+(sysdate - hiredate)/365/12/100),2) from emp
　　update emp
　　set sal= round(sal * (1+(sysdate - hiredate)/365/12/100),2);
　　38、查询出king所在部门的部门号\部门名称\部门人数
　　select e.deptno as 部门号,d.dname as 部门名称, (select count(*)
　　from (select deptno
　　from emp where deptno in (select deptno
　　from emp where ename='KING') ) ) as 部门人数
　　from emp e, dept d
　　where e.deptno = d.deptno and e.ename = 'KING';
　　39、查询出king所在部门的工作年限最大的员工名字
　　select ename, hiredate from emp
　　where hiredate in (select min(hiredate)
　　from (select hiredate from emp where deptno in
　　(select deptno from emp where ename='KING') ));
　　40、查询出管理员工人数最多的人的名字和他管理的人的名字
　　select a.ename 员工,b.ename as 管理员
　　from emp a join emp b on a.mgr=b.empno
　　where a.mgr=(
　　select mgr from emp
　　group by mgr
　　having count(mgr)=(
　　select max(cn)
　　from (
　　select count(mgr) as cn,mgr
　　from emp
　　group by mgr
　　) a
　　)
　　)
　　order by a.mgr;
　　41、查询出工资成本最高的部门的部门号和部门名称
　　select DEPTNO 部门编号,DNAME 部门名称
　　from Dept
　　where DEPTNO=(select deptno
　　from Emp
　　group by deptno
　　having sum(sal)=( select max(SSAL)
　　from (select sum(sal) as SSAL, deptno
　　from emp
　　group by deptno) a
　　)
　　);
　　42、查询出工资不超过2500的人数最多的部门名称
　　select deptno as 部门编号,DNAME as 部门名称
　　from dept
　　where deptno=(select deptno
　　from (select count(empno) as cn,deptno
　　from Emp
　　where SAL (select avg(sal) from emp)
　　group by deptno
　　having count(*) >= all (  select count(*)
　　from emp e
　　where e.sal > (select avg(sal) from emp)
　　group by e.deptno
　　)
　　);