728x90
반응형
[MySQL] 1일 1쿼리 (6) : 서브 쿼리 및 기본 개념 활용
1. 문제
2. 문제 풀이(답안)
1. 문제
순서 | 문제 |
1 | 남성 셔츠 여성 면바지 남성 슬랙스의 각자의 가격을 구하시오 |
2 | 여성복 중 색상이 '샤인'이 아니며, 가격이 10만원 이상인 여성복들을 출력해라. -- 출력해야 할 것: 상품명, 카테고리ID, 가격, 색상 |
3 | 재직중이고 1995년 이후 입사한 Senior Engineer 직원이 가장 많은 부서 3개를 출력하세요 |
4 | 현재 근무 중인 직원들 중에 38년 이상 근무했으나 직급이 Staff이고 salary_standard가 Low인 직원만 출력해주세요. (salary가 70000 이상이면 'High', 50000 이상이면 'Medium', 50000 미만이면 'Low'라고 표시하되 표기명은 salary_standard입니다.) --(단, 같은 직원일 경우 가장 높은 급여만 'max_salary'라는 표기로 출력해야 합니다.) |
5 | db sakila 에서 -- 각 언어 마다 몇개의 영화가 있는지 조회하라 (데이터상 모든 영화가 영어지만, 다른 언어가 0개 임을 표현해야함) |
6 | 가장 인원이 많은 부서의 매니저 이름을 출력하세요. db = employees -- (정답 = first_name : Leon , last_name : DasSarma) -- (퇴사한 매니저는 제외 && 퇴사 직원 제외) -- (서브쿼리를 사용 안해도 가능) |
7 | 데이터베이스 db_denco_market 에서 '김범수' 고객의 최종합계금액을 조회하세요. |
8 | 문제 : demo3에서 남성슬랙스, 여성면바지의 상품이름, 가격, 사이즈, 색상을 출력하시오 단, 서브쿼리를 쓰시오. |
9 | 제일 비싼 상품을 가지고 있는 카테고리를 출력(카테고리 이름 + 상품 이름 + 가격 ) mydb3 의 tb_products, tb_categories 사용 |
10 | 현재 어느 부서의 매니저도 아닌 직원 중, 재직중이며 연봉이 100000이 넘는 직원을 조회하세요. (사원번호,first_name,last_name,연봉) |
11 | 직원번호 10033 직원의 최고 연봉액과 근무일수를 구하시오 |
12 | 부서 번호가 d009인 부서에서 재직중이며 매니저가 있는 직원을 조회하여라.(직원 번호, 이름, 부서명) -- 단, 서브쿼리를 사용하여 departments 테이블의 dept_name에 담아서 출력하여라. |
13 | demo3 데이터베이스를 사용하여 색깔이 '퍼플' 인 남성복 셔츠의 정보를 조회하시오. -- ( 서브쿼리 사용 ) |
2. 문제 풀이(답안)
-- 문제 1. 남성 셔츠 여성 면바지 남성 슬랙스의 각자의 가격을 구하시오
select p.product_name , p.price , p.size
from TB_PRODUCTS as p
join tb_categories as c
on p.category_id = c.category_id
where (c.category_name ='면바지' and c.parent_id=5)
or (c.category_name ='슬랙스' and c.parent_id=3)
or (c.category_name ='셔츠' and c.parent_id=1);
-- 문제 2. 여성복 중 색상이 '샤인'이 아니며, 가격이 5만원 이상인 여성복들을 출력해라.
-- 출력해야 할 것: 상품명, 카테고리ID, 가격, 색상
select p.product_name,p.category_ID,p.price,p.color
from tb_products as p
left join tb_categories as c
on p.category_ID=c.category_ID
where (p.color!='샤인') and (p.price>=100000) and p.product_name like '여성%';
-- 문제 3. 재직중이고 1995년 이후 입사한 Senior Engineer 직원이 가장 많은 부서 3개를 출력하세요
select d.dept_name as 부서, count(*) as 직원수
from employees e
left join dept_emp de
on e.emp_no = de.emp_no
left join departments d
on d.dept_no = de.dept_no
left join titles t
on e.emp_no = t.emp_no
where year(e.hire_date) >= 1995 and t.title = 'Senior Engineer'
and de.to_date ='9999-01-01' and t.to_date = '9999-01-01'
group by d.dept_name
order by 직원수 desc
limit 3;
-- 문제 4
-- 현재 근무 중인 직원들 중에 38년 이상 근무했으나 직급이 Staff이고 salary_standard가 Low인 직원만 출력해주세요.
-- (salary가 70000 이상이면 'High', 50000 이상이면 'Medium', 50000 미만이면 'Low'라고 표시하되 표기명은 salary_standard입니다.)
-- (단, 같은 직원일 경우 가장 높은 급여만 'max_salary'라는 표기로 출력해야 합니다.)
select e.emp_no,e.first_name,e.last_name,t.title, MAX(s.salary) as max_salary,
case
when MAX(s.salary) >= 70000
then 'High'
when MAX(s.salary) >= 50000
then 'Medium'
else 'Low'
end as 'salary_standard'
from salaries as s
join employees as e
on s.emp_no = e.emp_no
join titles t
on e.emp_no = t.emp_no
where t.to_date = '9999-01-01'
and year(current_date) - year(hire_date) >= 38
and t.title = 'Staff'
group by s.emp_no
having salary_standard = 'Low';
-- 문제 5. db sakila 에서
-- 각 언어 마다 몇개의 영화가 있는지 조회하라 (데이터상 모든 영화가 영어지만, 다른 언어가 0개 임을 표현해야함)
select l.name, count(f.language_id) as count
from language as l
left join film as f on l.language_id = f.language_id
group by l.language_id;
-- 문제 6. 가장 인원이 많은 부서의 매니저 이름을 출력하세요. db = employees
-- (정답 = first_name : Leon , last_name : DasSarma)
-- (퇴사한 매니저는 제외 && 퇴사 직원 제외)
-- (서브쿼리를 사용 안해도 가능)
select emp.first_name, emp.last_name
from employees as emp
join dept_manager as dema
on emp.emp_no = dema.emp_no
join( select dept_no, count() as a
from dept_emp
where to_date = '9999-01-01'
group by dept_no
order by a desc
limit 1
) as count_max_dept
on dema.dept_no = count_max_dept.dept_no
where dema.to_date = '9999-01-01';
-- 문제 7. 데이터베이스 db_denco_market 에서 '김범수' 고객의 최종합계금액을 조회하세요.
select u.userName, sum(price amount) as '최종금액(만)'
from usertbl u
join buytbl b on u.userName = b.userName
where u.userName = '김범수'
group by userName;
-- 문제 8 : demo3에서 남성슬랙스, 여성면바지의 상품이름, 가격, 사이즈, 색상을 출력하시오 단, 서브쿼리를 쓰시오.
use demo3;
SELECT
p.product_name, p.price, p.size, p.color
FROM
tb_products AS p
JOIN
tb_categories AS c ON p.category_id = c.category_id
WHERE
c.category_name = '슬랙스'
AND c.parent_id = (SELECT
category_id
FROM
tb_categories
WHERE
category_name = '팬츠'
AND parent_id = 1)
OR category_name = '면바지'
AND c.parent_id = (SELECT
category_id
FROM
tb_categories
WHERE
category_name = '팬츠'
AND parent_id = 2);
-- 문제 9
-- 제일 비싼 상품을 가지고 있는 카테고리를 출력(카테고리 이름 + 상품 이름 + 가격 ) mydb3 의 tb_products, tb_categories 사용
select
c.category_name,
p.product_name,
p.price
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where p.price = (select max(p.price) from tb_products as p);
-- 문제 10. 현재 어느 부서의 매니저도 아닌 직원 중, 재직중이며 연봉이 100000이 넘는 직원을 조회하세요. (사원번호,first_name,last_name,연봉)
select e.emp_no, e.first_name, e.last_name, s.salary
from employees as e
left join salaries as s on e.emp_no = s.emp_no
left join dept_emp as de on e.emp_no = de.emp_no
where e.emp_no not in (select emp_no from dept_manager)
and s.salary >= 100000
and de.to_date = '9999-01-01'
group by e.emp_no, e.first_name, e.last_name;
-- 문제 11. 직원번호 10033 직원의 최고 연봉액과 근무일수를 구하시오
select
d.emp_no,
max(s.salary) as 최고연봉,
ABS(
datediff(
(select from_date from dept_emp where emp_no = 10033),
(select to_date from dept_emp where emp_no = 10033)
)
)+1 as 근무일수
from dept_emp as d
join salaries as s
on d.emp_no = s.emp_no
where d.emp_no = 10033;
-- 문제 12. 부서 번호가 d009인 부서에서 재직중이며 매니저가 있는 직원을 조회하여라.(직원 번호, 이름, 부서명)
-- 단, 서브쿼리를 사용하여 departments 테이블의 dept_name에 담아서 출력하여라.
select e.emp_no, e.first_name, d.dept_name
from employees as e
join dept_manager as m
on e.emp_no = m.emp_no
join departments as d
on d.dept_no = m.dept_no
where m.to_date = '9999-01-01'
and d.dept_name = (select dept_name from departments where dept_no = 'd009');
-- 문제 13. demo3 데이터베이스를 사용하여 색깔이 '퍼플' 인 남성복 셔츠의 정보를 조회하시오.
-- ( 서브쿼리 사용 )
use demo3;
select c.category_name, p.product_name, p.price, p.size, p.color,c.parent_id
from tb_products as p
join tb_categories as c
on p.category_id = c.category_id
where p.color = '퍼플'
and c.category_id = (select category_id from tb_categories
where category_name = '셔츠' and parent_id=1);
728x90
반응형
'MySQL > 1일 1쿼리' 카테고리의 다른 글
[MySQL] 1일 1쿼리 (7) : 서브 쿼리(중첩/인라인/스칼라) 및 기본 개념 활용 (1) | 2024.06.13 |
---|---|
[MySQL] 1일 1쿼리 (5) : Self join 및 기존 개념 복습 (0) | 2024.06.11 |
[MySQL] 1일 1쿼리 (4): HAVING과 GROUP BY 절 (0) | 2024.06.11 |
[MySQL] 1일 1쿼리 (3) : 함수 응용하기 (0) | 2024.06.07 |
[MySQL] 1일 1쿼리 (2) : JOIN 활용하기 (0) | 2024.06.05 |