2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 카테고리명을 기준으로 오름차순 정렬해주세요.
✅ 문제 풀이
BOOK 테이블과 BOOK_SALES 테이블의 공통 속성인 BOOK_ID를 기준으로 INNER JOIN 해준다.
FROM BOOK A JOIN BOOK_SALES B ON A.BOOK_ID=B.BOOK_ID
이 중에서 판매 날짜인 SALES_DATE가 2022-01-01과 2022-01-31 안에 있는 것만 WHERE 절로 추린다.
WHERE B.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
카테고리 별로 묶어준다.
GROUP BY A.CATEGORY
문제에서 CATEGORY 당 판매량 총합을 구하라고 했으니, CATEGORY 별로 SALES의 SUM을 구한다. 이름은 TOTAL_SALES로 출력한다.
SELECT A.CATEGORY, SUM(B.SALES) AS TOTAL_SALES
조회결과를 CATEGORY에 대해 오름차순 정렬해준다.
ORDER BY CATEGORY;
✏ 코드 전문
SELECT A.CATEGORY, SUM(B.SALES) AS TOTAL_SALES
FROM BOOK A JOIN BOOK_SALES B ON A.BOOK_ID=B.BOOK_ID
WHERE B.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY A.CATEGORY
ORDER BY CATEGORY;
FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.
✅ 문제 풀이
FOOD_PRODUCT와 FOOD_ORDER 테이블을 JOIN한다.
FOOD_PRODUCT A JOIN FOOD_ORDER B
ON A.PRODUCT_ID = B.PRODUCT_ID
두 테이블의 공통 속성인 PRODUCT_ID를 기준으로 JOIN한다.
생산일자가 2022년 5월인 식품을 추린다.
WHERE PRODUCE_DATE>='2022-05-01' AND PRODUCE_DATE<='2022-05-31'
제품 당 총매출을 구해야 하기 때문에, 제품 ID를 기준으로 그룹화한다.
GROUP BY A.PRODUCT_ID
조회할 정보를 가져온다.
SELECT A.PRODUCT_ID, A.PRODUCT_NAME, SUM(A.PRICE*B.AMOUNT) AS TOTAL_SALES
현재 PRODUCT_ID로 그룹화 되어 있기 때문에, SUM을 통해 같은 그룹 내 상품들의 매출을 더하여 총매출을 구할 수 있다.
조건에 따라 정렬한다.
ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC;
총매출을 기준으로 내림차순정렬,식품 ID를 기준으로 오름차순정렬 해준다.
✏ 코드 전문
SELECT A.PRODUCT_ID, A.PRODUCT_NAME, SUM(A.PRICE*B.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT A JOIN FOOD_ORDER B
ON A.PRODUCT_ID = B.PRODUCT_ID
WHERE PRODUCE_DATE>='2022-05-01' AND PRODUCE_DATE<='2022-05-31'
GROUP BY A.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC;
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.
문제의 조건
CAR_TYPE 이 '세단' 또는 'SUV'
2022-11-01 부터 2022-11-30 까지 대여가능
30일간의 대여금액 >=500000 AND <2000000
위를 만족하는 자동차의 CAR_ID, CAR_TYPE, FEE 출력
단, 대여금액 기준 내림차순, 자동차 종류 기준 오름차순, 자동차 ID기준 내림차순 정렬
✅ 문제 풀이
대여 가능 여부를 판단한다. HISTORY 테이블에서 START_DATE와 END_DATE가 있다. 11월 달에 대여한 적이 있는지를 보려면, START_DATE는 11월 30일 이전이어야 하고, END_DATE는 11월 1일 이후여야 한다. SQL문으로 나타내면 다음과 같다.
CAR_RENTAL_COMPANY_RENTAL_HISTORY C
WHERE C.START_DATE<='2022-11-30' AND C.END_DATE>='2022-11-01'
그럼 이 날짜에 RENTAL한 적이 있는 자동차는 제외하면 된다.
이렇게 생각할 수도 있다. 11월 달에 대여한 적 없는 자동차만 고르면 되는 거 아닌가? 로직을 어떻게 짜냐에 다르긴 하지만, 단순하게 각 레코드 마다 11월달에 대여한 적 없는 경우를 고른다면, 11월에도 대여했었지만 다른 날짜에도 대여했던 동일한 자동차도 포함되어 버릴 수 있기 때문에, 11월에 대여한 자동차의 ID 자체를 제외하는 식으로 접근하였다. 이는 NOT IN 연산자를 통해 제외할 수 있다.
A.CAR_ID NOT IN (
SELECT DISTINCT C.CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C
WHERE C.START_DATE<='2022-11-30' AND C.END_DATE>='2022-11-01'
)
첫 WHERE 조건을 하나 완성하였다.
30일간의 대여금액을 계산하기 위해 테이블을 조인한다. 이를 구하기 위해서는 CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블을 INNER 조인해주어야 한다. 그래서 각 자동차마다 CAR_TYPE에 따라 DISCOUNT 정보를 갖도록 할것이다.
FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
ON A.CAR_TYPE=B.CAR_TYPE
두 테이블의 공통 속성인 CAR_TYPE을 기준으로 JOIN한다.
조건에 따라 WHERE 절에 조건을 추가로 작성해준다.
AND A.CAR_TYPE IN('SUV', '세단')
AND B.DURATION_TYPE = '30일 이상'
AND A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)>=500000
AND A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)<2000000
CAR_TYPE이 'SUV', '세단' 중에 해당하면 조회한다. IN 연산자를 사용하였다.
조인 후에는 각 자동차 마다 CAR_TYPE에 따라 3개의 DISCOUNT_RATE 정보가 엮여있을 것이다. 이 중에서 DURATION_TYPE이 '30일 이상' 인것 만 사용하도록 한다.
대여금액은 조건에 해당하는 자동차의 DAILY_FEE*30*할인률 이기 때문에, 이 값이 50만원 이상 200만원 미만인 것만 추리도록 한다.
필요한 정보를 출력한다.
SELECT A.CAR_ID, A.CAR_TYPE, CAST(A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)AS SIGNED) AS FEE
위에 조건에 부합하는 자동차의 CAR_ID, CAR_TYPE, FEE를 조회하도록 한다.
이때 FEE의 경우 *0.01 때문에 소수점 이하 두자리 형태로 출력되게 되는데, 조건의 주의사항에서 FEE의 경우 정수 형태로 출력하도록 되어있기 때문에, CAST 연산자를 사용하여 정수형태로 출력하도록 한다. AS SIGNED는 소수점을 없애면서 부호는 그대로 유지한다는 것이다.
조회결과를 주어진 정렬 조건에 맞도록 설정하기
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;
마지막에 ORDER BY 문을 통해서 FEE를 기준으로 내림차순, CAR_TYPE을 기준으로 오름차순, CAR_ID를 기준으로 내림차순 정렬하도록 해준다.
✏ 코드 전문
SELECT A.CAR_ID, A.CAR_TYPE, CAST(A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)AS SIGNED) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
ON A.CAR_TYPE=B.CAR_TYPE
WHERE A.CAR_ID NOT IN (
SELECT DISTINCT C.CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C
WHERE C.START_DATE<='2022-11-30' AND C.END_DATE>='2022-11-01'
)
AND A.CAR_TYPE IN('SUV', '세단')
AND B.DURATION_TYPE = '30일 이상'
AND A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)>=500000
AND A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)<2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;
> JPA는 "Java Persistence API"의 약자로, Java에서 데이터베이스를 관리하는 표준 인터페이스를 제공하는 API입니다. JPA는 객체와 관계형 데이터베이스 간의 매핑을 처리하고, 객체 지향 프로그래밍(OOP) 언어인 Java와 관계형 데이터베이스 간의 상호 작용을 간소화하는 목적으로 만들어졌습니다.
= 즉, db의 데이터를 가져올 수 있도록 하는 매개체 역할이다.
= 자바의 객체와, 데이터베이스 간의 데이터를 매칭해준다.
build.gradle 파일에 들어가서 dependencies 부분에 아래와 같이 코드를 추가해준다.
원래는 spring_web이랑 test 밖에 없었으나, 가운데 두 종속성을 추가해주었다.
위는 jpa, 아래는 mysql에 대한 종속성이다.
💡 MYSQL 연동을 위한 정보를 가진 yml 파일 생성하기
yml 파일은 애플리케이션의 설정 정보를 가진다.
resource 디렉토리 하에 application.yml 파일을 생성하고 데이터베이스 연결 정보를 추가해준다.
# database 연동 설정
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
# 각자 PC에 만들어놓은 Database이름을 써야 합니다.
url: jdbc:mysql://localhost:3306/cloud_vendor?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
# mysql에 생성한 사용자 계정 정보를 써야 합니다.
username: 자신의 db username
password: 자신의 db pw
thymeleaf:
cache: false
# spring data jpa 설정
jpa:
database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
open-in-view: false
show-sql: true
hibernate:
ddl-auto: create //초기 설정 시 create로 하고 나중에 db가 완전해지면, update로 바꿔서 사용
# use-new-id-generator-mappings: false
위는 yml에 들어가는 코드이다.
datesource의 url에서 cloud_vendor는 이 프로젝트에서 사용하는 db 이름이고, 내 db에 미리 만들어져 있어야 한다.
아래 username과 password는 자신의 db에 맞게 넣어주면 된다.
아래 jpa에서 ddl-auto는 create로 두면 entity로 둔 table이 생성되고, update로 되면 변경사항만 변경된다.
초기에는 보통 create로 둔다.
💡 application controller 정상 실행되는지 확인하기
build.gradle을 수정하고 난 뒤에는 코끼리 모양을 눌러 다시 build해주어야 변경 사항이 적용된다.
yml 파일을 추가했으므로, 한번 application controller를 실행해 보아서 문제가 없는지 확인한다.
💡 문제) USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.
회원 ID, 닉네임, 총거래금액을 조회 SELECT B.USER_ID, B.NICKNAME, SUM(A.PRICE) AS TOTAL_SALES 문제에서 주어진 쿼리 결과대로 총거래금액의 출력 시 이름을 TOTAL_SALES로 바꾸어준다.
USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 테이블 두개가 연결되어야 함을 알 수 있다. 테이블을 보면 USER_ID에 대해서 중복된 것을 알 수 있다 (컬럼이름은 각 테이블에서 다르지만, 결국은 USER의 아이디를 가리키고 있음) 따라서 두 테이블을 유저 아이디에 대한 조건으로 INNER JOIN시켜야 한다. 그래야 각 상품 목록에 대해서 유저의 정보를 함께 출력하는 테이블을 얻을 수 있기 때문이다. FROM USED_GOODS_BOARD A JOIN USED_GOODS_USER B ON A.WRITER_ID=B.USER_ID
완료된 중고 거래의 위의 2번까지를 통해 각 상품에 대한 유저 정보까지 합친 테이블을 얻었으니, 이제 그 중에서도 완료된 거래에 대해서만 추려야 한다. 그러러면 WHERE 절을 이용해서 STATUS가 DONE인 것만을 가져올 수 있다. WHERE A.STATUS="DONE"
총금액이 70만 원 이상인 사람의 위의 과정까지는 각 상품에 대해서 개별적으로 조회되기 때문에, 한 사람의 전체 거래 내역을 고려하기 위해서는 사람의 단위로 GROUP을 지어야 한다. 따라서 GROUP BY 절을 통해 USER_ID 별로 그룹화 시킨다. 그러면 거래가 완료된 상품의 거래 주인의 아이디, 이름, 총거래금액이 출력되는데, 이 중에서도 조건에서 총거래금액이 70만원 이상인 사람의 것만 출력하라고 되어 있다. 그러므로 GROUP BY 절에 HAVING을 추가해서 그 그룹 중 총거래금액이 70만원이상인 것만 출력하도록 해준다. GROUP BY B.USER_ID HAVING TOTAL_SALES>=700000
결과는 총거래금액을 기준으로 오름차순 정렬해주세요. (나는 이 단계를 빼먹고 채점 결과가 자꾸 틀렸다고 떠서 시간을 낭비했다. 꼭 정렬 조건을 확인하도록 하자) 이제 조건에 만족하는 결과는 다 출력했다. 마지막 조건인 정렬을 해준다. ORDER BY절을 통해 해줄 수 있다. ORDER BY TOTAL_SALES;
위 다섯 단계를 거치면서 하나하나씩 접근하면 매우 쉬운 문제이다!
조인, WHERE절, GROUP BY절, ORDER BY절이 다 쓰인 문제라서, 순서를 한번 정리하고 넘어가자
SELECT > FROM > JOIN-ON > WHERE > GROUP BY - HAVING > ORDER BY
