[MySQL] 1일 1쿼리 (7) : 서브 쿼리(중첩/인라인/스칼라) 및 기본 개념 활용

[MySQL] 1일 1쿼리 (7) : 서브 쿼리(중첩/인라인/스칼라) 및 기본 개념 활용

1. 문제

2. 문제 풀이 (답안)

---

1. 문제

순서	문제
1	last_name Hofting 인 직원들 급여와 부서를 찾으시요
2	재직중인 매니저 성과 이름 명단을 뽑아주세요
3	중첩 서브 쿼리 응용 문제 -- 현재 재직 중인 직원 중, 직함이 'Staff'인 직원들의 이름, 사원번호, 평균 연봉를 출력해보자.
4	현재 재직중인 사람중에서 직급별 평균연봉을 구하시오
5	-- 마케팅 부서에서 재직중이고, 태어난 년도의 일의자리가 1,2,3 이면 O 아니면 X로 표시하세요 -- 사번, 이름(성+이름), 생년월일, 체크 로 출력
6	db sakila 에서 -- 가장 많은 작품을 찍은 배우의 작품을 모두 조회하라 -- 출력 예시 (first_name, title, category_name)
7	emp_no,birth_date,last_name,dept_name ,gender 그리고 -- ept_name 의 수가 2개 이상인 직원의 나이와 나이순으로 내림차순조회 -- 성별은 여성, dept_no번호는 d001 제외,고용날짜의 달이 짝수인 직원 ( 서브쿼리 사용안함)
8
9

 

 

2. 문제 풀이 (답안)

-- 문제 1. last_name Hofting 인 직원들 급여와 부서를 찾으시요
select em.last_name as 이름, d.dept_name as 부서,s.salary as 급여
from employees em 
join dept_emp de on em.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no
join salaries s on em.emp_no = s.emp_no
where last_name = 'Hofting'


-- 문제 2. 재직중인 매니저 성과 이름 명단을 뽑아주세요
select*from employees;
select em.first_name, em.last_name
from dept_manager as ma
join employees as em on ma.emp_no = em.emp_no
where ma.to_date = '9999-01-01'


-- 문제 3. 중첩 서브 쿼리 응용 문제
-- 현재 재직 중인 직원 중, 직함이 'Staff'인 직원들의 이름, 사원번호, 평균 연봉를 출력해보자.
select e.first_name,e.emp_no,avg(s.salary)
from employees as e
join salaries as s
on e.emp_no=s.emp_no
where e.emp_no in (select emp_no
                from titles
                where title='Staff' and to_date='9999-01-01')
group by e.emp_no; 


-- 문제 4. 현재 재직중인 사람중에서 직급별 평균연봉을 구하시오
select avg(s.salary) as '평균 연봉',  t.title,t.to_date
from salaries as s
join titles as t
on s.emp_no = t.emp_no
group by t.title
having t.to_date = '9999-01-01';


－－ 문제 ５。마케팅 부서에서 재직중이고, 태어난 년도의 일의자리가 1,2,3 이면 O 아니면 X로 표시하세요
-- 사번, 이름(성+이름), 생년월일, 체크 로 출력
select de.emp_no as 사번, concat(e.first_name,' ' ,e.last_name) as 이름, e.birth_date as 생년월일,
    case when mod(year(birth_date),10) in (1,2,3) then 'O' else 'x' end as 체크
from employees e
join dept_emp de
on e.emp_no = de.emp_no
where de.to_date = '9999-01-01' and de.dept_no = 'd001';



--  문제６。db sakila 에서 
-- 가장 많은 작품을 찍은 배우의 작품을 모두 조회하라
-- 출력 예시 (first_name, title, category_name)

SELECT a.first_name, f.title, c.name AS category_name
FROM film_actor AS fa
JOIN actor AS a ON fa.actor_id = a.actor_id
JOIN film AS f ON fa.film_id = f.film_id
JOIN film_category AS fc ON f.film_id = fc.film_id
JOIN category AS c ON fc.category_id = c.category_id
WHERE fa.actor_id = (
    SELECT actor_id
    FROM film_actor
    GROUP BY actor_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
);


-- 문제７。emp_no,birth_date,last_name,dept_name ,gender 그리고
-- ept_name 의 수가 2개 이상인 직원의 나이와 나이순으로 내림차순조회
-- 성별은 여성, dept_no번호는 d001 제외,고용날짜의 달이 짝수인 직원 ( 서브쿼리 사용안함)

select e.emp_no, birth_date,last_name,d.dept_name,gender,count(dept_name) 부서,
case when count(dept_name)=2 then year(current_time())-year(birth_date) end as 나이
from dept_emp e
join employees em on e.emp_no=em.emp_no and  gender='M'
join departments d on e.dept_no=d.dept_no and 
(d.dept_no not like  '%___1%'and month(hire_date)%2=0 )
group by emp_no
order by 나이 desc;