经典查询练手第一篇

--经典查询练手第一篇

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 ewhere d.deptno = e.deptno;DNAME--------------ACCOUNTINGRESEARCHSALES2.列出薪金比“SMITH”多的所有员工。select ename from emp where sal > (select sal from emp where ename='SMITH');ENAME----------JAMESADAMSWARDMARTINMILLERTURNERALLENCLARKBLAKEJONESSCOTTFORDKING13 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   JONESSCOTT   JONESTURNER   BLAKEALLEN   BLAKEWARD   BLAKEJAMES   BLAKEMARTIN   BLAKEMILLER   CLARKADAMS   SCOTTBLAKE   KINGJONES   KINGCLARK   KINGSMITH   FORD13 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   FORDALLEN   BLAKEWARD   BLAKEJONES   KINGMARTIN   BLAKEBLAKE   KINGCLARK   KINGSCOTT   JONESKINGTURNER   BLAKEADAMS   SCOTTJAMES   BLAKEFORD   JONESMILLER   CLARK14 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   BLAKEWARD   BLAKEBLAKE   KINGJONES   KINGCLARK   KINGSMITH   FORD6 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    1010 ACCOUNTING  NEW YORK      7839 KING       PRESIDENT    17-NOV-81   5000    1010 ACCOUNTING  NEW YORK      7934 MILLER     CLERK      7782 23-JAN-82   1300    1020 RESEARCH  DALLAS      7566 JONES      MANAGER      7839 02-APR-81   2975    2020 RESEARCH  DALLAS      7902 FORD       ANALYST      7566 03-DEC-81   3000    2020 RESEARCH  DALLAS      7876 ADAMS      CLERK      7788 23-MAY-87   1100    2020 RESEARCH  DALLAS      7369 SMITH      CLERK      7902 17-DEC-80    800    2020 RESEARCH  DALLAS      7788 SCOTT      ANALYST      7566 19-APR-87   3000    2030 SALES  CHICAGO      7521 WARD       SALESMAN      7698 22-FEB-81   1250        500   3030 SALES  CHICAGO      7844 TURNER     SALESMAN      7698 08-SEP-81   1500  0   3030 SALES  CHICAGO      7499 ALLEN      SALESMAN      7698 20-FEB-81   1600        300   3030 SALES  CHICAGO      7900 JAMES      CLERK      7698 03-DEC-81    950    3030 SALES  CHICAGO      7698 BLAKE      MANAGER      7839 01-MAY-81   2850    3030 SALES  CHICAGO      7654 MARTIN     SALESMAN      7698 28-SEP-81   1250       1400   3040 OPERATIONS  BOSTON15 rows selected.6.列出所有“CLERK”(办事员)的姓名及其部门名称。SCOTT@PROD1> select e.ename, d.dnamefrom emp e, dept dwhere e.deptno=d.deptno and e.job='CLERK';  ENAME   DNAME---------- --------------MILLER   ACCOUNTINGSMITH   RESEARCHADAMS   RESEARCHJAMES   SALES7.列出最低薪金大于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     1300OPERATIONSRESEARCH      800SALES      950--更正SCOTT@PROD1> select job from emp group by job having min(sal)>1500;JOB---------PRESIDENTMANAGERANALYST8.列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。SCOTT@PROD1> select e.enamefrom dept d, emp ewhere d.deptno=e.deptno and d.dname='SALES';  ENAME----------ALLENWARDMARTINBLAKETURNERJAMES6 rows selected.9.列出薪金高于公司平均薪金的所有员工。SCOTT@PROD1> select ename from emp where sal > (select avg(sal) from emp);ENAME----------CLARKBLAKEJONESSCOTTFORDKING6 rows selected.10.列出与“SCOTT”从事相同工作的所有员工。SCOTT@PROD1> select enamefrom emp where job = (select job from emp where ename='SCOTT'); ENAME----------SCOTTFORD11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。SCOTT@PROD1> select ename,sal from emp where sal in (select sal from emp where deptno=30) and deptno <> 30;no rows selected12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。SCOTT@PROD1> select ename,sal from emp where sal > (select max(sal) from emp where deptno=30);ENAME  SAL---------- ----------JONES 2975SCOTT 3000FORD 3000KING 500013.列出在每个部门工作的员工数量、平均工资和平均服务期限。SCOTT@PROD1> select deptno, count(*) emp_c, avg(sal) avg_sal, avg(sysdate-hiredate) avg_hir from emp group by deptno;    DEPTNOEMP_C AVG_SAL    AVG_HIR---------- ---------- ---------- ----------30    6 1566.66667 12958.281320    5    2175  12129.14810    3 2916.66667 12849.281314.列出所有员工的姓名、部门名称和工资。SCOTT@PROD1> select ename,d.dname,sal from emp, dept d where emp.deptno=d.deptno;ENAME   DNAME SAL---------- -------------- ----------CLARK   ACCOUNTING2450KING   ACCOUNTING5000MILLER   ACCOUNTING1300SMITH   RESEARCH 800JONES   RESEARCH2975SCOTT   RESEARCH3000ADAMS   RESEARCH1100FORD   RESEARCH3000ALLEN   SALES1600WARD   SALES1250MARTIN   SALES1250BLAKE   SALES2850TURNER   SALES1500JAMES   SALES 95014 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 510 ACCOUNTING  NEW YORK 330 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  LOCDEPT_COUNT---------- -------------- ------------- ----------10 ACCOUNTING  NEW YORK 320 RESEARCH  DALLAS 530 SALES  CHICAGO 640 OPERATIONS  BOSTON16.列出各种工作的最低工资。SCOTT@PROD1> select deptno,min(sal) from emp group by deptno;  --此处误算成了部门    DEPTNO   MIN(SAL)---------- ----------30  95020  80010 1300--更正SCOTT@PROD1> select job,avg(sal) from emp group by job;JOB    AVG(SAL)--------- ----------CLERK      1037.5SALESMAN1400PRESIDENT5000MANAGER   2758.33333ANALYST 300017.列出各个部门的MANAGER(经理)的最低薪金。SCOTT@PROD1> select e1.deptno,min(e1.sal)from emp e1,emp e2,dept dwhere e1.empno=e2.mgrgroup by e1.deptno;      DEPTNO MIN(E1.SAL)---------- -----------30  285020  297510  245018.列出所有员工的年工资,按年薪从低到高排序。SCOTT@PROD1> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;ENAME    SALPERSAL---------- ----------SMITH 9600JAMES11400ADAMS13200MILLER15600TURNER18000WARD21000ALLEN22800CLARK29400MARTIN31800BLAKE34200JONES35700FORD36000SCOTT36000KING6000014 rows selected.