-- 코드를 입력하세요
SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE,'%Y-%m-%d') AS START_DATE, DATE_FORMAT(END_DATE,'%Y-%m-%d') AS END_DATE,
CASE WHEN END_DATE - START_DATE >= 90 THEN "장기 대여"
ELSE "단기 대여" END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE (MONTH(START_DATE) = 9) AND (YEAR(START_DATE) = 2022)
ORDER BY HISTORY_ID DESC
case when 으로 비트 마스킹 후 min/max함수를 이용해 이용 가능한 차량 출력하기
3. 풀이
# car_id 기준으로 한번이라도 0이 등장하면 못빌리는 자동차
select car_id, case when possible = 1 then '대여 가능' else '대여중' end as AVAILABILITY
from(
select car_id,
min(possible) as possible
from
(
select car_id,
DATE_FORMAT(start_date,'%Y-%m-%d'), DATE_FORMAT(end_date,'%Y-%m-%d'),
case when start_date <= '2022-10-16' and end_date >= '2022-10-16' then 0
else 1 end as possible # 해당하는 기간에 16일이 껴있으면 0(불가), 1(가능)
from car_rental_company_rental_history
order by car_id asc
) t1
group by car_id
) t2
order by car_id desc
SELECT B.WRITER_ID AS USER_ID,
U.NICKNAME, CONCAT(U.STREET_ADDRESS1," ",U.STREET_ADDRESS2) AS 전체주소,
CONCAT(SUBSTR(U.TLNO,1,3),"-",SUBSTR(U.TLNO,4,4),"-",SUBSTR(U.TLNO,8,4)) AS 전화번호
FROM USED_GOODS_BOARD AS B JOIN USED_GOODS_USER AS U ON B.WRITER_ID=U.USER_ID
GROUP BY B.WRITER_ID
HAVING COUNT(*) >= 3
ORDER BY B.WRITER_ID DESC
정답
select distinct(u.USER_ID), u.NICKNAME,
concat(CITY,' ',STREET_ADDRESS1,' ',STREET_ADDRESS2) as 전체주소,
concat(left(TLNO,3),'-',substr(TLNO,4,4),'-',right(TLNO,4)) as 전화번호
from USED_GOODS_BOARD as b
join USED_GOODS_USER as u on b.WRITER_ID = u.USER_ID
where b.WRITER_ID in (select WRITER_ID
from USED_GOODS_BOARD
group by WRITER_ID
having count(WRITER_ID) >= 3)
order by user_id desc
위 오답과 정답 둘 다 예시 테이블에서는 적어도 같은 테이블로 출력된다. 하지만 채점하면 에러가 난다. 그 이유는 프로그래머스에서 채점하는 테이블이 예시 테이블이 아닌 채점 테이블이 따로 있기 때문이다. 오답의 경우 두 테이블을 조인하여 having count(*)을 이용하여 필터링을 했는데 다음과 같은 상황에 엣지 케이스가 있다.
"동일한 유저가 판매글을 2개 올리고, 주소가 2개 인 경우" 이 경우 문제에 따르면 중고 거래 게시물을 3건 이상 등록한 것이 아닌 2건이지만 오답처럼 join과정에서 4개로 뻥튀기되고 count(*)로 집계하는 과정에서 의도치 않은 필터링이 된다. 따라서 정답처럼 사전에 서브쿼리로 writer_id를 선별해놓고 필터링 하는게 적절하다고 할 수 있겠다.
인 오류도 생각할 수 있으나.. 그냥 오답에 CITY 컬럼이 누락됐다. 너무 복잡하게 생각했네 🤣
price의 가격이 상한선이 정해져 있지 않아서, 8만원까지 하드 코딩 하거나, resursive 문법으로 해결
SELECT
CASE
WHEN (PRICE/10000) < 1 THEN 0
WHEN (PRICE/10000) < 2 THEN 10000
WHEN (PRICE/10000) < 3 THEN 20000
WHEN (PRICE/10000) < 4 THEN 30000
WHEN (PRICE/10000) < 5 THEN 40000
WHEN (PRICE/10000) < 6 THEN 50000
WHEN (PRICE/10000) < 7 THEN 60000
WHEN (PRICE/10000) < 8 THEN 70000
WHEN (PRICE/10000) < 9 THEN 80000
END AS PRICE_GROUP,
COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP ASC
MySQL은 데이터베이스의 한 종류로, 가장 범용적인 데이터베이스입니다. 일반적으로 회사에 데이터베이스에 구축되어 있으면 따로 로컬컴퓨터에 설치하지 않고 SQL WorkBench, DBeaver등 어플리케이션 툴을 이용하여 접속하면 되기 때문에 설치할 일이 없습니다. 반면 개인 학습 혹은 서버를 띄운 상태에서 데이터베이스를 설치하고자 한다면 이 가이드를 통해 로컬에 MySQL 데이터베이스를 설치할 수 있습니다.
Last Updated
2024-06-26: Custom 설치방법 추가
2024-11-26: Initializing Database 오류 방법추가
2025-05-19: Trouble shooting 추가
1. MySQL 설치
MySQL을 설치하는 것은 설치파일을 실행 후 "다음", "다음"의연속입니다. 중요한 점은 중간에 mysql 접속을 위한 비밀번호 설정과 mysql 포트(지하철로 따지면 출구번호)가 3306으로 사전 지정이 잘 되어있는지 확인하면 됩니다. 이를 통해 MySQL을 로컬 컴퓨터에 설치할 수 있고 추후 Database에 접속할 수 있는 Command Line 혹은 DBeaver와 같은 Application을 통해서 테이블을 생성하고 SQL쿼리를 실행할 수 있게 됩니다.
연결할 수 있는 다양한 Database를 지원합니다. 우리는 MySQL을 설치했기 때문에 해당하는 것을 클릭합니다.
MySQL 설치시 설정한 Password를 넣어줍니다. Username은 기본적으로 root로 설정되어있어서 변경하지 않아도 됩니다.
3306 포트의 localhost가 연결된 것을 확인 할 수 있습니다.
4. 나의 데이터베이스 생성하기
localhost의 좌측 꺽쇄를 누르니 sys 라는 Database밖에 조회되지 않았습니다. 기본적으로 필수적이지 않은 Database는 숨겨진 채로 DBeaver에서 조회할 수 없네요. 또한, sys 이름으로 유추하건데 시스템 정보에 관한 데이터베이스므로, 테이블을 생성하는 등 작업에 적합하지 않아 보여 새로운 데이터베이스를 생성해보겠습니다.
문제1) ORDER_TOTAL이 500 이상인 주문의 ORDER_ID와 ORDER_TOTAL 값을 구하세요.
SELECT ORDER_ID, ORDER_TOTAL
FROM OE.orders
WHERE ORDER_TOTAL >= 500;
문제2) ORDER_STATUS가 “Entered” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요. (힌트 Schema를 참조)
SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS = 1;
문제3) ORDER_STATUS가 “Cancled” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요.
SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS in (2,3);
문제4) ORDER_STATUS가 “Shipped” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요.
SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS not in (0,1,2,3);
문제5)SALES_REP_ID가 153인 모든 주문의 ORDER_ID, ORDER_DATE, 및 ORDER_TOTAL 값을 구하세요.
SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL
FROM oe.orders
WHERE SALES_REP_ID = 5;
문제6) ORDER_MODE가 'online'인 주문 중, ORDER_TOTAL이 2000 이하인 주문의 ORDER_ID와 CUSTOMER_ID 값을 구하세요.
SELECT ORDER_ID, CUSTOMER_ID
FROM oe.orders
WHERE ORDER_MODE = 'online' AND ORDER_TOTAL <= 2000;
2. GROUP / HAVING 문제
문제1) OE.ORDERS 테이블에서 ORDER_STATUS 별로 주문 건수를 구하세요.
SELECT ORDER_STATUS, COUNT(*) as NUMBER_OF_ORDERS
FROM OE.ORDERS
GROUP BY ORDER_STATUS;
문제2) ORDER_TOTAL의 합계가 10,000 이상인 CUSTOMER_ID를 구하세요.
SELECT CUSTOMER_ID, SUM(ORDER_TOTAL) as TOTAL_AMOUNT
FROM OEorders
GROUP BY CUSTOMER_ID
HAVING SUM(ORDER_TOTAL) >= 10000;
문제3): 각 SALES_REP_ID별로 처리한 주문의 평균 ORDER_TOTAL 값을 구하세요.
SELECT SALES_REP_ID, AVG(ORDER_TOTAL) as AVERAGE_ORDER_TOTAL
FROM OE.orders
GROUP BY SALES_REP_ID;
문제4) 3번의 결과에서 평균 ORDER_TOTAL이 500 이상인 SALES_REP_ID를 구하세요.
SELECT SALES_REP_ID, AVG(ORDER_TOTAL) as AVERAGE_ORDER_TOTAL
FROM OE.orders
GROUP BY SALES_REP_ID
HAVING AVG(ORDER_TOTAL) >= 500;
문제5) ORDER_MODE별로 주문된 ORDER_TOTAL의 합계를 구하세요.
SELECT ORDER_MODE, SUM(ORDER_TOTAL) as TOTAL_AMOUNT
FROM OE.orders
GROUP BY ORDER_MODE;
문제6) CUSTOMER_ID별로 주문한 횟수가 5회 이상인 고객들의 ID를 구하세요.
SELECT CUSTOMER_ID, COUNT(*)
FROM OE.orders
GROUP BY CUSTOMER_ID
HAVING COUNT(*) >= 5;
문제7) 각 SALES_REP_ID별로 처리한 주문 중 ORDER_TOTAL이 100 이상인 주문의 건수를 구하세요.
SELECT SALES_REP_ID, COUNT(*) as NUMBER_OF_ORDERS
FROM OE.orders
WHERE ORDER_TOTAL >= 100
GROUP BY SALES_REP_ID;
3. built-in 함수 문제 1
문제 1) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)이 50000 이상인 주문들에 대해 주문 모드(ORDER_MODE)를 기반으로 'direct'인 경우 '직접 주문', 그렇지 않은 경우 '일반 주문'으로 출력하세요.
SELECT ORDER_ID, DECODE(ORDER_MODE, 'direct', '직접 주문', '일반 주문') AS ORDER_TYPE
FROM OE.ORDERS
WHERE ORDER_TOTAL >= 50000;
문제 2) OE.ORDERS 테이블에서 주문 상태(ORDER_STATUS)를 기반으로 상태 값이 0인 경우 '진행 중', 1인 경우 '완료', 2,3인 경우 '취소'로 출력하세요. 나머지는 ‘기타’로 출력하세요.
SELECT ORDER_ID, ORDER_STATUS, CASE ORDER_STATUS
WHEN 0 THEN '진행 중'
WHEN 1 THEN '완료'
WHEN 2 THEN '취소'
WHEN 3 THEN '취소'
ELSE '기타' END AS STATUS_DESC
FROM OE.ORDERS;
문제 3) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)의 절대값을 출력하세요.
SELECT ORDER_ID, ABS(ORDER_TOTAL) AS ABS_TOTAL
FROM OE.ORDERS;
문제 4) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)이 양수인지 음수인지를 판별하여 결과를 출력하세요.
SELECT ORDER_ID, SIGN(ORDER_TOTAL) AS SIGN_STATUS
FROM OE.ORDERS;
문제 5) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)을 100으로 나눈 나머지를 출력하세요.
SELECT ORDER_ID, ORDER_TOTAL, MOD(ORDER_TOTAL, 100) AS MOD_RESULT
FROM OE.ORDERS;
문제 6) OE.ORDERS 테이블에서 주문 날짜(ORDER_DATE)의 년도만을 출력하세요.
SELECT ORDER_ID, ORDER_DATE, TO_CHAR(ORDER_DATE, 'YYYY') AS ORDER_YEAR
FROM OE.ORDERS;
문제7) OE.ORDERS 테이블에서 ORDERS_STATUS 별로 그룹지은 후 ORDER_TOTAL 의 값을 평균을 내세요. 해당 테이블을 with 절을 이용하여 temp1 에 저장한 후 전체 temp1 테이블을 출력하세요.
WITH TEMP1 AS
(SELECT ORDER_STATUS, ROUND(AVG(ORDER_TOTAL))
FROM OE.ORDERS GROUP BY ORDER_STATUS)
SELECT * FROM TEMP1
문제8) 당신은 ORDE_STATUS 별로 전체 매출액을 알고 싶다. 다만, 현재 취소건 등 고객들의 클레임이 많은 것을 확인하였다. 얼마나 취소로 인한 손해가 많은지 알고 싶다. 해당 쿼리를 하단에 작성하라.
-- order_status 2,3 취소건 -> 취소건만 order_total
-- order_status 0 ~ 10 까지있는데, 카테고리화 / 퍼센트(분율) 로 보여주면 더 구체적으로 판단할수있다.
-- 0. 쓸 컬럼을 order_total, order_status
-- 1. case문을 이용해서 order_status 카테고리화 할예정.
-- 2. order_status 별로 그룹을 지어 sum(order_total)
with temp1 as(
select order_total,
case
when order_status = 0 then '주문미완료'
when order_status = 1 then '주문완료'
when order_status in (2,3) then '주문취소'
else '배송중'
end as os_cat
from oe.orders)
select os_cat, sum(order_total), round(sum(order_total)/3668054.7*100)
from temp1
group by os_cat
4. DML/DDL 문제
문제 1) 새로운 테이블 NEW_EMPLOYEES를 만들어 주세요. 이 테이블에는 EMP_ID, EMP_NAME, EMP_SALARY 세 개의 컬럼이 있어야 합니다.
EMP_ID는 숫자 타입이며, 기본 키로 설정해주세요.
EMP_NAME은 최대 50자의 문자열 타입으로 설정해주세요.
EMP_SALARY는 숫자 타입으로 설정해주세요.
CREATE TABLE NEW_EMPLOYEES (
EMP_ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(50),
EMP_SALARY NUMBER
);
INSERT ALL
INTO orders VALUES (1, '김영희', '노트북', 1, TO_DATE('2023-10-01', 'YYYY-MM-DD'))
INTO orders VALUES (2, '박철수', '키보드', 2, TO_DATE('2023-10-02', 'YYYY-MM-DD'))
INTO orders VALUES (3, '이영수', '마우스', 1, TO_DATE('2023-10-03', 'YYYY-MM-DD'))
INTO orders VALUES (4, '최민영', '모니터', 2, TO_DATE('2023-10-04', 'YYYY-MM-DD'))
INTO orders VALUES (5, '황영준', '노트북', 1, TO_DATE('2023-10-05', 'YYYY-MM-DD'))
INTO orders VALUES (6, '김영미', '키보드', 1, TO_DATE('2023-10-06', 'YYYY-MM-DD'))
INTO orders VALUES (7, '박진수', '마우스', 3, TO_DATE('2023-10-07', 'YYYY-MM-DD'))ㄷㄷ
INTO orders VALUES (8, '이민철', '모니터', 1, TO_DATE('2023-10-08', 'YYYY-MM-DD'))
INTO orders VALUES (9, '최영주', '노트북', 1, TO_DATE('2023-10-09', 'YYYY-MM-DD'))
INTO orders VALUES (10, '황지수', '키보드', 2, TO_DATE('2023-10-10', 'YYYY-MM-DD'))
SELECT * FROM DUAL;
문제8-3) 모든 주문 정보를 조회하되, 수량이 3 이상인 경우 '대량 주문'이라는 문자열을, 그렇지 않은 경우 '일반 주문'이라는 문자열을 새로운 컬럼 order_type에 표시하여 출력하세요.
SELECT order_id, customer_name, product_name, quantity, order_date,
CASE WHEN quantity >= 3 THEN '대량 주문'
ELSE '일반 주문'
END AS order_type
FROM orders;
문제8-4): 제품별로 총 주문 수량을 계산하고, 주문 수량이 많은 순으로 정렬하여 출력하세요.
with temp1 as (SELECT order_id, customer_name, product_name, quantity, order_date,
CASE WHEN quantity >= 3 THEN '대량 주문'
ELSE '일반 주문'
END AS order_type
FROM orders)
SELECT order_type, SUM(quantity) as total_quantity
FROM temp1
GROUP BY order_type
ORDER BY total_quantity DESC;;
5. 조인문제 1차
문제 1) OE.ORDERS 테이블에서 주문 상태(ORDER_STATUS)가 '1'인 모든 주문을 조회하십시오.
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1;
문제 2) OE.ORDERS와 OE.CUSTOMERS 테이블을 INNER JOIN을 사용하여 결합하고, 이를 기반으로 주문 상태(ORDER_STATUS)가 '0'인 고객의 이름 (CUST_FIRST_NAME, CUST_LAST_NAME)과 주문 총액(ORDER_TOTAL)을 조회하십시오.
SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.ORDER_STATUS = 0;
문제 3) OE.CUSTOMERS 테이블을 기준으로 OE.ORDERS 테이블과 LEFT JOIN을 하여, 주문 정보가 없는 고객의 이름 (CUST_FIRST_NAME, CUST_LAST_NAME)을 조회하십시오.
SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM OE.CUSTOMERS C
LEFT JOIN OE.ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
WHERE O.CUSTOMER_ID IS NULL;
문제 4) OE.ORDERS와 OE.CUSTOMERS 테이블을 INNER JOIN을 사용하여 결합하고, 주문 총액(ORDER_TOTAL)이 10,000 이상이며, 고객의 성별(GENDER)이 'M'인 모든 주문을 조회하십시오.
SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.ORDER_TOTAL >= 10000 AND C.GENDER = 'M';
문제 5) FULL OUTER JOIN 이용한 조회 OE.ORDERS와 OE.CUSTOMERS 테이블을 FULL OUTER JOIN을 사용하여 결합하고, 주문 정보나 고객 정보 중 하나라도 없는 모든 레코드를 조회하십시오.
SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
FULL OUTER JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.CUSTOMER_ID IS NULL OR C.CUSTOMER_ID IS NULL;
6. 조인문제 2차
문제 1) ORDER_DATE와 관련된 JOIN OE.ORDERS 테이블과 OE.ORDER_ITEMS 테이블을 JOIN하여 ORDER_DATE, PRODUCT_ID 및 QUANTITY 정보를 가져와보세요.
SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID;
SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
natural JOIN OE.ORDER_ITEMS I ;
SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I using(order_id)
문제 2) 특정 조건에 따른 집계 ORDER_STATUS가 1인 주문에 대하여 총 주문 금액(UNIT_PRICE * QUANTITY)을 계산하세요.
SELECT O.ORDER_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_ORDER_AMOUNT
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE O.ORDER_STATUS = 1
문제 3) ORDER_MODE가 'direct'이며 ORDER_STATUS가 0인 주문에 대해 총 주문된 제품 수(QUANTITY)를 계산하세요.
SELECT O.ORDER_ID, SUM(I.QUANTITY) AS TOTAL_QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE O.ORDER_MODE = 'direct' AND O.ORDER_STATUS = 0
문제 4) 각 SALES_REP_ID별로 주문된 제품의 총 가격(UNIT_PRICE * QUANTITY의 합)을 조회하고, 주문 총 가격이 높은 순으로 정렬하세요.
SELECT O.SALES_REP_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_SALES
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
GROUP BY O.SALES_REP_ID
ORDER BY TOTAL_SALES DESC;
문제 5) 2008년에 이루어진 주문과 관련된 모든 PRODUCT_ID와 해당 PRODUCT_ID의 총 판매 금액을 조회하세요.
SELECT O.ORDER_DATE, I.PRODUCT_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_AMOUNT
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE TO_CHAR(O.ORDER_DATE,'YYYY-MM-DD') BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY O.ORDER_DATE,I.PRODUCT_ID;
7. 집합 문제
문제1) 주문 상태(ORDER_STATUS)가 1과 2인 주문을 합쳐서 출력해보세요. (UNION을 이용)
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1
UNION
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 2;
문제2) 주문 상태(ORDER_STATUS)가 1인 주문과 ORDER_TOTAL이 50000을 초과하는 주문을 모두 출력하세요. 단, 중복된 주문이 있을 수 있습니다. (UNION ALL을 이용)
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1
UNION ALL
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;
문제3) 주문 상태(ORDER_STATUS)가 1인 주문과 ORDER_TOTAL이 50000을 초과하는 공통된 주문을 찾아 출력하세요. (INTERSECT를 이용)
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1
INTERSECT
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;
문제4) 주문 상태(ORDER_STATUS)가 1인 주문 중에서 ORDER_TOTAL이 50000 초과하는 주문을 제외하고 출력하세요. (MINUS를 이용
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1
MINUS
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;
8. SubQuery 문제
문제1)총 주문 금액이 가장 높은 주문의 금액을 조회하시오.
SELECT *
FROM OE.ORDERS WHERE ORDER_TOTAL = (SELECT MAX(ORDER_TOTAL) FROM OE.ORDERS);
문제2)2007년에 주문한 모든 고객의 평균 주문 금액을 조회하시오.
SELECT CUSTOMER_ID, AVG(ORDER_TOTAL) AS AVG_TOTAL
FROM (SELECT CUSTOMER_ID, ORDER_TOTAL FROM OE.ORDERS WHERE TO_CHAR(ORDER_DATE, 'YYYY') = '2007') GROUP BY CUSTOMER_ID;
문제3)SALES_REP_ID가 155인 판매 대표의 평균 주문 금액보다 높은 주문들만 조회하시오.
SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS
WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM OE.ORDERS WHERE SALES_REP_ID = 155);
문제4) IN 사용: 주문 금액이 10,000 이상 50,000 이하인 CUSTOMER_ID의 모든 주문을 조회하시오.
SELECT ORDER_ID, CUSTOMER_ID, ORDER_TOTAL FROM OE.ORDERS
WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM OE.ORDERS WHERE ORDER_TOTAL BETWEEN 10000 AND 50000);
문제5) ALL 사용: 모든 판매 대표의 평균 주문 금액보다 높은 주문들만 조회하시오.
SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS
WHERE ORDER_TOTAL > ALL (SELECT AVG(ORDER_TOTAL) FROM OE.ORDERS GROUP BY SALES_REP_ID);
문제6)ANY 사용:주문 상태가 1 또는 3인 주문보다 주문 금액이 높은 주문들을 조회하시오.
SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS
WHERE ORDER_TOTAL > ANY (SELECT ORDER_TOTAL FROM OE.ORDERS WHERE ORDER_STATUS IN (1,3))
9. GROUPING 문제
문제1) OE.ORDERS 테이블을 이용하여 ORDER_MODE, ORDER_STATUS 그룹짓고 모든 subtotal과 총합을 생성하세요.
SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY CUBE (ORDER_MODE, ORDER_STATUS);
문제2) Grouping sets 으로 OE.ORDERS 테이블을 이용하여 ORDER_MODE, ORDER_STATUS 과 전체 total을 생성하세요.
SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY GROUPING SETS ((ORDER_MODE), (ORDER_STATUS), ());
-- 두 컬럼을 모두 넣으면 모든행 출력
문제3) ORDER_MODE로 그룹화한 후, 각 ORDER_MODE 내에서 SALES_REP_ID로 추가 그룹화하여 주문 총액을 반환합니다. 또한 ORDER_MODE에 대한 subtotal와 Grand total도 반환하세요
SELECT ORDER_MODE, SALES_REP_ID, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY ROLLUP (ORDER_MODE, SALES_REP_ID);
문제4) 문제 3처럼 ORDER_MODE와 ORDER_STATUS의 각 조합에 대한 주문 총액을 반환하며, GROUPING 함수를 사용하여 어떤 열이 집계된 결과인지를 0과 1로 나타내세요.
SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL),
GROUPING(ORDER_MODE) AS GRP_MODE,
GROUPING(ORDER_STATUS) AS GRP_STATUS
FROM OE.ORDERS
GROUP BY ROLLUP (ORDER_MODE, ORDER_STATUS);
10. Window function 문제
문제1) 모든 주문에 대한 주문 총액(ORDER_TOTAL)의 합계를 출력하세요.
SELECT ORDER_ID, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER() AS TOTAL_SUM
FROM OE.ORDERS;
SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(ORDER BY ORDER_DATE) AS CUMULATIVE_TOTAL
FROM OE.ORDERS;
문제4) 주문 모드(ORDER_MODE)별로 그룹화하고, 주문 날짜(ORDER_DATE)에 따라 주문 총액과 해당 주문 모드 및 날짜까지의 누적 합계를 반환하세요.
SELECT ORDER_ID, ORDER_MODE, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(PARTITION BY ORDER_MODE ORDER BY ORDER_DATE) AS CUMULATIVE_MODE_TOTAL
FROM OE.ORDERS;
문제5) 문제: 주문 날짜(ORDER_DATE)에 따라 주문 총액 누적합계와 해당 주문 날짜를 포함한 최근 7일 동안의 누적 합계를 반환하세요.
Hint) BETWEEN INTERVAL ‘7’ DAY PRECEDING AND CURRENT ROW
SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(ORDER BY ORDER_DATE RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW) AS WEEKLY_CUMULATIVE_TOTAL
FROM OE.ORDERS;
문제6) 각 판매 대표(SALES_REP_ID)에 대한 주문 총액(ORDER_TOTAL)과 해당 판매 대표의 평균 주문 총액을 반환하세요.
SELECT ORDER_ID, SALES_REP_ID, ORDER_TOTAL,
AVG(ORDER_TOTAL) OVER(PARTITION BY SALES_REP_ID) AS AVG_SALES_BY_REP
FROM OE.ORDERS;
데이터 베이스를 조작할때 자주 나오는 단어가 DDL, DML 이다.Data Definition Language (DDL), Data Manipulatation Language(DML), Data Control Language(DCL)이다. 다양한 SQL 언어를 사용하다 보면 헷갈리니까 mysql 기준으로 한번 정리해보자.
1. Data Definition Language(DDL)
DDL 란 데이터베이스를 정의하는 언어이며 데이터가 들어오는 조건(제약조건), 데이터의 타입등을 정의하는 언어이다. 쉽게말하면 데이터를 수집하기 위한 껍데기 혹은 틀을 생성하는 것이며, Database 설계시 가장 먼저 수행한다. 다만, 사용하는 Tool(DBeaver 등)에 대해서 지원하는 파일(csv) 혹은 API 형식(Google Bigquery)에 따라 자동으로 데이터 type를 잡아주는 것들도 있다.
CREATE: 테이블을 만들기 위한 기본 statement로, 데이터 타입, 제약조건을 설정한다.
자주 헷갈리는 TRUNCATE, DROP, DELETE 비교. 사실상 TRUNCATE와 DELETE를 비교해보면 쉽다.
TRUNCATE
DROP
DELETE
범주
DDL
DDL
DML
where와 사용가능
X
X
O
성능
빠름 (transaction log 1번)
-
느림 (각 행마다 transaction log 기록)
ROLLBACK
불가
불가
가능
테이블 용량
초기화
-
용량 감소 없음
2. Data Manipulatation Language(DML)
DML 란 기존에 생성된 Database를 조작하는 것으로 기본적으로 조회하는 select이 가장 많이 쓰인다.
SELECT
INSERT
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ... }
-- 사용례
-- 전체 컬럼에 해당하는 모든 값을 넣을 때
INSERT INTO 테이블이름
VALUES (데이터값1, 데이터값2, 데이터값3, ...)
-- 특정 컬럼의 값을 넣을 때
INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...)
VALUES (데이터값1, 데이터값2, 데이터값3, ...)
UPDATE
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
-- 사용례
UPDATE 테이블이름
SET 컬럼명1=데이터값1
WHERE 컬럼명2=데이터값
DELETE
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
-- 사용례
DELETE FROM 테이블명 WHERE 조건;
3. Data Control Language(DCL)
DCL이란 데이터베이스의 권한 관리를 위한 명령어이다. 데이터베이스는 무결성과 정합성이 매우 중요하기 때문에, 데이터 변환,삭제 등에 대한 관리를 엄격하게 한다. 이를 위해서 특정 user의 수행권한을 관리하는 것이라고 이해하면 된다. mysql에서는Account Manger Statements라고 분류된다.
GRANT
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
REVOKE
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
