[Programmers/MySQL] SELECT 문제 풀이 모음

1. 상위 N개 레코드

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME ASC
LIMIT 1;

 

2. 여러 기준으로 정렬하기

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME ASC, DATETIME DESC;

 

3. 동물의 ID와 이름

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC;

 

4. 어린 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID ASC;

 

5. 아픈 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'
ORDER BY ANIMAL_ID ASC;

 

6. 역순 정렬하기

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC;

 

7. 모든 레코드 조회하기

SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC;

 

8. 조건에 맞는 회원수 구하기

SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE AGE>=20 AND AGE<=29 AND YEAR(JOINED)=2021;

 

9. 과일로 만든 아이스크림 고르기

SELECT FIRST_HALF.FLAVOR
FROM FIRST_HALF, ICECREAM_INFO
WHERE FIRST_HALF.FLAVOR = ICECREAM_INFO.FLAVOR
AND FIRST_HALF.TOTAL_ORDER >= 3000
AND ICECREAM_INFO.INGREDIENT_TYPE = 'fruit_based'
ORDER BY TOTAL_ORDER DESC;

 

10. 인기있는 아이스크림

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID;

 

11. 강원도에 위치한 생산공장 목록 출력하기

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '강원도%'
ORDER BY FACTORY_ID ASC;

 

12. 흉부외과 또는 일반외과 의사 목록 출력하기

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD='CS' OR MCDP_CD='GS'
ORDER BY HIRE_YMD DESC;

 

13. 조건에 맞는 도서 리스트 출력하기

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE YEAR(PUBLISHED_DATE) = 2021
AND CATEGORY='인문'
ORDER BY PUBLISHED_DATE ASC;

 

14. 12세 이하인 여자 환자 목록 출력하기

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE') AS TLNO
FROM PATIENT
WHERE GEND_CD = 'W' AND AGE <= 12
ORDER BY AGE DESC, PT_NAME;

 

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

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

 

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

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 TLNO IS NOT NULL AND MONTH(DATE_OF_BIRTH) = 3
ORDER BY MEMBER_ID ASC;

 

17. 서울에 위치한 식당 목록 출력하기

SELECT REVIEW.REST_ID, INFO.REST_NAME, INFO.FOOD_TYPE, INFO.FAVORITES, INFO.ADDRESS, ROUND(AVG(REVIEW.REVIEW_SCORE), 2) AS SCORE

FROM REST_REVIEW REVIEW
INNER JOIN REST_INFO INFO ON REVIEW.REST_ID = INFO.REST_ID

GROUP BY REVIEW.REST_ID
HAVING INFO.ADDRESS LIKE '서울%'

ORDER BY SCORE DESC, FAVORITES DESC;

 

18. 오프라인/온라인 판매 데이터 통합하기

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'

UNION

SELECT SALES_DATE, PRODUCT_ID, NULL, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'

ORDER BY SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC;

 

 

 

프로그래머스