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;