[프로그래머스(Programmers)][SQL] (Lv2)

 

SELECT

 

< 3월에 태어난 여성 회원 목록 출력하기 >

생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.

-- MySql
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH,'%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE GENDER = 'W' AND MONTH(DATE_OF_BIRTH) = 3 AND TLNO IS NOT NULL
ORDER BY MEMBER_ID;

-- Oracle
SELECT MEMBER_ID, MEMBER_NAME, GENDER, TO_CHAR(DATE_OF_BIRTH,'YYYY-MM-DD') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE GENDER = 'W' AND EXTRACT(MONTH FROM DATE_OF_BIRTH) = 3 AND TLNO IS NOT NULL
ORDER BY MEMBER_ID;

 

< 재구매가 일어난 상품과 회원 리스트 구하기 >

동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(1) > 1
ORDER BY USER_ID, PRODUCT_ID DESC;

 

< 특정 물고기를 잡은 총 수 구하기 >

FISH_INFO 테이블에서 잡은 BASS와 SNAPPER의 수를 출력하는 SQL 문을 작성해주세요.
컬럼명은 'FISH_COUNT`로 해주세요.

SELECT  COUNT(1) AS FISH_COUNT
FROM    FISH_INFO I JOIN FISH_NAME_INFO N USING(FISH_TYPE)
WHERE   FISH_NAME IN('BASS','SNAPPER')

 

<업그레이드 된 아이템 구하기>  (25.02.10)

아이템의 희귀도가 'RARE'인 아이템들의 모든 다음 업그레이드 아이템의 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME), 아이템의 희귀도(RARITY)를 출력하는 SQL 문을 작성해 주세요. 이때 결과는 아이템 ID를 기준으로 내림차순 정렬주세요.

SELECT  ITEM_ID, ITEM_NAME, RARITY
FROM    ITEM_INFO A
WHERE   EXISTS (
    SELECT  1
    FROM    ITEM_INFO I, ITEM_TREE T
    WHERE   I.RARITY = 'RARE' 
    	AND I.ITEM_ID = T.PARENT_ITEM_ID
    	AND A.ITEM_ID = T.ITEM_ID
)
ORDER BY ITEM_ID DESC;

<조건에 맞는 개발자 찾기>  (25.02.10)

DEVELOPERS 테이블에서 Python이나 C# 스킬을 가진 개발자의 정보를 조회하려 합니다. 조건에 맞는 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요.
결과는 ID를 기준으로 오름차순 정렬해 주세요.

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM   DEVELOPERS D
WHERE  EXISTS (
    SELECT 1 
    FROM   SKILLCODES S 
    WHERE  NAME IN ('Python','C#') 
      AND  D.SKILL_CODE & S.CODE
)
ORDER BY ID;

 

< 부모의 형질을 모두 가지는 대장균 찾기 >  (25.02.10)

부모의 형질을 모두 보유한 대장균의 ID(ID), 대장균의 형질(GENOTYPE), 부모 대장균의 형질(PARENT_GENOTYPE)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 ID에 대해 오름차순 정렬해주세요.

SELECT    C.ID, C.GENOTYPE, P.GENOTYPE AS PARENT_GENOTYPE
FROM      ECOLI_DATA C, ECOLI_DATA P
WHERE     C.PARENT_ID = P.ID
    AND   C.GENOTYPE & P.GENOTYPE = P.GENOTYPE
ORDER BY  C.ID

 

 

IS NULL

 

< ROOT 아이템 구하기 >  (25.02.07)

ROOT 아이템을 찾아 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME)을 출력하는 SQL문을 작성해 주세요. 이때, 결과는 아이템 ID를 기준으로 오름차순 정렬해 주세요.

SELECT I.ITEM_ID, I.ITEM_NAME 
FROM ITEM_INFO I JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID 
WHERE T.PARENT_ITEM_ID IS NULL 
ORDER BY I.ITEM_ID;

 

 

SUM, MAX, MIN

 

< 가격이 제일 비싼 식품의 정보 출력하기 >

가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회하는 SQL문을 작성해주세요.

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

------------------------------------------------------------------

-- MySql
SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1;

--Oracle
SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
OFFSET 0 ROW FETCH FIRST 1 ROW ONLY;

 

< 조건에 맞는 아이템들의 가격의 총합 구하기 >   (25.02.07)

ITEM_INFO 테이블에서 희귀도가 'LEGEND'인 아이템들의 가격의 총합을 구하는 SQL문을 작성해 주세요. 이때 컬럼명은 'TOTAL_PRICE'로 지정해 주세요.

SELECT SUM(PRICE) AS TOTAL_PRICE FROM ITEM_INFO WHERE RARITY = 'LEGEND';

 

< 연도별 대장균 크기의 편차 구하기 >  (25.02.10)

분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 분화된 연도별 대장균 크기의 편차는 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기로 구하며 결과는 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬해주세요.

WITH E AS (
    SELECT ID, SIZE_OF_COLONY, YEAR(DIFFERENTIATION_DATE) AS DIFFERENTIATION_YEAR
    FROM ECOLI_DATA
)
SELECT E.DIFFERENTIATION_YEAR AS YEAR, M.MAX_SIZE_OF_COLONY - E.SIZE_OF_COLONY AS YEAR_DEV, E.ID
FROM E
    , (
        SELECT DIFFERENTIATION_YEAR, MAX(SIZE_OF_COLONY) AS MAX_SIZE_OF_COLONY
        FROM E
        GROUP BY DIFFERENTIATION_YEAR
    ) M
WHERE E.DIFFERENTIATION_YEAR = M.DIFFERENTIATION_YEAR
ORDER BY YEAR, YEAR_DEV
------------------------------------------------------------------------------------------------
SELECT    E.DIFFERENTIATION_YEAR AS YEAR, M.MAX_SIZE_OF_COLONY - E.SIZE_OF_COLONY AS YEAR_DEV, E.ID
FROM 
    (
        SELECT    ID, SIZE_OF_COLONY, YEAR(DIFFERENTIATION_DATE) AS DIFFERENTIATION_YEAR
        FROM      ECOLI_DATA
    ) E
    , (
        SELECT    YEAR(DIFFERENTIATION_DATE) AS DIFFERENTIATION_YEAR, MAX(SIZE_OF_COLONY) AS MAX_SIZE_OF_COLONY
        FROM      ECOLI_DATA
        GROUP BY  DIFFERENTIATION_YEAR
    ) M
WHERE     E.DIFFERENTIATION_YEAR = M.DIFFERENTIATION_YEAR
ORDER BY  YEAR, YEAR_DEV

 

 

STRING, DATE

 

< 카테고리 별 상품 개수 구하기 >

PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

-- MySql
SELECT SUBSTR(PRODUCT_CODE,1,2) AS CATEGORY, COUNT(1) AS PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY
ORDER BY CATEGORY;

-- Oracle
SELECT SUBSTR(PRODUCT_CODE,1,2) AS CATEGORY, COUNT(1) AS PRODUCTS
FROM PRODUCT
GROUP BY SUBSTR(PRODUCT_CODE,1,2)
ORDER BY SUBSTR(PRODUCT_CODE,1,2);

 

< 조건에 부합하는 중고거래 상태 조회하기 >

USED_GOODS_BOARD 테이블에서 2022년 10월 5일에 등록된 중고거래 게시물의 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래상태를 조회하는 SQL문을 작성해주세요. 거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력해주시고, 결과는 게시글 ID를 기준으로 내림차순 정렬해주세요.

-- MySql
SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, CASE STATUS WHEN 'SALE' THEN '판매중' WHEN 'RESERVED' THEN '예약중' WHEN 'DONE' THEN '거래완료' END AS STATUS
FROM USED_GOODS_BOARD
WHERE DATE_FORMAT(CREATED_DATE,'%Y%m%d') = '20221005'
ORDER BY BOARD_ID DESC;

-- Oracle
SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, CASE STATUS WHEN 'SALE' THEN '판매중' WHEN 'RESERVED' THEN '예약중' WHEN 'DONE' THEN '거래완료' END AS STATUS
FROM USED_GOODS_BOARD
WHERE TO_CHAR(CREATED_DATE,'YYYYMMDD') = '20221005'
ORDER BY BOARD_ID DESC;

 

< 자동차 평균 대여 기간 구하기 >

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.

-- MySql
SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1),1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

-- Oracle
SELECT CAR_ID, ROUND(AVG(END_DATE-START_DATE+1),1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING ROUND(AVG(END_DATE-START_DATE+1),1) >= 7
ORDER BY ROUND(AVG(END_DATE-START_DATE+1),1) DESC, CAR_ID DESC;

-------------------------------------------------------------------------------

SELECT *
FROM (
    SELECT CAR_ID, ROUND(AVG(END_DATE-START_DATE+1),1) AS AVERAGE_DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    GROUP BY CAR_ID
)
WHERE AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

 

< 연도 별 평균 미세먼지 농도 조회하기  >  (25.02.07)

AIR_POLLUTION 테이블에서 수원 지역의 연도 별 평균 미세먼지 오염도와 평균 초미세먼지 오염도를 조회하는 SQL문을 작성해주세요. 이때, 평균 미세먼지 오염도와 평균 초미세먼지 오염도의 컬럼명은 각각 PM10, PM2.5로 해 주시고, 값은 소수 셋째 자리에서 반올림해주세요.
결과는 연도를 기준으로 오름차순 정렬해주세요.

SELECT  YEAR(YM) AS YEAR
        , ROUND(AVG(PM_VAL1),2) AS PM10
        , ROUND(AVG(PM_VAL2),2) AS "PM2.5" 
FROM    AIR_POLLUTION 
WHERE   LOCATION2 = '수원' 
GROUP BY YEAR(YM) 
ORDER BY YEAR(YM);

---------------------------------------------------

SELECT  TO_CHAR(YM,'YYYY') AS YEAR
        , ROUND(AVG(PM_VAL1),2) AS PM10
        , ROUND(AVG(PM_VAL2),2) AS "PM2.5" 
FROM    AIR_POLLUTION 
WHERE   LOCATION2 = '수원' 
GROUP BY TO_CHAR(YM,'YYYY') 
ORDER BY TO_CHAR(YM,'YYYY');

 

< 분기별 분화된 대장균의 개체 수 구하기 >   (25.02.07)

각 분기(QUARTER)별 분화된 대장균의 개체의 총 수(ECOLI_COUNT)를 출력하는 SQL 문을 작성해주세요. 이때 각 분기에는 'Q' 를 붙이고 분기에 대해 오름차순으로 정렬해주세요. 대장균 개체가 분화되지 않은 분기는 없습니다.

SELECT
    CONCAT(QUARTER,'Q') AS QUARTER
    ,COUNT(1) AS ECOLI_COUNT
FROM (
    SELECT CEIL(MONTH(DIFFERENTIATION_DATE)/3) AS QUARTER 
    FROM ECOLI_DATA
) T
GROUP BY QUARTER
ORDER BY QUARTER

 

 

GROUP BY

 

< 진료과별 총 예약 횟수 출력하기 >

APPOINTMENT 테이블에서 2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL문을 작성해주세요. 이때, 컬럼명은 '진료과 코드', '5월예약건수'로 지정해주시고 결과는 진료과별 예약한 환자 수를 기준으로 오름차순 정렬하고, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬해주세요.

-- MySql
SELECT MCDP_CD AS "진료과코드", COUNT(1) AS "5월예약건수"
FROM APPOINTMENT
WHERE DATE_FORMAT(APNT_YMD,'%Y%m') = '202205'
GROUP BY MCDP_CD
ORDER BY COUNT(1), MCDP_CD;

-- Oracle
SELECT MCDP_CD AS "진료과코드", COUNT(1) AS "5월예약건수"
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD,'YYYYMM') = '202205'
GROUP BY MCDP_CD
ORDER BY COUNT(1), MCDP_CD;

 

< 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기 >

CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.

SELECT CAR_TYPE, COUNT(1) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%' OR OPTIONS LIKE '%열선시트%' OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE;

 

< 성분으로 구분한 아이스크림 총 주문량 >

상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총주문량이 작은 순서대로 조회하는 SQL 문을 작성해주세요. 이때 총주문량을 나타내는 컬럼명은 TOTAL_ORDER로 지정해주세요.

-- MySql
SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF O JOIN ICECREAM_INFO I USING(FLAVOR)
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER;

-- Oracle
SELECT I.INGREDIENT_TYPE, SUM(O.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF O JOIN ICECREAM_INFO I ON O.FLAVOR = I.FLAVOR
GROUP BY I.INGREDIENT_TYPE
ORDER BY SUM(O.TOTAL_ORDER);
------------------------------------------------------------
SELECT I.INGREDIENT_TYPE, SUM(O.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF O, ICECREAM_INFO I
WHERE O.FLAVOR = I.FLAVOR
GROUP BY I.INGREDIENT_TYPE
ORDER BY SUM(O.TOTAL_ORDER);

 

< 가격대 별 상품 개수 구하기 >

PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요. 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요. 결과는 가격대를 기준으로 오름차순 정렬해주세요.

-- MySql
SELECT FLOOR(PRICE*0.0001)*10000 AS PRICE_GROUP, COUNT(1) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

-- Oracle
SELECT FLOOR(PRICE*0.0001)*10000 AS PRICE_GROUP, COUNT(1) AS PRODUCTS
FROM PRODUCT
GROUP BY FLOOR(PRICE*0.0001)
ORDER BY FLOOR(PRICE*0.0001);

 

< 물고기 종류 별 잡은 수 구하기 >  (25.02.07)

FISH_NAME_INFO에서 물고기의 종류 별 물고기의 이름과 잡은 수를 출력하는 SQL문을 작성해주세요.
물고기의 이름 컬럼명은 FISH_NAME, 잡은 수 컬럼명은 FISH_COUNT로 해주세요.
결과는 잡은 수 기준으로 내림차순 정렬해주세요.

SELECT 
    COUNT(F.FISH_TYPE) AS FISH_COUNT
    , FN.FISH_NAME AS FISH_NAME
FROM 
    FISH_NAME_INFO FN RIGHT OUTER JOIN FISH_INFO F 
        ON FN.FISH_TYPE = F.FISH_TYPE
GROUP BY FN.FISH_NAME
ORDER BY COUNT(F.FISH_TYPE) DESC;

 

< 월별 잡은 물고기 수 구하기 >   (25.02.07)

월별 잡은 물고기의 수와 월을 출력하는 SQL문을 작성해주세요.
잡은 물고기 수 컬럼명은 FISH_COUNT, 월 컬럼명은 MONTH로 해주세요.
결과는 월을 기준으로 오름차순 정렬해주세요.
단, 월은 숫자형태 (1~12) 로 출력하며 9 이하의 숫자는 두 자리로 출력하지 않습니다. 잡은 물고기가 없는 월은 출력하지 않습니다.

SELECT COUNT(FISH_TYPE) AS FISH_COUNT, MONTH(TIME) AS MONTH
FROM FISH_INFO
GROUP BY MONTH(TIME)
HAVING COUNT(FISH_TYPE) > 0
ORDER BY MONTH(TIME);

 

< 조건에 맞는 사원 정보 조회하기 >   (25.02.07)

HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 테이블에서 2022년도 한해 평가 점수가 가장 높은 사원 정보를 조회하려 합니다. 2022년도 평가 점수가 가장 높은 사원들의 점수, 사번, 성명, 직책, 이메일을 조회하는 SQL문을 작성해주세요.
2022년도의 평가 점수는 상,하반기 점수의 합을 의미하고, 평가 점수를 나타내는 컬럼의 이름은 SCORE로 해주세요

SELECT
    S.SCORE
    ,E.EMP_NO
    ,E.EMP_NAME
    ,E.POSITION
    ,E.EMAIL
FROM (
        SELECT EMP_NO, SUM(SCORE) AS SCORE 
        FROM HR_GRADE 
        GROUP BY EMP_NO 
        ORDER BY SUM(SCORE) DESC 
        LIMIT 1
    ) S
    , HR_EMPLOYEES E
WHERE S.EMP_NO = E.EMP_NO;

 

< 노선별 평균 역 사이 거리 조회하기 >   (25.02.07)

SUBWAY_DISTANCE 테이블에서 노선별로 노선, 총 누계 거리, 평균 역 사이 거리를 노선별로 조회하는 SQL문을 작성해주세요.
총 누계거리는 테이블 내 존재하는 역들의 역 사이 거리의 총 합을 뜻합니다. 총 누계 거리와 평균 역 사이 거리의 컬럼명은 각각 TOTAL_DISTANCE, AVERAGE_DISTANCE로 해주시고, 총 누계거리는 소수 둘째자리에서, 평균 역 사이 거리는 소수 셋째 자리에서 반올림 한 뒤 단위(km)를 함께 출력해주세요.
결과는 총 누계 거리를 기준으로 내림차순 정렬해주세요.

-- MySql
SELECT 
    ROUTE
    , CONCAT(ROUND(SUM(D_BETWEEN_DIST),1),'km') AS TOTAL_DISTANCE
    , CONCAT(ROUND(AVG(D_BETWEEN_DIST),2),'km') AS AVERAGE_DISTANCE 
FROM SUBWAY_DISTANCE 
GROUP BY ROUTE 
ORDER BY SUM(D_BETWEEN_DIST) DESC

-- Oracle
SELECT 
    ROUTE
    , ROUND(SUM(D_BETWEEN_DIST),1)||'km' AS TOTAL_DISTANCE
    , ROUND(AVG(D_BETWEEN_DIST),2)||'km' AS AVERAGE_DISTANCE 
FROM SUBWAY_DISTANCE 
GROUP BY ROUTE 
ORDER BY SUM(D_BETWEEN_DIST) DESC

 

 

JOIN

 

< 조건에 맞는 도서와 저자 리스트 출력하기 >

'경제' 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 출판일을 기준으로 오름차순 정렬해주세요.

-- MySql
SELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE,'%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK JOIN AUTHOR USING(AUTHOR_ID)
WHERE CATEGORY = '경제'
ORDER BY PUBLISHED_DATE;

-- Oracle
SELECT B.BOOK_ID, A.AUTHOR_NAME, TO_CHAR(B.PUBLISHED_DATE,'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK B JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY B.PUBLISHED_DATE;

 

< 상품 별 오프라인 매출 구하기 >

PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

-- MySql
SELECT PRODUCT_CODE, SUM(PRICE*SALES_AMOUNT) AS SALES
FROM PRODUCT JOIN OFFLINE_SALE USING(PRODUCT_ID)
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE;

-- Oracle
SELECT PRODUCT_CODE, SUM(PRICE*SALES_AMOUNT) AS SALES
FROM PRODUCT P JOIN OFFLINE_SALE S ON P.PRODUCT_ID = S.PRODUCT_ID
GROUP BY PRODUCT_CODE
ORDER BY SUM(PRICE*SALES_AMOUNT) DESC, PRODUCT_CODE;