Oracle练习 DAY1

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’;