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

oraclehr样例经典查询语句

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;*3>

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
     ￥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  
$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');