Oracle 查询基础练习题

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_date2000;   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)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_date150 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 '高级销售'          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 rownum5000) 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';