HR1, HR2

HR1

select * from employees;

-- 오라클에서 문자열끼리 더하려면 || 기호를 사용한다
-- as 를 이용하여 컬럼이름을 내가 원하는 형태로 수정할 수 있다
select first_name, last_name, salary from employees;
select first_name || ' ' || last_name as name, salary from employees;

select max(salary) from employees;      -- 가장 높은 급여
select max(hire_date) from employees;   -- 가장 나중에 입사한 날짜

select min(salary) from employees;      -- 가장 낮은 급여
select min(hire_date) from employees;   -- 가장 먼저 입사한 날짜

select count(*) from employees;         -- 전체 직원 수 107명

select sum(salary) from employees;      -- 월간 급여로 지출되는 총 금액

select avg(salary) from employees;
select floor(avg(salary)) from employees;      -- 직원들의 평균 월급
-- floor() 는 소수점 이하 자리를 버리는 함수

-- 두개 이상의 컬럼을 조회하면서 집계함수를 사용하는 예시

select max(salary), job_id from employees
    group by job_id
    order by max(salary) desc;
    
select first_name, salary, job_id from employees 
    where job_id = 'IT_PROG';
    
-- 각 직책에 따른 인원 수를 파악하세요
-- x에 따른 y를 파악하세요

select job_id, count(*) from employees
    where job_id='IT_PROG'
    group by job_id
    order by count(*) desc;
    
    
select department_id, MIN(salary) from employees
    group by department_id;

HR2

select department_id, first_name from employees;
select department_id, department_name from departments;

select count(*) from employees;     -- 107
select count(*) from departments;   -- 27

select 
    E.department_id, D.department_name, E.first_name 
        from employees E, departments D;
        
select 
    E.department_id, E.first_name, D.department_name, E.salary
        from employees E
        join departments D
            on D.department_id = E.department_id;
            
--------------------------------------------------------------

select 
    E.employee_id as 사원번호,
    E.first_name || ' ' || E.last_name as 이름,
    E.salary as 급여,
    D.department_id as 부서번호,
    D.department_name as 부서명
        from employees E
        join departments D
            on E.department_id = D.department_id
            --on 1=1;
        where 
            E.salary >= 10000
        order by 
            E.department_id, E.salary desc;