ORACLE day 01

1. 根据数据字典，查询当前用户下有哪些表

select * from all_tables t where t.owner = 'STUDENT1';

    2 查询所有员工编号，员工姓名、邮件、雇佣日期、部门、部门详细地址信息。

select e.employee_id,

          e.first_name||'.'||e.last_name as full_name,

          e.emil,

          to_char(e.hire_date,'yyyy-mm-fmdd')

          d.department_name,

          e2.first_name||'.'||e2.last_name as manager_name,

          l.street_address,

          l.city,

          l.state_province

from employees e,employees e2,departments d,locations l

where e.department_id = d.department_id and

           d.location_id = l.location_id;

3.查询1997年入职员工员工编号，员工姓名，雇佣日期，工龄(保留2位小数)、按照First_Name升序排列

select e.employee_id,
       e.first_name,
       to_char(e.hire_date,'yyyy-mm-fmdd'),
round((sysdate-e.hire_date)/365,2) as work_age
from employees e
where to_char(e.hire_date,'yyyy') = 1997
order by e.first_name asc;

4.显示姓、薪水，佣金（commission） ，然后按薪水降序排列

select e.first_name,
       e.last_name,
       e.salary,
       e.commission_pct as commission
from   employees e
where e.commission_pct is not null
order by e.salary desc;

5.显示姓名、薪水，佣金（commission），佣金为空的，统一加上0.05;其余的加上0.03，按照薪资降序、变更后佣金升序排列

select e.first_name,
       e.last_name,
       e.commission_pct as commission
       NVL2(e.commission_pct,0.03,0.05)
from   employees e
order by e.salary desc,
         e.commission_pct asc;

6.显示名字以J、K、L、M开头的雇员

select * from employees e
where substr(e.first_name,1,1) in ('J','K','L','K');

 

7.显示员工姓名，薪水，调整后薪水(按部门调整：IT提升30%,Salse 提升50%、其余部门提升20%)；按照调整后薪水升序排列

 

select e.first_name,
       e.last_name,
       e.salary,
case d.department_name
when 'IT'
    then e.salary*1.2
when 'Sales'
    then e.salary*1.5
else e.salary*1.2
end as new_salary
from employees e,departments d
where e.department_id = d.department_id
order by new_salary asc;
       

 

8.显示在每月中旬雇佣的员工,显示姓名，雇佣日期

select e.first_name,
       e.last_name,
       e.hire_date
from employees e
where to_char(e.hire_date,'fmdd') 
between '11' and '19';