select employee_id,
first_name || ''|| last_name names,
decode(round((sysdate- hire_date)/365),5,'5년근속',10,'10년근속',round((sysdate - hire_date)/365)) work_years
from employees
order by employee_id;
select round((sysdate - hire_date)/365) as round
from employees;
select decode(manager_id, 108,'dlfrhdvkf',manager_id) as id
from employees ;
select employee_id, first_name, last_name
from employees
where employee_id = decode(:emp, null, employee_id, : emp)
and first_name = decode(:names , null, first_name, : names);
select country_id, case region_id when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
else 'etc' end as case_region_id
from countries;
select department_id, department_name
from departments
where (department_id, manager_id )in (select department_id, employee_id
from employees
where manager_id is null);
select count(*)
from employees
where department_id in (select department_id
from departments
where manager_id is not null);
select count(*)
from employees emp
where exists (select 1
from departments dep where dep.manager_id is not null
and emp.department_id = dep.department_id);
select emp.first_name ||' '|| emp.last_name as emp_names
, emp.department_id
,(select dep.department_name
from departments dep where dep.department_id = emp.department_id )as dep_name
from employees emp;
select a.employee_id ,a.first_name || '' || a.last_name as names, a.salary,
round(b.avgs), b.maxs
from employees a,
(select avg(salary) avgs,
max (salary) maxs
from employees) b
where a.salary between b.avgs and b.maxs
order by a.salary desc;
select *
from (select employee_id, first_name, last_name, salary
from employees
order by salary desc)
where rownum <11;
