[MySQL] 쿼리문 연습

/* [문제 1] 사원정보(EMPLOYEES) 테이블에서 사원의 성과 이름은 Name, 업무는 Job, 급여는 
Salary, 연봉에 $100 보너스를 추가하여 계산한 값은 Increased Ann_Salary, 급여에 $100 보너 
스를 추가하여 계산한 연봉은 “Increased Salary”라는 별칭으로 출력하시오.(107) */
select concat(first_name, ' ', last_name) as Name, job_id as Job, salary as Salary, 
salary *12 + 100 as 'Increased Ann_Salary', (salary + 100) * 12 as 'Increased Salary'
from employees;

/* [문제 2] 사원정보(EMPLOYEE) 테이블에서 모든 사원의 이름(last_name)과 연봉을 “이름: 
Year Salary = $연봉” 형식으로 출력하고, “1 Year Salary”라는 별칭을 붙여 출력하시오.(107)*/
select concat(last_name,": Year Salary = $", salary*12) as '1 Year Slary' 
from employees
order by '1 Year Slary';

/*[문제 3] 부서별로 담당하는 업무를 한 번씩만 출력하시오.(20)*/
select distinct department_name from departments order by department_name;
select distinct department_id, job_id from employees order by department_id;

/*[문제 4] 사원의 이름(last_name) 중에 ‘e’ 및 ‘o’ 글자가 포함된 사원을 출력하시오. 이때 머리글 
은 ‘e and o Name’라고 출력하시오.(10)*/
select last_name as 'e and o Name' 
from employees 
where last_name like '%e%' and last_name like '%o%';

/*[문제 5] HR 부서에서는 급여(salary)와 수당율(commission_pct)에 대한 지출 보고서를 작성하 
려고 한다. 이에 수당을 받는 모든 사원의 성과 이름(Name으로 별칭), 급여, 업무, 수당율을 출력 
하시오. 이때 급여가 큰 순서대로 정렬하되, 급여가 같으면 수당율이 큰 순서대로 정렬하시오.(35)*/

select concat(first_name,' ',last_name) as Name, salary, job_id, commission_pct from employees where commission_pct is not null
order by salary desc, commission_pct desc;

/*[문제 6] 모든 사원은 직속 상사 및 직속 직원을 갖는다. 단, 최상위 또는 최하위 직원은 직속 
상사 및 직원이 없다. 소속된 사원들 중 어떤 사원의 상사로 근무 중인 사원의 총 수를 출력하시 
오.(1)*/

select count(distinct manager_id) as '어떤 사원의 상사로 근무 중인 사원의 총 수'
from employees;

/*[문제 7] 사원들의 업무별 전체 급여 평균이 $10,000보다 큰 경우를 조회하여 업무, 급여 평균을 
출력하시오. 단 업무에 사원(CLERK)이 포함된 경우는 제외하고 전체 급여 평균이 높은 순서대로 
출력하시오.(7)*/

select job_id, avg(salary)
from employees 
WHERE job_id not LIKE '%CLERK'
group by job_id
having avg(salary) > 10000
order by job_id;

/*[문제 8] hr 스키마에 존재하는 Employees, Departments, Locations 테이블의 구조를 파악 
한 후 Oxford에 근무하는 사원의 성과 이름(Name으로 별칭), 업무, 부서명, 도시명을 출력하시 
오. 이때 첫 번째 열은 회사명인 ‘Han-Bit’이라는 상수값이 출력되도록 하시오.(34)*/

select concat('Han-Bit ',e.first_name," ",e.last_name), e.job_id, d.department_id, l.location_id
from employees e
	Join departments d on d.department_id = e.department_id
    Join locations l on d.location_id = l.location_id
where l.city = 'Oxford'
order by e.first_name;

/*[문제 9] HR 스키마에 있는 Employees, Departments 테이블의 구조를 파악한 후 사원수가 5명 
이상인 부서의 부서명과 사원수를 출력하시오. 이때 사원수가 많은 순으로 정렬하시오.(5)*/

select d.department_name as 부서명, count(e.department_id) as 사원수
from employees e
	Join departments d on d.department_id=e.department_id
group by e.department_id
having count(e.department_id) >= 5
order by count(e.department_id) desc;
    
/*[문제 10] 각 사원의 급여에 따른 급여 등급을 보고하려고 한다. 급여 등급은 JOB_GRADES 테이 
블에 표시된다. 해당 테이블의 구조를 살펴본 후 사원의 성과 이름(Name으로 별칭), 업무, 부서 
명, 입사일, 급여, 급여등급을 출력하시오.(106)*/
select concat(e.first_name," ",e.last_name) as Name, e.job_id as 업무, d.department_name as 부서명, e.hire_date as 입사일, e.salary as 급여, jd.grade_level as 급여등급
from employees e
	Join departments d on d.department_id = e.department_id
    Join locations l on d.location_id = l.location_id
    Join job_grades jd on e.salary between jd.lowest_sal and jd.highest_sal;