sql综合练习----Oracle中

1、显示表employees中所有数据；

select * from employees

2、显示表employees中员工编号、姓、名，并按工资升序排序；

 select EMPLOYEE_ID,FIRST_NAME,LAST_NAME from employees order by SALARY 

3、显示表employees中员工姓（first_name）、手机号、邮箱，限制条件：员工名（last_name）中含有D或G或H，且在上半月入职，并按员工编号降序排序：

方法一：
select FIRST_NAME,PHONE_NUMBER,EMAIL
 FROM employees 
 where REGEXP_LIKE (LAST_NAME,'D|G|H')
 and TO_CHAR(HIRE_DATE,'dd')>0
 and TO_CHAR(HIRE_DATE,'dd')<=15
 ORDER BY EMPLOYEE_ID
 
 方法二：
 select first_name,
       phone_number,
       email
  from employees
 where (
       last_name like '%D%'
    or last_name like '%G%'
    or last_name like '%H%'
    )
   and to_char(hire_date, 'dd') > 0
   and to_char(hire_date, 'dd') <= 15
 order by employee_id asc;

4、显示表employees中员工姓、名、薪水，限制条件：员工编号在124和176之间，且在1997年后入职；

方法一：
select FIRST_NAME,LAST_NAME,SALARY
from  employees
where
EMPLOYEE_ID >= 124
and
EMPLOYEE_ID <=176
and
to_char(HIRE_DATE,'YYYY')>=1997

方法二：
select first_name,
       last_name,
       salary
  from employees
 where employee_id >= 124
   and employee_id <= 176
   and hire_date >= to_date('1998-01-01', 'yyyy-mm-dd');

5、显示如下内容：给表employees中字段commission_pct为空的员工加薪10%；

select salary * 1.1
  from employees
 where commission_pct is null;

6、显示表employees中员工名、工龄（工作年月，不足一个月的舍掉）；

select last_name,
       round(months_between(sysdate, hire_date) / 12) as sumYear, 
       round(months_between(sysdate, hire_date)) - round(months_between(sysdate, hire_date) / 12) * 12 sumMonth
  from employees
 where round(months_between(sysdate, hire_date)) - round(months_between(sysdate, hire_date) / 12) * 12 > 0;

7、显示表employees中员工名、薪水、commission_pct，注意：commission_pct为空时显示NO（至少三种写法）；

方法一：
select last_name,
       salary,
       nvl(to_char(commission_pct, '0.00'), 'NO')
  from employees;

方法二：
select last_name,
       salary,
       nvl2(commission_pct, to_char(commission_pct, '0.00'), 'NO')
  from employees;

方法三：
select last_name,
       salary,
       decode(commission_pct, null, 'NO', to_char(commission_pct, '0.00'))
  from employees;

8、显示表employees中有多少员工名以'n'结尾（至少两种写法）；

方法一：
select last_name
  from employees
 where last_name like '%n';

方法二：
select last_name
  from employees
 where last_name like concat('%', 'n');

9、显示在每月上旬被雇佣的员工名、薪水，要求将薪水以“千”为单位显示

select last_name,
       round(salary / 1000, 2) || 'k'
  from employees
 where to_char(hire_date, 'dd') > 0
   and to_char(hire_date, 'dd') <= 15;

10、显示部门编号、部门名称、位置编号、员工数量（没有员工的部门显示0）、每个部门的平均工资（没有员工的部门显示'No average'），部门内的员工信息，包括姓名、薪水、工作编号，限制条件：位置编号在1500和2000之间，并按部门编号和部门平均工资升序排序，提示：表departments和表employees；

select d.DEPARTMENT_ID,
       d.DEPARTMENT_NAME,
       d.LOCATION_ID,
       nvl(emp.count, 0),
       nvl2(emp.count, emp.avg || '', 'no avg') as avg,
       e.EMPLOYEE_ID,
       e.LAST_NAME,
       e.SALARY
  from
      (
          select department_id,
                 employee_id,
                 count(employee_id) as count,
                 round(nvl2(employee_id, null, avg(salary)), 2) as avg
            from employees
           group by rollup (department_id, employee_id)
      ) emp
          left join employees e on e.employee_id = emp.employee_id
          right join departments d on d.department_id = emp.department_id
 where d.location_id >= 1500
   and d.location_id <= 2000
 order by d.department_id, emp.avg;

11、显示员工名，员工薪水，员工上司名，员工上司薪水，员工与上司薪水的差异，员工上司薪水等级，限制条件：员工上司薪水大于10000，提示：表departments、表employees、表job_grades；

select *
  from (
            select distinct
                e.employee_id,
                e.last_name,
                e.salary,
                e.manager_id,
                (select last_name from employees em where em.employee_id = e.manager_id) as manager_last_name,
                (select salary from employees em where em.employee_id = e.manager_id) as manager_salary,
                (select GRADE_LEVEL
                   from JOB_GRADES
                  where LOWEST_SAL <= (select salary from employees em where em.employee_id = e.manager_id)
                    and HIGHEST_SAL >= (select salary from employees em where em.employee_id = e.manager_id)) as grades_level,
                e.salary - (select salary from employees em where em.employee_id = e.manager_id) as sub
              from employees e
             start with employee_id = e.employee_id
           connect by prior manager_id = employee_id
       ) emp
 where emp.manager_salary > 10000;

12、只查询表employees，显示员工编号、姓、名、薪资水平（当薪水为20000以上为“高等收入”、10000到20000为“中等收入”、10000以下为低等收入。

select employee_id,
       first_name,
       last_name,
       salary,
       '高等收入' as salary_level
  from employees
 where salary > 20000
 union all
select employee_id,
       first_name,
       last_name,
       salary,
       '中等收入' as salary_level
  from employees
 where salary <= 20000
   and salary >= 10000
 union all
select employee_id,
       first_name,
       last_name,
       salary,
       '低等收入' as salary_level
  from employees
 where salary < 10000
   and salary > 0
 union all
select employee_id,
       first_name,
       last_name,
       salary,
       '异常收入' as salary_level
  from employees
 where salary < 0