☑️ 주문량이 많은 아이스크림들 조회
1. MySQL
Every derived table must have its own alias: MySQL에서 서브쿼리 사용 시 해당 테이블에 별칭을 지정해줘야 한다.
SELECT FLAVOR
FROM (SELECT F.FLAVOR, SUM(F.TOTAL_ORDER + J.TOTAL_ORDER ) AS SUM
FROM FIRST_HALF F LEFT JOIN JULY J
ON F.FLAVOR = J.FLAVOR
GROUP BY F.FLAVOR
ORDER BY SUM DESC) A
LIMIT 3;2. Oracle
- Oracle에서는 서브쿼리에 별칭을 따로 지정하지 않아도 된다.
- 상위 3개의 데이터를 출력해야 하므로
ROWNUM이 3 이하인 레코드를 출력해주면 된다.
SELECT FLAVOR
FROM (SELECT F.FLAVOR, SUM(F.TOTAL_ORDER + J.TOTAL_ORDER ) AS SUM
FROM FIRST_HALF F LEFT JOIN JULY J
ON F.FLAVOR = J.FLAVOR
GROUP BY F.FLAVOR
ORDER BY SUM DESC)
WHERE ROWNUM <= 3;☑️ 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
1. MySQL
- WHERE절에서는 SELECT절에서 사용한 ailas 사용 불가능하고, HAVING절에서는 가능하다! (MySQL / Oracle)
IN(A,B, C, ...):A, B, C,...에 해당하는 데이터 출력NOT IN(A,B, C, ...):A, B, C,...에 해당하지 않는 데이터 출력
SELECT CAR_ID, C.CAR_TYPE,
ROUND((DAILY_FEE * (1-(DISCOUNT_RATE/100)) * 30)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단', 'SUV')
AND CAR_ID NOT IN (SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(START_DATE, '%Y-%m-%d') <= '2022-11-30'
AND DATE_FORMAT(END_DATE, '%Y-%m-%d') >= '2022-11-01')
AND DURATION_TYPE = '30일 이상'
HAVING FEE BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, C.CAR_TYPE, CAR_ID DESC;2. Oracle
SELECT CAR_ID, C.CAR_TYPE,
(DAILY_FEE * (1-(DISCOUNT_RATE/100)) * 30) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단', 'SUV')
AND CAR_ID NOT IN (SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE TO_CHAR(START_DATE, 'YYYYMMDD') <= '20221130'
AND TO_CHAR(END_DATE, 'YYYYMMDD') >= '20221101')
AND DURATION_TYPE = '30일 이상'
AND (DAILY_FEE * (1-(DISCOUNT_RATE/100)) * 30)
BETWEEN 500000 AND 2000000
ORDER BY 3 DESC, 2, 1 DESC;☑️ 5월 식품들의 총매출 조회하기
1. MySQL
SELECT P.PRODUCT_ID, PRODUCT_NAME, SUM(PRICE * AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT P JOIN FOOD_ORDER O
ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE MONTH(PRODUCE_DATE) = 5
GROUP BY P.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID;2. Oracle
SELECT P.PRODUCT_ID, PRODUCT_NAME, SUM(PRICE * AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT P JOIN FOOD_ORDER O
ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE EXTRACT(MONTH FROM PRODUCE_DATE) = 5
GROUP BY P.PRODUCT_ID, PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID;☑️ 조건에 맞는 도서와 저자 리스트 출력하기
1. MySQL
SELECT BOOK_ID, AUTHOR_NAME,
DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISED_DATE
FROM BOOK B JOIN AUTHOR A
ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE CATEGORY = '경제'
ORDER BY PUBLISED_DATE;2. Oracle
SELECT BOOK_ID, AUTHOR_NAME,
TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISED_DATE
FROM BOOK B JOIN AUTHOR A
ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE CATEGORY = '경제'
ORDER BY PUBLISED_DATE;☑️ 그룹별 조건에 맞는 식당 목록 출력하기
1. MySQL
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery': LIMIT와 IN/ALL/ANY/SOME 연산자를 함께 사용할 수 없다.
SELECT MEMBER_NAME, REVIEW_TEXT,
DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE M JOIN REST_REVIEW R
ON M.MEMBER_ID = R.MEMBER_ID
WHERE R.MEMBER_ID = (SELECT MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY COUNT(*) DESC
LIMIT 1)
ORDER BY REVIEW_DATE, REVIEW_TEXT;2. Oracle
SELECT MEMBER_NAME, REVIEW_TEXT,
TO_CHAR(REVIEW_DATE, 'YYYY-MM-DD') AS REVIEW_DATE
FROM MEMBER_PROFILE M JOIN REST_REVIEW R
ON M.MEMBER_ID = R.MEMBER_ID
WHERE M.MEMBER_ID IN (SELECT MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM REST_REVIEW
GROUP BY MEMBER_ID))
ORDER BY 3, 2;☑️ 없어진 기록 찾기
1. MySQL / 2. Oracle
SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (SELECT I.ANIMAL_ID
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID)
ORDER BY ANIMAL_ID;☑️ 있었는데요 없었습니다
1. MySQL / 2. Oracle
SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME > O.DATETIME
ORDER BY I.DATETIME;☑️ 오랜 기간 보호한 동물(1)
1. MySQL
SELECT NAME, DATETIME
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID
FROM ANIMAL_OUTS)
ORDER BY DATETIME
LIMIT 3;2. Oracle
SELECT *
FROM (SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME)
WHERE ROWNUM <= 3;☑️ 보호소에서 중성화한 동물
1. MySQL / 2. Oracle
SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE LIKE 'Intact%'
AND (O.SEX_UPON_OUTCOME LIKE 'Spayed%'
OR O.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY I.ANIMAL_ID;☑️ 상품 별 오프라인 매출 구하기
1. MySQL
SELECT P.PRODUCT_CODE, (SUM(SALES_AMOUNT) * PRICE) AS SALES
FROM PRODUCT P JOIN OFFLINE_SALE O
ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY P.PRODUCT_CODE
ORDER BY SALES DESC, P.PRODUCT_CODE;2. Oracle
SELECT P.PRODUCT_CODE, (SUM(SALES_AMOUNT) * PRICE) AS SALES
FROM PRODUCT P JOIN OFFLINE_SALE O
ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY P.PRODUCT_CODE, PRICE
ORDER BY 2 DESC, 1;☑️ 상품을 구매한 회원 비율 구하기
1. MySQL
SELECT YEAR, MONTH, COUNT(*) AS PUCHASED_USERS,
ROUND(COUNT(*)/
(SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021), 1)
AS PUCHASED_RATIO
FROM (SELECT DISTINCT YEAR(SALES_DATE) AS YEAR,
MONTH(SALES_DATE) AS MONTH, U.USER_ID
FROM USER_INFO U JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
AND YEAR(JOINED) = 2021) A
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;2. Oracle
SELECT YEAR, MONTH, COUNT(*) AS PUCHASED_USERS,
ROUND(COUNT(*)/
(SELECT COUNT(*) FROM USER_INFO
WHERE EXTRACT(YEAR FROM JOINED) = 2021), 1)
AS PUCHASED_RATIO
FROM (SELECT DISTINCT EXTRACT(YEAR FROM SALES_DATE) AS YEAR,
EXTRACT(MONTH FROM SALES_DATE) AS MONTH, U.USER_ID
FROM USER_INFO U JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
AND EXTRACT(YEAR FROM JOINED) = 2021)
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;
백엔드 개발자👩🏻💻가 되고 싶다