SELECT A.ID , CASE COLONY_NAME WHEN 1 THEN 'CRITICAL' WHEN 2 THEN 'HIGH' WHEN 3 THEN 'MEDIUM' WHEN 4 THEN 'LOW' END AS COLONY_NAME FROM ( SELECT ID , NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS COLONY_NAME FROM ECOLI_DATA ) AORDER BY A.IDNTILE함수는 특정컬럼의 값 기준으로 N개의 등급으로 분류해주는 함수입니다.SELECT N..

SELECT B.ID , B.GENOTYPE , A.GENOTYPE AS PARENT_GENOTYPE FROM ECOLI_DATA A LEFT JOIN ECOLI_DATA B ON A.ID = B.PARENT_ID WHERE A.GENOTYPE & B.GENOTYPE = A.GENOTYPE ORDER BY B.ID

SELECT COUNT(1) AS COUNT FROM ECOLI_DATA WHERE (GENOTYPE & 2 = 0) AND (GENOTYPE & 5) IN (1,4,5)1번 항체만 보유한 경우 GENOTYPE & 5 > 13번 항체만 보유한 경우 GENOTYPE & 5 > 41,3번 항체를 모두 보유한경우 GENOTYPE & 5 > 5

▶ IF형식SELECT ID , CASE WHEN SIZE_OF_COLONY 1000 THEN 'HIGH' END AS SIZE FROM ECOLI_DATA ORDER BY ID CASE WHEN은 SELECT절에서 SWITCH형식과 IF형식 두 방법으로 사용할 수 있습니다. ▶ SWITCH형식CASE SAMPLEWHEN 1 TEHN '1'WHEN 2 THEN '2'END​

SELECT A.ID , COUNT(B.ID) AS CHILD_COUNT FROM ECOLI_DATA A LEFT JOIN ECOLI_DATA B ON A.ID = B.PARENT_ID GROUP BY A.ID ORDER BY A.ID

SELECT DISTINCT B.ID , B.EMAIL , B.FIRST_NAME , B.LAST_NAME FROM SKILLCODES A JOIN DEVELOPERS B ON A.CATEGORY = 'Front End' AND A.CODE & B.SKILL_CODE ORDER BY ID

WITH J2021 AS( -- 2021년 가입한 회원 SELECT USER_ID FROM USER_INFO WHERE EXTRACT(YEAR FROM JOINED) = '2021'),B2022 AS( -- 2022년 구매한 회원 SELECT USER_ID , EXTRACT(YEAR FROM SALES_DATE) AS YEAR , EXTRACT(MONTH FROM SALES_DATE) AS MONTH FROM ONLINE_SALE WHERE EXTRACT(YEAR FROM SALES_DATE) = '2022')SELECT B.YEAR , B.MONTH , COUNT(DISTINCT B.USE..

SELECT A.PRODUCT_CODE , SUM(B.SALES_AMOUNT) * MAX(A.PRICE) AS SALES FROM PRODUCT A INNER JOIN OFFLINE_SALE B ON A.PRODUCT_ID = B.PRODUCT_ID GROUP BY B.PRODUCT_ID, A.PRODUCT_CODE ORDER BY SUM(B.SALES_AMOUNT) * MAX(A.PRICE) DESC, A.PRODUCT_CODE

SELECT B.ANIMAL_ID , B.ANIMAL_TYPE , B.NAME FROM ANIMAL_INS A INNER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE A.SEX_UPON_INTAKE != B.SEX_UPON_OUTCOME

SELECT A.NAME , A.DATETIME FROM ANIMAL_INS A LEFT OUTER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE B.DATETIME IS NULL ORDER BY A.DATETIME FETCH NEXT 3 ROW ONLY

SELECT A.ANIMAL_ID , A.NAME FROM ANIMAL_INS A INNER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE A.DATETIME > B.DATETIME ORDER BY A.DATETIME

SELECT A.MEMBER_NAME , B.REVIEW_TEXT , TO_CHAR(B.REVIEW_DATE, 'YYYY-MM-DD') AS REVIEW_DATE FROM MEMBER_PROFILE A INNER JOIN REST_REVIEW B ON A.MEMBER_ID = B.MEMBER_ID INNER JOIN( SELECT COUNT(SA.REVIEW_TEXT) AS CNT , SA.MEMBER_ID FROM REST_REVIEW SA GROUP BY SA.MEMBER_ID ORDER BY CNT DESC FETCH NEXT 1 ROW..