Orcale数据库查询练习题

Oracle 数据库

1 找出佣金高于薪金60%的雇员。

select ename from emp where comm>sal*0.6;

2 找出部门10中所有经理和部门20中所有办事员的详细资料。

  select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');

3 找出部门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);

4 找出收取佣金的雇员的不同工作。

 select ename,job from emp where comm>0;

5 找出不收取佣金或收取的佣金低于300的雇员。

select ename from emp where comm is null or comm<300;

6 找出各月最后一天受雇的所有雇员。

select * from emp where (last_day(hiredate))=hiredate;

7 找出晚于26年之前受雇的雇员。

 select * from emp where months_between(sysdate,hiredate)/26<26;

8 显示只有首字母大写的的所有雇员的姓名。

select initcap(ename) from emp;

9 显示正好为5个字符的雇员的姓名。

select ename from emp where length(ename)=5;

10显示不带有“R”的雇员姓名。

select ename from emp where ename not like '%R%';

11显示雇员的详细资料，按姓名排序。

select * from emp order by ename;

12显示雇员姓名，根据其服务年限，将最老的雇员排在最前面。

select ename,hiredate from emp order by hiredate;

13显示所有雇员的姓名、工作和薪金，按工作的降序排序，而工作按薪金排序。

select ename,job,sal from emp order by job desc,sal;

14.找出所有的普通员工：

select * from emp where job != 'PRESIDENT';

15.显示所有雇员的姓名的前三个字符。

select substr(ename,0,3) from emp;

16.显所有雇员的姓名，用a替换所有“A”。

select replace(ename,'A','a') from emp;

17.显示所有雇员的姓名以及满10年服务年限的日期。

select ename,hiredate from emp where months_between(sysdate,hiredate)/12>10;

18.显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，并将最早年份的项目排在最前面。

select ename,to_char(hiredate,'yyyy')year,to_char(hiredate,'MM')mon from emp order by year,mon;

19.显示在一个月为30天的情况下所有雇员的日薪金，取整。

select ename,round(sal/30)日薪金 from emp;

20.找出在（任何年份的）2月受聘的所有雇员。

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

21.对于每个雇员，显示其加入公司的天数。

select round(sysdate-hiredate)from emp;

22.求部门中哪些人的薪水最高：

select ename,sal from emp join (select max(sal)max_sal,deptno from emp group by deptno)t on(emp.deptno=t.deptno and emp.sal=t.max_sal);

23.求部门平均薪水的等级：

select deptno,avg_sal,grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t join salgrade s on(t.avg_sal  between s.losal and s.hisal);

24.求部门平均的薪水等级

select avg(grade)from (select deptno,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal))t group by deptno;

25.雇员中哪些人是经理人：

select ename from emp where empno in(select mgr from emp);

26.不准用组函数，求薪水的最高值

select distinct sal max_sal from emp where sal not in(select e1.sal e1_sal from emp e1 join emp e2 on e1.sal<e2.sal);

27．求平均薪水最高的部门的部门编号

select deptno,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno)
where avg_sal=(select max(avg_sal)from 
(select avg(sal)avg_sal from emp group by deptno));

28.求平均薪水最高的部门的部门名字

select t1.deptno,dname,avg_sal from
(select deptno,avg(sal)avg_sal from emp group by deptno)t1
join dept on t1.deptno=dept.deptno
where avg_sal=(select max(avg_sal)from
(select deptno,avg(sal)avg_sal from emp group by deptno));

29.求比普通员工的最高薪水还要高的经理人名称

select ename from emp where empno
in (select mgr from emp)and sal>(select max(sal)from 
(select e2.sal from emp e1 right join emp e2 on e1.mgr=e2.empno
where e1.ename is null)t);