[MySQL] 쿼리 실습문제

 

문제 01

사번, 성명, 급여, 입사일자, 이메일주소를 출력하시오.
결과가 존재하지 않을 수도 있습니다!
단,
- 급여가 10000이상이어야 합니다.
- 커미션지급대상이어야 합니다.
- 부서가 80이거나 100이어야 합니다.
- 그리고 관리자가 있어야 합니다.
- 그리고 직무가 'P'로 시작되는 이름이어야 합니다.
- 입사일자가 빠른 순서로 정렬하시오!

select employee_id, concat(last_name, ' ', first_name) name, salary, hire_date, email
from employees
where 1 = 1 #이게 없으면 그냥 오류라서 and를 붙여줘야하니까 귀찮음
	and salary >= 1000
    and commission_pct is not null 
    and department_id in (80, 100)
    and manager_id is not null
    and job_id like 'P%'
order by hire_date;

 

 

 

문제 02

2000년1월1일 이후 입사자입니다.
- 연봉은 커미션을 반영한 급여의 12배입니다.
- 연봉은 원화로 표시합니다.(환율 : 1295)
- 연봉은 천원 단위로 반올림합니다.
- 연봉은 천원 단위로 콤마를 부여합니다.
- 근속년수는 소수2째자리까지 표시합니다.
- 성명은 성과 명을 합하여 출력합니다. 각 첫글자를 대문자로 합니다.

 

select concat(last_name, ' ', first_name) 성명, hire_date 입사날,
	concat(format(round(((salary *(1 +ifnull(commission_pct, 0)) *12) *1295), -3), 0), '원') 연봉, 
    round((datediff(sysdate(), hire_date) /365), 2) 근속년수
from employees
where year(hire_date) >= 2000
order by hire_date;

 

 

 

문제 03

1. 20번 부서의 이름과 그 부서에 근무하는 사원의 이름을 출력하시오. 단, 급여로 오름차순 정렬하시오.

select d.department_name, e.last_name
from departments d, employees e
where d.department_id = 20 and d.department_id = e.department_id
order by salary;

 

2. 1400,1500 번 위치의 도시 이름과 그곳에 있는 부서의 이름을 출력하시오.

select l.location_id, l.city, d.department_name
from locations l, departments d
where l.location_id = d.location_id and l.location_id in (1400, 1500);

 

3. IT_PROG가 직무인 직원들의 다음 정보를 출력.
-- - 입사일자를 일/월/년으로 출력
-- - 부서명, 도시명도 출력합니다

select e.last_name, date_format(e.hire_date, '%d/%m/%Y') 입사일자, department_name 부서명, city 도시명
from employees e, departments d, locations l
where e.job_id = 'IT_PROG' and e.department_id = d.department_id and d.location_id = l.location_id;

 

 

문제 04

1. 자신의 매니저보다 채용일(hire_date)이 빠른 사원의 사번(employee_id), 성(last_name)과 채용일(hire_date)을 조회하라.

select e.employee_id, e.last_name, e.hire_date, m.hire_date 관리자입사일
from employees e join employees m
on e.manager_id = m.employee_id and e.hire_date < m.hire_date;

 

2. 2000년 1월 1일 이전에 입사한 직원들의 다음 정보를 조회하시오.
- 사번, 성명, 급여, 입사일자, 관리자사번, 관리자명, 관리자급여 
- 관리자가 없는 경우엔 관리자없음으로 표기하시오.

select e.employee_id, e.last_name, e.first_name, e.salary, e.hire_date, 
	ifnull(m.manager_id, '관리자없음') 관리자사번, m.last_name 관리자명, m.salary 관리자급여
from employees e join employees m on e.manager_id = m.employee_id
where year(e.hire_date) < 2000
order by e.hire_date;

 

부서별로 평균급여를 출력합니다.
- 단, 부서명으로 출력합니다. 
- 단, 평균급여가 8000이상이어야 합니다. 
- 단, 짝수년도에 입사한 사원들만 평균급여를 계산합니다.
- 단, 평균급여는 천단위를 버립니다.

select d.department_name 부서명, concat(format(round(avg(salary), -3), 0), '$') 평균급여
from employees e join departments d on e.department_id = d.department_id
where year(hire_date) %2 = 0
group by d.department_name
having avg(salary) >= 8000;

 

 

1.Neena의 관리자의 부하직원들의 사번, 급여, 입사일자를 출력합니다.

select employee_id, first_name, last_name, salary, hire_date from employees
where manager_id = (select manager_id from employees where first_name = 'Neena');

 

 

2.Neena가 입사한 년도에 입사한 직원들의 사번, 급여, 입사일자를 출력합시다.

select employee_id, last_name, first_name, salary, hire_date from employees
where year(hire_date) = (select year(hire_date) from employees where first_name = 'Neena');

 

 

1. Seattle에 있는 부서에 근무하는 직원들의 성명, 근속년수(소수 2째자리까지), 입사일자를 출력하시오.
-- (서브쿼리를 사용합니다)
-- - 조인을 전혀 사용하지 않습니다!
-- - 서브쿼리 내에 서브쿼리를 사용해도 됩니다.
-- - 서브쿼리 내에 조인을 써도 됩니다.
-- - 단지 메인쿼리에는 조인을 사용하지 말기 바랍니다.

select concat(first_name, ' ', last_name) 성명, hire_date 입사일자, 
		round((datediff(sysdate(), hire_date) /365), 2) 근속년수 from employees
where department_id in (select department_id from departments 
						where location_id = (select location_id from locations 
                        						where city = 'Seattle'));