[PROGRAMMERS] SUM, MAX, MIN

[Notification]
DBMS : MySQL

1. SUM, MAX, MIN LV.1

1. 가장 비싼 상품 구하기

문제 링크

1.1 정답

SELECT MAX(PRICE) AS MAX_PRICE FROM PRODUCT

2. 잡은 물고기 중 가장 큰 물고기의 길이 구하기

문제 링크

2.1 정답

SELECT 
    CONCAT(LENGTH, "cm") AS MAX_LENGTH
FROM 
    FISH_INFO
ORDER BY 
    LENGTH DESC
LIMIT 1;

3. 최댓값 구하기

문제 링크

3.1 정답

SELECT 
    DATETIME AS "시간" 
FROM 
    ANIMAL_INS 
ORDER BY 
    DATETIME DESC
limit 1

2. SUM, MAX, MIN LV.2

1. 연도별 대장균 크기의 편차 구하기

문제 링크

1.1 정답

SELECT 
    A.YEAR, 
    (B.MAX_SIZE - A.SIZE_OF_COLONY) AS YEAR_DEV, 
    A.ID 
FROM 
    (SELECT 
        YEAR(DIFFERENTIATION_DATE) AS YEAR, 
        SIZE_OF_COLONY, 
        ID 
    FROM 
        ECOLI_DATA) AS A
JOIN 
    (SELECT
        YEAR(DIFFERENTIATION_DATE) AS YEAR,
        MAX(SIZE_OF_COLONY) AS MAX_SIZE
    FROM
        ECOLI_DATA
    GROUP BY
        YEAR) AS B
ON 
    A.YEAR = B.YEAR
ORDER BY 
    YEAR ASC, 
    YEAR_DEV ASC;

2. 조건에 맞는 아이템들의 가격의 총합 구하기

문제 링크

2.1 정답

SELECT 
    SUM(PRICE) as TOTAL_PRICE 
FROM 
    ITEM_INFO 
WHERE 
    RARITY = "LEGEND"

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

문제 링크

3.1 정답

SELECT PR
    ODUCT_ID,PRODUCT_NAME,PRODUCT_CD,CATEGORY,PRICE 
FROM 
    FOOD_PRODUCT
WHERE 
    PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT)

4. 중복 제거하기

문제 링크

4.1 정답

SELECT 
    COUNT(DISTINCT NAME) as "count" 
FROM 
    ANIMAL_INS

5. 동물 수 구하기

문제 링크

5.1 정답

SELECT 
    count(*) as "count" 
FROM 
    ANIMAL_INS

6. 최솟값 구하기

문제 링크

6.1 정답

SELECT 
    MIN(DATETIME) AS "시간" 
FROM 
    ANIMAL_INS

OR 

SELECT 
    DATETIME AS "시간" 
FROM 
    ANIMAL_INS 
ORDER BY 
    DATETIME ASC
limit 1

3. SUM, MAX, MIN LV.3

1. 물고기 종류 별 대어 찾기

문제 링크

1.1 정답

SELECT 
    A.ID, 
    B.FISH_NAME, 
    A.MAX_LENGTH AS LENGTH 
FROM 
    (
        SELECT 
            B.ID, 
            A.FISH_TYPE, 
            A.MAX_LENGTH
        FROM 
            (
                SELECT 
                    FISH_TYPE,
                    MAX(LENGTH) AS MAX_LENGTH 
                FROM 
                    FISH_INFO 
                GROUP BY 
                    FISH_TYPE
            ) AS A, 
            FISH_INFO AS B
        WHERE 
            B.FISH_TYPE = A.FISH_TYPE 
            AND B.LENGTH = A.MAX_LENGTH
    ) AS A, 
    FISH_NAME_INFO AS B
WHERE 
    A.FISH_TYPE = B.FISH_TYPE
ORDER BY 
    A.ID ASC;