Oracle練習題及答案

23、列出與"SCOTT"從事相同工作的所有員工及部門名稱

Select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno where job = (Select job from emp where ename = 'SCOTT')

24、列出薪金等于部門30中員工的薪金的所有員工的姓名和薪金

第一步 獲取部門是30的所有員工薪水

select sal from emp where deptno = 30

第二步 獲取結果

Select ename, sal from emp where sal in (select sal from emp where deptno = 30)

25、列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金.部門名稱

select ename, sal, dname from emp e join dept d on e.deptno = d.deptno where sal >  (select max (sal) maxSal from emp where deptno = 30)

26、列出在每個部門工作的員工數量,平均工資和平均服務期限

Select d.dname 部門名稱, count(e.empno) 員工數量, round(avg(e.sal), 2) 平均薪水, round(avg(sysdate-hiredate)/365,0) 服務期限 from emp e, dept d where e.deptno = d.deptno group by d.dname

27、列出所有員工的姓名、部門名稱和工資

Select ename, dname, sal from emp e, dept d where e.deptno = d.deptno

28、列出所有部門的詳細信息和人數

Select d.*, (select count(e.empno) from emp e where e.deptno = d.deptno) 人數 from dept d

29、列出各種工作的最低工資及從事此工作的雇員姓名

30、列出各個部門的MANAGER(經理)的最低薪金

select deptno, min(sal) from (select deptno, sal, empno from emp where empno in (Select distinct mgr from emp where mgr is not null)) group by deptno

31、列出所有員工的年工資,按年薪從低到高排序

32、查出某個員工的上級主管，并要求出這些主管中的薪水超過3000

select ename 主管, sal 主管工資 from emp where empno in (Select distinct mgr from emp where mgr is not null) and sal > 3000

33、求出部門名稱中,帶'S'字符的部門員工的工資合計、部門人數

select deptno from dept where dname like '%S%'
select d.dname, sum(e.sal), count(e.empno) from emp e right join dept d on e.deptno = d.deptno group by d.dname having d.dname like '%S%'

34、給任職日期超過28年的員工加薪10%

Update emp set sal = sal*1.1 where (months_between(sysdate, hiredate)/12) > 28