欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle根据数据字典的查询练习

程序员文章站 2022-06-21 21:03:04
根据数据字典,查询当前用户下有哪些表 select * from all_tables t where t.owner ='hr'; 查询所有员工编号,员工姓名、邮件、雇佣日期、部门...

根据数据字典,查询当前用户下有哪些表 select * from all_tables t where t.owner ='hr'; 查询所有员工编号,员工姓名、邮件、雇佣日期、部门、部门详细地址信息 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; 查询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

显示姓、薪水,佣金(commission) ,然后按薪水降序排列

select first_name,last_name,salary,commission_pct

from employees

order by salary desc

显示姓名、薪水,佣金,佣金为空的,统一加上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 显示名字以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')

显示员工姓名,薪水,调整后薪水(按部门调整: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

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

select first_name,last_name,hire_date

from employees

where to_char(hire_date, 'dd') between 11 and 20