코딩연습이 좋아서/SQL이 좋아서

이름에 el이 들어가는 동물 찾기

SELECT ANIMAL_ID, NAMEFROM ANIMAL_INSWHERE (NAME LIKE '%EL%' OR NAME LIKE '%el%')AND ANIMAL_TYPE = 'Dog'ORDER BY NAME;

루시와 엘라 찾기

SELECT ANIMAL_ID, NAME,SEX_UPON_INTAKEFROM ANIMAL_INS WHERE NAME = 'Lucy' OR NAME = 'Ella' OR NAME = 'Pickle'OR NAME = 'Rogan' OR NAME = 'Sabrina' OR NAME = 'Mitty';

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

SELECT HISTORY_ID, CAR_ID,DATE_FORMAT(START_DATE, '%Y-%m-%d'),DATE_FORMAT(END_DATE, '%Y-%m-%d'),CASE WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '장기 대여'ELSE '단기 대여'END AS RENT_TYPEFROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE START_DATE LIKE '2022-09%'ORDER BY HISTORY_ID DESC;

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

SELECT CAR_ID, CAR_TYPE,DAILY_FEE,OPTIONSFROM CAR_RENTAL_COMPANY_CAR WHERE OPTIONS LIKE '%네비게이션%'ORDER BY CAR_ID DESC;

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

SELECT BOARD_ID, WRITER_ID,TITLE,PRICE,CASE WHEN STATUS = 'SALE' THEN '판매중' WHEN STATUS = 'RESERVED' THEN '예약중' ELSE '거래완료'END AS STATUSFROM USED_GOODS_BOARD WHERE CREATED_DATE = '2022-10-05'ORDER BY BOARD_ID DESC;

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

SELECT CAR_ID,ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1),1) AS AVERAGE_DURATIONFROM CAR_RENTAL_COMPANY_RENTAL_HISTORYGROUP BY CAR_IDHAVING AVERAGE_DURATION >= 7ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

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

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

ROOT 아이템 구하기

SELECT A.ITEM_ID, A.ITEM_NAMEFROM ITEM_INFO A JOIN ITEM_TREE B ON A.ITEM_ID = B.ITEM_ID AND B.PARENT_ITEM_ID IS NULLORDER BY A.ITEM_ID;

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

SELECT COUNT(*) AS USERSFROM USER_INFOWHERE AGE IS NULL;

NULL 처리하기

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'),SEX_UPON_INTAKEFROM ANIMAL_INS ORDER BY ANIMAL_ID;