[SQL] 프로그래머스 물고기세트

가장 큰 물고기 10마리 구하기 ->클릭하면 문제로 이동

SELECT ID, LENGTH FROM FISH_INFO
ORDER BY LENGTH DESC, ID ASC
LIMIT 10;

 

잔챙이 잡은 수 구하기

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE LENGTH IS NULL;

 

한 해에 잡은 물고기 수 구하기

SELECT COUNT(ID) AS FISH_COUNT
FROM FISH_INFO 
WHERE YEAR(TIME) = 2021;

 

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

SELECT CONCAT(MAX(LENGTH), 'cm' ) AS MAX_LENGTH
FROM FISH_INFO;

 

 

잡은 물고기의 평균 길이 구하기

SELECT ROUND(AVG(IFNULL(LENGTH, 10)),2) AS AVERAGE_LENGTH
FROM FISH_INFO;

 

 

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

SELECT COUNT(ID) AS FISH_COUNT
FROM FISH_INFO F INNER JOIN FISH_NAME_INFO N ON F.FISH_TYPE = N.FISH_TYPE 
WHERE FISH_NAME IN ('BASS' , 'SNAPPER');

 

월별 잡은 물고기 수 구하기

SELECT COUNT(ID) AS FISH_COUNT, MONTH(TIME) AS MONTH
FROM FISH_INFO
GROUP BY MONTH(TIME)
ORDER BY MONTH;

 

물고기 종류 별 잡은 수 구하기

SELECT COUNT(ID) AS FISH_COUNT, FISH_NAME 
FROM FISH_INFO F LEFT JOIN FISH_NAME_INFO N ON F.FISH_TYPE=N.FISH_TYPE
GROUP BY FISH_NAME
ORDER BY FISH_COUNT DESC;

 

특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

SELECT COUNT(ID) AS FISH_COUNT, MAX(LENGTH) AS MAX_LENGTH, FISH_TYPE
FROM FISH_INFO
GROUP BY FISH_TYPE
HAVING AVG(IFNULL(LENGTH,10)) >=33
ORDER BY FISH_TYPE ASC;

 

물고기 종류 별 대어 찾기

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

 

 

 

 

 

문제 잘 읽고 정렬조건을 빼먹지 않는 것이 중요하다!