经典查询练手第一篇

--经典查询练手第一篇

scott.emp员工表结构如下：
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) 员工号
ENAME VARCHAR2(10) Y 员工姓名
JOB VARCHAR2(9) Y 工作
MGR NUMBER(4) Y 上级编号
HIREDATE DATE Y 雇佣日期
SAL NUMBER(7,2) Y 薪金
COMM NUMBER(7,2) Y 佣金
DEPTNO NUMBER(2) Y 部门编号

scott.dept部门表：
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2) 部门编号
DNAME VARCHAR2(14) Y 部门名称
LOC VARCHAR2(13) Y 地点
提示：工资 = 薪金 + 佣金

题目：

1.列出至少有一个员工的所有部门。
2.列出薪金比“SMITH”多的所有员工。
3.列出所有员工的姓名及其直接上级的姓名。
4.列出受雇日期早于其直接上级的所有员工。
5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
6.列出所有“CLERK”(办事员)的姓名及其部门名称。
7.列出最低薪金大于1500的各种工作。
8.列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
9.列出薪金高于公司平均薪金的所有员工。
10.列出与“SCOTT”从事相同工作的所有员工。
11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
14.列出所有员工的姓名、部门名称和工资。
15.列出所有部门的详细信息和部门人数。
16.列出各种工作的最低工资。
17.列出各个部门的MANAGER(经理)的最低薪金。
18.列出所有员工的年工资,按年薪从低到高排序。

1.列出至少有一个员工的所有部门。
select distinct(dname) 
from dept d,emp e
where d.deptno = e.deptno;

DNAME
--------------
ACCOUNTING
RESEARCH
SALES

2.列出薪金比“SMITH”多的所有员工。
select ename 
from emp 
where sal > (select sal from emp where ename='SMITH');

ENAME
----------
JAMES
ADAMS
WARD
MARTIN
MILLER
TURNER
ALLEN
CLARK
BLAKE
JONES
SCOTT
FORD
KING

13 rows selected.

3.列出所有员工的姓名及其直接上级的姓名。
SCOTT@PROD1> select b.ename hired, a.ename boss 
from emp a,emp b 
where a.empno=b.mgr;

HIRED	   BOSS
---------- ----------
FORD	   JONES
SCOTT	   JONES
TURNER	   BLAKE
ALLEN	   BLAKE
WARD	   BLAKE
JAMES	   BLAKE
MARTIN	   BLAKE
MILLER	   CLARK
ADAMS	   SCOTT
BLAKE	   KING
JONES	   KING
CLARK	   KING
SMITH	   FORD

13 rows selected.

--第二种解包括无上级的情况
SCOTT@PROD1> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name 
from emp a;

ENAME	   BOSS_NAME
---------- ----------
SMITH	   FORD
ALLEN	   BLAKE
WARD	   BLAKE
JONES	   KING
MARTIN	   BLAKE
BLAKE	   KING
CLARK	   KING
SCOTT	   JONES
KING
TURNER	   BLAKE
ADAMS	   SCOTT
JAMES	   BLAKE
FORD	   JONES
MILLER	   CLARK

14 rows selected.

4.列出受雇日期早于其直接上级的所有员工。
SCOTT@PROD1> select b.ename hired, a.ename boss 
from emp a,emp b 
where a.empno=b.mgr and a.hiredate>b.hiredate;
--select a.ename 
--from emp a 
--where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);

HIRED	   BOSS
---------- ----------
ALLEN	   BLAKE
WARD	   BLAKE
BLAKE	   KING
JONES	   KING
CLARK	   KING
SMITH	   FORD

6 rows selected.

5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SCOTT@PROD1> select * 
from dept left join emp on dept.deptno=emp.deptno;

    DEPTNO DNAME	  LOC		     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
	10 ACCOUNTING	  NEW YORK	      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
	10 ACCOUNTING	  NEW YORK	      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
	10 ACCOUNTING	  NEW YORK	      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10
	20 RESEARCH	  DALLAS	      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
	20 RESEARCH	  DALLAS	      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
	20 RESEARCH	  DALLAS	      7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20
	20 RESEARCH	  DALLAS	      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
	20 RESEARCH	  DALLAS	      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
	30 SALES	  CHICAGO	      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
	30 SALES	  CHICAGO	      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
	30 SALES	  CHICAGO	      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
	30 SALES	  CHICAGO	      7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
	30 SALES	  CHICAGO	      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
	30 SALES	  CHICAGO	      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
	40 OPERATIONS	  BOSTON

15 rows selected.

6.列出所有“CLERK”(办事员)的姓名及其部门名称。
SCOTT@PROD1> select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno and e.job='CLERK';  

ENAME	   DNAME
---------- --------------
MILLER	   ACCOUNTING
SMITH	   RESEARCH
ADAMS	   RESEARCH
JAMES	   SALES

7.列出最低薪金大于1500的各种工作。
SCOTT@PROD1> select d.dname, min(sal)    --此处错误，计算成了部门，题目要求工作
from dept d left join emp e on (d.deptno=e.deptno) 
group by d.dname;

DNAME		 MIN(SAL)
-------------- ----------
ACCOUNTING	     1300
OPERATIONS
RESEARCH	      800
SALES		      950

--更正
SCOTT@PROD1> select job from emp group by job having min(sal)>1500;

JOB
---------
PRESIDENT
MANAGER
ANALYST

8.列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
SCOTT@PROD1> select e.ename
from dept d, emp e
where d.deptno=e.deptno and d.dname='SALES';  

ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

6 rows selected.

9.列出薪金高于公司平均薪金的所有员工。
SCOTT@PROD1> select ename from emp where sal > (select avg(sal) from emp);

ENAME
----------
CLARK
BLAKE
JONES
SCOTT
FORD
KING

6 rows selected.

10.列出与“SCOTT”从事相同工作的所有员工。
SCOTT@PROD1> select ename
from emp 
where job = (select job from emp where ename='SCOTT'); 

ENAME
----------
SCOTT
FORD

11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
SCOTT@PROD1> select ename,sal 
from emp 
where sal in (select sal from emp where deptno=30) and deptno <> 30;

no rows selected

12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
SCOTT@PROD1> select ename,sal 
from emp 
where sal > (select max(sal) from emp where deptno=30);

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

13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
SCOTT@PROD1> select deptno, count(*) emp_c, avg(sal) avg_sal, avg(sysdate-hiredate) avg_hir 
from emp 
group by deptno;

    DEPTNO	EMP_C	 AVG_SAL    AVG_HIR
---------- ---------- ---------- ----------
	30	    6 1566.66667 12958.2813
	20	    5	    2175  12129.148
	10	    3 2916.66667 12849.2813
	
	
14.列出所有员工的姓名、部门名称和工资。
SCOTT@PROD1> select ename,d.dname,sal 
from emp, dept d 
where emp.deptno=d.deptno;

ENAME	   DNAME		 SAL
---------- -------------- ----------
CLARK	   ACCOUNTING		2450
KING	   ACCOUNTING		5000
MILLER	   ACCOUNTING		1300
SMITH	   RESEARCH		 800
JONES	   RESEARCH		2975
SCOTT	   RESEARCH		3000
ADAMS	   RESEARCH		1100
FORD	   RESEARCH		3000
ALLEN	   SALES		1600
WARD	   SALES		1250
MARTIN	   SALES		1250
BLAKE	   SALES		2850
TURNER	   SALES		1500
JAMES	   SALES		 950

14 rows selected.

15.列出所有部门的详细信息和部门人数。
SCOTT@PROD1> select d.deptno,d.dname,d.loc,count(*)   --此处没有输出部门中无人数的部门信息
from dept d,emp e 
where d.deptno=e.deptno 
group by d.deptno,d.dname,d.loc;

    DEPTNO DNAME	  LOC		  COUNT(*)
---------- -------------- ------------- ----------
	20 RESEARCH	  DALLAS		 5
	10 ACCOUNTING	  NEW YORK		 3
	30 SALES	  CHICAGO		 6

--更正
SCOTT@PROD1> select d.deptno,d.dname,d.loc
,(select count(*) from emp e where e.deptno=d.deptno group by e.deptno) dept_count 
from dept d;

    DEPTNO DNAME	  LOC		DEPT_COUNT
---------- -------------- ------------- ----------
	10 ACCOUNTING	  NEW YORK		 3
	20 RESEARCH	  DALLAS		 5
	30 SALES	  CHICAGO		 6
	40 OPERATIONS	  BOSTON
	
16.列出各种工作的最低工资。
SCOTT@PROD1> select deptno,min(sal) from emp group by deptno;  --此处误算成了部门

    DEPTNO   MIN(SAL)
---------- ----------
	30	  950
	20	  800
	10	 1300

--更正
SCOTT@PROD1> select job,avg(sal) from emp group by job;

JOB	    AVG(SAL)
--------- ----------
CLERK	      1037.5
SALESMAN	1400
PRESIDENT	5000
MANAGER   2758.33333
ANALYST 	3000


17.列出各个部门的MANAGER(经理)的最低薪金。
SCOTT@PROD1> select e1.deptno,min(e1.sal)
from emp e1,emp e2,dept d
where e1.empno=e2.mgr
group by e1.deptno;  

    DEPTNO MIN(E1.SAL)
---------- -----------
	30	  2850
	20	  2975
	10	  2450
	
18.列出所有员工的年工资,按年薪从低到高排序。
SCOTT@PROD1> select ename,(sal+nvl(comm,0))*12 as salpersal 
from emp 
order by salpersal;

ENAME	    SALPERSAL
---------- ----------
SMITH		 9600
JAMES		11400
ADAMS		13200
MILLER		15600
TURNER		18000
WARD		21000
ALLEN		22800
CLARK		29400
MARTIN		31800
BLAKE		34200
JONES		35700
FORD		36000
SCOTT		36000
KING		60000

14 rows selected.