Oracle的SQL练习Day1

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

select * from all_tables t where t.OWNER =’HR’;

/* 
2. 查询所有员工编号，员工姓名、邮件、雇佣日期、部门、部门详细地址信息。 
*/ 
select employee.employee_id,employee.first_name||employee.last_name as full_name, employee.email,employee.hire_date, 
department_name,manager.first_name||manager.last_name as manager_name, 
street_address,city,state_province 
from employees employee,employees manager,departments,locations 
where employee.manager_id=manager.employee_id 
and employee.department_id=departments.department_id 
and departments.location_id=locations.location_id;

/* 
3. 查询 1997 年入职员工员工编号，员工姓名，雇佣日期，工龄(保留 2 位小数)、按照 
First_Name 升序排列 
*/ 
select employee_id,first_name,last_name,hire_date,round((sysdate-hire_date)/365,2) as work_ages 
from employees 
where to_char(hire_date,’YYYY’)=’1997’ 
order by first_name asc;

/* 
4. 显示姓、薪水，佣金（commission） ，然后按薪水降序排列 
*/ 
select first_name,last_name,salary,commission_pct as commissions 
from employees 
where commission_pct IS NOT NULL 
order by salary desc;

/* 
5. 显示姓名、薪水，佣金（commission），佣金为空的，统一加上 0.05;其余的加上 
0.03，按照薪资降序、变更后佣金升序排列 
*/ 
select first_name,last_name,salary,NVL(commission_pct+0.03,0.05) as commissions 
from employees 
order by salary desc,commissions asc;

/* 
6. 显示名字以 J、 K、 L、 M 开头的雇员 
*/ 
select * from employees 
where Substr(FIRST_NAME,1,1) in(‘J’,’K’,’L’,’M’);

/* 
7. 显示员工姓名，薪水，调整后薪水(按部门调整： IT 提升 30%,Salse 提升 50%、其余部 
门提升 20%)；按照调整后薪水升序排列 
*/ 
select first_name,last_name,salary, 
decode (department_name,’IT’,1.3*salary,’Salse’,1.5*salary,1.2*salary) new_salary 
from employees,departments 
where employees.department_id=departments.department_id 
order by new_salary;

/* 
8. 显示在每月中旬雇佣的员工,显示姓名，雇佣日期 
*/ 
select first_name,last_name,hire_date 
from employees 
where to_char(HIRE_DATE,’dd’) between ‘11’ and ‘20’;