oracle练习题

/*1、得到平均工资大于2000的工作职位 */
select job, avg(sal)
from emp
group by job
having avg(sal) > 2000

/*2、分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500 */
select avg(sal)
from scott.emp
where sal > 2000
group by deptno
having avg(sal) > 2500

/*3、得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 */
select deptno, dname, loc
from dept
where deptno in
(select deptno
from emp
group by deptno
having sum(sal) = (select min(sum(sal))
from scott.emp
group by deptno))

/*4、分部门得到平均工资等级为2级（等级表）的部门编号 */ ----????
select * from salgrade;

select deptno,avg(sal)
from emp
group by deptno
having avg(sal) between (select losal from salgrade where grade=2)
and (select hisal from salgrade where grade=2);

/*5、查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置*/

select p1.ename,dept.deptno,loc
from (select e.* ,rownum rn
from (select ename,deptno,sal
from emp
where deptno in(10,20)
order by sal desc) e) p1,dept
where p1.deptno=dept.deptno and rn between 3 and 5;

/*6、查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入*/
select empno, ename, sal + nvl(comm, 0) from scott.emp e1
where sal + nvl(comm, 0) > (select sal + nvl(comm, 0) from scott.emp where empno = e1.mgr)

/*7、查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资 */
select avg(sal) from scott.emp where job in (select job from scott.emp where ename = 'MARTIN' or ename = 'SMITH')

/*8、查找出不属于任何部门的员工 */

select * from scott.emp where deptno is null
select * from scott.emp where deptno not in (select deptno from scott.emp)

/*9、按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）*/
select dname,loc
from (select e.*,rownum rn
from(select deptno,count(*)
from emp
group by deptno
order by count(*) desc) e) e1,dept
where e1.deptno=dept.deptno and rn between 2 and 5;

/*10、查询出king所在部门的部门号\部门名称\部门人数（多种方法）*/
select sc.deptno, dname, count(*)
from scott.emp sc, scott.dept de
where sc.deptno = ((select deptno from scott.emp where ename = 'KING')) and de.deptno = sc.deptno group by sc.deptno, dname

/*11、查询出king所在部门的工作年限最大的员工名字*/
select * from scott.emp
where hiredate = (select min(hiredate) from scott.emp where deptno in (select deptno from scott.emp where ename = 'KING')) and deptno = (select deptno from scott.emp where ename = 'KING')

/*12、查询出工资成本最高的部门的部门号和部门名称 */
select deptno, dname from scott.dept where deptno = (select deptno from scott.emp group by deptno having sum(sal) = (select max(sum(sal)) from scott.emp group by deptno))

/*13、显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序. */
select ename,job, sal
from emp
order by job desc, sal asc;

/*14、显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面. */
select ename, to_char(hiredate,'yyyy') year, to_char(hiredate,'mm') months
from emp
order by months, year asc;

/*15、显示在一个月为30天的情况所有员工的日薪金,忽略余数. */

select ename,trunc((sal/30))
from emp;

select ename, sal/30 dailysal
from emp;

/*16、找出在(任何年份的)2月受聘的所有员工。*/
select ename, hiredate
from emp
where to_char(hiredate,'mm')='02';

/*16、找出在(任何年份的)2月受聘的所有员工。*/

select *
from emp
where to_char(hiredate,'mm')=2;

/*17、对于每个员工,显示其加入公司的天数. */

select ename,trunc(sysdate-hiredate)
from emp;

/*18、显示姓名字段的任何位置包含"A"的所有员工的姓名. */

select ename from emp where instr(ename,'A',1)>0;
/*19、以年月日的方式显示所有员工的服务年限. */
select ename, to_char(hiredate,'yyyy/mm/dd')
from emp;

/*20.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.*/
select ename, hiredate
from emp
order by hiredate desc;

、对emp表中sal、comm进行加计算，并使用别名命令为员工的月总收入，同时展示出员工部门编号、员工姓名信息。

 

select * from emp;

select ename, deptno, sal+nvl(comm,0)from emp;

 

2、使用连接符查询emp表中员工的姓名和工资，并以如下格式列出且字段名展示为 TOTAL INCOME：

SMITH total income is XXXXX

 

select ename || 'total income is' || job

 

from emp;

3、使用distinct排重查询emp中的job类型

 

select distinct job from emp;

4、从emp表中找出奖金高于 薪水60%的员工

 

select * from emp;

where nvl(comm,0)> sal*60%

 

5、找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料。

 

select * from emp

where deptno=10 and job='MANAGER'or deptno=20 and job='CLERK'

6、从emp和dept中联合查询，并将员工编号、姓名、职位、地址信息列出。

 

select * from dept emp

select e.deptno, e.ename, e.job, d.deptno, d.dname, d.loc

from emp e ,dept d

where e.deptno=d.deptno

7、统计各部门的薪水总和。 

select deptno sum(sal) from emp;

 

8、找出部门10中所有理（MANAGER），部门20中所有办事员（CLERK）以及既不是经理又不是办事员但其薪水大于或等2000的所有员工的详细资料。  

select * from emp

where (deptno = 10 and job = 'MANAGER')

or (job = 'CLERK' and deptno = 20) or

(job != 'MANAGER' and

job != 'CLERK' and sal >= 2000);

 

9、列出各种工作的最低工资。

select job,MIN(sal) from emp

group by job;

 

10、列出各个部门的MANAGER（经理）的最低薪水。 

select deptno, min(sal)from emp

where job = 'MANAGER'

group by deptno, job;

 

11、列出有奖金的员工的不同工作。  

 

select ename, job, comm, from emp

where comm is not null;

12、找出无奖金或奖金低于300的员工。 

select ename, comm, from emp

where nvl(comm,0) or comm is not null < 300;

 

13、显示所有员工的姓名,并使姓名首字母大写。 

 

SELECT INITCAP(ename)

FROM emp;

14、显示正好为5个字符的员工的姓名。 

 

SELECT ename

FROM emp

WHERE LENGTH(ename) = 5;

15、显示不带有“R”的员工姓名。  

 

select ename from emp

where ENAME not like 'R';

16、列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水。 

 

select sal, ename

from emp

where sal > ALL(select sal from emp

                where deptno = 30);

17、列出在每个部门工作的员工数量、平均工资和平均服务期限。

 select deptno,count(deptno),

avg(sal),avg(sysdate-hiredate)

from emp

group by deptno;

 

18、列出从事同一种工作但属于不同部门的员工的一种组合。

 

 

SELECT d. ename, p. ename

from emp d, emp p

where d.job = p.job and d .deptno <> p.deptno;

 

19、列出薪水比“SMITH”多的所有员工。 

 

select ENAME from emp

where sal > (select sal from emp

               where ename = 'SMITH');

20、列出至少有一个员工的所有部门。

   SELECT deptno

FROM emp

GROUP BY deptno

HAVING COUNT(*) >= 1;

 

21、对于每个员工，显示其加入公司的天数、月数、年数。 

 

SELECT  ename , TRUNC(sysdate - hiredate) "天数" ,

 

 TRUNC(sysdate - hiredate)/30 "月数" , TRUNC(sysdate - hiredate)/365 "年"

 

FROM  emp;

22、对21中的天数、月数、年数取整显示。

 

SELECT  ename , ROUND(TRUNC(sysdate - hiredate)) "天数" ,

 

ROUND(TRUNC(sysdate - hiredate)/30) "月数" , ROUND(TRUNC(sysdate - hiredate)/365) "年"

 

FROM  emp;

23、找出在每年5月受聘的所有员工。 

 

select ename from emp

 

where to_char(hiredate,'mm') like '%5'

24、显示在一个月为30天的情况下所有员工的日薪水，取整。 

 

select ename, round(sal/30)daysal from emp;

25、显示所有员工的姓名和加入公司的年份和月份，并将员工入职年月从低到高排序。

select ename,to_char(hiredate,'yyyy-mon')

from emp

order by to_char(hiredate,'mon'),to_char(hiredate,'yyyy');