Oracle day1

 

1. 根据数据字典，查询当前用户下有哪些表                                                                                                                                    select * from all_tables t where t.OWNER ='HR';
2. 查询所有员工编号，员工姓名、邮件、雇佣日期、部门、部门详细地址信息                                                                             select e.employee_id,e.first_name||e.last_name as full_name, e.email,e.hire_date, 
   d.department_name,m.first_name||m.last_name as manager_name, 
   street_address,city,state_province 
   from employees e,employees m,departments d,locations l
   where e.manager_id=m.employee_id 
   and e.department_id=d.department_id 
   and d.location_id=l.location_id;
3. 查询1997年入职员工员工编号，员工姓名，雇佣日期，工龄(保留2位小数)、按照First_Name升序排列

   select employee_id,first_name,last_name,round((sysdate-hire_date)/365,2) as work_age

   from employees

   where to_char(hire_date,'yyyy') = 1997

   order by first_name

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

   select first_name,last_name,salary,commission_pct

   from employees

   order by salary desc

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

   select first_name,last_name,salary,commission_pct, NVL2(commission_pct,commission_pct+0.03,0.05)  NEW_COMMISSION_PCT

   from employees

   order by  NEW_COMMISSION_PCT asc , salary desc
6. 显示名字以J、K、L、M开头的雇员

   select *

   from employees

   where substr(first_name,1,1) in ('J','K','L','M') or substr(last_name,1,1) in ('J','K','L','M')

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

   select first_name,last_name,salary,department_name,

   case department_name

     when 'IT' then 1.30*salary

     when 'Sales' then 1.50*salary

     else 1.20*salary

   end "REVISED_SALARY"

   from employees left join departments on

   employees.department_id = departments.department_id

   order by REVISED_SALARY

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

   select first_name,last_name,hire_date

   from employees

   where TO_CHAR(hire_date, 'DD') between 11 and 20