oraclehr样例数据库经典查询语句

最新推荐文章于 2022-05-27 13:30:30 发布

qq_1849018727

最新推荐文章于 2022-05-27 13:30:30 发布

阅读量929

点赞数

1.查询工资大于12000的员工姓名和工资

Select initcap(concat(last_name,first_name)) "姓名",salary from employees where salary>12000;

2.查询员工号为176的员工的姓名和部门号 select initcap(concat(last_name,first_name)) "姓名",department_id from employees where employee_id = 176;

3.选择工资不在5000到12000的员工的姓名和工资 select initcap(concat(last_name,first_name)) "姓名", salary from employees where salary<5000 or salary>12000;

4.选择雇用时间在1908-02-01到1908-05-01之间的员工姓名，job_id和雇用时间

写法一： select initcap(concat(last_name,first_name)) "姓名",job_id,hire_date from employees where hire_date between '01-2月 -08' and '01-5月 -08';

写法二： select initcap(concat(last_name,first_name)) "姓名",job_id,hire_date from employees where hire_date between to_date('1908-02-01','YYYY-MM-DD') and to_date('1908-05-01','YYYY-MM-DD');

5.选择在20或50号部门工作的员工姓名和部门号
写法一： Select initcap(concat(last_name,first_name)) "姓名",department_id from employees where department_id=20 or department_id=50;

写法二: select initcap(concat(last_name,first_name)) "姓名",department_id from employees where department_id in (20,50);

6.选择在1908年雇用的员工的姓名和雇用时间
写法一： select initcap(concat(last_name,first_name))"姓名",hire_date from employees where hire_date like '%08';

写法二: select initcap(concat(last_name,first_name))"姓名",hire_date from employees where hire_date between to_date('1908-1-1','YYYY-MM-DD') and to_date('1908-12-31','YYYY-MM-DD');(这个可能会因为将字符串转换为日期的时候与系统日期不符和，所以没有显示出来查询到的项目）

7.选择公司中没有管理者的员工姓名及job_id
方法一: Select initcap(concat(last_name,first_name)) "姓名",job_id from employees where manager_id is null; 方法二: select initcap(concat(last_name,first_name)) "姓名",job_id from employees where nvl(manager_id,0)=0;

8.选择公司中有奖金的员工姓名，工资和奖金级别
方法一: Select initcap(concat(last_name,first_name)) "姓名",salary,commission_pct from employees where commission_pct is not null;

方法二: Select initcap(concat(last_name,first_name)) "姓名",salary,commission_pct from employees where nvl2(commission_pct, commission_pct,0)>0;

方法三: select initcap(concat(last_name,first_name)) "姓名", commission_pct from employees where nvl（commission_pct,0)<>0;

9.选择员工姓名的第三个字母是a的员工姓名
select initcap(concat(last_name,first_name)) "姓名" from employees where initcap(concat(last_name,first_name)) like '__a%';

10.选择姓名中有字母a和e的员工姓名 select initcap(concat(last_name,first_name)) "姓名" from employees where initcap(concat(last_name,first_name)) like '%a%' and initcap(concat(last_name,first_name)) like '%e%';

11.显示系统时间
方法一:
Select sysdate from dual;

方法二:
Select current_timestamp from dual;

12.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary） Select employee_id,initcap(concat(last_name,first_name)) "姓名",salary*1.2 as"new salary" from employees;

13.将员工的姓名按首字母排序，并写出姓名的长度（length） Select initcap(concat(last_name,first_name)) "姓名",length(initcap(concat(last_name,first_name))) as"名字长度" from employees order by substr(initcap(concat(last_name,first_name)),1,1);

14.查询各员工的姓名，并显示出各员工在公司工作的月份数 Select initcap(concat(last_name,first_name)) "姓名",trunc(months_between(sysdate,hire_date),0) "在职时间" from employees;

15.查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列
方法一:
Select initcap(concat(last_name,first_name)) "姓名",trunc(months_between(sysdate,hire_date),0) "在职时间" from employees order by trunc(months_between(sysdate,hire_date),0) desc; 方法二: select initcap(concat(last_name,first_name))"姓名",trunc(months_between(sysdate,hire_date),0)as worked_month from employees order by worked_month desc; 16.做一个查询，产生下面的结果
earns monthly but wants
Dream Salary
King earns $24000 monthly but wants $72000
语法为： Select last_name||' earns '||to_char(salary,'$99999')||' monthly but wants '||to_char(salary*3,'$99999') as "Dream Salary" from employees;

17.使用decode函数，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
Others F
产生下面的结果：
Last_name Job_id Grade
king AD_PRES A
语法：
写法一： select last_name,job_id,decode(job_id,'AD_PRES','A','ST_MAN','B','IT_PROG','C','SA_REP ','D','ST_CLERK','E','F')Grade from employees; 写法二: Select last_name,job_id, case job_id when 'AD_PRES' then 'A' when 'ST_MAN' then 'B' when 'IT_PROG' then 'C' when 'SA_REP' then 'D' when 'ST_CLERK' then 'E' else 'F' end "Grage" from employees;

18.查询公司员工工资的最大值，最小值，平均值，总和 select max(salary) "最大值",min(salary) "最小值",avg(salary) "平均值",sum(salary) "总和" from employees;

19.查询各job_id的员工工资的最大值，最小值，平均值，总和 select job_id,max(salary) "最大值",min(salary) "最小值",avg(salary) "平均值",sum(salary) "总和" from employees group by job_id;

20.选择具有各个job_id的员工人数 Select job_id,count(*) from employees group by job_id;

21.查询员工最高工资和最低工资的差距（DIFFERENCE） Select max(salary)-min(salary) as "DIFFERENCE" from employees;

22.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内Select manager_id,min(salary) from employees group by manager_id having min(salary)>=6000 and manager_id is not null;

23.查询所有部门的名字，location_id，员工数量和工资平均值 Select department_name,location_id,count(e.job_id) as "部门总人数",avg(e.salary) as "平均工资" from departments d,employees e where d.department_id=e.department_id group by department_name,location_id;

1.列出至少有一个员工的所有部门 select department_name from departments where department_id in(select department_id from employees); 或者 select department_name from departments where department_id in(select department_id from employees group by department_id having count(department_id) >=1);

2.列出薪金比“Hall”多的所有员工
select * from employees where salary > (select salary from employees where last_name = 'Hall');(需要注意的是查询语句不区分大小写，但是里面的字段严格区分大小写）

3.列出所有员工的姓名及其直接上级的姓名 select a.first_name|| ' '||a.last_name "姓名",(select a.first_name|| ' '||a.last_name from employees b where b.employee_id=a.manager_id) as "老板姓名" from employees a;

4.列出受雇日期早于其直接上级的所有员工 select a.first_name|| ' '||a.last_name "姓名" from employees a where a.hire_date<(select hire_date from employees b where b.employee_id=a.manager_id);

5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select a.department_name,b.employee_id,b.first_name|| ' '||b.last_name "姓名",b.job_id,b.manager_id,b.hire_date,b.salary,b.department_id from departments a left join employees b on a.department_id=b.department_id;

6.列出所有“SH_CLERK”（办事员）的姓名及其部门名称 select a.first_name|| ' '||a.last_name "姓名",b.department_name from employees a join departments b on a.department_id=b.department_id and a.job_id='SH_CLERK';

7.列出最低薪金大于1500的各种工作 select distinct job_id as HighSalJob from employees group by job_id having min(salary)>1500;

8.列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号 select first_name|| ' '||last_name "姓名" from employees where department_id=(select department_id from departments where department_name='Sales');

9.列出薪金高于公司平均薪金的所有员工 select first_name|| ' '||last_name "姓名" from employees where salary>(select avg(salary) from employees);

10.列出与“first_name='Jean'”从事相同工作的所有员工 select first_name|| ' '||last_name "姓名" from employees where job_id=(select job_id from employees where first_name='Jean');

11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金 select a.first_name|| ' '||a.last_name "姓名",a.salary from employees a where a.salary in (select b.salary from employees b where b.department_id=30) and a.department_id<>30;

12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金 select a.first_name|| ' '||a.last_name "姓名",a.salary from employees a where a.salary > (select max(b.salary)from employees b where b.department_id=30);

13.列出在每个部门工作的员工数量、平均工资和平均服务期限 select (select b.department_name from departments b where a.department_id=b.department_id) as "部门名称" ,count(department_id) as "员工数量",avg(salary) as "部门平均工资" from employees a group by department_id;

14.列出所有员工的姓名、部门名称和工资 select a.first_name|| ' '||a.last_name "姓名",(select b.department_name from departments b where b.department_id=a.department_id) as "部门名称",salary from employees a;

15.列出所有部门的详细信息和部门人数 select a.department_id,a.department_name,a.location_id,(select count(department_id) from employees b where b.department_id=a.department_id group by b.department_id) as "部门人数" from departments a;

16.列出各种工作的最低工资 select job_id,min(salary) from employees group by job_id;

17.列出job_id="ST_MAN"的最低薪金 select department_id,min(salary) from employees where job_id='ST_MAN' group by department_id;

18.列出所有员工的年工资,按年薪从低到高排序 select first_name|| ' '||last_name "姓名",(salary+nvl(commission_pct,0))*12 as "年薪" from employees order by "年薪" asc; 1.找出EMP表中的姓名（ENAME）第三个字母是a 的员工姓名 SELECT concat(first_name,last_name) "员工姓名" FROM employees WHERE concat(first_name,last_name) like '__a%';

2.找出employees表员工名字中含有a和n的员工姓名 SELECT concat(first_name,last_name) "员工姓名" FROM employees WHERE concat(first_name,last_name) like '%a%' and concat(first_name,last_name) like '%n%'; 或者 SELECT concat(first_name,last_name) "员工姓名" FROM employees WHERE concat(first_name,last_name) like '%a%n%' ;

3.找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。 SELECT concat(first_name,last_name) "员工姓名",salary + commission_pct AS 工资,commission_pct FROM employees ORDER BY 工资 asc,commission_pct DESC;
(这个应该有错误，因为不可能同一个数据使用两种排序方法）

4.列出部门编号为20的所有职位 SELECT DISTINCT job_id FROM employees WHERE department_id = 20;

5.列出不属于Sales 的部门 SELECT DISTINCT * FROM departments WHERE department_name <> 'Sales'; 或者 SELECT DISTINCT * FROM departments WHERE department_name != 'Sales'; 或者 SELECT DISTINCT * FROM departments WHERE department_name not in('Sales'); 或者 SELECT DISTINCT * FROM departments WHERE department_name not like 'Sales';

6.显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序 SELECT concat(first_name,last_name) "员工姓名",salary + salary*commission_pct AS Wage FROM employees WHERE salary + salary*commission_pct NOT BETWEEN 1000 AND 1500 ORDER BY Wage DESC; 或者 SELECT concat(first_name,last_name) "员工姓名",salary + salary*commission_pct AS Wage FROM employees WHERE salary + salary*commission_pct<1000 or salary + salary*commission_pct>1500 ORDER BY Wage DESC;

7.显示职位为SA_MAN 和SA_REP，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪SELECT concat(first_name,last_name) "员工姓名",job_id 职位,(salary + salary*commission_pct) * 12 AS 年薪 FROM employees WHERE (salary + salary*commission_pct) * 12 BETWEEN 150000 AND 400000 AND job_id IN('SA_MAN','SA_REP');

8.说明以下两条SQL语句的输出结果：
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL; 和 SELECT employee_id,commission_pct FROM employees WHERE commission_pct=NULL; 简要说明有何不同？

9.语句SELECT concat(first_name,last_name) "员工姓名",salary FROM employees WHERE salary > '1500'是否报错？

1.改变NLS_LANG 的值，让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。 SELECT TO_CHAR(SALARY,'L99,999.99') FROM EMPLOYEES WHERE ROWNUM < 5; 结果如下：

TO_CHAR(SALARY,'L99,999.99')

￥24,000.00

￥20,000.00

￥9,000.00

SELECT TO_CHAR(SALARY,'$99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5; 显示结果如下所示：
TO_CHAR(SALARY,'$99,999.99')

$24,000.00

$20,000.00

$9,000.00

（说明：对于'$99,999.99'格式符： L：表示强制显示当地货币符号
$: 表示显示美元符号
9: 表示一个数字
0: 表示强制0显示
.: 表示一个小数点
,: 表示一个千位分隔符）

2.列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。SELECT FIRST_NAME|| ' '||last_name "姓名",SALARY,ROUND(SALARY * 1.08) FROM EMPLOYEES WHERE ROWNUM <=5;

3.找出谁是最高领导，将名字按大写形式显示 SELECT UPPER(FIRST_NAME || ' ' || LAST_NAME) AS NAME FROM HR.EMPLOYEES WHERE MANAGER_ID IS NULL;

4.找出David 的直接领导的名字 SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME FROM EMPLOYEES WHERE EMPLOYEE_ID IN(SELECT MANAGER_ID FROM EMPLOYEES WHERE FIRST_NAME = 'David' AND LAST_NAME = 'Austin'); 或者 SELECT UPPER( EMP1.FIRST_NAME ||' ' || EMP1.LAST_NAME) AS NAME FROM EMPLOYEES EMP1,EMPLOYEES EMP2 WHERE EMP1.EMPLOYEE_ID = EMP2.MANAGER_ID AND EMP2.FIRST_NAME = 'David' AND EMP2.LAST_NAME = 'Austin';

5.First_Name 为Alexander，LAST_NAME为Hunold领导谁。（谁向David 报告）。 SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME FROM EMPLOYEES WHERE MANAGER_ID IN(SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE FIRST_NAME = 'Alexander' AND LAST_NAME = 'Hunold'); 或者 SELECT UPPER( EMP1.FIRST_NAME || ' ' || EMP1.LAST_NAME) AS NAME FROM EMPLOYEES EMP1,EMPLOYEES EMP2 WHERE EMP1.MANAGER_ID = EMP2.EMPLOYEE_ID AND EMP2.FIRST_NAME = 'Alexander' AND EMP2.LAST_NAME = 'Hunold';

6.哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。 SELECT E.FIRST_NAME,E.SALARY,M.FIRST_NAME,M.SALARY FROM EMPLOYEES E,EMPLOYEES M WHERE E.MANAGER_ID = M.EMPLOYEE_ID AND E.SALARY > M.SALARY;

要是只列出员工的名字与工资的话，还可以这样： SELECT E.FIRST_NAME,E.SALARY FROM EMPLOYEES E WHERE E.SALARY >(SELECT M.SALARY FROM EMPLOYEES M WHERE E.MANAGER_ID = M.EMPLOYEE_ID);

7.哪些员工和Chen(LAST_NAME)同部门 SELECT FIRST_NAME||' '||last_name "员工姓名" FROM EMPLOYEES WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'Chen') AND LAST_NAME <> 'Chen'; 或者 SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID AND E2.LAST_NAME = 'Chen' AND E1.LAST_NAME <> 'Chen';

8.哪些员工跟De Haan(LAST_NAME)做一样职位 SELECT FIRST_NAME||' '||last_name "员工姓名" FROM EMPLOYEES WHERE JOB_ID IN(SELECT JOB_ID FROM EMPLOYEES WHERE LAST_NAME = 'De Haan') AND LAST_NAME <> 'De Haan';

或者 SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2 WHERE E1.JOB_ID = E2.JOB_ID AND E2.LAST_NAME = 'De Haan' AND E1.LAST_NAME <> 'De Haan';

9.哪些员工跟Hall(LAST_NAME)不在同一个部门 SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES WHERE DEPARTMENT_ID NOT IN(SELECT DEPARTMENT_ID FROM HR.EMPLOYEES WHERE LAST_NAME = 'Hall');

或者： SELECT e1.FIRST_NAME|| ' '||e1.last_name "姓名" FROM EMPLOYEES e1,EMPLOYEES e2 WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID(+) and e2.LAST_NAME(+) = 'Hall' and e2.LAST_NAME IS NULL;

10.哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位 SELECT FIRST_NAME || ' ' || LAST_NAME "员工姓名" FROM HR.EMPLOYEES WHERE JOB_ID <> (SELECT DISTINCT JOB_ID FROM EMPLOYEES WHERE FIRST_NAME = 'William' AND LAST_NAME = 'Smith');

11.显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称 SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME, E.COMMISSION_PCT,D.DEPARTMENT_NAME,L.CITY FROM HR.EMPLOYEES E,HR.DEPARTMENTS D,HR.LOCATIONS L WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID AND E.COMMISSION_PCT IS NOT NULL;

12.显示Executive部门有哪些职位 SELECT DISTINCT E.JOB_ID FROM HR.EMPLOYEES E,HR.DEPARTMENTS D WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID AND D.DEPARTMENT_NAME = 'Executive';

13.整个公司中，最高工资和最低工资相差多少 SELECT MAX(SALARY) - MIN(SALARY) FROM HR.EMPLOYEES;

14.提成大于0 的人数 SELECT COUNT(*) AS 提成大于0的人数 FROM HR.EMPLOYEES WHERE COMMISSION_PCT > 0;

或者： SELECT COUNT(COMMISSION_PCT) AS 提成大于0的人数 FROM HR.EMPLOYEES WHERE COMMISSION_PCT > 0;

15.显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位 SELECT MAX(NVL(SALARY,0)) AS 最高工资,MIN(NVL(SALARY,0)) AS 最低工资,SUM(NVL(SALARY,0)) AS 工资总和,ROUND(AVG(NVL(SALARY,0))) AS 平均工资 FROM HR.EMPLOYEES;

16.整个公司有多少个领导 SELECT COUNT(DISTINCT(MANAGER_ID)) FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL;

17.列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期。 SELECT DISTINCT E1.FIRST_NAME || ' ' || E1.LAST_NAME AS 姓名,E1.SALARY AS 工资,E1.HIRE_DATE AS 入职日期 FROM HR.EMPLOYEES E1,HR.EMPLOYEES E2 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID AND E1.HIRE_DATE > E2.HIRE_DATE AND E1.SALARY > E2.SALARY ORDER BY 工资 DESC;

1.各个部门平均、最大、最小工资、人数，按照部门号升序排列 SELECT DEPARTMENT_ID AS 部门号,AVG(SALARY) AS 平均工资,MAX(SALARY) AS 最高工资,MIN(SALARY) AS 最低工资,COUNT(*) AS 人数 FROM EMPLOYEES GROUP BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID ASC;

2.各个部门中工资大于5000的员工人数 SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES WHERE SALARY > 5000 GROUP BY DEPARTMENT_ID;

3.各个部门平均工资和人数，按照部门名字升序排列 SELECT DEPT.DEPARTMENT_NAME,AVG(EMP.SALARY),COUNT(*) FROM EMPLOYEES EMP,DEPARTMENTS DEPT WHERE EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID GROUP BY DEPT.DEPARTMENT_NAME ORDER BY DEPT.DEPARTMENT_NAME;

或者: SELECT DPTNAME,AVG(SALARY),COUNT(*) FROM(SELECT(SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME, EMP.SALARY FROM EMPLOYEES EMP) GROUP BY DPTNAME ORDER BY DPTNAME;

4.列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。 SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT FROM EMPLOYEES EMP1,EMPLOYEES EMP2 WHERE EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND EMP1.SALARY = EMP2.SALARY AND EMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY;

5.列出同部门中工资高于1000 的员工数量超过2 人的部门，显示部门名字、地区名称。 SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*) FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID AND E.SALARY > 1000 GROUP BY D.DEPARTMENT_NAME,L.CITY HAVING COUNT(*) > 2;

6.哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）。 SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY FROM EMPLOYEES WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES) ORDER BY SALARY DESC;

7.哪些员工的工资，介于50号和80号部门平均工资之间 SELECT FIRST_NAME || ' ' || LAST_NAME AS 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);

8.所在部门平均工资高于5000 的员工名字 SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING AVG(SALARY) > 5000);

9.列出各个部门中工资最高的员工的信息：名字、部门号、工资 SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY,DEPARTMENT_ID FROM EMPLOYEES WHERE (DEPARTMENT_ID,SALARY) IN(SELECT DEPARTMENT_ID,MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);

10.最高的部门平均工资是多少 SELECT MAX(AVGSALARY) FROM(SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY FROM EMPLOYEES GROUP BY DEPARTMENT_ID);

11.哪些部门的人数比90号部门的人数多 SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(*) > (SELECT COUNT(*) FROM EMPLOYEES WHERE DEPARTMENT_ID = 90);

12.Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁（非关联子查询） SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID =(SELECT MANAGER_ID FROM EMPLOYEES WHERE FIRST_NAME = 'Den' AND LAST_NAME = 'Raphaely');

13.Den(FIRST_NAME)、Raphaely(LAST_NAME)领导谁（非关联子查询） SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES WHERE MANAGER_ID IN(SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE FIRST_NAME = 'Den' AND LAST_NAME = 'Raphaely'); 或者: SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES WHERE MANAGER_ID =(SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE FIRST_NAME = 'Den' AND LAST_NAME = 'Raphaely');

14.列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期（关联子查询） SELECT FIRST_NAME || ' ' || LAST_NAME AS 姓名,SALARY AS 工资,HIRE_DATE AS 入职日期 FROM EMPLOYEES EMP1 WHERE EXISTS (SELECT 1 FROM EMPLOYEES EMP2 WHERE EMP2.DEPARTMENT_ID = EMP1.DEPARTMENT_ID AND EMP1.HIRE_DATE > EMP2.HIRE_DATE AND EMP1.SALARY > EMP2.SALARY);

15.哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME) 不在同一个部门（非关联子查询） SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID <>(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE FIRST_NAME = 'Den' AND LAST_NAME = 'Raphaely');

或者： 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');

16.哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门（关联子查询） SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES EMP1 WHERE NOT EXISTS (SELECT 1 FROM EMPLOYEES EMP2 WHERE EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND EMP2.FIRST_NAME ='Den' AND EMP2.LAST_NAME ='Raphaely');

17.Finance部门有哪些职位（非关联子查询） SELECT DISTINCT JOB_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = 'Finance');

或者: SELECT DISTINCT JOB_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = 'Finance');

18.Finance部门有哪些职位（关联子查询） SELECT DISTINCT JOB_ID FROM EMPLOYEES WHERE EXISTS(SELECT 1 FROM DEPARTMENTS WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID AND DEPARTMENTS.DEPARTMENT_NAME = 'Finance');