프로그래머스 level 1 sql 문제들

- 아픈 동물 찾기

SELECT ANIMAL_ID, NAME from ANIMAL_INS where INTAKE_CONDITION like 'Sick' order by ANIMAL_ID

- 역순 정렬하기

SELECT NAME, DATETIME from ANIMAL_INS order by ANIMAL_ID desc

- 상위 n개 레코드

select NAME from ANIMAL_INS where DATETIME = (SELECT MIN(DATETIME) from ANIMAL_INS)
--또는
select NAME from ANIMAL_INS order by datetime limit 1

- 어린 동물 찾기

SELECT animal_id, name from animal_ins where intake_condition != 'Aged'

- 이름이 있는 동물의 아이디

SELECT animal_id from animal_ins where name is not null

- 동물의 아이디와 이름

SELECT animal_id, name from animal_ins order by animal_id

- 여러 기준으로 정렬하기

SELECT animal_id, name, datetime from animal_ins order by name, datetime desc

- 이름이 없는 동물의 아이디

SELECT animal_id from animal_ins where name is null order by animal_id

- 나이 정보가 없는 회원 수 구하기

SELECT count(*) as USERS from user_info where age is null

- 가장 비싼 상품 구하기

SELECT max(price) as MAX_PRICE from product

- 경기도에 위치한 식품창고 목록 출력하기

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, ifnull(FREEZER_YN, 'N') as FREESER_YN
from food_warehouse where address like '%경기도%'

IFNULL(p1, p2)함수는 p1가 null일 때, p2를 출력한다.

 

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

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS from food_factory 
where address like '강원도%' order by factory_id

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

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') from DOCTOR
where MCDP_CD = 'CS' or MCDP_CD = 'GS' order by HIRE_YMD desc, DR_NAME

- 조건에 맞는 회원수 구하기

SELECT count(*) from USER_INFO where JOINED like '2021%' and (AGE between 20 and 29)

- 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

- 인기있는 아이스크림

SELECT FLAVOR FROM FIRST_HALF order by TOTAL_ORDER desc, SHIPMENT_ID

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

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') from BOOK 
where PUBLISHED_DATE like '2021%' and CATEGORY = '인문'
order by PUBLISHED_DATE

- 평균 일일 대여 요금 구하기

SELECT round(avg(DAILY_FEE)) as AVERAGE_FEE from CAR_RENTAL_COMPANY_CAR where CAR_TYPE = 'SUV'

- 모든 레코드 조회하기

SELECT * from ANIMAL_INS order by ANIMAL_ID

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

SELECT FIRST_HALF.FLAVOR from FIRST_HALF, ICECREAM_INFO
where TOTAL_ORDER > 3000 and FIRST_HALF.FLAVOR = ICECREAM_INFO.FLAVOR and INGREDIENT_TYPE = 'fruit_based'
order by TOTAL_ORDER desc

- 최댓값 구하기

SELECT MAX(DATETIME) from ANIMAL_INS

- 특정 옵션이 포함된 자동차 리스트 구하기

SELECT * from CAR_RENTAL_COMPANY_CAR where OPTIONS like '%네비게이션%' order by CAR_ID desc

- 자동차 대여 기록에서 장기/단기 대여 구분하기

SELECT HISTORY_ID, CAR_ID, date_format(START_DATE, '%Y-%m-%d'), date_format(END_DATE, '%Y-%m-%d'), if(datediff(END_DATE,START_DATE)>=29, '장기 대여', '단기 대여') as RENT_TYPE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where START_DATE like '2022-09%'
order by HISTORY_ID desc

- 조건에 부합하는 중고거래 댓글 조회하기

SELECT TITLE, USED_GOODS_BOARD.BOARD_ID, REPLY_ID, USED_GOODS_REPLY.WRITER_ID, USED_GOODS_REPLY.CONTENTS, date_format(USED_GOODS_REPLY.CREATED_DATE, '%Y-%m-%d')
from USED_GOODS_BOARD, USED_GOODS_REPLY
where USED_GOODS_BOARD.BOARD_ID = USED_GOODS_REPLY.BOARD_ID
and USED_GOODS_BOARD.CREATED_DATE like '2022-10%'
order by USED_GOODS_REPLY.CREATED_DATE, TITLE