ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Practice 18:

Practice 18:

原创 Linux操作系统 作者:zhaoyu728 时间:2019-05-25 12:54:07 0 删除 编辑

SQL> select ename,deptno,sal from emp
2 where exists(select 'x' from dept
3 where dept.deptno=emp.deptno) and sal in(select sal from emp where comm is not null);

ENAME DEPTNO SAL
---------- ---------- ----------
ALLEN 30 1600
MARTIN 30 1250
WARD 30 1250
TURNER 30 1500

SQL> select ename,dname,sal from emp e,dept d
2 where (sal,nvl(comm,0)) in
3 (select sal,nvl(comm,0) from emp e,dept d
4 where e.deptno=d.deptno and d.loc='DALLAS') and e.deptno=d.deptno;

ENAME DNAME SAL
---------- -------------- ----------
SMITH RESEARCH 800
JONES RESEARCH 2975
FORD RESEARCH 3000
SCOTT RESEARCH 3000
ADAMS RESEARCH 1100
ZHAOYU RESEARCH 2410

已选择6行。


SQL> select ename,hiredate,sal from emp
2 where (sal,nvl(comm,0))=(select sal,nvl(comm,0) from emp
3 where ename='FORD') and ename!='FORD';

ENAME HIREDATE SAL
---------- -------------- ----------
SCOTT 19-4月 -87 3000

SQL> select ename,sal from emp
2 where sal>all (select sal from emp where job='MANAGER') order by sal desc;

ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
FORD 3000

SQL> with sal_avg as (select sum(sal)/count(*) as dept_avg,deptno from emp
2 group by deptno)
3 select ename,sal,e.deptno,dept_avg from emp e,sal_avg
4 where sal>dept_avg and e.deptno=sal_avg.deptno;

ENAME SAL DEPTNO DEPT_AVG
---------- ---------- ---------- ----------
ALLEN 1600 30 1566.66667
JONES 2975 20 2214.16667
BLAKE 2850 30 1566.66667
SCOTT 3000 20 2214.16667
KING 5000 10 2916.66667
FORD 3000 20 2214.16667
ZHAOYU 2410 20 2214.16667

已选择7行。

SQL> run
1 select e.ename,e.sal,e.deptno,avg(a.sal) dept_avg from emp e,emp a
2 where a.deptno=e.deptno
3 and e.sal>(select avg(sal) from emp where deptno=e.deptno)
4* group by e.ename,e.sal,e.deptno order by avg(a.sal)

ENAME SAL DEPTNO DEPT_AVG
---------- ---------- ---------- ----------
ALLEN 1600 30 1566.66667
BLAKE 2850 30 1566.66667
FORD 3000 20 2214.16667
SCOTT 3000 20 2214.16667
ZHAOYU 2410 20 2214.16667
JONES 2975 20 2214.16667
KING 5000 10 2916.66667

已选择7行。

SQL> select e.ename from emp e
2 where e.sal <(select avg(sal) from emp where deptno=e.deptno )
3 group by e.deptno,e.ename;

ENAME
----------
CLARK
MILLER
ADAMS
SMITH
WARD
JAMES
MARTIN
TURNER

已选择8行。

SQL> select e.ename from emp e
2 where e.sal <(select avg(sal) from emp where deptno=e.deptno );

ENAME
----------
SMITH
WARD
MARTIN
CLARK
TURNER
ADAMS
JAMES
MILLER

SQL> select ename from emp e where exists(
2 select 'interview' from emp a where a.deptno=e.deptno
3 and a.hiredatee.sal);

ENAME
----------
JAMES
TURNER
MARTIN
WARD
ZHAOYU
ADAMS
MILLER

已选择7行。


SQL> select empno,ename,(select dname from dept d where e.deptno=d.deptno) deparent
2 from emp e order by deparent;

EMPNO ENAME DEPARENT
---------- ---------- --------------
7782 CLARK ACCOUNTING
7839 KING ACCOUNTING
7934 MILLER ACCOUNTING
7369 SMITH RESEARCH
7876 ADAMS RESEARCH
7902 FORD RESEARCH
7788 SCOTT RESEARCH
7566 JONES RESEARCH
7400 ZHAOYU RESEARCH
7499 ALLEN SALES
7698 BLAKE SALES

EMPNO ENAME DEPARENT
---------- ---------- --------------
7654 MARTIN SALES
7900 JAMES SALES
7844 TURNER SALES
7521 WARD SALES

已选择15行。

SQL> with summary as(select dname,sum(sal) as dept_total from emp e,dept d
2 where e.deptno=d.deptno group by dname)
3 select dname,dept_total from summary where dept_total>(select sum(dept_total)/8 from summary)
4 order by dept_total desc;

DNAME DEPT_TOTAL
-------------- ----------
RESEARCH 13285
SALES 9400
ACCOUNTING 8750

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8382469/viewspace-259844/,如需转载,请注明出处,否则将追究法律责任。

下一篇: External Tables :
请登录后发表评论 登录
全部评论

注册时间:2006-12-03

  • 博文量
    36
  • 访问量
    28399