To Infinity And Beyond

오랜 기간 보호한 동물(2)

SELECT A.ANIMAL_ID,A.NAMEFROM ANIMAL_OUTS A JOIN ANIMAL_INS BON A.ANIMAL_ID = B.ANIMAL_IDORDER BY DATEDIFF(A.DATETIME, B.DATETIME) DESC LIMIT 2;

조건에 맞는 사용자 정보 조회

SELECT B.USER_ID, B.NICKNAME, CONCAT(B.CITY, ' ', B.STREET_ADDRESS1, ' ', B.STREET_ADDRESS2) AS '전체주소', CONCAT(LEFT(B.TLNO, 3), '-', MID(B.TLNO, 4, 4), '-', RIGHT(B.TLNO, 4)) AS '전화번호'FROM USED_GOODS_USER BWHERE B.USER_ID IN ( SELECT A.WRITER_ID FROM USED_GOODS_BOARD A GROUP BY A.WRITER_ID HAVING COUNT(*) >= 3)ORDER BY B.USER_ID DESC;- 서브쿼리를 쓸 때 별칭을 주어서 명확하게 구분하자

대여 기록이 존재하는 자동차

SELECT DISTINCT(A.CAR_ID)FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY BON A.CAR_ID = B.CAR_IDWHERE A.CAR_TYPE = '세단' AND MONTH(B.START_DATE) = 10ORDER BY A.CAR_ID DESC;

조건별로 분류하여 주문상태 출력하기

SELECT ORDER_ID, PRODUCT_ID,DATE_FORMAT(OUT_DATE, '%Y-%m-%d'),CASE WHEN OUT_DATE '2022-05-01' THEN '출고대기'ELSE '출고미정'END AS '출고여부'FROM FOOD_ORDER ORDER BY ORDER_ID;

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

SELECT CONCAT('/home/grep/src/', A.BOARD_ID, '/', B.FILE_ID, B.FILE_NAME, B.FILE_EXT) AS FILE_PATHFROM USED_GOODS_BOARD A JOIN USED_GOODS_FILE BON A.BOARD_ID = B.BOARD_IDWHERE A.VIEWS IN (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)ORDER BY B.FILE_ID DESC;

물고기 종류 별 대어 찾기

SELECT A.ID,B.FISH_NAME, A.LENGTHFROM FISH_INFO A JOIN FISH_NAME_INFO BON A.FISH_TYPE = B.FISH_TYPEWHERE (A.FISH_TYPE, A.LENGTH) IN (SELECT FISH_TYPE, MAX(LENGTH) FROM FISH_INFO GROUP BY FISH_TYPE)ORDER BY A.ID;

업그레이드 할 수 없는 아이템 구하기

SELECT ITEM_ID, ITEM_NAME, RARITYFROM ITEM_INFO WHERE ITEM_ID NOT IN (SELECT PARENT_ITEM_ID FROM ITEM_TREE WHERE PARENT_ITEM_ID IS NOT NULL)ORDER BY ITEM_ID DESC;

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

SELECT COUNT(*) AS FISH_COUNTFROM FISH_INFO A JOIN FISH_NAME_INFO BON A.FISH_TYPE = B.FISH_TYPEWHERE B.FISH_NAME = 'BASS' OR B.FISH_NAME = 'SNAPPER';

조건에 맞는 개발자 찾기

SELECT ID, EMAIL,FIRST_NAME,LAST_NAMEFROM DEVELOPERS WHERE SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'Python') OR SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'C#')ORDER BY ID;

업그레이드 된 아이템 구하기

SELECT A.ITEM_ID,A.ITEM_NAME,A.RARITYFROM ITEM_INFO A JOIN ITEM_TREE BON A.ITEM_ID = B.ITEM_IDWHERE B.PARENT_ITEM_ID IN(SELECT ITEM_ID FROM ITEM_INFO WHERE RARITY = 'RARE')ORDER BY A.ITEM_ID DESC;