SQL 쿼리연습- IS NULL, JOIN(feat.프로그래머스, SQL고득점 KIT)

 

4. IS NULL 완료

- NULL 처리하기 (문법 다름)

-- mysql IFNULL(값1,값2) : 값1이 null이면 값2출력, null이 아니면 값1
SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME ,SEX_UPON_INTAKE	
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

 

-- oracle NVL(값1,값2) : 값1이 null이면 값2 출력, null이 아니면 값1
SELECT ANIMAL_TYPE, NVL(NAME, 'No name') AS NAME ,SEX_UPON_INTAKE	
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

 

5. JOIN   완료

-없어진 기록찾기

-- LEFT OUTER JOIN으로 푸는 방법
SELECT A.ANIMAL_ID , A.NAME
FROM ANIMAL_OUTS A  -- 입양간 기록
LEFT OUTER JOIN ANIMAL_INS B -- 보호소 들어온 기록
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.ANIMAL_ID ASC

--  서브쿼리로 푸는 방법
SELECT  A.ANIMAL_ID , A.NAME FROM ANIMAL_OUTS A 
WHERE A.ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_INS)
ORDER BY A.ANIMAL_ID ASC

 

- 오랜 기간 보호한 동물(1)

오래된 순으로 3건 조회하기

-- mysql
SELECT A.NAME, A.DATETIME FROM ANIMAL_INS A
LEFT OUTER JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE  B.ANIMAL_ID IS NULL
ORDER BY A.DATETIME ASC
limit 3

 

-- oracle
SELECT * FROM
(
SELECT A.NAME, A.DATETIME FROM ANIMAL_INS A
LEFT OUTER JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE  B.ANIMAL_ID IS NULL
ORDER BY A.DATETIME ASC
)
WHERE ROWNUM <= 3

 

- 보호소에서 중성화 된 동물

-- 보호소 들어올때 중성화 x (SEX_UPON_INTAKE :"Intact" 표시)
-- 보호소 나갈때 중성화(SEX_UPON_OUTCOME : "Spayed" 또는 "Neutered" 표시)
    SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
    FROM ANIMAL_INS A , ANIMAL_OUTS B
    WHERE A.ANIMAL_ID = B.ANIMAL_ID
    AND A.SEX_UPON_INTAKE like 'Intact%'   
    AND B.SEX_UPON_OUTCOME not like 'Intact%'

6. String, Date   완료

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

   오라클은 대소문자 구분하여 조회해야함 

   UPPER() : 대문자로 변환

   LOWER() : 소문자로 변환

 

SELECT ANIMAL_ID, NAME FROM ANIMAL_INS 
-- mysql
-- WHERE NAME LIKE '%el%' AND ANIMAL_TYPE='Dog'
--oracle 대소문자 구분! UPPER() : 대문자로 변환
WHERE UPPER(NAME) LIKE UPPER('%el%') AND ANIMAL_TYPE='Dog'
ORDER BY NAME ASC

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

  CASE WHEN 조건 THEN 결과 ELSE 결과 END (오라클,mysql 동일)

--mysql
SELECT ANIMAL_ID, NAME, 
    CASE WHEN SEX_UPON_INTAKE like 'Neutered%' THEN 'O' 
    WHEN SEX_UPON_INTAKE like 'Spayed%' THEN 'O' 
    ELSE 'X'
    END
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID

 

--oracle
SELECT ANIMAL_ID, NAME, 
    --REGEXP_LIKE 포함한대상
    CASE WHEN REGEXP_LIKE(SEX_UPON_INTAKE ,'Neutered') THEN 'O' 
    WHEN REGEXP_LIKE(SEX_UPON_INTAKE ,'Spayed') THEN 'O' 
    ELSE 'X'
    END
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID

 

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

--mysql 두건조회
SELECT O.ANIMAL_ID, O.NAME 
FROM ANIMAL_OUTS O , ANIMAL_INS I
WHERE O.ANIMAL_ID = I.ANIMAL_ID
ORDER BY (O.DATETIME-I.DATETIME) DESC
limit 2

 

--oracle
-- 보호소들어온 동물 중 입양보낸정보 추출
-- O.DATETIME(입양일), I.DATETIME(보호시작일)
SELECT * FROM 
(
SELECT O.ANIMAL_ID, O.NAME 
FROM ANIMAL_OUTS O , ANIMAL_INS I
WHERE O.ANIMAL_ID = I.ANIMAL_ID
ORDER BY (O.DATETIME-I.DATETIME) DESC
)
WHERE ROWNUM <=2

- DATETIME에서 DATE로 형 변환

  mysql 날짜변환 : date_format(SYSDATE(),'%Y-%m-%d %T')

-- mysql
SELECT ANIMAL_ID, NAME, date_format(DATETIME,'%Y-%m-%d') FROM ANIMAL_INS 
ORDER BY ANIMAL_ID ASC

--oracle
SELECT ANIMAL_ID, NAME,TO_CHAR(DATETIME,'YYYY-MM-DD') FROM ANIMAL_INS 
ORDER BY ANIMAL_ID ASC