oracle 练习之关联查询练习

最新推荐文章于 2024-06-08 16:49:03 发布

杨恒_

最新推荐文章于 2024-06-08 16:49:03 发布

阅读量777

点赞数

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.youkuaiyun.com/qq_37643150/article/details/68927163

版权

oracle 专栏收录该内容

8 篇文章

订阅专栏

1.列出至少有一个员工的所有部门。
select ename,dname,dept.deptno from emp ,dept where dept.deptno=emp.deptno and ename is not null;

SQL> select ename,dname,dept.deptno from emp ,dept where dept.deptno=emp.deptno and ename is not null;

ENAME DNAME DEPTNO

CLARK ACCOUNTING 10
KING ACCOUNTING 10
MILLER ACCOUNTING 10
JONES RESEARCH 20
FORD RESEARCH 20
ADAMS RESEARCH 20
SMITH RESEARCH 20
SCOTT RESEARCH 20
WARD SALES 30
TURNER SALES 30
ALLEN SALES 30
JAMES SALES 30
BLAKE SALES 30
MARTIN SALES 30

已选择14行。

SQL>
2.列出工资比‘SMITH’多的所有员工。
select t1.ename ,t1.sal,t2.sal from emp t1 ,(select job,sal from emp where ename=’SMITH’) t2 where t1.sal>t2.sal;

SQL> select t1.ename ,t1.sal,t2.sal from emp t1 ,(select job,sal from emp where ename=’SMITH’) t2 where t1.sal>t2.sal;

ENAME SAL SAL

ALLEN 1600 800
WARD 1250 800
JONES 2975 800
MARTIN 1250 800
BLAKE 2850 800
CLARK 2450 800
SCOTT 3000 800
KING 5000 800
TURNER 1500 800
ADAMS 1100 800
JAMES 950 800
FORD 3000 800
MILLER 1300 800

已选择13行。

3.列出所有员工的姓名及其直接上级的姓名。
select t1.ename,t1.mgr,t2.empno, t2.ename from emp t1 ,(select ename from emp ) t2 where t1.mgr=t2.empno ;

SQL> select t1.ename ,t1.mgr,t2.empno ,t2.ename from emp t1 ,(select * from emp ) t2 where t1.mgr=t2.empno ;

ENAME MGR EMPNO ENAME

FORD 7566 7566 JONES
SCOTT 7566 7566 JONES
TURNER 7698 7698 BLAKE
ALLEN 7698 7698 BLAKE
WARD 7698 7698 BLAKE
JAMES 7698 7698 BLAKE
MARTIN 7698 7698 BLAKE
MILLER 7782 7782 CLARK
ADAMS 7788 7788 SCOTT
BLAKE 7839 7839 KING
JONES 7839 7839 KING
CLARK 7839 7839 KING
SMITH 7902 7902 FORD

已选择13行。

4.列出受雇日起早于其直接上级的所有员工。
select t1.ename ,t1.hiredate,t2.hiredate,t2.ename from emp t1, (select * from emp ) t2 where t1.mgr=t2.empno and t1.hiredate> t2.hiredate ;

SQL> select t1.ename ,t1.hiredate,t2.hiredate,t2.ename from emp t1 ,(select ename,hiredate,empno from emp ) t2
where t1.mgr=t2.empno and t1.hiredate> t2.hiredate ;

ENAME HIREDATE HIREDATE ENAME

FORD 03-12月-81 02-4月 -81 JONES
SCOTT 19-4月 -87 02-4月 -81 JONES
TURNER 08-9月 -81 01-5月 -81 BLAKE
JAMES 03-12月-81 01-5月 -81 BLAKE
MARTIN 28-9月 -81 01-5月 -81 BLAKE
MILLER 23-1月 -82 09-6月 -81 CLARK
ADAMS 23-5月 -87 19-4月 -87 SCOTT

已选择7行。

@@@5.列出部门名称和这些部门的员工信息，同时列出哪些没有员工的部门。
select t1.ename,dept.dname ,t2.ename from emp t1 ,dept ,(select job from emp where ename is null group by job) t2 where dept.deptno= t1.deptno ;
select t1.ename,t1.deptno,dept.dname ,t3.job from emp t1 ,dept ,(select job from emp group by job) t3 where dept.deptno=t1.deptno ;//打印70行
select t1.ename,t1.deptno,dept.dname from emp t1 ,dept where dept.deptno=t1.deptno ;//正常
select t1.ename,t1.deptno,dept.dname from emp t1 , dept where dept.deptno=t1.deptno ;?????

6.列出所有’CLARK’的姓名及其部门名称。
select t1.ename,dept.deptno from emp t1 ,dept where t1.deptno=dept.deptno and t1.job=’CLERK’;

SQL> select t1.ename,t2.deptno from emp t1 ,dept t2 where t1.deptno=t2.deptno and t1.job=’CLERK’;

ENAME DEPTNO

CLARK 10

SQL> select t1.ename,t2.deptno from emp t1 ,dept t2 where t1.deptno=t2.deptno and t1.job=’CLERK’;

ENAME DEPTNO

SMITH 20
ADAMS 20
JAMES 30
MILLER 10

列出在部门’销售部sale‘工作的员工的姓名，假定不知道销售部的部门编号。

select t1.ename,dept.dname from emp t1 ,dept where dept.deptno=t1.deptno and dept.dname=’SALES’;

SQL> select t1.ename,dept.dname from emp t1 ,dept where dept.deptno=t1.deptno and dept.dname =’SALES’;

ENAME DNAME

WARD SALES
TURNER SALES
ALLEN SALES
JAMES SALES
BLAKE SALES
MARTIN SALES

8.列出薪金高于公司平均薪金的所有员工。
select t1.ename ,t1.sal ,avg from emp t1 ,(select avg(sal) avg from emp) t2 where t1.sal>t2.avg;

SQL> select t1.ename ,t1.sal ,avg from emp t1 ,(select avg(sal) avg from emp) t2 where t1.sal>t2.avg;

ENAME SAL AVG

JONES 2975 2073.21429
BLAKE 2850 2073.21429
CLARK 2450 2073.21429
SCOTT 3000 2073.21429
KING 5000 2073.21429
FORD 3000 2073.21429

已选择6行。
9.列出与’SCOTT‘ 从事相同工作的所有员工。
select t1,ename ,t1.job, t2.job, t2.ename from emp t1 ,(select ename,job from emp where ename=’SCOTT’) t2 where t1.job=t2.job;

SQL> select t1.ename ,t1.job, t2.job, t2.ename from emp t1 , (select ename,job from emp where ename=’SCOTT’) t2 where t1.job=t2.job;

ENAME JOB JOB ENAME

SCOTT ANALYST ANALYST SCOTT
FORD ANALYST ANALYST SCOTT

10.列出薪金等于部门30中的员工的薪金的所有员工的姓名和薪金。
select t1.ename,t1.sal from emp t1 ,dept t2 ,(select ename ,sal ,deptno from emp ) t3 where t3.deptno=t2.deptno t1.sal=t3.sal ;
select t1.ename ,t1.sal from emp t1,dept t2 where t1.deptno =t2.deptn=’30’ ;

SQL> select t1.ename ,t1.sal ,t3.sal,t3.deptno from emp t1 ,(select deptno from dept where deptno=30) t2 ,(select ename ,sal ,deptno from emp )t3
where t3.deptno=t2.deptno and t1.sal =t3.sal ;

ENAME SAL SAL DEPTNO

ALLEN 1600 1600 30
WARD 1250 1250 30
WARD 1250 1250 30
MARTIN 1250 1250 30
MARTIN 1250 1250 30
BLAKE 2850 2850 30
TURNER 1500 1500 30
JAMES 950 950 30

已选择8行。

select t1.ename,t1.sal from emp t1,(select ename,sal from emp where deptno=30）t2 where t1.sal=t2.sal;

11.列出高于在部门30工作的所有员工的薪金和员工姓名和薪金。
@@（select t1.ename ,t1.sal ,t3.sal,t3.deptno from emp t1 ,(select deptno from dept where deptno=30) t2 ,(select ename ,sal ,deptno from emp )t3
@@where t3.deptno=t2.deptno and t1.sal =t3.sal ;）
select t1.ename ,t1.sal from emp t1 (select deptno from dept where deptno=30 ) t2 ,(select ename, sal ,max(sal) maxsal from emp ) t3
where t3.deptno=t2.deptno and t1.sal >t3.maxsal ;

select t1.ename,t1.sal from emp t1,(select ename,max(sal) maxsal from emp where deptno=30）t2 where t1.sal=t2.maxsal;

12.列出在每个部门工作的员工数量，平均工资，和平均服务期限。
select t1.avg(sal),dept.deptno,t1.froot(sysdate-hiredate) from emp t1 ,dept t2 where t2.deptno=t1.deptno ;
select t1.ename , t3.a, t2.deptno from emp t1 ,deot t2 ,(select job,avg(sal) a from emp where group by job) t3 where t1.job=t2.job

13.列出所有员工的姓名，部门名称和工资。（多表）
select * ,dept.* from emp ,dept where dept.deptno=emp.deptno;

SQL> select emp.* ,dept.* from emp ,dept where dept.deptno=emp.deptno;

 EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO     DEPTNO DNAME          LOC

  7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10         10 ACCOUNTING     NEW YORK
  7839 KING       PRESIDENT            17-11月-81           5000                    10         10 ACCOUNTING     NEW YORK
  7934 MILLER     CLERK           7782 23-1月 -82           1300                    10         10 ACCOUNTING     NEW YORK
  7566 JONES      MANAGER         7839 02-4月 -81           2975                    20         20 RESEARCH       DALLAS
  7902 FORD       ANALYST         7566 03-12月-81           3000                    20         20 RESEARCH       DALLAS
  7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20         20 RESEARCH       DALLAS
  7369 SMITH      CLERK           7902 17-12月-80            800                    20         20 RESEARCH       DALLAS
  7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20         20 RESEARCH       DALLAS
  7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30         30 SALES          CHICAGO
  7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30         30 SALES          CHICAGO
  7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30         30 SALES          CHICAGO
  7900 JAMES      CLERK           7698 03-12月-81            950                    30         30 SALES          CHICAGO
  7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30         30 SALES          CHICAGO
  7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30         30 SALES          CHICAGO

已选择14行。

SQL>

14.列出所有员工的年工资，按年薪从低到高排序。
select (sal*12) abc ,ename from emp order by abc ;

SQL> select (sal*12) abc,ename from emp order by abc ;

   ABC ENAME

  9600 SMITH
 11400 JAMES
 13200 ADAMS
 15000 WARD
 15000 MARTIN
 15600 MILLER
 18000 TURNER
 19200 ALLEN
 29400 CLARK
 34200 BLAKE
 35700 JONES
 36000 SCOTT
 36000 FORD
 60000 KING

已选择14行。

15.列出经理人的名字。

select ename from emp where job=’MANAGER’;