Cats Who Code

SELECT ID, CASE WHEN RANK_SIZE

-- GOOD TO PRACTICE TEMP TABLE WITH GET_MAX_SIZE_TABLE AS( SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) as MAX_SIZE FROM ECOLI_DATA GROUP BY YEAR(DIFFERENTIATION_DATE) )-- JOIN ECLIO TABLE AND GET MAX TABLE SELECT B.YEAR, (B.MAX_SIZE - A.SIZE_OF_COLONY) AS YEAR_DEV, A.ID FROM ECOLI_DATA AS A JOIN GET_MAX_SIZE_TABLE AS BON YEAR(A.DIFFERENTIATION_DATE) = B.YEAR ORDER BY..

-- USING SUBQUERY SELECT f.ID, n.FISH_NAME, f.LENGTH FROM FISH_INFO as f JOIN FISH_NAME_INFO as n on f.FISH_TYPE = n.FISH_TYPE WHERE f.LENGTH IN (SELECT MAX(f2.LENGTH) FROM FISH_INFO AS f2 WHERE f2.FISH_TYPE = f.FISH_TYPE)ORDER BY f.ID

-- MONTH() SELECT CASE WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 1 AND 3 THEN '1Q' WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 4 AND 6 THEN '2Q' WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 7 AND 9 THEN '3Q' WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 10 AND 12 THEN '4Q' END AS QUARTER, COUNT(*) AS ECOLI_COUNT FROM ECOLI_DATA GROUP BY QUARTER ORDER BY QUARTER

-- LEFT OUTER JOIN SELECT i.ITEM_ID, i.ITEM_NAME, i.RARITY FROM ITEM_INFO as i LEFT JOIN ITEM_TREE as t ON i.ITEM_ID = t.PARENT_ITEM_ID WHERE t.PARENT_ITEM_ID is null ORDER BY i.ITEM_ID DESC

-- SELF JOIN SELECT E1.ID, COUNT(E2.ID) AS CHILD_COUNT FROM ECOLI_DATA AS E1 LEFT JOIN ECOLI_DATA AS E2 ON E1.ID = E2.PARENT_ID GROUP BY E1.ID ORDER BY E1.ID

-- GOOD PROBLEM TO PRACTICE -- COMMON TABLE EXPRESSION WITH T AS(SELECTBOARD_IDFROM USED_GOODS_BOARDORDER BY VIEWS DESC LIMIT 1) -- MAIN QUERY SELECT CONCAT ('/home/grep/src/', BOARD_ID,'/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH FROM USED_GOODS_FILE WHERE BOARD_ID = (SELECT * FROM T)ORDER BY FILE_ID DESCprompt - BOARD 테이블과 FILE 테이블에서 조회수가 가장 높은 게시물에 대한 첨부파일 경로를 조회 하는 문 작성해주세요. 1. BOARD 테이블에..

SELECT ID, CASE WHEN SIZE_OF_COLONY 100 AND SIZE_OF_COLONY 1000 THEN 'HIGH' END AS SIZE FROM ECOLI_DATA ORDER BY ID

SELECT U.USER_ID, U.NICKNAME, CONCAT(U.CITY, ' ', U.STREET_ADDRESS1,' ', U.STREET_ADDRESS2) AS 전체주소, CONCAT (SUBSTRING(U.TLNO, 1,3), '-', SUBSTRING(U.TLNO, 4,4), '-', SUBSTRING(U.TLNO, 8,4)) AS 전화번호 FROM USED_GOODS_BOARD AS B JOIN USED_GOODS_USER U ON B.WRITER_ID = U.USER_ID GROUP BY U.USER_ID HAVING COUNT(B.WRITER_ID) >=3 ORDER BY B.WRITER_ID DESC-- how to combine string? -- CONCAT(CITY, STREET..

-- USING OUTER JOIN SELECT o.ANIMAL_ID, o.NAME FROM ANIMAL_INS AS i RIGHT OUTER JOIN ANIMAL_OUTS AS oON i.ANIMAL_ID = o.ANIMAL_ID WHERE i.ANIMAL_ID is null ORDER BY o.ANIMAL_ID-- USING NOT EXIST SELECT o.ANIMAL_ID, o.NAME FROM ANIMAL_OUTS AS o WHERE NOT EXISTS (SELECT 1 FROM ANIMAL_INS AS i WHERE i.ANIMAL_ID = o.ANIMAL_ID) ORDER BY o.ANIMAL_ID prompt 이해하기 - OUTS 에는 있지만 INS에는 존재 ..