SQL> conn scott/tiger@clonepdb_plug Connected. SQL> desc emp Name Null? Type
EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> desc dept Name Null? Type
DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) 提示：工资＝SAL＋COMM SQL> set line 100 SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
12 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
1．列出至少有一个员工的所有部门。 SQL> select dname from dept where deptno in (select DEPTNO from emp);
DNAME
ACCOUNTING RESEARCH SALES 2．列出薪金比“SMITH”多的所有员工。 SQL> select ENAME from emp where sal>(select sal from emp where ename='SMITH');
ENAME
ALLEN WARD JONES MARTIN BLAKE CLARK KING TURNER JAMES FORD MILLER
11 rows selected. 3．列出所有员工的姓名及其直接上级的姓名。 SQL> select a.ename,(select b.ename from emp b where a.mgr=b.empno) Boss from emp a;
ENAME BOSS
SMITH FORD ALLEN BLAKE WARD BLAKE JONES KING MARTIN BLAKE BLAKE KING CLARK KING KING TURNER BLAKE JAMES BLAKE FORD JONES
ENAME BOSS
MILLER CLARK
12 rows selected. 4．列出受雇日期早于其直接上级的所有员工。 SQL> select a.ename from emp a where a.HIREDATE>(select b.HIREDATE from emp b where a.mgr=b.empno);
ENAME
MARTIN TURNER JAMES FORD MILLER
5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门 SQL> select a.deptno,a.dname,b.ename,b.empno from dept a,emp b where a.deptno=b.deptno(+);
DEPTNO DNAME ENAME EMPNO
10 ACCOUNTING KING 7839
10 ACCOUNTING CLARK 7782
10 ACCOUNTING MILLER 7934
20 RESEARCH FORD 7902
20 RESEARCH SMITH 7369
20 RESEARCH JONES 7566
30 SALES JAMES 7900
30 SALES TURNER 7844
30 SALES MARTIN 7654
30 SALES WARD 7521
30 SALES ALLEN 7499
DEPTNO DNAME ENAME EMPNO
30 SALES BLAKE 7698
12 rows selected. SQL> select a.deptno,a.dname,b.ename,b.empno from dept a left join emp b on a.deptno=b.deptno;
DEPTNO DNAME ENAME EMPNO
10 ACCOUNTING KING 7839
10 ACCOUNTING CLARK 7782
10 ACCOUNTING MILLER 7934
20 RESEARCH FORD 7902
20 RESEARCH SMITH 7369
20 RESEARCH JONES 7566
30 SALES JAMES 7900
30 SALES TURNER 7844
30 SALES MARTIN 7654
30 SALES WARD 7521
30 SALES ALLEN 7499
DEPTNO DNAME ENAME EMPNO
30 SALES BLAKE 7698
12 rows selected. Oracle 外连接(OUTER JOIN)包括以下：
左外连接(左边的表不加限制) 右外连接(右边的表不加限制) 全外连接(左右两表都不加限制) 对应SQL：LEFT/RIGHT/FULL OUTER JOIN。 通常省略OUTER关键字， 写成：LEFT/RIGHT/FULL JOIN。
在左连接和右连接时都会以一张A表为基础表，该表的内容会全部显示，然后加上A表和B表匹配的内容。 如果A表的数据在B表中没有记录。 那么在相关联的结果集行中列显示为空值（NULL）。
对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：
（+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。 当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。 （+）操作符只适用于列，而不能用在表达式上。 （+）操作符不能与OR和IN操作符一起使用。 （+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。 LEFT JOIN是以左表的记录为基础的；用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。 全外连接（FULL OUTER JOIN/FULL JOIN） 左表和右表都不做限制，所有的记录都显示，两表不足的地方均为NULL。 全外连接不支持（+）写法。 6．列出所有“CLERK”（办事员）的姓名及其部门名称。 SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and A.job='CLERK';
ENAME DNAME
MILLER ACCOUNTING SMITH RESEARCH JAMES SALES
SQL> select a.ename,b.dname from emp a ,dept b where a.deptno=b.deptno and a.job='CLERK';
ENAME DNAME
SMITH RESEARCH JAMES SALES MILLER ACCOUNTING 7．列出最低薪金大于1500的各种工作。 SQL> select distinct Job from emp group by job having min(sal)>1500 ;
JOB
PRESIDENT MANAGER ANALYST 8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES');
ENAME
ALLEN WARD MARTIN BLAKE TURNER JAMES
6 rows selected. SQL> select ename from emp a join dept b on a.deptno=b.deptno and b.dname='SALES';
ENAME
JAMES TURNER MARTIN WARD ALLEN BLAKE
6 rows selected. SQL> select ename from emp a,dept b where a.deptno=b.deptno and b.dname='SALES';
ENAME
JAMES TURNER MARTIN WARD ALLEN BLAKE
6 rows selected. 9．列出薪金高于公司平均薪金的所有员工。 SQL> select * from emp where sal >(select avg(sal) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
10．列出与“SMITH”从事相同工作的所有员工 SQL> select ename from emp where job=(select job from emp where ename='SMITH');
ENAME
SMITH JAMES MILLER 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal from emp b where b.deptno=30) and a.deptno<>30;
no rows selected 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 SQL> select a.ename,a.sal from emp a where a.sal > (select max(b.sal) from emp b where b.deptno=30) and a.deptno<>30;
ENAME SAL
JONES 2975 KING 5000 FORD 3000 13．列出在每个部门工作的员工数量、平均工资 SQL> select a.dname,(select count(*) from emp b where a.deptno=b.deptno) as DEPTCOUNT,(select avg(sal) from emp b where a.deptno=b.deptno) as deptavgsal from dept a;
DNAME DEPTCOUNT DEPTAVGSAL
ACCOUNTING 3 2916.66667 RESEARCH 3 2258.33333 SALES 6 1566.66667 SQL> select (select dname from dept b where a.deptno=b.deptno) as dname,count(a.deptno) as deptcount,avg(sal) as deptavgsal from emp a group by deptno 2 ;
DNAME DEPTCOUNT DEPTAVGSAL
SALES 6 1566.66667 RESEARCH 3 2258.33333 ACCOUNTING 3 2916.66667 14．列出所有员工的姓名、部门名称和工资。 SQL> select a.ename,b.dname,a.sal+NVL(a.comm,0) from emp a join dept b on a.deptno=b.deptno;
ENAME DNAME A.SAL+NVL(A.COMM,0)
KING ACCOUNTING 5000 CLARK ACCOUNTING 2450 MILLER ACCOUNTING 1300 FORD RESEARCH 3000 SMITH RESEARCH 800 JONES RESEARCH 2975 JAMES SALES 950 TURNER SALES 1500 MARTIN SALES 2650 WARD SALES 1750 ALLEN SALES 1900
ENAME DNAME A.SAL+NVL(A.COMM,0)
BLAKE SALES 2850 15．列出所有部门的详细信息和部门人数。 SQL> select a.dname,a.loc,(select count(*) from emp where deptno=a.deptno) from dept a;
DNAME LOC (SELECTCOUNT(*)FROMEMPWHEREDEPTNO=A.DEPTNO)
ACCOUNTING NEW YORK 3 RESEARCH DALLAS 3 SALES CHICAGO 6 16．列出各种工作的最低工资。 SQL> select job,avg(sal) from emp group by job;
JOB AVG(SAL)
CLERK 1016.66667 SALESMAN 1400 PRESIDENT 5000 MANAGER 2758.33333 ANALYST 3000 17．列出各个部门的MANAGER（经理）的最低薪金 SQL> select deptno,avg(sal) from emp where job='MANAGER' group by deptno;
DEPTNO AVG(SAL)
30 2850
20 2975
10 2450
18．列出所有员工的年工资,按年薪从低到高排序。 SQL> select ename,(sal+nvl(comm,0))*12 from emp order by (sal+nvl(comm,0))*12;
ENAME (SAL+NVL(COMM,0))*12
SMITH 9600 JAMES 11400 MILLER 15600 TURNER 18000 WARD 21000 ALLEN 22800 CLARK 29400 MARTIN 31800 BLAKE 34200 JONES 35700 FORD 36000
ENAME (SAL+NVL(COMM,0))*12
KING 60000 SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal ;
ENAME SALPERSAL
SMITH 9600 JAMES 11400 MILLER 15600 TURNER 18000 WARD 21000 ALLEN 22800 CLARK 29400 MARTIN 31800 BLAKE 34200 JONES 35700 FORD 36000
ENAME SALPERSAL
KING 60000
12 rows selected. 19．找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。 SQL> select ename from emp where substr(ename,3,1)='A';
ENAME
BLAKE CLARK SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';
ENAME
BLAKE CLARK 20. 找出EMP表员工名字中含有A 和N的员工姓名。 SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%'and ENAME LIKE '%N%';
ENAME
ALLEN MARTIN
- 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大， 佣金从大到小。 SQL> select ename,sal+nvl(comm,0) as total ,comm from emp order by total, comm desc nulls last;
ENAME TOTAL COMM
SMITH 800 JAMES 950 MILLER 1300 TURNER 1500 0 WARD 1750 500 ALLEN 1900 300 CLARK 2450 MARTIN 2650 1400 BLAKE 2850 JONES 2975 FORD 3000
ENAME TOTAL COMM
KING 5000
12 rows selected.
22.列出部门编号为20的所有职位 SQL> select job from emp where deptno=20;
JOB
CLERK MANAGER ANALYST
23.列出不属于SALES 的部门 SQL> select dname from dept where dname!='SALES' 2 ;
DNAME
ACCOUNTING RESEARCH
SQL> select dname from dept where dname<>'SALES';
DNAME
ACCOUNTING RESEARCH 24. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。 SQL> select ename,sal+nvl(comm,0) as total from emp where sal+nvl(comm,0) not between 1000 and 1500 order by total desc;
ENAME TOTAL
KING 5000 FORD 3000 JONES 2975 BLAKE 2850 MARTIN 2650 CLARK 2450 ALLEN 1900 WARD 1750 JAMES 950 SMITH 800
10 rows selected. 25. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。 SQL> select ename,job,(sal+nvl(comm,0))*12 as total from emp where (sal+nvl(comm,0))*12 between 15000 and 20000;
ENAME JOB TOTAL
TURNER SALESMAN 18000 MILLER CLERK 15600 26. 说明以下两条SQL语句的输出结果： SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL; SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL; SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
EMPNO COMM
7369
7566
7698
7782
7839
7900
7902
7934
8 rows selected.
SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
no rows selected IS NULL是判断某个字段是否为空，为空并不等价于为空字符串或为数字0； 而 =NULL 是判断某个值是否等于 NULL，NULL = NULL和NULL <> NULL都为 FALSE 27. 让SELECT 语句的输出结果为 SELECT * FROM SALGRADE; SELECT * FROM BONUS; SELECT * FROM EMP; SELECT * FROM DEPT; …… 列出当前用户有多少张数据表，结果集中存在多少条记录 SQL> SELECT 'SELECT * FROM '||TABLE_NAME||';' FROM USER_TABLES;
'SELECT*FROM'||TABLE_NAME||';'
SELECT * FROM DEPT; SELECT * FROM EMP; SELECT * FROM BONUS; SELECT * FROM SALGRADE; SQL> SELECT concat(concat('select * from ',table_name),';') FROM user_tables;
CONCAT(CONCAT('SELECT*FROM',TABLE_NAME),';')
select * from DEPT; select * from EMP; select * from BONUS; select * from SALGRADE; 28. 语句SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错？ SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > '1500' 2 ;
ENAME SAL
ALLEN 1600 JONES 2975 BLAKE 2850 CLARK 2450 KING 5000 FORD 3000
6 rows selected. 不会报错，这儿存在隐式数据类型的。 SQL> conn hr/hr@pdbtest Connected. SQL> desc employees; Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
SQL> desc DEPARTMENTS Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)
SQL> desc REGIONS Name Null? Type
REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) 29.让SELECT TO_CHAR(SALARY,'L99,999.99') FROM EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。 SQL> SELECT TO_CHAR(SALARY,'L99,999.99') FROM EMPLOYEES WHERE ROWNUM < 5;
TO_CHAR(SALARY,'L99,
$24,000.00
$17,000.00
$17,000.00
$9,000.00
30.列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。 SQL> select FIRST_NAME,SALARY,round(SALARY*1.08) from EMPLOYEES where rownum <6;
FIRST_NAME SALARY ROUND(SALARY*1.08)
Steven 24000 25920 Neena 17000 18360 Lex 17000 18360 Alexander 9000 9720 Bruce 6000 6480 31.、找出谁是最高领导，将名字按大写形式显示。 SQL> select upper(FIRST_NAME||' '||LAST_NAME) from EMPLOYEES where MANAGER_ID is null;
UPPER(FIRST_NAME||''||LAST_NAME)
STEVEN KING 32.找出First_Name 为David，Last_Name为Austin 的直接领导名字。 SQL> select FIRST_NAME||' '||LAST_NAME from EMPLOYEES where EMPLOYEE_ID=(select MANAGER_ID from EMPLOYEES where FIRST_NAME='David' and LAST_NAME='Austin');
FIRST_NAME||''||LAST_NAME
Alexander Hunold 33.First_Name 为Alexander，LAST_NAME为Hunold领导谁 SQL> select FIRST_NAME||' '||LAST_NAME from EMPLOYEES where MANAGER_ID=(select EMPLOYEE_ID from EMPLOYEES where FIRST_NAME='Alexander' and LAST_NAME='Hunold');
FIRST_NAME||''||LAST_NAME
Bruce Ernst David Austin Valli Pataballa Diana Lorentz 34.哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。 SQL> select FIRST_NAME||' '||LAST_NAME,salary,(select FIRST_NAME||' '||LAST_NAME from EMPLOYEES where EMPLOYEE_id=a.manager_id) as boss_name,(select salary from EMPLOYEES where EMPLOYEE_id=a.manager_id ) as boss_salary from EMPLOYEES a where a.salary>(select salary from EMPLOYEES where EMPLOYEE_id=a.manager_id);
FIRST_NAME||''||LAST SALARY BOSS_NAME BOSS_SALARY
Lisa Ozer 11500 Gerald Cambrault 11000 Ellen Abel 11000 Eleni Zlotkey 10500 SQL> select a.FIRST_NAME||' '||a.LAST_NAME,a.salary,b.FIRST_NAME||' '||b.LAST_NAME,b.salary from EMPLOYEES a join EMPLOYEES b on b.EMPLOYEE_id=a.manager_id and a.salary>b.salary;
A.FIRST_NAME||''||A. SALARY B.FIRST_NAME||''||B.LAST_NAME SALARY
Lisa Ozer 11500 Gerald Cambrault 11000 Ellen Abel 11000 Eleni Zlotkey 10500 35.哪些员工和Chen(LAST_NAME)同部门 SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES where DEPARTMENT_ID=(select DEPARTMENT_ID from EMPLOYEES where LAST_NAME='Chen') and LAST_NAME!='Chen';
EMPLOYEE_ID FIRST_NAME
108 Nancy
109 Daniel
111 Ismael
112 Jose Manuel
113 Luis
SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select DEPARTMENT_ID from EMPLOYEES where LAST_NAME='Chen') b using(DEPARTMENT_ID) where LAST_NAME!='Chen';
EMPLOYEE_ID FIRST_NAME
108 Nancy
109 Daniel
111 Ismael
112 Jose Manuel
113 Luis
36.哪些员工跟De Haan(LAST_NAME)做一样职位 SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select job_id from EMPLOYEES where LAST_NAME='De Haan') b using (job_id) where LAST_NAME!='De Haan' 2 ;
EMPLOYEE_ID FIRST_NAME
101 Neena
SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select job_id from EMPLOYEES where LAST_NAME='De Haan') b on a.job_id=b.job_id and LAST_NAME!='De Haan' 2 ;
EMPLOYEE_ID FIRST_NAME
101 Neena
37.哪些员工跟Hall(LAST_NAME)不在同一个部门。 SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select DEPARTMENT_ID from EMPLOYEES where LAST_NAME='Hall') b on a.DEPARTMENT_ID!=b.DEPARTMENT_ID ;
EMPLOYEE_ID FIRST_NAME
100 Steven
101 Neena
102 Lex
103 Alexander
104 Bruce
105 David
106 Valli
107 Diana
108 Nancy
109 Daniel
110 John
EMPLOYEE_ID FIRST_NAME
111 Ismael
112 Jose Manuel
113 Luis
114 Den
115 Alexander
116 Shelli
117 Sigal
118 Guy
119 Karen
120 Matthew
121 Adam
EMPLOYEE_ID FIRST_NAME
122 Payam
123 Shanta
124 Kevin
125 Julia
126 Irene
127 James
128 Steven
129 Laura
130 Mozhe
131 James
132 TJ
EMPLOYEE_ID FIRST_NAME
133 Jason
134 Michael
135 Ki
136 Hazel
137 Renske
138 Stephen
139 John
140 Joshua
141 Trenna
142 Curtis
143 Randall
EMPLOYEE_ID FIRST_NAME
144 Peter
180 Winston
181 Jean
182 Martha
183 Girard
184 Nandita
185 Alexis
186 Julia
187 Anthony
188 Kelly
189 Jennifer
EMPLOYEE_ID FIRST_NAME
190 Timothy
191 Randall
192 Sarah
193 Britney
194 Samuel
195 Vance
196 Alana
197 Kevin
198 Donald
199 Douglas
200 Jennifer
EMPLOYEE_ID FIRST_NAME
201 Michael
202 Pat
203 Susan
204 Hermann
205 Shelley
206 William
72 rows selected. 38.哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。 SQL> select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES a join (select job_ID from EMPLOYEES where FIRST_NAME='William'and LAST_NAME='Smith') b on a.job_ID!=b.job_ID ;
EMPLOYEE_ID FIRST_NAME
100 Steven
101 Neena
102 Lex
103 Alexander
104 Bruce
105 David
106 Valli
107 Diana
108 Nancy
109 Daniel
110 John
EMPLOYEE_ID FIRST_NAME
111 Ismael
112 Jose Manuel
113 Luis
114 Den
115 Alexander
116 Shelli
117 Sigal
118 Guy
119 Karen
120 Matthew
121 Adam
EMPLOYEE_ID FIRST_NAME
122 Payam
123 Shanta
124 Kevin
125 Julia
126 Irene
127 James
128 Steven
129 Laura
130 Mozhe
131 James
132 TJ
EMPLOYEE_ID FIRST_NAME
133 Jason
134 Michael
135 Ki
136 Hazel
137 Renske
138 Stephen
139 John
140 Joshua
141 Trenna
142 Curtis
143 Randall
EMPLOYEE_ID FIRST_NAME
144 Peter
145 John
146 Karen
147 Alberto
148 Gerald
149 Eleni
180 Winston
181 Jean
182 Martha
183 Girard
184 Nandita
EMPLOYEE_ID FIRST_NAME
185 Alexis
186 Julia
187 Anthony
188 Kelly
189 Jennifer
190 Timothy
191 Randall
192 Sarah
193 Britney
194 Samuel
195 Vance
EMPLOYEE_ID FIRST_NAME
196 Alana
197 Kevin
198 Donald
199 Douglas
200 Jennifer
201 Michael
202 Pat
203 Susan
204 Hermann
205 Shelley
206 William
77 rows selected. 39.显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。 SQL> select FIRST_NAME||' '||LAST_NAME, COMMISSION_PCT from EMPLOYEES a join (DEPARTMENTS b join LOCATIONS c using(LOCATION_ID)) using(DEPARTMENT_ID) where COMMISSION_PCT is not null;
FIRST_NAME||''||LAST COMMISSION_PCT
John Russell .4 Karen Partners .3 Alberto Errazuriz .3 Gerald Cambrault .3 Eleni Zlotkey .2 Peter Tucker .3 David Bernstein .25 Peter Hall .25 Christopher Olsen .2 Nanette Cambrault .2 Oliver Tuvault .15
FIRST_NAME||''||LAST COMMISSION_PCT
Janette King .35 Patrick Sully .35 Allan McEwen .35 Lindsey Smith .3 Louise Doran .3 Sarath Sewall .25 Clara Vishney .25 Danielle Greene .15 Mattea Marvins .1 David Lee .1 Sundar Ande .1
FIRST_NAME||''||LAST COMMISSION_PCT
Amit Banda .1 Lisa Ozer .25 Harrison Bloom .2 Tayler Fox .2 William Smith .15 Elizabeth Bates .15 Sundita Kumar .1 Ellen Abel .3 Alyssa Hutton .25 Jonathon Taylor .2 Jack Livingston .2
FIRST_NAME||''||LAST COMMISSION_PCT
Charles Johnson .1
34 rows selected. SQL> select FIRST_NAME||' '||LAST_NAME, COMMISSION_PCT from EMPLOYEES a ,DEPARTMENTS b,HR.LOCATIONS c where a.DEPARTMENT_ID = b.DEPARTMENT_ID and a.COMMISSION_PCT is not null and b.LOCATION_ID = c.LOCATION_ID;
FIRST_NAME||''||LAST COMMISSION_PCT
John Russell .4 Karen Partners .3 Alberto Errazuriz .3 Gerald Cambrault .3 Eleni Zlotkey .2 Peter Tucker .3 David Bernstein .25 Peter Hall .25 Christopher Olsen .2 Nanette Cambrault .2 Oliver Tuvault .15
FIRST_NAME||''||LAST COMMISSION_PCT
Janette King .35 Patrick Sully .35 Allan McEwen .35 Lindsey Smith .3 Louise Doran .3 Sarath Sewall .25 Clara Vishney .25 Danielle Greene .15 Mattea Marvins .1 David Lee .1 Sundar Ande .1
FIRST_NAME||''||LAST COMMISSION_PCT
Amit Banda .1 Lisa Ozer .25 Harrison Bloom .2 Tayler Fox .2 William Smith .15 Elizabeth Bates .15 Sundita Kumar .1 Ellen Abel .3 Alyssa Hutton .25 Jonathon Taylor .2 Jack Livingston .2
FIRST_NAME||''||LAST COMMISSION_PCT
Charles Johnson .1
34 rows selected. 40.示Executive部门有哪些职位 SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES a join DEPARTMENTS b using(DEPARTMENT_ID) where b.DEPARTMENT_NAME = 'Executive';
JOB_ID
AD_VP AD_PRES 41.整个公司中，最高工资和最低工资相差多少。 SQL> SELECT MAX(SALARY) - MIN(SALARY) FROM EMPLOYEES;
MAX(SALARY)-MIN(SALARY)
21900
42.提成大于0 的人数。 SQL> SELECT count(*) FROM EMPLOYEES where COMMISSION_PCT>0;
COUNT(*)
35
43.显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。 SQL> SELECT MAX(SALARY),MIN(SALARY),sum(SALARY),avg(SALARY) FROM EMPLOYEES;
MAX(SALARY) MIN(SALARY) SUM(SALARY) AVG(SALARY)
24000 2100 691416 6461.83178
44.整个公司有多少个领导。 SQL> SELECT count(DISTINCT(NVL(manager_id,'1'))) FROM employees e;
COUNT(DISTINCT(NVL(MANAGER_ID,'1')))
19
45.列出在同一部门入职日期晚但工资高于其他同事的员工： 名字、工资、入职日期。 SQL> select distinct a.FIRST_NAME,a.SALARY,a.HIRE_DATE from employees a join employees b on a.DEPARTMENT_ID=b.DEPARTMENT_ID and a.SALARY>b.SALARY and a.HIRE_DATE>b.HIRE_DATE order by a.salary desc;
FIRST_NAME SALARY HIRE_DATE
Steven 24000 2003-06-17 00:00:00 John 14000 2004-10-01 00:00:00 Karen 13500 2005-01-05 00:00:00 Nancy 12008 2002-08-17 00:00:00 Alberto 12000 2005-03-10 00:00:00 Lisa 11500 2005-03-11 00:00:00 Ellen 11000 2004-05-11 00:00:00 Gerald 11000 2007-10-15 00:00:00 Clara 10500 2005-11-11 00:00:00 Eleni 10500 2008-01-29 00:00:00 Harrison 10000 2006-03-23 00:00:00
FIRST_NAME SALARY HIRE_DATE
Peter 10000 2005-01-30 00:00:00 Tayler 9600 2006-01-24 00:00:00 Danielle 9500 2007-03-19 00:00:00 David 9500 2005-03-24 00:00:00 Alexander 9000 2006-01-03 00:00:00 Peter 9000 2005-08-20 00:00:00 Alyssa 8800 2005-03-19 00:00:00 Jonathon 8600 2006-03-24 00:00:00 Jack 8400 2006-04-23 00:00:00 Adam 8200 2005-04-10 00:00:00 Christopher 8000 2006-03-30 00:00:00
FIRST_NAME SALARY HIRE_DATE
Matthew 8000 2004-07-18 00:00:00 Jose Manuel 7800 2006-03-07 00:00:00 Nanette 7500 2006-12-09 00:00:00 William 7400 2007-02-23 00:00:00 Elizabeth 7300 2007-03-24 00:00:00 Mattea 7200 2008-01-24 00:00:00 David 6800 2008-02-23 00:00:00 Shanta 6500 2005-10-10 00:00:00 Sundar 6400 2008-03-24 00:00:00 Bruce 6000 2007-05-21 00:00:00 Kevin 5800 2007-11-16 00:00:00
FIRST_NAME SALARY HIRE_DATE
Nandita 4200 2004-01-27 00:00:00 Alexis 4100 2005-02-20 00:00:00 Sarah 4000 2004-02-04 00:00:00 Britney 3900 2005-03-03 00:00:00 Kelly 3800 2005-06-14 00:00:00 Jennifer 3600 2005-08-13 00:00:00 Julia 3400 2006-06-24 00:00:00 Laura 3300 2005-08-20 00:00:00 Julia 3200 2005-07-16 00:00:00 Samuel 3200 2006-07-01 00:00:00 Stephen 3200 2005-10-26 00:00:00
FIRST_NAME SALARY HIRE_DATE
Winston 3200 2006-01-24 00:00:00 Alana 3100 2006-04-24 00:00:00 Jean 3100 2006-02-23 00:00:00 Anthony 3000 2007-02-07 00:00:00 Kevin 3000 2006-05-23 00:00:00 Michael 2900 2006-08-26 00:00:00 Shelli 2900 2005-12-24 00:00:00 Timothy 2900 2006-07-11 00:00:00 Girard 2800 2008-02-03 00:00:00 Mozhe 2800 2005-10-30 00:00:00 Vance 2800 2007-03-17 00:00:00
FIRST_NAME SALARY HIRE_DATE
Irene 2700 2006-09-28 00:00:00 John 2700 2006-02-12 00:00:00 Donald 2600 2007-06-21 00:00:00 Douglas 2600 2008-01-13 00:00:00 Randall 2600 2006-03-15 00:00:00 Martha 2500 2007-06-21 00:00:00 Randall 2500 2007-12-19 00:00:00 Ki 2400 2007-12-12 00:00:00 Hazel 2200 2008-02-06 00:00:00 Steven 2200 2008-03-08 00:00:00
65 rows selected. SQL> select distinct a.FIRST_NAME,a.SALARY,a.HIRE_DATE from employees a join employees b using(DEPARTMENT_ID) where a.SALARY>b.SALARY and a.HIRE_DATE>b.HIRE_DATE order by SALARY desc;
FIRST_NAME SALARY HIRE_DATE
Steven 24000 2003-06-17 00:00:00 John 14000 2004-10-01 00:00:00 Karen 13500 2005-01-05 00:00:00 Nancy 12008 2002-08-17 00:00:00 Alberto 12000 2005-03-10 00:00:00 Lisa 11500 2005-03-11 00:00:00 Ellen 11000 2004-05-11 00:00:00 Gerald 11000 2007-10-15 00:00:00 Clara 10500 2005-11-11 00:00:00 Eleni 10500 2008-01-29 00:00:00 Harrison 10000 2006-03-23 00:00:00
FIRST_NAME SALARY HIRE_DATE
Peter 10000 2005-01-30 00:00:00 Tayler 9600 2006-01-24 00:00:00 Danielle 9500 2007-03-19 00:00:00 David 9500 2005-03-24 00:00:00 Alexander 9000 2006-01-03 00:00:00 Peter 9000 2005-08-20 00:00:00 Alyssa 8800 2005-03-19 00:00:00 Jonathon 8600 2006-03-24 00:00:00 Jack 8400 2006-04-23 00:00:00 Adam 8200 2005-04-10 00:00:00 Christopher 8000 2006-03-30 00:00:00
FIRST_NAME SALARY HIRE_DATE
Matthew 8000 2004-07-18 00:00:00 Jose Manuel 7800 2006-03-07 00:00:00 Nanette 7500 2006-12-09 00:00:00 William 7400 2007-02-23 00:00:00 Elizabeth 7300 2007-03-24 00:00:00 Mattea 7200 2008-01-24 00:00:00 David 6800 2008-02-23 00:00:00 Shanta 6500 2005-10-10 00:00:00 Sundar 6400 2008-03-24 00:00:00 Bruce 6000 2007-05-21 00:00:00 Kevin 5800 2007-11-16 00:00:00
FIRST_NAME SALARY HIRE_DATE
Nandita 4200 2004-01-27 00:00:00 Alexis 4100 2005-02-20 00:00:00 Sarah 4000 2004-02-04 00:00:00 Britney 3900 2005-03-03 00:00:00 Kelly 3800 2005-06-14 00:00:00 Jennifer 3600 2005-08-13 00:00:00 Julia 3400 2006-06-24 00:00:00 Laura 3300 2005-08-20 00:00:00 Julia 3200 2005-07-16 00:00:00 Samuel 3200 2006-07-01 00:00:00 Stephen 3200 2005-10-26 00:00:00
FIRST_NAME SALARY HIRE_DATE
Winston 3200 2006-01-24 00:00:00 Alana 3100 2006-04-24 00:00:00 Jean 3100 2006-02-23 00:00:00 Anthony 3000 2007-02-07 00:00:00 Kevin 3000 2006-05-23 00:00:00 Michael 2900 2006-08-26 00:00:00 Shelli 2900 2005-12-24 00:00:00 Timothy 2900 2006-07-11 00:00:00 Girard 2800 2008-02-03 00:00:00 Mozhe 2800 2005-10-30 00:00:00 Vance 2800 2007-03-17 00:00:00
FIRST_NAME SALARY HIRE_DATE
Irene 2700 2006-09-28 00:00:00 John 2700 2006-02-12 00:00:00 Donald 2600 2007-06-21 00:00:00 Douglas 2600 2008-01-13 00:00:00 Randall 2600 2006-03-15 00:00:00 Martha 2500 2007-06-21 00:00:00 Randall 2500 2007-12-19 00:00:00 Ki 2400 2007-12-12 00:00:00 Hazel 2200 2008-02-06 00:00:00 Steven 2200 2008-03-08 00:00:00
65 rows selected. 46.各个部门平均、最大、最小工资、人数，按照部门号升序排列。 47.SQL> select distinct DEPARTMENT_ID,max(SALARY),min(salary),count(*) from employees group by DEPARTMENT_ID order by DEPARTMENT_ID;
DEPARTMENT_ID MAX(SALARY) MIN(SALARY) COUNT(*)
10 4400 4400 1
20 13000 6000 2
30 11000 2500 6
40 6500 6500 1
50 8200 2100 45
60 9000 4200 5
70 10000 10000 1
80 14000 6100 34
90 24000 17000 3
100 12008 6900 6
110 12008 8300 2
DEPARTMENT_ID MAX(SALARY) MIN(SALARY) COUNT(*)
7000 7000 1
12 rows selected. 47.各个部门中工资大于5000的员工人数 SQL> select distinct DEPARTMENT_ID,count(*) from employees where salary>5000 group by DEPARTMENT_ID ;
DEPARTMENT_ID COUNT(*)
100 6
30 1
1
90 3
20 2
70 1
110 2
50 5
80 34
40 1
60 2
11 rows selected. 48.各个部门平均工资和人数，按照部门名字升序排列 SQL> select DEPARTMENT_name,avg(a.salary),count(*) from employees a left join DEPARTMENTS b using (DEPARTMENT_ID) group by DEPARTMENT_NAME order by b.DEPARTMENT_NAME ;
DEPARTMENT_NAME AVG(A.SALARY) COUNT(*)
Accounting 10154 2 Administration 4400 1 Executive 19333.3333 3 Finance 8601.33333 6 Human Resources 6500 1 IT 5760 5 Marketing 9500 2 Public Relations 10000 1 Purchasing 4150 6 Sales 8955.88235 34 Shipping 3475.55556 45
DEPARTMENT_NAME AVG(A.SALARY) COUNT(*)
7000 1
12 rows selected.
SQL> select avg(a.salary),count(*) from employees a left join DEPARTMENTS b on a.DEPARTMENT_ID=b.DEPARTMENT_id group by DEPARTMENT_NAME order by b.DEPARTMENT_NAME;
AVG(A.SALARY) COUNT(*)
10154 2
4400 1
19333.3333 3 8601.33333 6 6500 1 5760 5 9500 2 10000 1 4150 6 8955.88235 34 3475.55556 45
AVG(A.SALARY) COUNT(*)
7000 1
49.列出每个部门中有同样工资的员工的统计信息， 列出他们的部门号，工资，人数。 SQL> select DEPARTMENT_id,salary,count() from employees group by DEPARTMENT_id, salary having count()>1;
DEPARTMENT_ID SALARY COUNT(*)
90 17000 2
50 3200 4
50 2200 2
50 3600 2
80 10500 2
80 9000 2
50 2700 2
50 3100 3
80 10000 3
50 3000 2
60 4800 2
DEPARTMENT_ID SALARY COUNT(*)
50 3300 2
80 6200 2
50 2800 3
50 2500 5
50 2600 3
50 2400 2
80 9500 3
80 7500 2
80 11000 2
80 7000 2
50 2900 2
DEPARTMENT_ID SALARY COUNT(*)
80 8000 2
23 rows selected. SQL> SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT 2 FROM EMPLOYEES EMP1,EMPLOYEES EMP2 3 WHERE EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND 4 EMP1.SALARY = EMP2.SALARY 5 AND EMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID 6 GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY;
DEPARTMENT_ID SALARY CNT
90 17000 2
50 3200 12
50 2200 2
50 3600 2
80 10500 2
80 9000 2
50 2700 2
50 3100 6
80 10000 6
50 3000 2
60 4800 2
DEPARTMENT_ID SALARY CNT
50 3300 2
80 6200 2
50 2800 6
50 2500 20
50 2600 6
50 2400 2
80 9500 6
80 7500 2
80 11000 2
80 7000 2
50 2900 2
DEPARTMENT_ID SALARY CNT
80 8000 2
23 rows selected. 50.列出同部门中工资高于1000 的员工数量超过2 人的部门， 显示部门名字、地区名称。 SQL> select b.DEPARTMENT_NAME,c.CITY,count() from EMPLOYEES a join (DEPARTMENTS b join LOCATIONS c using(LOCATION_ID)) using(DEPARTMENT_ID) where a.SALARY > 1000 GROUP BY b.DEPARTMENT_NAME,c.CITY having count()>2;
DEPARTMENT_NAME CITY COUNT(*)
IT Southlake 5 Sales Oxford 34 Shipping South San Francisco 45 Purchasing Seattle 6 Executive Seattle 3 Finance Seattle 6
6 rows selected. 51.哪些员工的工资，高于整个公司的平均工资， 列出员工的名字和工资（降序） SQL> select first_name||' '||last_name,salary from EMPLOYEES where salary>(select avg(salary) from EMPLOYEES) order by salary desc;
FIRST_NAME||''||LAST SALARY
Steven King 24000 Neena Kochhar 17000 Lex De Haan 17000 John Russell 14000 Karen Partners 13500 Michael Hartstein 13000 Nancy Greenberg 12008 Shelley Higgins 12008 Alberto Errazuriz 12000 Lisa Ozer 11500 Ellen Abel 11000
FIRST_NAME||''||LAST SALARY
Den Raphaely 11000 Gerald Cambrault 11000 Clara Vishney 10500 Eleni Zlotkey 10500 Peter Tucker 10000 Harrison Bloom 10000 Janette King 10000 Hermann Baer 10000 Tayler Fox 9600 David Bernstein 9500 Danielle Greene 9500
FIRST_NAME||''||LAST SALARY
Patrick Sully 9500 Daniel Faviet 9000 Alexander Hunold 9000 Peter Hall 9000 Allan McEwen 9000 Alyssa Hutton 8800 Jonathon Taylor 8600 Jack Livingston 8400 William Gietz 8300 Adam Fripp 8200 John Chen 8200
FIRST_NAME||''||LAST SALARY
Christopher Olsen 8000 Matthew Weiss 8000 Lindsey Smith 8000 Payam Kaufling 7900 Jose Manuel Urman 7800 Ismael Sciarra 7700 Louise Doran 7500 Nanette Cambrault 7500 William Smith 7400 Elizabeth Bates 7300 Mattea Marvins 7200
FIRST_NAME||''||LAST SALARY
Oliver Tuvault 7000 Kimberely Grant 7000 Sarath Sewall 7000 Luis Popp 6900 David Lee 6800 Susan Mavris 6500 Shanta Vollman 6500
51 rows selected. 52.哪些员工的工资，介于50号 和80号 部门平均工资之间 SQL> select first_name||' '||last_name,salary from EMPLOYEES where salary between (select avg(salary) from EMPLOYEES where DEPARTMENT_ID = 50) and (SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 80);
FIRST_NAME||''||LAST SALARY
Bruce Ernst 6000 David Austin 4800 Valli Pataballa 4800 Diana Lorentz 4200 John Chen 8200 Ismael Sciarra 7700 Jose Manuel Urman 7800 Luis Popp 6900 Matthew Weiss 8000 Adam Fripp 8200 Payam Kaufling 7900
FIRST_NAME||''||LAST SALARY
Shanta Vollman 6500 Kevin Mourgos 5800 Renske Ladwig 3600 Trenna Rajs 3500 Christopher Olsen 8000 Nanette Cambrault 7500 Oliver Tuvault 7000 Lindsey Smith 8000 Louise Doran 7500 Sarath Sewall 7000 Mattea Marvins 7200
FIRST_NAME||''||LAST SALARY
David Lee 6800 Sundar Ande 6400 Amit Banda 6200 William Smith 7400 Elizabeth Bates 7300 Sundita Kumar 6100 Alyssa Hutton 8800 Jonathon Taylor 8600 Jack Livingston 8400 Kimberely Grant 7000 Charles Johnson 6200
FIRST_NAME||''||LAST SALARY
Nandita Sarchand 4200 Alexis Bull 4100 Kelly Chung 3800 Jennifer Dilly 3600 Sarah Bell 4000 Britney Everett 3900 Jennifer Whalen 4400 Pat Fay 6000 Susan Mavris 6500 William Gietz 8300
43 rows selected. 53.所在部门平均工资高于5000 的员工名字。 SQL> select first_name||' '||last_name,salary from EMPLOYEES where DEPARTMENT_ID IN (select distinct DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID having avg(salary)>5000);
FIRST_NAME||''||LAST SALARY
Steven King 24000 Neena Kochhar 17000 Lex De Haan 17000 Alexander Hunold 9000 Bruce Ernst 6000 David Austin 4800 Valli Pataballa 4800 Diana Lorentz 4200 Nancy Greenberg 12008 Daniel Faviet 9000 John Chen 8200
FIRST_NAME||''||LAST SALARY
Ismael Sciarra 7700 Jose Manuel Urman 7800 Luis Popp 6900 John Russell 14000 Karen Partners 13500 Alberto Errazuriz 12000 Gerald Cambrault 11000 Eleni Zlotkey 10500 Peter Tucker 10000 David Bernstein 9500 Peter Hall 9000
FIRST_NAME||''||LAST SALARY
Christopher Olsen 8000 Nanette Cambrault 7500 Oliver Tuvault 7000 Janette King 10000 Patrick Sully 9500 Allan McEwen 9000 Lindsey Smith 8000 Louise Doran 7500 Sarath Sewall 7000 Clara Vishney 10500 Danielle Greene 9500
FIRST_NAME||''||LAST SALARY
Mattea Marvins 7200 David Lee 6800 Sundar Ande 6400 Amit Banda 6200 Lisa Ozer 11500 Harrison Bloom 10000 Tayler Fox 9600 William Smith 7400 Elizabeth Bates 7300 Sundita Kumar 6100 Ellen Abel 11000
FIRST_NAME||''||LAST SALARY
Alyssa Hutton 8800 Jonathon Taylor 8600 Jack Livingston 8400 Charles Johnson 6200 Michael Hartstein 13000 Pat Fay 6000 Susan Mavris 6500 Hermann Baer 10000 Shelley Higgins 12008 William Gietz 8300 SQL> select first_name||' '||last_name,salary from EMPLOYEES join (select distinct DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID having avg(salary)>5000) using(DEPARTMENT_ID);
FIRST_NAME||''||LAST SALARY
Steven King 24000 Neena Kochhar 17000 Lex De Haan 17000 Alexander Hunold 9000 Bruce Ernst 6000 David Austin 4800 Valli Pataballa 4800 Diana Lorentz 4200 Nancy Greenberg 12008 Daniel Faviet 9000 John Chen 8200
FIRST_NAME||''||LAST SALARY
Ismael Sciarra 7700 Jose Manuel Urman 7800 Luis Popp 6900 John Russell 14000 Karen Partners 13500 Alberto Errazuriz 12000 Gerald Cambrault 11000 Eleni Zlotkey 10500 Peter Tucker 10000 David Bernstein 9500 Peter Hall 9000
FIRST_NAME||''||LAST SALARY
Christopher Olsen 8000 Nanette Cambrault 7500 Oliver Tuvault 7000 Janette King 10000 Patrick Sully 9500 Allan McEwen 9000 Lindsey Smith 8000 Louise Doran 7500 Sarath Sewall 7000 Clara Vishney 10500 Danielle Greene 9500
FIRST_NAME||''||LAST SALARY
Mattea Marvins 7200 David Lee 6800 Sundar Ande 6400 Amit Banda 6200 Lisa Ozer 11500 Harrison Bloom 10000 Tayler Fox 9600 William Smith 7400 Elizabeth Bates 7300 Sundita Kumar 6100 Ellen Abel 11000
FIRST_NAME||''||LAST SALARY
Alyssa Hutton 8800 Jonathon Taylor 8600 Jack Livingston 8400 Charles Johnson 6200 Michael Hartstein 13000 Pat Fay 6000 Susan Mavris 6500 Hermann Baer 10000 Shelley Higgins 12008 William Gietz 8300
54 rows selected. 54.列出各个部门中工资最高的员工的信息：名字、部门号、工资。 55.SQL> select first_name||' '||last_name,DEPARTMENT_ID,salary from EMPLOYEES where (DEPARTMENT_ID,salary) in (SELECT DEPARTMENT_ID,MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);
FIRST_NAME||''||LAST DEPARTMENT_ID SALARY
Nancy Greenberg 100 12008 Den Raphaely 30 11000 Steven King 90 24000 Michael Hartstein 20 13000 Hermann Baer 70 10000 Shelley Higgins 110 12008 Adam Fripp 50 8200 John Russell 80 14000 Susan Mavris 40 6500 Alexander Hunold 60 9000 Jennifer Whalen 10 4400
11 rows selected. SQL> select first_name||' '||last_name,DEPARTMENT_ID,salary from EMPLOYEES join (SELECT DEPARTMENT_ID,MAX(SALARY) as salary FROM EMPLOYEES GROUP BY DEPARTMENT_ID) using (DEPARTMENT_ID,salary);
FIRST_NAME||''||LAST DEPARTMENT_ID SALARY
Steven King 90 24000 Alexander Hunold 60 9000 Nancy Greenberg 100 12008 Den Raphaely 30 11000 Adam Fripp 50 8200 John Russell 80 14000 Jennifer Whalen 10 4400 Michael Hartstein 20 13000 Susan Mavris 40 6500 Hermann Baer 70 10000 Shelley Higgins 110 12008
11 rows selected. 55.最高的部门平均工资是多少。 SQL> select max(AVGSALARY) from (SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY FROM EMPLOYEES GROUP BY DEPARTMENT_ID);
MAX(AVGSALARY)
19333.3333
19334.SQL> select max(avg(salary))
2 from employees 3 group by department_id;
MAX(AVG(SALARY))
19333.3333
56.哪些部门的人数比90 号部门的人数多。 SQL> select department_id, count() from employees group by department_id having count()>(select count(*) from employees where department_id=90);
DEPARTMENT_ID COUNT(*)
100 6
30 6
50 45
80 34
60 5
57.Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁（非关联子查询） SQL> select first_name || ' '||last_name from employees where employee_ID = (select manager_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');
FIRST_NAME||''||LAST
Steven King
SQL> select first_name || ' '||last_name from employees where employee_ID in (select manager_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');
FIRST_NAME||''||LAST
Steven King 58.Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁（非关联子查询）。 SQL> select first_name || ' '||last_name from employees where MANAGER_ID in (select EMPLOYEE_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');
FIRST_NAME||''||LAST
Alexander Khoo Shelli Baida Sigal Tobias Guy Himuro Karen Colmenares SQL> select first_name || ' '||last_name from employees where MANAGER_ID = (select EMPLOYEE_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');
FIRST_NAME||''||LAST
Alexander Khoo Shelli Baida Sigal Tobias Guy Himuro Karen Colmenares 59.Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁（关联子查询）。 SQL> SELECT FIRST_NAME || ' ' || LAST_NAME 2 FROM EMPLOYEES EMP1 3 WHERE EXISTS ( 4 SELECT 1 FROM EMPLOYEES EMP2 5 WHERE FIRST_NAME = 'Den' 6 AND LAST_NAME = 'Raphaely' 7 AND EMP1.EMPLOYEE_ID = EMP2.MANAGER_ID);
FIRST_NAME||''||LAST
Steven King 60.Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁（关联子查询） SQL> SELECT FIRST_NAME || ' ' || LAST_NAME 2 FROM EMPLOYEES EMP1 3 WHERE EXISTS ( 4 SELECT 1 FROM EMPLOYEES EMP2 5 WHERE FIRST_NAME = 'Den' 6 AND LAST_NAME = 'Raphaely' 7 AND EMP2.EMPLOYEE_ID = EMP1.MANAGER_ID);
FIRST_NAME||''||LAST
Alexander Khoo Shelli Baida Sigal Tobias Guy Himuro Karen Colmenares。 61.列出在同一部门共事，入职日期晚但工资高于其他同事的员工： 名字、工资、入职日期（关联子查询）。 SQL> SELECT FIRST_NAME || ' ' || LAST_NAME,salary,HIRE_DATE 2 FROM EMPLOYEES EMP1 3 WHERE EXISTS ( 4 SELECT 1 FROM EMPLOYEES EMP2 5 WHERE EMP1.HIRE_DATE>EMP2.HIRE_DATE and EMP1.salary>EMP2.salary 6 AND EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID);
FIRST_NAME||''||LAST SALARY HIRE_DATE
Steven King 24000 2003-06-17 00:00:00 Alexander Hunold 9000 2006-01-03 00:00:00 Bruce Ernst 6000 2007-05-21 00:00:00 Nancy Greenberg 12008 2002-08-17 00:00:00 Jose Manuel Urman 7800 2006-03-07 00:00:00 Shelli Baida 2900 2005-12-24 00:00:00 Adam Fripp 8200 2005-04-10 00:00:00 Matthew Weiss 8000 2004-07-18 00:00:00 Shanta Vollman 6500 2005-10-10 00:00:00 Kevin Mourgos 5800 2007-11-16 00:00:00 Laura Bissot 3300 2005-08-20 00:00:00
FIRST_NAME||''||LAST SALARY HIRE_DATE
Julia Dellinger 3400 2006-06-24 00:00:00 Jennifer Dilly 3600 2005-08-13 00:00:00 Girard Geoni 2800 2008-02-03 00:00:00 Anthony Cabrio 3000 2007-02-07 00:00:00 Vance Jones 2800 2007-03-17 00:00:00 Martha Sullivan 2500 2007-06-21 00:00:00 Randall Perkins 2500 2007-12-19 00:00:00 Donald OConnell 2600 2007-06-21 00:00:00 Douglas Grant 2600 2008-01-13 00:00:00 Michael Rogers 2900 2006-08-26 00:00:00 Winston Taylor 3200 2006-01-24 00:00:00
FIRST_NAME||''||LAST SALARY HIRE_DATE
Jean Fleaur 3100 2006-02-23 00:00:00 Timothy Gates 2900 2006-07-11 00:00:00 Samuel McCain 3200 2006-07-01 00:00:00 Alana Walsh 3100 2006-04-24 00:00:00 Kevin Feeney 3000 2006-05-23 00:00:00 Julia Nayer 3200 2005-07-16 00:00:00 Irene Mikkilineni 2700 2006-09-28 00:00:00 Mozhe Atkinson 2800 2005-10-30 00:00:00 Stephen Stiles 3200 2005-10-26 00:00:00 John Seo 2700 2006-02-12 00:00:00 Randall Matos 2600 2006-03-15 00:00:00
FIRST_NAME||''||LAST SALARY HIRE_DATE
Alexis Bull 4100 2005-02-20 00:00:00 Kelly Chung 3800 2005-06-14 00:00:00 Britney Everett 3900 2005-03-03 00:00:00 Steven Markle 2200 2008-03-08 00:00:00 Ki Gee 2400 2007-12-12 00:00:00 Hazel Philtanker 2200 2008-02-06 00:00:00 Nandita Sarchand 4200 2004-01-27 00:00:00 Sarah Bell 4000 2004-02-04 00:00:00 Alberto Errazuriz 12000 2005-03-10 00:00:00 Gerald Cambrault 11000 2007-10-15 00:00:00 Eleni Zlotkey 10500 2008-01-29 00:00:00
FIRST_NAME||''||LAST SALARY HIRE_DATE
Clara Vishney 10500 2005-11-11 00:00:00 Lisa Ozer 11500 2005-03-11 00:00:00 Harrison Bloom 10000 2006-03-23 00:00:00 Tayler Fox 9600 2006-01-24 00:00:00 Danielle Greene 9500 2007-03-19 00:00:00 Jack Livingston 8400 2006-04-23 00:00:00 Mattea Marvins 7200 2008-01-24 00:00:00 John Russell 14000 2004-10-01 00:00:00 Karen Partners 13500 2005-01-05 00:00:00 Ellen Abel 11000 2004-05-11 00:00:00 Peter Tucker 10000 2005-01-30 00:00:00
FIRST_NAME||''||LAST SALARY HIRE_DATE
David Bernstein 9500 2005-03-24 00:00:00 Peter Hall 9000 2005-08-20 00:00:00 Alyssa Hutton 8800 2005-03-19 00:00:00 Jonathon Taylor 8600 2006-03-24 00:00:00 Christopher Olsen 8000 2006-03-30 00:00:00 Nanette Cambrault 7500 2006-12-09 00:00:00 William Smith 7400 2007-02-23 00:00:00 Elizabeth Bates 7300 2007-03-24 00:00:00 David Lee 6800 2008-02-23 00:00:00 Sundar Ande 6400 2008-03-24 00:00:00
65 rows selected. 62.哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门（非关联子查询）。 SQL> select first_name || ' '||last_name from employees where DEPARTMENT_ID != (select DEPARTMENT_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');
FIRST_NAME||''||LAST
Ellen Abel Sundar Ande Mozhe Atkinson David Austin Hermann Baer Amit Banda Elizabeth Bates Sarah Bell David Bernstein Laura Bissot Harrison Bloom
FIRST_NAME||''||LAST
Alexis Bull Anthony Cabrio Gerald Cambrault Nanette Cambrault John Chen Kelly Chung Curtis Davies Lex De Haan Julia Dellinger Jennifer Dilly Louise Doran
FIRST_NAME||''||LAST
Bruce Ernst Alberto Errazuriz Britney Everett Daniel Faviet Pat Fay Kevin Feeney Jean Fleaur Tayler Fox Adam Fripp Timothy Gates Ki Gee
FIRST_NAME||''||LAST
Girard Geoni William Gietz Douglas Grant Nancy Greenberg Danielle Greene Peter Hall Michael Hartstein Shelley Higgins Alexander Hunold Alyssa Hutton Charles Johnson
FIRST_NAME||''||LAST
Vance Jones Payam Kaufling Janette King Steven King Neena Kochhar Sundita Kumar Renske Ladwig James Landry David Lee Jack Livingston Diana Lorentz
FIRST_NAME||''||LAST
Jason Mallin Steven Markle James Marlow Mattea Marvins Randall Matos Susan Mavris Samuel McCain Allan McEwen Irene Mikkilineni Kevin Mourgos Julia Nayer
FIRST_NAME||''||LAST
Donald OConnell Christopher Olsen TJ Olson Lisa Ozer Karen Partners Valli Pataballa Joshua Patel Randall Perkins Hazel Philtanker Luis Popp Trenna Rajs
FIRST_NAME||''||LAST
Michael Rogers John Russell Nandita Sarchand Ismael Sciarra John Seo Sarath Sewall Lindsey Smith William Smith Stephen Stiles Martha Sullivan Patrick Sully
FIRST_NAME||''||LAST
Jonathon Taylor Winston Taylor Peter Tucker Oliver Tuvault Jose Manuel Urman Peter Vargas Clara Vishney Shanta Vollman Alana Walsh Matthew Weiss Jennifer Whalen
FIRST_NAME||''||LAST
Eleni Zlotkey
100 rows selected. SQL> select first_name || ' '||last_name from employees where DEPARTMENT_ID not in (select DEPARTMENT_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');
FIRST_NAME||''||LAST
Matthew Weiss Adam Fripp Payam Kaufling Shanta Vollman Kevin Mourgos Julia Nayer Irene Mikkilineni James Landry Steven Markle Laura Bissot Mozhe Atkinson
FIRST_NAME||''||LAST
James Marlow TJ Olson Jason Mallin Michael Rogers Ki Gee Hazel Philtanker Renske Ladwig Stephen Stiles John Seo Joshua Patel Trenna Rajs
FIRST_NAME||''||LAST
Curtis Davies Randall Matos Peter Vargas Winston Taylor Jean Fleaur Martha Sullivan Girard Geoni Nandita Sarchand Alexis Bull Julia Dellinger Anthony Cabrio
FIRST_NAME||''||LAST
Kelly Chung Jennifer Dilly Timothy Gates Randall Perkins Sarah Bell Britney Everett Samuel McCain Vance Jones Alana Walsh Kevin Feeney Donald OConnell
FIRST_NAME||''||LAST
Douglas Grant Susan Mavris Shelley Higgins William Gietz Steven King Neena Kochhar Lex De Haan Hermann Baer Jennifer Whalen Michael Hartstein Pat Fay
FIRST_NAME||''||LAST
Alexander Hunold Bruce Ernst David Austin Valli Pataballa Diana Lorentz Nancy Greenberg Daniel Faviet John Chen Ismael Sciarra Jose Manuel Urman Luis Popp
FIRST_NAME||''||LAST
John Russell Karen Partners Alberto Errazuriz Gerald Cambrault Eleni Zlotkey Peter Tucker David Bernstein Peter Hall Christopher Olsen Nanette Cambrault Oliver Tuvault
FIRST_NAME||''||LAST
Janette King Patrick Sully Allan McEwen Lindsey Smith Louise Doran Sarath Sewall Clara Vishney Danielle Greene Mattea Marvins David Lee Sundar Ande
FIRST_NAME||''||LAST
Amit Banda Lisa Ozer Harrison Bloom Tayler Fox William Smith Elizabeth Bates Sundita Kumar Ellen Abel Alyssa Hutton Jonathon Taylor Jack Livingston
FIRST_NAME||''||LAST
Charles Johnson
100 rows selected. 63.哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门（关联子查询）。 SQL> select first_name || ' '||last_name from employees emp1 where not exists (select 1 from employees emp2 where FIRST_NAME='Den' and LAST_NAME='Raphaely'and emp1.DEPARTMENT_ID =emp2.DEPARTMENT_ID );
FIRST_NAME||''||LAST
Matthew Weiss Adam Fripp Payam Kaufling Shanta Vollman Kevin Mourgos Julia Nayer Irene Mikkilineni James Landry Steven Markle Laura Bissot Mozhe Atkinson
FIRST_NAME||''||LAST
James Marlow TJ Olson Jason Mallin Michael Rogers Ki Gee Hazel Philtanker Renske Ladwig Stephen Stiles John Seo Joshua Patel Trenna Rajs
FIRST_NAME||''||LAST
Curtis Davies Randall Matos Peter Vargas Winston Taylor Jean Fleaur Martha Sullivan Girard Geoni Nandita Sarchand Alexis Bull Julia Dellinger Anthony Cabrio
FIRST_NAME||''||LAST
Kelly Chung Jennifer Dilly Timothy Gates Randall Perkins Sarah Bell Britney Everett Samuel McCain Vance Jones Alana Walsh Kevin Feeney Donald OConnell
FIRST_NAME||''||LAST
Douglas Grant Susan Mavris Shelley Higgins William Gietz Steven King Neena Kochhar Lex De Haan Hermann Baer Jennifer Whalen Kimberely Grant Michael Hartstein
FIRST_NAME||''||LAST
Pat Fay Alexander Hunold Bruce Ernst David Austin Valli Pataballa Diana Lorentz Nancy Greenberg Daniel Faviet John Chen Ismael Sciarra Jose Manuel Urman
FIRST_NAME||''||LAST
Luis Popp John Russell Karen Partners Alberto Errazuriz Gerald Cambrault Eleni Zlotkey Peter Tucker David Bernstein Peter Hall Christopher Olsen Nanette Cambrault
FIRST_NAME||''||LAST
Oliver Tuvault Janette King Patrick Sully Allan McEwen Lindsey Smith Louise Doran Sarath Sewall Clara Vishney Danielle Greene Mattea Marvins David Lee
FIRST_NAME||''||LAST
Sundar Ande Amit Banda Lisa Ozer Harrison Bloom Tayler Fox William Smith Elizabeth Bates Sundita Kumar Ellen Abel Alyssa Hutton Jonathon Taylor
FIRST_NAME||''||LAST
Jack Livingston Charles Johnson
101 rows selected. 发现上面一题错误 SQL> select first_name || ' '||last_name from employees where nvl(DEPARTMENT_ID,1) not in (select DEPARTMENT_ID from employees where FIRST_NAME='Den' and LAST_NAME='Raphaely');
FIRST_NAME||''||LAST
Matthew Weiss Adam Fripp Payam Kaufling Shanta Vollman Kevin Mourgos Julia Nayer Irene Mikkilineni James Landry Steven Markle Laura Bissot Mozhe Atkinson
FIRST_NAME||''||LAST
James Marlow TJ Olson Jason Mallin Michael Rogers Ki Gee Hazel Philtanker Renske Ladwig Stephen Stiles John Seo Joshua Patel Trenna Rajs
FIRST_NAME||''||LAST
Curtis Davies Randall Matos Peter Vargas Winston Taylor Jean Fleaur Martha Sullivan Girard Geoni Nandita Sarchand Alexis Bull Julia Dellinger Anthony Cabrio
FIRST_NAME||''||LAST
Kelly Chung Jennifer Dilly Timothy Gates Randall Perkins Sarah Bell Britney Everett Samuel McCain Vance Jones Alana Walsh Kevin Feeney Donald OConnell
FIRST_NAME||''||LAST
Douglas Grant Susan Mavris Kimberely Grant Shelley Higgins William Gietz Steven King Neena Kochhar Lex De Haan Hermann Baer Jennifer Whalen Michael Hartstein
FIRST_NAME||''||LAST
Pat Fay Alexander Hunold Bruce Ernst David Austin Valli Pataballa Diana Lorentz Nancy Greenberg Daniel Faviet John Chen Ismael Sciarra Jose Manuel Urman
FIRST_NAME||''||LAST
Luis Popp John Russell Karen Partners Alberto Errazuriz Gerald Cambrault Eleni Zlotkey Peter Tucker David Bernstein Peter Hall Christopher Olsen Nanette Cambrault
FIRST_NAME||''||LAST
Oliver Tuvault Janette King Patrick Sully Allan McEwen Lindsey Smith Louise Doran Sarath Sewall Clara Vishney Danielle Greene Mattea Marvins David Lee
FIRST_NAME||''||LAST
Sundar Ande Amit Banda Lisa Ozer Harrison Bloom Tayler Fox William Smith Elizabeth Bates Sundita Kumar Ellen Abel Alyssa Hutton Jonathon Taylor
FIRST_NAME||''||LAST
Jack Livingston Charles Johnson
101 rows selected. not in <>忽略null 64.Finance部门有哪些职位（非关联子查询）。 SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES where DEPARTMENT_ID = (select DEPARTMENT_ID from DEPARTMENTS where DEPARTMENT_NAME = 'Finance');
JOB_ID
FI_ACCOUNT FI_MGR
SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES where DEPARTMENT_ID in (select DEPARTMENT_ID from DEPARTMENTS where DEPARTMENT_NAME = 'Finance');
JOB_ID
FI_ACCOUNT FI_MGR 65.Finance部门有哪些职位（关联子查询）。 SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES a where exists (select DEPARTMENT_ID from DEPARTMENTS b where a.DEPARTMENT_ID=b.DEPARTMENT_ID and DEPARTMENT_NAME = 'Finance');
JOB_ID
FI_ACCOUNT FI_MGR
