데이터베이스 CTE, 상관 서브쿼리, 다중 컬럼 서브쿼리, DML

 

CTE(Common Table Expression)

CTE

: 쿼리로 만든 임시 데이터셋으로 WITH절에서 정의함

인라인 뷰와 마찬가지로 파생 테이블 처럼 사용 가능

 

부서명과 평균연봉 조회(단, 평균 연봉이 50000 이상인 부서만)

 

방법1)

SELECT d.dept_name, stats.avg_salary
FROM department d
JOIN (
    SELECT dept_no, AVG(salary) AS avg_salary
    FROM employee
    GROUP BY dept_no
) stats
ON d.dept_no = stats.dept_no
WHERE stats.avg_salary >= 50000;

 

방법2) WITH절

with dept_evg_sal as (
select dept_no, avg(salary) as avg_sal
from employee
group by dept_no
) select d.dept_name, a.avg_sal
from department d
join dept_avg_sal on d.dept_no = a.dept_no
where a.avg_sal >= 50000

 

 

 

각 직원의 정보 조회

with employee_hierarchy as (
select e.emp_no, e.name as employee_name, e.boss_number, b.name as boss_name
from employee e
left join employee b on e.boss_number = b.emp_no
)

select emp_no, employee_name, boss_name
from employee_hierarchy

 

 

---

 

 

문제 1. 부서별로 직원 수가 5명 이상인 부서의 부서명과 직원 수를 조회하세요.

with emp_count as (
select Dept_no, count(*) as in_emp_count
from employee 
group by Dept_no 
having count(*) >= 5
)
select d.dept_name, c.in_emp_count
from department d join emp_count c
on d.dept_no = c.Dept_no
where c.in_emp_count >= 5;

 

 

문제 2. 모든 주문의 총 금액을 계산하는 쿼리를 작성하세요.

with order_total as (
select order_no, sum(unit_price * order_quantity * (1 - discount_rate)) as order_sum
from order_details
group by order_no
)
select sum(order_sum) as all_total from order_total

 

 

문제 3. 각 제품의 재고가 10개 이하인 제품의 제품명과 재고 수량을 조회하세요.

with check_inven as (
select product_name, inventory
from products 
where inventory <= 10
)
select product_name, inventory from check_inven

 

 

 문제 4. 모든 직원의 연봉과 그 연봉의 평균 연봉을 함께 조회하는 쿼리를 작성하세요. - CTE 2개 사용

# CTE1 : 각 직원에 대한 연봉정보 조회
# CTE2 : 전체 직원의 평균연봉 조회

with emp_sal as (
select emp_no, name, salary
from employee
),
avg_sal as (
select avg(salary) as sal_avg
from employee
)

select e.emp_no, e.name, e.salary, a.sal_avg 
from emp_sal e, avg_sal a

 

 

응용문제 1. 각 고객의 주문 수와 총 주문 금액을 조회하세요. 단, 총 주문 금액이 1000 이상인 고객만 조회하세요.

with cust_order as (
select c.customer_no, c.customer_comp_name,
count(o.order_no) as order_cnt,
sum(od.unit_price * od.order_quantity * (1 - od.discount_rate)) as total_amount
from customer c
join orders o on c.customer_no = o.customer_no
join order_details od on o.order_no = od.order_no
group by c.customer_no, c.customer_comp_name
)

select customer_no, customer_comp_name, order_cnt, total_amount
from cust_order
where total_amount >= 1000

 

 

---

 

상관 서브쿼리(Correlated SubQuery)

: 메인쿼리와 서브쿼리 간의 상관관계를 포함하는 형태

SELECT절, FROM절, WHERE절 등에서 사용 가능

 

 

상관 서브쿼리 문제 1 : 각 부서에서 가장 높은 연봉을 받는 직원의 이름과 연봉을 조회하세요.

# 일반 서브쿼리
select emp_no, name, salary
from employee
where salary = (select max(salary) from employee);

# 상관 서브쿼리
select e.name, e.salary
from employee employee
where e.salary = (
	select max(salary)
    from employee)

 

 

다중 컬럼 서브쿼리(Multi-Column SubQuery)

: 서브쿼리에서 여러개의 컬럼을 사용하여 다중 비교를 하는 쿼리

서브쿼리의 결과를 메인쿼리 값과 비교하여 결과를 출력 

 

 주문번호, 주문날짜 조작

# 다중컬럼 서브쿼리 예
# 주문번호, 주문날짜 조작
select o.order_no, o.order_date 
from orders o
join order_details od on o.order_no = od.order_no
#서브쿼리의 결과와 일치하는 행을 필터링하기 위한 구간
where (od.product_no, od.order_quantity) in (
select product_no, inventory
from products
where inventory <= 10)

 

 

---

 

DML

INSERT

INSERT INTO 테이블(컬럼) VALUES(바꿀값)

insert into employee(Emp_no, name, eng_name, position, salary) values(1, 'John', 'Doe', 'Sales', 50000);

 

 

 employee 테이블에 새로운 직원 'Jane Smith'를 추가하고, 

직원 번호는 '마지막 번호', 영어 이름은 'Smith', 직위는 'Manager', 

성별은 'F', 생일은 '1985-07-19', 입사일은 '2020-05-10', 

주소는 '123 Maple St', 도시는 'New York', 지역은 'NY', 전화번호는 '555-1234', 

상사 번호는 'E5', 부서 번호는 'A2'로 설정하세요.

insert into employee(emp_no, name, eng_name, position, gender, birthday, date_of_emp,address,city,area,telephone,boss_number,Dept_no)
values('E12', 'Jane Smith', 'Smith', 'Manager', 'F', '1985-07-19', '2020-05-10', '123 Maple St', 'New York', 'NY', '555-1234', 'E5', 'A2');

 

 

UPDATE

UPDATE 테이블명 SET 바꿀내용 WHERE 조건

/*
문제 1. employee 테이블에서 직원 번호가 'E02'인 직원의 주소를 '456 Elm St'로 업데이트하세요.
문제 2. products 테이블에서 제품 번호가 21인 제품의 재고를 70으로 업데이트하세요.
문제 3. customer 테이블에서 고객 번호가 'ANRFR'인 고객의 마일리지를 1500으로 업데이트하세요.
*/

update employee set address = '456 Elm St' where Emp_no = 'E02'
update products set inventory = 70 where product_no = 21
update customer set mileage = 1500 where customer_no = 'ANRFR'

 

 

DELETE

DELETE FROM 테이블명 WHERE 조건

DELETE FROM employee WHERE (Emp_no = 'E11');

 

 

INSERT ON DUPLICATE KEY UPDATE

: 레코드가 없다면 새롭게 추가하고, 이미 있다면 데이터를 변경하는 경우에 사용

 

 

---

 

문제 1. emp_no가 E01인 직원의 상사 번호를 'E2'로 변경하시오.

update employee set boss_number = 'E2' where Emp_no = 'E01';

 

문제 2. 특정 기간 동안 주문한 고객들의 마일리지를 업데이트하는 쿼리 고객명 및 마일리지 보이는 쿼리

2020-04-01 ~ 2020-04-10

update customer c 
set c.mileage =(select max(od.unit_price * od.order_quantity * (1 - od.discount_rate))
from orders o
join order_details od on o.order_no = od.order_no
where o.order_date between '2020-04-01' and '2020-04-10')
where c.customer_no in (
select o.customer_no from orders o
where o.order_date between '2020-04-01' and '2020-04-10')

 

 

문제3. 특정 마일리지 이하의 모든 고객을 삭제하세요.

delete from customer where mileage = '50'

 

 

문제4. 특정 도시의 모든 고객을 삭제하세요. (도시: 'San Francisco')

delete from customer where city = 'San Francisco';

 

 

---