서브쿼리
서브쿼리 (Subquery)
서브쿼리는 SQL 문장 내에서 다른 SQL 문장을 포함하는 구조입니다. 서브쿼리를 사용하면 데이터베이스에서 더 복잡한 질의를 수행할 수 있으며, 한 번에 여러 단계의 처리를 수행할 수 있습니다. 서브쿼리는 주로 괄호 ()로 둘러싸여 있으며, 일반적으로 결과를 반환합니다.
서브쿼리의 종류
- 단일 행 서브쿼리: 하나의 행만 반환하는 경우입니다. 비교 연산자와 함께 사용됩니다.
- 다중 행 서브쿼리: 두 개 이상의 행을 반환하는 경우입니다. 연산자로는 IN, ANY, ALL, EXISTS 등이 사용됩니다.
- 다중 열 서브쿼리: 두 개 이상의 열을 반환하는 경우입니다.
서브쿼리의 사용 위치
1. SELECT절
-- Users 테이블에서 사용자 이름과 그들의 대출 건수를 선택하는 메인 쿼리
SELECT
username,
-- 각 사용자별 대출 기록 수를 계산하는 서브쿼리
(SELECT COUNT(*)
FROM LoanRecords
WHERE user_id = Users.user_id) AS loans_count
FROM
Users;
작동 방식:
- 외부 쿼리: 외부 쿼리는 Users 테이블에서 username을 선택합니다. Users 테이블의 각 행에 대해 내부 서브쿼리가 실행됩니다.
- 서브쿼리:
- 이 서브쿼리는 외부 쿼리에서 참조하는 user_id (Users.user_id)를 사용하는 연관 서브쿼리입니다.
- LoanRecords 테이블에서 외부 쿼리의 현재 행 (Users 테이블)의 user_id와 일치하는 user_id의 모든 항목을 계산합니다.
- 이 계산의 결과는 loans_count라는 이름으로 반환되어, 각 사용자의 대출 건수를 나타냅니다.
2. FROM절
-- Users 테이블과 LoanRecords 테이블을 조인하여 사용자별 대출 건수를 조회하는 쿼리
SELECT
user_data.username, -- 사용자 이름 선택
loan_count.loans -- 대출 건수 선택
FROM
Users AS user_data -- Users 테이블을 user_data라는 별칭으로 사용
JOIN
( -- LoanRecords 테이블에서 각 user_id별로 대출 건수를 집계하는 서브쿼리
SELECT
user_id,
COUNT(*) AS loans -- 대출 건수를 계산하여 loans라는 이름으로 반환
FROM
LoanRecords
GROUP BY
user_id -- user_id별로 그룹화
) AS loan_count -- 서브쿼리의 결과에 loan_count라는 별칭 부여
ON
user_data.user_id = loan_count.user_id; -- Users 테이블의 user_id와 서브쿼리 결과의 user_id를 매칭하여 조인
작동 방식:
- 서브쿼리:
- LoanRecords 테이블에서 user_id별로 그룹화하여 각 사용자의 대출 건수를 계산합니다.
- 각 사용자의 user_id와 해당 사용자의 대출 건수(loans)를 포함하는 결과를 생성합니다.
- 외부 쿼리:
- Users 테이블의 데이터를 user_data라는 별칭으로 참조합니다.
- 외부 쿼리는 서브쿼리의 결과(loan_count)와 Users 테이블을 user_id를 기준으로 조인합니다.
- 이 조인을 통해 각 사용자의 이름과 대응하는 대출 건수를 선택하여 결과를 출력합니다.
3. WHERE절
-- Users 테이블에서 사용자 이름을 조회
SELECT
username -- 사용자 이름을 선택
FROM
Users
WHERE
user_id IN ( -- Users 테이블의 user_id가 서브쿼리에서 선택된 user_id에 포함되는 경우만 조회
-- LoanRecords 테이블에서 반납 날짜가 NULL인 대출 기록의 user_id를 선택
SELECT
user_id
FROM
LoanRecords
WHERE
return_date IS NULL -- 반납 날짜가 없는 (즉, 아직 반납되지 않은) 대출 기록을 필터링
);작동 방식:
- 서브쿼리:
- LoanRecords 테이블에서 return_date 필드가 NULL인 행을 찾아냅니다. 이는 아직 책을 반납하지 않은 대출 기록을 의미합니다.
- 이 조건을 만족하는 대출 기록의 user_id들을 선택합니다.
- 외부 쿼리:
- Users 테이블에서 username을 선택합니다.
- WHERE 절의 IN 연산자를 사용하여 서브쿼리에서 얻은 user_id들과 Users 테이블의 user_id를 비교합니다.
- 서브쿼리에서 반환된 user_id에 해당하는 사용자의 이름만 결과로 반환됩니다.
4. HAVING절
-- 대출 기록 테이블에서 사용자별 대출 건수를 계산하고, 이 건수가 전체 사용자의 평균 대출 건수보다 많은 사용자를 조회
SELECT
user_id, -- 사용자 ID
COUNT(*) AS loan_count -- 대출 건수
FROM
LoanRecords
GROUP BY
user_id -- 사용자 ID별로 그룹화
HAVING
COUNT(*) > ( -- 각 사용자의 대출 건수가 서브쿼리에서 계산된 평균 대출 건수보다 클 경우에만 결과를 포함
SELECT
AVG(loan_count) -- 평균 대출 건수 계산
FROM
(SELECT
COUNT(*) AS loan_count -- 각 사용자별 대출 건수
FROM
LoanRecords
GROUP BY
user_id) AS average_loans -- 이중 서브쿼리 구조를 사용하여 각 사용자별 대출 건수를 먼저 계산하고, 그 결과를 기반으로 평균을 계산
);
작동 방식:
- 내부 서브쿼리 (average_loans):
- LoanRecords 테이블에서 user_id별로 그룹화하여 각 사용자의 대출 건수를 계산합니다.
- 이 데이터는 loan_count라는 임시 필드에 저장됩니다.
- 외부 서브쿼리:
- 내부 서브쿼리의 결과를 사용하여 모든 사용자의 대출 건수에 대한 평균값을 계산합니다.
- 이 평균값은 외부 쿼리의 HAVING 절에서 사용됩니다.
- 외부 쿼리:
- LoanRecords 테이블에서 다시 한번 각 사용자별 대출 건수를 계산합니다.
- GROUP BY 절은 사용자별로 결과를 그룹화합니다.
- HAVING 절은 각 그룹의 대출 건수가 계산된 평균 대출 건수보다 큰 경우만 결과에 포함시키도록 합니다.
5. INSERT절
-- LoanRecords 테이블에 새로운 대출 기록을 삽입
INSERT INTO LoanRecords (user_id, book_id, loan_date)
SELECT
user_id,
-- Books 테이블에서 임의로 선택된 한 권의 책의 book_id를 가져옴
(SELECT book_id FROM Books ORDER BY RAND() LIMIT 1),
CURDATE() -- 대출 날짜를 현재 날짜로 설정
FROM
Users
WHERE
-- 현재 반납되지 않은 책을 가진 대출 기록이 없는 사용자만 선택
NOT EXISTS (
SELECT *
FROM LoanRecords
WHERE user_id = Users.user_id AND return_date IS NULL
);작동 방식:
- 서브쿼리 (SELECT book_id FROM Books...):
- 이 부분은 Books 테이블에서 임의의 책 하나를 선택합니다. ORDER BY RAND() 구문은 모든 책 중에서 무작위로 하나를 선택하게 하고, LIMIT 1은 단 하나의 결과만 반환하도록 제한합니다. 이렇게 선택된 책의 book_id는 외부 쿼리의 삽입 과정에 사용됩니다.
- 외부 SELECT 쿼리:
- Users 테이블에서 사용자를 선택합니다. 이때, WHERE 절에 포함된 NOT EXISTS 서브쿼리를 사용하여, 현재 대출 중인 책이 없는(즉, return_date가 NULL인 대출 기록이 없는) 사용자만을 대상으로 합니다. 이는 반납되지 않은 책을 가진 사용자는 새로운 책을 대출받지 못하게 하는 조건을 설정합니다.
- INSERT INTO LoanRecords:
- 외부 쿼리의 결과를 이용하여 LoanRecords 테이블에 새로운 대출 기록을 추가합니다. 각 기록에는 사용자 ID(user_id), 무작위로 선택된 책의 ID(book_id), 그리고 현재 날짜(CURDATE())가 대출 날짜로 설정됩니다.
6. UPDATE절
-- LoanRecords 테이블의 return_date를 업데이트
UPDATE LoanRecords
SET return_date = CURDATE() -- 반납 날짜를 현재 날짜로 설정
WHERE loan_date = (
-- 사용자별로 가장 이른 대출 날짜를 찾는 서브쿼리
SELECT MIN(loan_date)
FROM LoanRecords
WHERE user_id = LoanRecords.user_id -- 외부 쿼리의 사용자 ID와 일치하는 사용자 ID를 가진 대출 기록 중에서
);
작동 방식:
- 서브쿼리 (SELECT MIN(loan_date)...):
- 이 서브쿼리는 LoanRecords 테이블 내에서 각 사용자(user_id)에 대해 가장 이른 loan_date를 찾습니다. 즉, 사용자가 대출한 책들 중 가장 먼저 대출한 날짜를 선택합니다.
- WHERE user_id = LoanRecords.user_id 조건은 서브쿼리가 현재 처리 중인 외부 쿼리의 user_id와 같은 user_id를 가진 대출 기록들만을 대상으로 최소 대출 날짜를 계산하도록 합니다.
- 외부 UPDATE 문:
- LoanRecords 테이블에서 loan_date가 서브쿼리로부터 반환된 최소 loan_date와 일치하는 대출 기록의 return_date를 현재 날짜(CURDATE())로 업데이트합니다.
- 이 작업은 해당 사용자의 가장 오래된 대출 기록을 반납 처리하는 효과를 가집니다.
7. DELETE절
-- LoanRecords 테이블에서 특정 조건을 만족하는 대출 기록을 삭제
DELETE FROM LoanRecords
WHERE
loan_date < ( -- 대출 날짜가 현재 날짜로부터 1년 이전인 대출 기록 선택
SELECT DATE_SUB(CURDATE(), INTERVAL 1 YEAR) -- 현재 날짜에서 1년을 뺀 날짜를 계산
);작동 방식:
- 서브쿼리 (SELECT DATE_SUB(CURDATE(), INTERVAL 1 YEAR)):
- 이 부분은 CURDATE() 함수를 사용해 현재 날짜를 얻고, DATE_SUB 함수를 통해 1년 전 날짜를 계산합니다. DATE_SUB 함수는 첫 번째 인자로 주어진 날짜에서 두 번째 인자로 지정된 시간 간격을 빼서 결과 날짜를 반환합니다.
- DELETE 문:
- LoanRecords 테이블에서 loan_date가 1년 전 날짜보다 이른 모든 기록을 찾아 삭제합니다.
- 이는 데이터베이스에서 오래된 대출 기록을 제거하여 데이터 관리를 최적화하고, 저장 공간을 효율적으로 사용할 수 있도록 돕습니다.
연관 서브쿼리와 비연관 서브쿼리
1. 비연관 서브쿼리: 외부 쿼리와 독립적으로 실행되는 서브쿼리입니다.
-- Users 테이블에서 사용자 이름을 조회
SELECT
username -- 사용자의 이름을 선택
FROM
Users
WHERE
user_id IN ( -- Users 테이블의 user_id가 서브쿼리에서 반환된 user_id 목록에 포함되는 경우만 조회
-- LoanRecords 테이블에서 반납되지 않은 책을 가진 대출 기록의 user_id를 선택하는 서브쿼리
SELECT
user_id
FROM
LoanRecords
WHERE
return_date IS NULL -- 반납 날짜가 없는(즉, 아직 반납되지 않은) 대출 기록을 필터링
);
작동 방식:
- 서브쿼리:
- LoanRecords 테이블을 조회하여 return_date가 NULL인 행을 찾습니다. 이는 아직 책을 반납하지 않은 대출 기록을 의미합니다.
- 이 조건을 만족하는 대출 기록의 user_id를 선택합니다. 즉, 현재 책을 대출 중인 모든 사용자의 ID를 찾아냅니다.
- 외부 SELECT 쿼리:
- Users 테이블에서 user_id가 서브쿼리로부터 반환된 user_id 목록에 속하는 사용자의 username을 선택합니다.
- 이를 통해 현재 책을 대출하고 있는 모든 사용자의 이름이 조회됩니다.
2. 연관 서브쿼리: 외부 쿼리와 연결되어 외부 쿼리의 각 행마다 반복해서 실행됩니다.
-- Users 테이블에서 사용자 이름을 조회
SELECT
username -- 사용자의 이름을 선택
FROM
Users u -- Users 테이블에 'u'라는 별칭을 사용
WHERE
EXISTS ( -- 서브쿼리가 결과를 반환하는 경우에만 해당 사용자를 선택
-- LoanRecords 테이블에서 조건을 만족하는 대출 기록을 검색
SELECT *
FROM LoanRecords lr -- LoanRecords 테이블에 'lr'라는 별칭을 사용
WHERE lr.user_id = u.user_id AND return_date IS NULL -- 반납 날짜가 없고, 현재 Users 테이블의 사용자 ID와 일치하는 대출 기록을 찾음
);작동 방식:
- 서브쿼리 (SELECT * FROM LoanRecords lr ...):
- LoanRecords 테이블을 조회하며, lr.user_id = u.user_id 조건을 통해 현재 처리 중인 Users 테이블의 사용자(u.user_id)와 같은 user_id를 가진 대출 기록을 찾습니다.
- 추가적으로, return_date IS NULL 조건을 사용하여 아직 반납되지 않은 책을 가진 대출 기록만을 필터링합니다.
- EXISTS는 이 서브쿼리가 최소 하나 이상의 행을 반환하면 참(true)을 반환하며, 그 결과로 외부 쿼리의 WHERE 조건을 만족시킵니다.
- 외부 SELECT 쿼리:
- Users 테이블에서 username을 선택하며, 해당 사용자가 EXISTS 조건에 의해 식별된 사용자일 경우에만 결과에 포함됩니다.
예제 5: 사무용품 관리 시스템
-- Products 테이블 생성
-- 이 테이블은 판매하는 모든 제품의 정보를 저장합니다.
-- 각 제품은 고유 ID, 이름, 설명, 가격을 가지고 있습니다.
CREATE TABLE Products (
);
-- Suppliers 테이블 생성
-- 이 테이블은 모든 공급업체의 정보를 저장합니다.
-- 각 공급업체는 고유 ID, 이름, 담당자 이름, 전화번호를 가지고 있습니다.
CREATE TABLE Suppliers (
);
-- SupplyOrders 테이블 생성
-- 이 테이블은 모든 공급 주문 정보를 저장합니다.
-- 주문은 고유 ID, 공급업체 ID, 주문 날짜, 배송 날짜를 가지고 있습니다.
CREATE TABLE SupplyOrders (
);
-- ProductSupplies 테이블 생성
-- 이 테이블은 각 주문의 제품 공급 정보를 저장합니다.
-- 각 레코드는 고유 ID, 제품 ID, 주문 ID, 수량, 단가를 가지고 있습니다.
CREATE TABLE ProductSupplies (
);
-- Inventory 테이블 생성
-- 이 테이블은 모든 제품의 재고 수량을 저장합니다.
-- 각 레코드는 고유 ID, 제품 ID, 재고 수량을 가지고 있습니다.
CREATE TABLE Inventory (
);
- 테이블 생성 & 더미 데이터
-- Products 테이블 생성
-- 이 테이블은 판매하는 모든 제품의 정보를 저장합니다.
-- 각 제품은 고유 ID, 이름, 설명, 가격을 가지고 있습니다.
CREATE TABLE Products (
product_id INT AUTO_INCREMENT PRIMARY KEY, -- 제품의 고유 식별자, 자동 증가
name VARCHAR(100) NOT NULL, -- 제품 이름, 필수 입력
description TEXT, -- 제품 설명
price DECIMAL(10, 2) NOT NULL -- 제품 가격, 소수점 두 자리까지 허용, 필수 입력
);
-- Suppliers 테이블 생성
-- 이 테이블은 모든 공급업체의 정보를 저장합니다.
-- 각 공급업체는 고유 ID, 이름, 담당자 이름, 전화번호를 가지고 있습니다.
CREATE TABLE Suppliers (
supplier_id INT AUTO_INCREMENT PRIMARY KEY, -- 공급업체의 고유 식별자, 자동 증가
name VARCHAR(100) NOT NULL, -- 공급업체 이름, 필수 입력
contact_name VARCHAR(100), -- 담당자 이름
phone_number VARCHAR(15) -- 전화번호
);
-- SupplyOrders 테이블 생성
-- 이 테이블은 모든 공급 주문 정보를 저장합니다.
-- 주문은 고유 ID, 공급업체 ID, 주문 날짜, 배송 날짜를 가지고 있습니다.
CREATE TABLE SupplyOrders (
order_id INT AUTO_INCREMENT PRIMARY KEY, -- 주문의 고유 식별자, 자동 증가
supplier_id INT NOT NULL, -- 공급업체 ID, Suppliers 테이블의 supplier_id 참조
order_date DATE NOT NULL, -- 주문 날짜, 필수 입력
delivery_date DATE, -- 배송 예정 날짜
FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id) -- 외래 키로 Suppliers 테이블의 supplier_id 참조
);
-- ProductSupplies 테이블 생성
-- 이 테이블은 각 주문의 제품 공급 정보를 저장합니다.
-- 각 레코드는 고유 ID, 제품 ID, 주문 ID, 수량, 단가를 가지고 있습니다.
CREATE TABLE ProductSupplies (
product_supply_id INT AUTO_INCREMENT PRIMARY KEY, -- 제품 공급의 고유 식별자, 자동 증가
product_id INT NOT NULL, -- 제품 ID, Products 테이블의 product_id 참조
order_id INT NOT NULL, -- 주문 ID, SupplyOrders 테이블의 order_id 참조
quantity INT NOT NULL, -- 공급된 제품 수량, 필수 입력
unit_price DECIMAL(10, 2) NOT NULL, -- 단위 가격, 소수점 두 자리까지 허용, 필수 입력
FOREIGN KEY (product_id) REFERENCES Products(product_id),
FOREIGN KEY (order_id) REFERENCES SupplyOrders(order_id)
);
-- Inventory 테이블 생성
-- 이 테이블은 모든 제품의 재고 수량을 저장합니다.
-- 각 레코드는 고유 ID, 제품 ID, 재고 수량을 가지고 있습니다.
CREATE TABLE Inventory (
inventory_id INT AUTO_INCREMENT PRIMARY KEY, -- 재고의 고유 식별자, 자동 증가
product_id INT NOT NULL, -- 제품 ID, Products 테이블의 product_id 참조
quantity INT NOT NULL, -- 재고 수량, 필수 입력
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
INSERT INTO Products (name, description, price) VALUES
('제품 1', '제품 설명 1', 15000.00),
('제품 2', '제품 설명 2', 30000.00),
('제품 3', '제품 설명 3', 45000.00),
('제품 4', '제품 설명 4', 50000.00),
('제품 5', '제품 설명 5', 75000.00),
('제품 6', '제품 설명 6', 90000.00),
('제품 7', '제품 설명 7', 110000.00),
('제품 8', '제품 설명 8', 130000.00),
('제품 9', '제품 설명 9', 150000.00),
('제품 10', '제품 설명 10', 200000.00);
INSERT INTO Suppliers (name, contact_name, phone_number) VALUES
('공급업체 A', '담당자 A', '010-1234-5678'),
('공급업체 B', '담당자 B', '010-2345-6789'),
('공급업체 C', '담당자 C', '010-3456-7890'),
('공급업체 D', '담당자 D', '010-4567-8901'),
('공급업체 E', '담당자 E', '010-5678-9012'),
('공급업체 F', '담당자 F', '010-6789-0123'),
('공급업체 G', '담당자 G', '010-7890-1234'),
('공급업체 H', '담당자 H', '010-8901-2345'),
('공급업체 I', '담당자 I', '010-9012-3456'),
('공급업체 J', '담당자 J', '010-0123-4567');
INSERT INTO SupplyOrders (supplier_id, order_date, delivery_date) VALUES
(1, '2023-04-01', '2023-04-05'),
(2, '2023-04-03', '2023-04-07'),
(3, '2023-04-05', '2023-04-09'),
(4, '2023-04-07', '2023-04-11'),
(5, '2023-04-09', '2023-04-13'),
(6, '2023-04-11', '2023-04-15'),
(7, '2023-04-13', '2023-04-17'),
(8, '2023-04-15', '2023-04-19'),
(9, '2023-04-17', '2023-04-21'),
(10, '2023-04-19', '2023-04-23');
INSERT INTO ProductSupplies (product_id, order_id, quantity, unit_price) VALUES
(1, 1, 20, 15000.00),
(2, 2, 30, 30000.00),
(3, 3, 40, 45000.00),
(4, 4, 50, 50000.00),
(5, 5, 60, 75000.00),
(6, 6, 70, 90000.00),
(7, 7, 80, 110000.00),
(8, 8, 90, 130000.00),
(9, 9, 100, 150000.00),
(10, 10, 110, 200000.00);
INSERT INTO Inventory (product_id, quantity) VALUES
(1, 100),
(2, 90),
(3, 80),
(4, 70),
(5, 60),
(6, 50),
(7, 40),
(8, 30),
(9, 20),
(10, 10);초급 문제 (5문제)
1. 모든 제품 조회하기: Products 테이블에서 모든 제품의 name, description, price를 조회하세요.
SELECT p.name, p.description, p.price
FROM Products p;
2. 특정 가격 이상의 제품 찾기: Products 테이블에서 가격이 50,000원 이상인 제품의 모든 정보를 조회하세요.
SELECT p.*
FROM Products p
WHERE p.price >= 50000;
3. 공급업체 연락처 조회하기: Suppliers 테이블에서 모든 공급업체의 name, contact_name, phone_number를 조회하세요.
SELECT s.name, s.contact_name, s.phone_number
FROM Suppliers s;
4. 최근 주문 조회하기: SupplyOrders 테이블에서 2023년 1월 1일 이후에 이루어진 모든 주문의 order_id, supplier_id, order_date를 조회하세요.
SELECT so.order_id, so.supplier_id, so.order_date
FROM SupplyOrders so
WHERE so.order_date > '2023-01-01';
5. 재고 수량이 10개 미만인 제품 조회하기: Inventory 테이블에서 재고 수량이 10개 미만인 모든 제품의 product_id와 quantity를 조회하세요.
SELECT i.product_id, i.quantity
FROM Inventory i
WHERE i.quantity < 10;
중급 문제 (5문제)
1. 특정 공급업체로부터 제품 주문 조회하기: SupplyOrders 테이블과 Suppliers 테이블을 조인하여, "A 공급업체"로부터의 모든 주문의 주문 ID와 날짜를 조회하세요.
SELECT s.name, s.supplier_id, so.order_id, so.order_date
FROM Suppliers s
JOIN SupplyOrders so
ON so.supplier_id = s.supplier_id
WHERE s.name = '공급업체 A';
2. 제품별 총 주문량 계산하기: ProductSupplies 테이블에서 각 제품별로 주문된 총 수량을 계산하세요.
SELECT p.name, SUM(ps.quantity)
FROM Products p
JOIN ProductSupplies ps
ON p.product_id = ps.product_id
GROUP BY p.name;
3. 주문에 따른 총 비용 계산하기: ProductSupplies 테이블에서 각 주문의 총 비용을 계산하여 주문 ID와 함께 조회하세요. 총 비용은 quantity와 unit_price의 곱의 합입니다.
SELECT ps.order_id, SUM(ps.quantity * ps.unit_price) as total_price
FROM ProductSupplies ps
GROUP BY ps.order_id;
4. 재고가 있는 제품 정보 조회하기: Products 테이블과 Inventory 테이블을 조인하여, 재고가 1개 이상인 제품의 모든 정보와 재고 수량을 조회하세요.
SELECT p.*
FROM Products p
JOIN Inventory i
ON p.product_id = i.product_id
WHERE i.quantity >= 1;
5. 특정 날짜에 주문된 모든 제품 조회하기: SupplyOrders 테이블과 ProductSupplies 테이블을 조인하여, 2023년 5월 1일에 주문된 모든 제품의 ID, 수량, 단가를 조회하세요.
SELECT ps.product_id, ps.quantity, ps.unit_price
FROM ProductSupplies ps
JOIN SupplyOrders so
ON ps.order_id = so.order_id
WHERE so.order_date = '2023-05-01';
고급 문제 (9문제)
1. 최고가 제품 조회하기: Products 테이블에서 가장 비싼 제품의 이름과 가격을 조회하세요.
SELECT p.name, p.price
FROM Products p
WHERE p.price = (
SELECT MAX(p.price)
FROM Products p
);
2. 최소 재고량이 있는 제품 조회하기: Inventory 테이블에서 각 제품의 최소 재고량을 조회하고, 그 중 재고량이 가장 적은 제품의 ID와 재고량을 조회하세요.
SELECT i.product_id, i.quantity
FROM Products p
JOIN Inventory i
ON i.product_id = p.product_id
WHERE i.quantity = (
SELECT MIN(quantity) as min_quantity
FROM Inventory i
);
3. 공급업체별 최대 주문량 조회하기: ProductSupplies 테이블과 SupplyOrders 테이블, Suppliers 테이블을 조인하여 각 공급업체별로 최대 주문량을 가진 주문의 주문 ID와 그 주문량을 조회하세요.
SELECT q.name, MAX(q.cnt)
FROM (
SELECT sq.name, sq.cnt
FROM (
SELECT s.name, so.order_id, SUM(ps.quantity) as cnt
FROM Suppliers s
JOIN SupplyOrders so
ON so.supplier_id = s.supplier_id
JOIN ProductSupplies ps
ON ps.order_id = so.order_id
GROUP BY order_id
) as sq
)as q
GROUP BY q.name
ORDER BY q.name;
4. 가장 많이 주문된 제품 조회하기: ProductSupplies 테이블에서 가장 많이 주문된 제품의 ID와 총 주문량을 조회하세요.
SELECT ps.product_id, SUM(ps.quantity) as max_quantity
FROM ProductSupplies ps
GROUP BY ps.product_id
HAVING max_quantity = (
SELECT MAX(sq.cnt)
FROM (
SELECT ps.product_id, SUM(ps.quantity) as cnt
FROM ProductSupplies ps
GROUP BY ps.product_id
) as sq
);
5. 특정 공급업체에 대한 모든 주문의 평균 단가 계산하기: ProductSupplies 테이블과 SupplyOrders 테이블을 조인하여, "B 공급업체"에 대한 모든 주문의 평균 단가를 계산하세요.
SELECT so.order_id, AVG(ps.quantity * ps.unit_price)
FROM SupplyOrders so
JOIN ProductSupplies ps
ON so.order_id = ps.order_id
JOIN Suppliers s
ON s.supplier_id = so.supplier_id
WHERE s.name = '공급업체 A'
GROUP BY so.order_id;
6. 제품별 평균 단가와 최대 단가 조회하기: ProductSupplies 테이블에서 각 제품의 평균 단가와 최대 단가를 조회하세요.
SELECT ps.product_id, AVG(ps.unit_price), MAX(ps.unit_price)
FROM ProductSupplies ps
GROUP BY ps.product_id;
7. 각 공급업체별로 가장 비싼 주문 찾기: ProductSupplies 테이블과 SupplyOrders 테이블을 조인하여, 각 공급업체별로 가장 비싼 주문의 주문 ID와 그 주문의 총 비용을 조회하세요. (서브쿼리 사용)
SELECT so.supplier_id, ps.order_id, SUM(ps.unit_price * ps.quantity) as total_price
FROM SupplyOrders so
JOIN ProductSupplies ps
ON ps.order_id = so.order_id
GROUP BY so.supplier_id, ps.order_id
HAVING (so.supplier_id, total_price) IN (
SELECT s_id, MAX(t_price)
FROM (
SELECT s_id, t_price
FROM (
SELECT so.supplier_id as s_id, SUM(ps.unit_price * ps.quantity) as t_price
FROM SupplyOrders so
JOIN ProductSupplies ps
ON ps.order_id = so.order_id
GROUP BY so.supplier_id, ps.order_id
) as sq
) as q
GROUP BY s_id
);
8. 특정 제품의 총 공급 비용 조회하기: 제품 ID를 기준으로 ProductSupplies 테이블에서 해당 제품의 총 공급 비용을 조회하세요. (집계 함수 사용)
SELECT ps.product_id, SUM(ps.quantity * ps.unit_price) as total_price
FROM ProductSupplies ps
WHERE ps.product_id = 1
GROUP BY ps.product_id;
9. 특정 기간 동안 가장 많이 팔린 제품 찾기: ProductSupplies 테이블과 SupplyOrders 테이블을 조인하여, 2023년 1월부터 3월까지 가장 많이 주문된 제품의 ID와 총 주문량을 조회하세요. (서브쿼리와 집계 함수 사용)
SELECT ps.product_id , SUM(ps.quantity) as max_count
FROM productsupplies ps
JOIN supplyorders s
ON s.order_id = ps.order_id
WHERE s.order_date BETWEEN '2023-01-01' AND '2023-04-01'
GROUP BY ps.product_id
HAVING max_count = (
SELECT max(cnt)
FROM (
SELECT ps.product_id , sum(ps.quantity) as cnt
FROM productsupplies ps
JOIN supplyorders s
ON s.order_id = ps.order_id
WHERE s.order_date BETWEEN '2023-01-01' AND '2023-04-01'
GROUP BY ps.product_id
) as sq
);
