SQL 사무용품 관리 시스템 예제

사무용품 관리 예제

 

모델링 기본 틀

 

-- 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**를 조회하세요.
2. 특정 가격 이상의 제품 찾기: Products 테이블에서 가격이 50,000원 이상인 제품의 모든 정보를 조회하세요.
3. 공급업체 연락처 조회하기: Suppliers 테이블에서 모든 공급업체의 name, contact_name, **phone_number**를 조회하세요.
4. 최근 주문 조회하기: SupplyOrders 테이블에서 2023년 1월 1일 이후에 이루어진 모든 주문의 order_id, supplier_id, **order_date**를 조회하세요.
5. 재고 수량이 10개 미만인 제품 조회하기: Inventory 테이블에서 재고 수량이 10개 미만인 모든 제품의 **product_id**와 **quantity**를 조회하세요.

중급 문제 (5문제)

1. 특정 공급업체로부터 제품 주문 조회하기: SupplyOrders 테이블과 Suppliers 테이블을 조인하여, "A 공급업체"로부터의 모든 주문의 주문 ID와 날짜를 조회하세요.
2. 제품별 총 주문량 계산하기: ProductSupplies 테이블에서 각 제품별로 주문된 총 수량을 계산하세요.
3. 주문에 따른 총 비용 계산하기: ProductSupplies 테이블에서 각 주문의 총 비용을 계산하여 주문 ID와 함께 조회하세요. 총 비용은 **quantity**와 **unit_price**의 곱의 합입니다.
4. 재고가 있는 제품 정보 조회하기: Products 테이블과 Inventory 테이블을 조인하여, 재고가 1개 이상인 제품의 모든 정보와 재고 수량을 조회하세요.
5. 특정 날짜에 주문된 모든 제품 조회하기: SupplyOrders 테이블과 ProductSupplies 테이블을 조인하여, 2023년 5월 1일에 주문된 모든 제품의 ID, 수량, 단가를 조회하세요.

고급 문제 (10문제)

1. 최고가 제품 조회하기: Products 테이블에서 가장 비싼 제품의 이름과 가격을 조회하세요.
2. 최소 재고량이 있는 제품 조회하기: Inventory 테이블에서 각 제품의 최소 재고량을 조회하고, 그 중 재고량이 가장 적은 제품의 ID와 재고량을 조회하세요.
3. 공급업체별 최대 주문량 조회하기: ProductSupplies 테이블과 SupplyOrders 테이블, Suppliers 테이블을 조인하여 각 공급업체별로 최대 주문량을 가진 주문의 주문 ID와 그 주문량을 조회하세요.
4. 가장 많이 주문된 제품 조회하기: ProductSupplies 테이블에서 가장 많이 주문된 제품의 ID와 총 주문량을 조회하세요.
5. 특정 공급업체에 대한 모든 주문의 평균 단가 계산하기: ProductSupplies 테이블과 SupplyOrders 테이블을 조인하여, "B 공급업체"에 대한 모든 주문의 평균 단가를 계산하세요.
6. 제품별 평균 단가와 최대 단가 조회하기: ProductSupplies 테이블에서 각 제품의 평균 단가와 최대 단가를 조회하세요.
7. 지난 달 대비 주문 증가율 계산하기: SupplyOrders 테이블에서 지난 달 대비 이번 달의 주문 증가율을 계산하세요. (서브쿼리 사용)
8. 각 공급업체별로 가장 비싼 주문 찾기: ProductSupplies 테이블과 SupplyOrders 테이블을 조인하여, 각 공급업체별로 가장 비싼 주문의 주문 ID와 그 주문의 총 비용을 조회하세요. (서브쿼리 사용)
9. 특정 제품의 총 공급 비용 조회하기: 제품 ID를 기준으로 ProductSupplies 테이블에서 해당 제품의 총 공급 비용을 조회하세요. (집계 함수 사용)
10. 특정 기간 동안 가장 많이 팔린 제품 찾기: ProductSupplies 테이블과 SupplyOrders 테이블을 조인하여, 2023년 1월부터 3월까지 가장 많이 주문된 제품의 ID와 총 주문량을 조회하세요. (서브쿼리와 집계 함수 사용)

 

 

개인적인 풀이

 

-- 초급 1번

SELECT p.name, p.description, p.price

FROM Products p;

 

-- 초급 2번

SELECT *

FROM Products

WHERE price >= 50000;

 

-- 초급 3번

SELECT s.name, s.contact_name, s.phone_number

FROM Suppliers s;

 

-- 초급 4번

SELECT so.order_id , so.supplier_id, so.order_date

FROM SupplyOrders so

WHERE DATE(so.order_date) >= '2023-01-01%';

 

-- 초급 5번

SELECT product_id, quantity

FROM Inventory

WHERE quantity < 10;

 

-- 중급 1번

SELECT so.order_id, so.order_date

FROM SupplyOrders so

JOIN Suppliers s

ON s.supplier_id = so.supplier_id

WHERE s.name like '공급업체 A';

 

-- 중급 2번

SELECT p.name, SUM(ps.quantity)

FROM ProductSupplies ps

JOIN Products p

ON p.product_id = ps.product_id

GROUP BY p.name;

 

-- 중급 3번

SELECT order_id, SUM(quantity * unit_price)

FROM ProductSupplies

GROUP BY order_id;

 

-- 중급 4번

SELECT p.*, i.quantity

FROM Products p

JOIN Inventory i

ON i.product_id = p.product_id

WHERE i.quantity >= 1;

 

-- 중급 5번

SELECT ps.product_id, ps.quantity, ps.unit_price

FROM ProductSupplies ps

JOIN SupplyOrders so

ON so.order_id = ps.order_id

WHERE DATE(so.order_date) LIKE '2023-04-01';

 

-- 고급 1번

SELECT name, price

FROM Products p

WHERE price = (SELECT MAX(PRICE) FROM Products);

 

-- 고급 2번

SELECT sq.product_id, sq.min_quantity

FROM(SELECT product_id, MIN(quantity) as min_quantity FROM Inventory

GROUP BY product_id) as sq

WHERE sq.min_quantity = (SELECT MIN(quantity) FROM Inventory);

 

SELECT i.product_id, i.quantity

FROM Inventory i

WHERE i.quantity = (SELECT MIN(quantity) FROM Inventory);

 

-- 고급 3번

 

SELECT sq.name, sq.orderid, MAX(sq.quan)

FROM(

SELECT s.name as name, ps.order_id as orderid, ps.quantity as quan

FROM Suppliers s

LEFT JOIN SupplyOrders so

ON so.supplier_id = s.supplier_id

LEFT JOIN ProductSupplies ps

ON ps.order_id = so.order_id

WHERE ps.quantity =

(SELECT MAX(quantity) FROM ProductSupplies ps2

WHERE ps2.product_id = ps.product_id)

) as sq

GROUP BY sq.name, sq.orderid;

 

-- 고급 4번

SELECT ps.product_id, SUM(ps.quantity) as quan

FROM ProductSupplies ps

GROUP BY ps.product_id

HAVING quan = (

SELECT MAX(quan2) FROM(

SELECT ps.product_id, SUM(ps.quantity) as quan2

FROM ProductSupplies ps

GROUP BY ps.product_id) as sq);

 

-- 고급 5번

SELECT so.order_id, AVG(ps.quantity * ps.unit_price)

FROM ProductSupplies ps

JOIN SupplyOrders so

ON so.order_id = ps.order_id

JOIN Suppliers s

ON s.supplier_id = so.supplier_id

WHERE s.name like '공급업체 B'

GROUP BY so.order_id;

 

-- 고급 6번

SELECT p.name, AVG(ps.unit_price), MAX(ps.unit_price)

FROM ProductSupplies ps

JOIN Products p

ON p.product_id = ps.product_id

GROUP BY p.name;

 

-- 고급 7번

 

SELECT s.name, so.order_id, SUM(ps.quantity * ps.unit_price) as total_price

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 s.name, so.order_id

HAVING total_price in (SELECT MAX(ps.quantity * ps.unit_price)

FROM ProductSupplies ps

JOIN SupplyOrders so2

ON so2.order_id = ps.order_id

GROUP BY so2.supplier_id);

 

-- 고급 8번

SELECT ps.product_id, SUM(ps.quantity * ps.unit_price)

FROM ProductSupplies ps

GROUP BY ps.product_id;

 

-- 고급 9번

SELECT ps.product_id, SUM(ps.quantity) as max_count

FROM ProductSupplies ps

JOIN SupplyOrders so

ON so.order_id = ps.order_id

WHERE DATE(so.order_date) BETWEEN '2023-04-01' AND '2023-04-19'

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 so

ON so.order_id = ps.order_id

WHERE DATE(so.order_date) BETWEEN '2023-04-01' AND '2023-04-19'

GROUP BY ps.product_id )

as sq

);