데이터베이스 SQL 집계함수, 조인, 서브쿼리

카테고리 없음

데이터베이스 SQL 집계함수, 조인, 서브쿼리

hyuniiie 2024. 7. 8. 17:46

내부 조인(inner join)

: 두 개의 테이블에 대해 조건에 일치되는 데이터만 가져오는 조인

select o.order_no, o.customer_no, product_no, order_quantity
from orders o
inner join order_details od on o.order_no = od.order_no;

employee, deapartment 테이블을 조인해서 각 직원의 이름과 부서명 출력

select e.name as 직원명, d.dept_name as 부서명
from employee e
inner join department d on e.Dept_no = d.dept_no;

외부 조인(Outer Join)

: 두 테이블에서 한쪽에는 데이터가 있고 한쪽에는 데이터가 없는 경우

데이터가 있는 테이블 기준으로 데이터를 출력

-> RIGHT / LEFT 외부조인

LEFT JOIN

RIGHT JOIN

문제 1-1.각 직원의 이름과 부서명을 조회하고, 직원이 없는 부서도 포함하여 조회

-> department 테이블을 기준으로 모든 부서의 정보를 표시하며, 해당 부서에 속한 직원의 이름도 함께 출력

만약 어떤 부서에 속한 직원이 없을 경우, 그 부서명은 여전히 출력되지만, 직원명은 NULL로 표시

select e.name as 직원명, d.dept_name as 부서명
from employee e
right join department d on e.Dept_no = d.dept_no;

문제 1-2. orders 테이블과 customer 테이블을 조인하여 각 주문의 고객명을 조회하세요.

select o.order_no, c.customer_comp_name as 고객명 
from orders o inner join customer c
on o.customer_no = c.customer_no;

문제 1-4. 각 주문 상세의 제품 이름을 조회하세요.

select o.order_no, p.product_name 
from order_details o inner join products p
on o.product_no = p.product_no;

문제 1-5. 재고가 0인 제품도 포함하여 각 주문 상세의 제품 이름을 조회하세요.

select o.order_no, p.product_name, p.inventory 
from order_details o left join products p
on o.product_no = p.product_no;

문제 1-6. 각 주문의 고객명을 조회하세요.

select * from orders;
select * from customer;
select o.order_no, c.customer_comp_name as 고객명
from orders o left join customer c
on o.customer_no = c.customer_no;

문제 1-7. 각 주문을 처리한 직원의 이름을 조회하세요.

select o.order_no, e.name as 직원명
from orders o
left join employee e on o.Emp_no = e.Emp_no;

크로스조인 (Cross Join)

: 한쪽 테이블의 각 행마다 다른 쪽 테이블의 행이 모드 한번씩 각각 매칭

select p.product_name, d.dept_name
from products p
cross join department d;

select o.order_no, m1.grade_name
from orders o
cross join mileage_level m1;

셀프조인

: 동일한 테이블 내에서 한 컬럼이 다른 컬럼을 참조하는 조인

셀프조인을 하려면 조인 조건에 동일한 테이블명이 두 번 나타남

예제1. employee 테이블에서 직원, 상사 정보를 셀프조인으로 조회

select e1.name as 직원명, e2.name as 상사명
from employee e1
inner join employee e2 on e1.boss_number = e2.Emp_no;

예제2. 각 직원과 동일한 부서에 속한 다른 직원 정보 조회

select e1.name as 직원명, e2.name as 같은부서직원
from employee e1 inner join employee e2
on e1.Dept_no = e2.Dept_no
where e1.Emp_no <> e2.Emp_no;

문제1. 각 직원과 동일한 성별을 가진 다른 직원 정보를 조회하세요.

select e1.name as 직원명, e2.name as 같은성별직원
from employee e1
inner join employee e2 on e1.gender = e2.gender
where e1.emp_no <> e2.Emp_no;

문제2. employee 테이블에서 각 직원과 동일한 지역에 사는 다른 직원 정보를 조회하고 지역명도 함께 보이시오.

select e1.name as 직원명, e2.name as 동일지역직원, e1.area as 지역
from employee e1
inner join employee e2 on e1.area = e2.area;

문제 3-1. 특정 고객의 주문과 주문 상세를 조회하세요.

select o.order_no, od.product_no
from orders o, order_details od
where o.order_no = od.order_no and o.customer_no = 'CTUCA';

문제 3-2(응용문제). 각 주문에 대해 해당 주문의 고객 정보와 주문에 포함된 모든 제품의 정보(제품명, 단가)를 조회하되, 주문에 포함된 제품의 총 금액(수량 * 단가)을 계산하여 결과에 포함하세요.
응용문제 사용 테이블 : orders o, customer c, order_details od, products p

select o.order_no, c.customer_comp_name, p.product_name, p.unit_price, od.order_quantity
from orders o, customer c, order_details od, products p
where o.customer_no = c.customer_no and o.order_no = od.order_no and od.product_no = p.product_no;

응용문제 2. 각 직원의 상사 이름과 해당 직원이 처리한 주문의 총 금액을 조회하세요. 직원이 처리한 주문이 없는 경우도 포함하세요.

select e.name as 직원명, b.name as 심사명, coalesce(sum(od.order_quantity * p.unit_price)) 
from employee e 
left join employee b on e.boss_number = b.Emp_no
left join orders o on e.emp_no = o.emp_no
left join order_details od on o.order_no = od.order_no
left join products p on od.product_no = p.product_no
group by e.name, b.name;

-> coalesce 함수 : 특정 값이 null 일 경우, 원하는 값으로 반환할시 사용

서브쿼리

컬럼 삽입 후 데이터 추가

# employee 테이블에 salary 추가
ALTER TABLE employee
ADD COLUMN salary DECIMAL(10, 2);

# 컬럼 삽입 후 데이터 추가
-- UPDATE employee
-- SET salary = CASE Emp_no
--     WHEN 'E01' THEN 42000.00
--     WHEN 'E02' THEN 73000.00
--     WHEN 'E03' THEN 43000.00
--     WHEN 'E04' THEN 40000.00
--     WHEN 'E05' THEN 52000.00
--     WHEN 'E06' THEN 48000.00
--     WHEN 'E07' THEN 38000.00
--     WHEN 'E08' THEN 65000.00
--     WHEN 'E09' THEN 62000.00
--     WHEN 'E10' THEN 32000.00
--     ELSE NULL
-- END;

서브쿼리 개념

: SQL문 내부에서 사용하는 SELECT문

사용 이유 -> 데이터 필터링, 집계 및 계산, 비교 및 검증, 중첩된 데이터 추출, EXIST절

단일 행 서브쿼리 : =, <, >
복수 행 서브쿼리 : IN, ALL, ANY, SOME, EXISTS

서브쿼리 예제1. 급여 가장 높게 받는 사람 누구인가

select Emp_no, name, salary from employee
where salary = (select max(salary) from employee);

서브쿼리 예제2. 특정 부서에서 가장 최근에 입사한 직원의 정보 조회

select emp_no, name, salary from employee 
where date_of_emp = (select max(date_of_emp) from employee);

문제 1: 가장 많은 주문을 한 고객의 정보를 조회

select customer_no, customer_comp_name from customer 
where customer_no = (select customer_no from orders group by customer_no order by count(*) DESC limit 1);

-> limit 1 : 가장 높은 값

문제 2: 특정 부서에서 가장 높은 연봉을 받는 직원의 정보를 조회

select emp_no, name, salary, dept_no from employee
where salary = (select max(salary) from employee where Dept_no = 'A1')

문제 3: 특정 제품을 가장 많이 주문한 고객의 정보를 조회

select customer_no, customer_comp_name from customer 
where customer_no = (
select customer_no from orders o 
join order_details od on o.order_no = od.order_no 
where od.product_no = '1' 
group by customer_no order by sum(od.order_quantity) DESC limit 1);

문제 4: 특정 부서에서 가장 많은 주문을 처리한 직원의 정보를 조회

select emp_no, name, dept_no, date_of_emp from employee 
where emp_no = (
select emp_no from orders where emp_no in (select emp_no from employee where dept_no = 'A1')
group by emp_no order by count(*) DESC limit 1);

문제5: 가장 최근 입사한 직워다 먼저 입사한 직원 정보 조회

select emp_no, name, date_of_emp
from employee
where date_of_emp < (
select max(date_of_emp) from employee)

복수행 서브쿼리

문제1 : 가장 많이 주문한 제품 상위 3개를 주문한 고객들의 정보를 조회

select customer_no, customer_comp_name from customer
where customer_no in (
	select customer_no from orders o
	join order_details od on o.order_no = od.order_no
	where od.product_no in(
		select product_no from (
			select product_no from order_details 
			group by product_no 
			order by sum(order_quantity) DESC limit 3) as top_products))

# 메인쿼리 : 서브 쿼리에서 반환된 고객 번호를 이용한 'customer' 테이블 내 해당 고객 정보 조회
select customer_no, customer_comp_name from customer
where customer_no in (
	# sub query : 서브-서브 쿼리에서 반환된 상위 3개 제품번호를 사용해서, 'orders'와 'order_details' 테이블을 join
    # 각 주문에 대한 고객 번호 반환
    select customer_no from orders o
    join order_details od on o.order_no = od.order_no
	where od.product_no in(
		# sub of sub query : 'order_details' 테이블에서 각 제품의 총 주문수량 계산, 상위 3개의 제품 번호를 선택
		select product_no from (
			select product_no from order_details
			group by product_no
			order by sum(order_quantity) DESC
			limit 3) as top_products
		)
	)

-> 메인쿼리 : 서브쿼리에서 반환된 고객 번호를 이용한 'customer' 테이블 내 해당 고객 정보 조회

특정 제품을 주문한 고객들 중에서, 해당 제품을 주문한 모든 고객의 평균 주문 수량보다
더 많은 수량을 주문한 고객들의 정보를 조회

# 특정 제품을 주문한 고객들 중에서, 해당 제품을 주문한 모든 고객의 평균 주문 수량보다
# 더 많은 수량을 주문한 고객들의 정보를 조회
# 'customer' 테이블의 첫번째 서브쿼리에 대한 결과를 바탕으로 'customer_no'를 가진 고객 정보 조회
SELECT customer_no, customer_comp_name
FROM customer
WHERE customer_no IN (
# 각 고객의 총 주문수량을 확인(having)
# 'orders', 'order_details' 테이블을 조인하여 원하는 특정 제품을
# 주문한 각 고객의 주문번호 확인
    SELECT customer_no
    FROM orders o
    JOIN order_details od ON o.order_no = od.order_no
    WHERE od.product_no = '1'
    GROUP BY customer_no
    HAVING SUM(od.order_quantity) > (
        SELECT AVG(total_quantity) # avg_quantity 서브쿼리 내 각 고객의 총 주문수량의 평균 계산
        FROM (
        # 'order', 'order_details' 테이블 조인하여, 특정 제품을 주문한 각 고객의 총 주문량 계산
        # group by을 통해서 고객별 그룹화 진행 및 각 고객의 총 주문수량을 'total_quantity'로 계산
            SELECT customer_no, SUM(order_quantity) AS total_quantity
            FROM orders o
            JOIN order_details od ON o.order_no = od.order_no
            WHERE od.product_no = '1'
            GROUP BY customer_no
        ) AS avg_quantities
    )
);