• <nav id="wkkge"><strong id="wkkge"></strong></nav>
• Oracle教程
Oracle練習題

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``

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``

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``

全部教程
• <nav id="wkkge"><strong id="wkkge"></strong></nav>