Oracle 查询基础练习题 -hr

hr用户下: 
1.显示员工编号  first_name 工资
 select employee_id,first_name,salary from employees; 

2.显示员工的名字(first_name),email,电话 
 select first_name,email,phone_number from employees;
 
3.显示员工的编号,名字(first_name),入职日期,职位 
 select employee_id,first_name,hire_date,job_id from employees;
 
4.显示部门编号,经理编号
 select department_id,manager_id from employees;
 
5.显示位置id,地址
 select location_id,street_address from locations;
 
6.查询员工编号不在150到200之间的人的员工编号,名字,工资
 select employee_id,first_name,salary
 from employees
 where employee_id between 150 and 200;

7.查询电话号码中包含123的人的员工编号,名字,电话号码
 select employee_id,first_name,phone_number
 from employees
 where phone_number like '%123%';
 
8.查询email中包含字母A的人的名字,email,部门编号
 select first_name,email,department_id
 from employees
 where email like '%A%';

9.查询部门编号是10,20,50的人的员工编号,名字,部门编号
 select employee_id,first_name,department_id
 from employees
 where department_id in (10,20,50);

10.查询职位是SH开头的人的员工编号,名字,职位,工资
 select employee_id,first_name,job_id,salary
 from employees
 where job_id like 'SH%';

11.查询email是PF开头,并且是4个字符的人的名字,email,职位
 select first_name,email,job_id
 from employees
 where email like 'PF__';
 
12.查询经理是101,149的员工的名字,经理,部门编号
 select first_name,manager_id,department_id
 from employees
 where manager_id in (101,149);
 
13.查询工资不在10000到20000之间的名字,工资
 select first_name,salary 
 from employees
 where salary not between 10000 and 20000;
 
14.查询名字以E开头的人的员工编号,名字,工资
 select employee_id,first_name,salary
 from employees
 where first_name like 'E%';
 
15.查询2006-2-23之前入职的员工的编号,名字,工资,部门编号
 select employee_id,first_name,salary,department_id
 from employees
 where hire_date<to_date(20060223,'YYYY-MM-DD');
 
16.查询职位中包括ACC的员工的名字,工资,职位
 select first_name,salary,job_id
 from employees
 where job_id like '%ACC%';

17.查询位置在美国(US)的位置编号,地址,邮政编码   
 select location_id,street_address,postal_code
 from locations
 where country_id='US';

18.查询意大利(IT)的国家编号,国家名称,所属区域编号
 select * from countries where country_id='IT';
 
19.查询邮政编号是50090的区域的所有的信息
 select * from locations where postal_code='50090';
 
20.查询区域编号大于等于2000的区域的编号,地址,邮政编码 
 select location_id,street_address,postal_code from locations where location_id>2000;
 
21.查询部门名称第二个字母是u的部门的编号,名称,经理编号   
 select department_id,department_name,manager_id
 from departments
 where department_name like '_u%';

22.查询从2006-03-24开始,到2007-12-31结束,并且职位是ST_CLERK的人的员工编号,开始时间,结束时间,职位
 select employee_id,start_date,end_date,job_id
 from job_history
 where start_date between to_date(20060324,'YYYY-MM-DD') and to_date(20071231,'YYYY-MM-DD') and job_id='ST_CLERK';

23.查询FI_MGR的职位全称,显示职位编号,职位名称
 select job_id,job_title
 from jobs
 where job_id='FI_MGR';

34.查询职位是IT_PROG,并且在2006-1-1之后入职的员工的名字,职位,入职日期
 select first_name,job_id,hire_date
 from employees
 where job_id='IT_PROG';

25.查询名字中有a,或者职位是PU_CLERK的人的名字,职位,工资
 select first_name,job_id,salary
 from employees
 where first_name like '%a%' or job_id='PU_CLERK';

26.查询提成小于0.15的人(包括没提成的)的名字,提成,工资
 select * from employees;
 select first_name,commission_pct,salary
 from employees
 where nvl(commission_pct,0)<0.15;
 
27.查询2007-02-07之前入职并且职位为IT_PROG,并且工资大于5000的员工的名字,工资,职位,入职日期
 select first_name,salary,job_id,hire_date
 from employees
 where hire_date<to_date(20070207,'YYYY-MM-DD') and job_id='IT_PROG' and salary>5000;

28.查询90部门工资大于10000的,或者100部门职位是FI_ACCOUNT的员工的名字,工资,职位,部门s 
 select first_name,salary,job_id,department_id
 from employees
 where (department_id=90 and salary>1000) or (department_id=100 and job_id='FI_ACCOUNT');

29.查询电话以515开头,以1结尾的,或者职位以S开头,以N结尾的员工的名字,职位,电话 
 select first_name,job_id,phone_number
 from employees
 where phone_number like '515%1' or job_id like 'S%N';

30.查询部门名称中包含a,并且以g结尾的部门编号,名称,位置ID   
 select * from employees;
 select department_id,department_name,location_id
 from departments
 where department_name like '%a%g';

31.有哪些经理职位,并且工资能到8000,显示职位名称和工资范围
 select job_title,min_salary,max_salary
 from jobs
 where job_id like '%MAN' and min_salary>=8000;

32.查询经理是114,工资大于2700,电话以3结尾的员工名字,工资,经理编号
 select first_name,salary,manager_id
 from employees
 where manager_id=114 and salary>2700 and phone_number like '%3'; 
 
33.显示区域为1的国家简称和国家名字 countries 表
 select country_id,country_name from countries where region_id=1;

34.显示工资能到15000的所有的职位编号和职位名称
 select job_id,job_title
 from jobs
 where min_salary>=15000;

35.显示email以S结尾并且电话以515开头,或者职位是IT_PROG并且工资大于5000的人的名字,工资,email,职位,电话
 select first_name,salary,email,job_id,phone_number
 from employees
 where (email like '%S'and phone_number like '515%') or (job_id='IT_PROG' and salary>5000);
 
36.显示职位以S开头,P结尾,并且长度是6的,2008-02-23之前入职的员工姓名,职位,入职日期
 select first_name,job_id,hire_date
 from employees
 where job_id like 'S%P' and length(job_id)=6 and hire_date<to_date(20080223,'YYYY-MM-DD');
 
37.求全名(first_name和last_name连在一起)的长度. 显示员工编号,全名,长度
 select employee_id,first_name||last_name name,length(first_name||last_name) length      -- 连接符 ||    length()
 from employees; 

38.将first_name转成大写,last_name转成小写,显示first_name原名,大写名,last_name原名,小写名
 select first_name,upper(first_name),last_name,lower(last_name) from employees;     --lower( )  upper( )
  
39.将JOB_ID中的A替换成D,显示员工编号,替换前后的职位 
 select employee_id,job_id,replace(job_id,'A','D') from employees;         --replace( , , )

40.找出全名(first_name和last_name连在一起)第一个a的位置,和第二个a的位置,  显示全名,第一个a的位置,第二个a的位置
 select first_name||last_name,instr(first_name||last_name,'a',1,1),instr(first_name||last_name,'a',1,2) from employees;  --instr( , , , )
  
41.截取first_name第4个字符到第6个字符,显示first_name,截取后的字符
 select first_name,substr(first_name,4,6) from employees;     --substr( , , )

42.从first_name倒数第3个位置开始截取,到末尾,显示first_name,截取后的字符
 select first_name,substr(first_name,-3) from employees;         --substr( , , )

43.从first_name倒数第5个位置开始截取,截取3个长度,显示first_name,截取后的字符.
 select first_name,substr(first_name,-5,3) from employees;  --substr( , , )

44.去掉phone_number中的.  显示名字,电话,变更后的电话
 select first_name,phone_number,replace(phone_number,'.') from employees;    --replace( , , )

45.截取job_id中下划线之后的字符串,显示名字,职位,截取的字符串 
 select first_name,job_id,substr(job_id,instr(job_id,'_')+1) from employees;   --instr( , )   substr( , , , )

46.将job_id中的下划线变成/  显示名字,职位,变更后的职位
 select first_name,job_id,replace(job_id,'_','/') from employees;    --replace( , , )

47.查询first_name中有jo的,不区分大小写(大写也查出来,小写也查出来),显示员工编号,first_name,工资
 select employee_id,first_name,salary
 from employees
 where upper(first_name) like '%JO%';         --UPPER()

48.查员工工资在9000到15000之间的.显示员工编号,first_name,工资
 select employee_id,first_name,salary
 from employees
 where salary between 9000 and 15000;
 
49.查员工编号大于150,并且职位是SH_CLERK的人,显示员工编号,名字,职位
 select employee_id,first_name,job_id
 from employees
 where employee_id>150 and job_id='SH_CLERK';

50.查名字里面有'ar'的人(名字包括first_name和last_name)
 select *
 from employees
 where first_name||last_name like '%ar%';

51.查电话号码有几部分,(比如: 12.34.56.32 由4部分组成, 求结果 4)
 select phone_number,instr(translate(phone_number,'.1234567890','.'),'.',-1)+1 from employees;

52.求电话号码,去除.以后的长度
 select length(replace(phone_number,'.','')) from employees;

53.求电话号码的最后一部分 (比如: 12.34.56.78  ,求结果 78)
 select phone_number,substr(phone_number,instr(phone_number,'.',-1)+1)
 from employees;

54.求员工编号为奇数的员工信息.
 select * from employees where mod(employee_id,2)=1;

55.求部门编号和经理114编号一样的员工.
 select * from employees where department_id in (select department_id from employees where manager_id=114); 

56.把入职日期看成是员工生日,求下周日过生日的员工.
 select * from employees where hire_date between next_day(sysdate,'星期日') and next_day(sysdate,'星期日')+6 

57.把入职日期看成是员工生日,求未来一个月内(30天)过生日的员工.(生日提前提醒功能)
  select * from employees where to_char(hire_date,'MM')=to_char(sysdate,'MM')+1;

58.把入职日期看成是员工生日,本月过生日的员工,工资增加500.
   update employees set salary_500 where to_char(hire_date,'MM')=to_char(sysdate,'MM');

59.显示入职11年以上的员工的员工编号,first_name,入职日期
   select employee_id,first_name,hire_date
   from employees
   where months_between(sysdate,hire_date)/12>11;

60.将街道地址(street_address)列,拆分成字母和数字两列 ( 比如, 1234 Via  拆分成 1234 和  Via 两列)
   select street_address,substr(street_address,1,instr(street_address,' ')),substr(street_address,instr(street_address,' ')+1)
   from locations;

61.查询所有周一入职的人，显示名字，入职日期，工资，部门
 select first_name,hire_date,salary,department_id           --to_char(日期,'day')
 from employees
 where to_char(hire_date,'day')='星期一';

62.将入职日期，显示为年月日形式（比如：2017-3-5显示为2017年3月5日）
 select to_char(hire_date,'YYYY')||'年'||to_char(hire_date,'MM')||'月'||to_char(hire_date,'DD')||'日' from employees;

63.用sysdate求上个月10号
 select trunc(add_months(sysdate,-1),'MM')+9 from dual;

64.求今年总共有多少天（是365天，还是366天）
 select to_date(20200101,'YYYY-MM-DD')-to_date(20190101,'YYYY-MM-DD')
 from dual;

65.求每个月倒数第5天入职的员工（比如：2017-7-27就是这个月的倒数第5天）
 select * from employees where hire_date= last_day(hire_date)-4

66.求每个月下旬入职的员工
 select * from employees where hire_date between trunc(hire_date,'MM')+19 and last_day(hire_date) ;
 
67.进公司工作10年以上的员工，工资增加1000
 update employees set salary=salary+1000 where months_between(sysdate,hire_date);

68.30天后是周几
 select to_char(sysdate+30,'day') from dual;

69.3个月前到今天，总共多少天（比如：今天是2017-7-21，从2017-4-21到2017-7-21总共多少天）
   select sysdate-add_months(sysdate,-3) from dual;
  
70. employees表中,如果manager_id在100到120,是显示为'一级经理'在121到150,显示为'二级经理',其它显示为'其它'  
 select case when manager_id between 100 and 120 then '一级经理'
              when  manager_id between 121 and 150 then '二级经理'
              else '其他'
         end case
 from employees;

71.电话号码是515开头的,显示为'区域1', 590开头的,显示为'区域2',650开头的,显示为'区域3'其它的,显示为'其它区域' 
 select decode(substr(phone_number,1,3),515,'区域1',590,'区域2',650,'区域3','其他区域') from employees;     

72.员工1-3月入职的,显示为'第一季度', 4-6月入职的,显示为'第二季度',6-9月入职的,显示为'第四季度',10-12月入职的,显示为'第四季度'
 select case when to_char(hire_date,'MM') between 1 and 3 then '第一季度'
               when to_char(hire_date,'MM') between 4 and 6 then '第二季度'
               when to_char(hire_date,'MM') between 7 and 9 then '第三季度'
               when to_char(hire_date,'MM') between 10 and 12 then '第四季度'
          end case
 from employees;

73.提成是0-0.1的,是显示为'初级销售', 提成大于0.1到0.25的,显示为'中级销售'0.25以上的,显示为'高级销售'
 select case when nvl(commission_pct,0) between 0 and 0.1 then '初级销售'
               when nvl(commission_pct,0)>0.1 and nvl(commission_pct,0)<=0.25 then '中级销售'
               when nvl(commission_pct,0)>0.25 then '高级销售'
         end case   
  from employees;

74.入职日期,是1-10日的,显示为'上旬', 11-20日的,显示为'中旬',大于20的,显示为'下旬'
 select case when to_char(hire_date,'MM') between 1 and 10 then '上旬'
              when to_char(hire_date,'MM') between 11 and 20 then '中旬'
              else '下旬'
          end case
 from employees;

75.求每个部门，工资最高的人，按最高工资从高到低排序
 select department_id,first_name,salary
 from employees
 where (department_id,salary) in (select department_id,max(salary)
                                 from employees
                                 group by department_id)
 order by  salary desc;

76.求100部门，工资最高的人  
 select first_name
 from employees
 where salary=(select max(salary)
                 from employees
                 group by department_id
                 having department_id=100);
 
77.求平均工资最高的2个部门
 select a.dno,a.sal
 from (select department_id dno,avg(salary) sal
       from employees 
       group by department_id
       order by sal desc ) a
 where rownum<3;
 
78.求每个经理的下属中，工资最低的人
 select manager_id,first_name,salary
 from employees
 where (manager_id,salary) in ( select manager_id,min(salary)
                                 from employees
                                 group by manager_id）
 
79.求2003年到2005年之间入职的人平均工资 
 select avg(a.salary)
 from (select salary from employees where to_char(hire_date,'YYYY') between 2003 and 2005 ) a

80.求各种职位的平均工资，最高工资，最低工资
 select job_id,avg(salary),max(salary),min(salary)
 from employees
 group by job_id;

81.求每个部门工资高于5000的人平均工资
  select a.department_id dno,avg(a.salary) sal
  from (select department_id,salary
  from employees
  where salary>5000) a
  group by a.department_id
          
82.求每个人的月薪（工资+提成），按月新从高到低排序，月薪相同的，按入职日期排序
 select first_name,salary+nvl(commission_pct,0) monsal
 from employees
 order by monsal desc,hire_date;

83.求部门人数大于5的部门，按部门人数从少到多排序
 select department_id,count(department_id)
 from employees
 group by department_id
 having count(department_id)>5
 order by count(department_id) asc;
 
84.统计每个部门，工资大于8000的人数，人数从少到多排序
 select department_id,count(department_id)
 from employees
 where salary>8000
 group by department_id
 order by count(department_id) asc;

85.统计每个部门有提成人数，按部门排序
 select department_id,count(department_id)
 from employees
 where nvl(commission_pct,0)>0
 group by department_id
 order by department_id

86.统计每年入职的人数，按入职人数从高到低排序
 select year,count(id)
 from (select to_char(hire_date,'YYYY') year,employee_id id from employees )
 group by year
 order by count(id)

87.统计每个经理下每年入职的人数，按经理编号从低到高排序
 select mana,year,count(id)
 from (
 select manager_id mana,to_char(hire_date,'YYYY') year,employee_id id
 from employees) 
 group by mana,year

88.统计每个部门，每种职位的平均工资，最高工资，最低工资，按部门和职位排序
 select department_id,job_id,avg(salary),max(salary),min(salary)
 from employees
 group by department_id,job_id
 order by department_id,job_id;

89.统计每个国家区域（country）的城市(city)数量，按数量排序
 select country_id,count(city)
 from locations
 group by country_id;
 
90.统计每人区域（location）的部门数量，按部门数量排序
 select location_id,count(location_id)
 from departments
 group by location_id
 order by count(location_id);

91.统计每年每个月入职的人数
 select year,mon,count(*)
 from (select employee_id id,to_char(hire_date,'YYYY') year,to_char(hire_date,'MM') mon from employees)
 group by year,mon

92.哪些部门的人比90部门人数多
 with dep as (select department_id deptno,count(*) cou from employees group by department_id)
 select deptno
 from dep where cou>(select cou from dep where deptno=90);

93.Den(first_name)、Ernst(LAST_NAME)的领导分别是谁
 select a.first_name,b.first_name 
 from employees a inner join employees b on a.manager_id=b.employee_id
 where a.first_name='Den' or a.last_name='Ernst';
 
94.Den(first_name)、Ernst(LAST_NAME)的下属分别有谁
 select b.first_name,a.first_name
 from employees a inner join employees b on a.manager_id=b.employee_id
 where b.first_name='Den' or b.last_name='Ernst';
 
95.列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字  工资 入职日期
 select a.first_name,a.salary,a.hire_date
 from employees a inner join employees b on a.department_id=b.department_id
 where a.hire_date>b.hire_date and a.salary>b.salary;

96.Finance 部门有哪些职位
 select a.job_id
 from employees a inner join departments b on a.department_id=b.department_id
 where b.department_name='Finance';