프로그래머스 SQL

❗ oracle과 mysql 섞여있음

[SELECT]

1. 모든 레코드 조회하기

   🐶 SELECT * FROM ANIMAL_INS ORDER BY ANIMAL_ID ASC;

2. 역순 정렬하기

   🐶 SELECT NAME, DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESC;

3. 아픈 동물 찾기

   🐶 SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION=’Sick’;

4. 어린 동물 찾기

   🐶 SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION != ‘Aged’;

5. 동물의 아이디와 이름

   🐶 SELECT ANIMAL_ID, NAME FROM ANIMAL_INS;

6. 여러 기준으로 정렬하기

   🐶 SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS ORDER BY NAME ASC, DATETIME DESC;

7. 상위 n개 레코드

   🐶 SELECT NAME FROM ANIMAL_INS ORDER BY DATETIME ASC LIMIT 1;

[SUM, MAX, MIN]

1. 최댓값 구하기

   🐰 SELECT DATETIME FROM ANIMAL_INS ORDER BY DATETIME DESC LIMIT 1;

   🐰 SELECT MAX(DATETIME) FROM ANIMAL_INS;

2. 최솟값 구하기

   🐰 SELECT MIN(DATETIME) FROM ANIMAL_INS;

3. 동물 수 구하기

   🐰 SELECT COUNT(ANIMAL_ID) FROM ANIMAL_INS;

4. 중복 제거하기

   🐰 SELECT COUNT(DISTINCT(NAME)) FROM ANIMAL_INS;

[GROUP BY]

1. 고양이와 개는 몇 마리 있을까

   SELECT ANIMAL_TYPE, COUNT(ANIMAL_ID) AS count 
   FROM ANIMAL_INS 
   GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE ASC;
   

2. 동명 동물 수 찾기

   SELECT NAME, COUNT(NAME) AS COUNT 
   FROM ANIMAL_INS 
   GROUP BY NAME HAVING COUNT(NAME) >= 2
   ORDER BY NAME ASC;
   

3. 입양 시각 구하기 (1)

   SELECT HOUR(DATETIME) AS HOUR, COUNT(HOUR(DATETIME)) AS COUNT
   FROM ANIMAL_OUTS
   GROUP BY HOUR
   HAVING HOUR >= 9 AND HOUR <= 19
   ORDER BY HOUR ASC;
   

[IS NULL]

1. 이름이 없는 동물의 아이디

   🐷 SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID ASC;

2. 이름이 있는 동물의 아이디

   🐷 SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL ORDER BY ANIMAL_ID ASC;

3. NULL 처리하기

   🐷 SELECT ANIMAL_TYPE, NVL(NAME, ‘No name’), SEX_UPON_INTAKE FROM ANIMAL_INS ORDER BY ANIMAL_ID ASC;

[JOIN]

1. 없어진 기록 찾기

   SELECT O.ANIMAL_ID, O.NAME
   FROM ANIMAL_INS I ,ANIMAL_OUTS O
   WHERE I.ANIMAL_ID (+) =  O.ANIMAL_ID 
   AND I.ANIMAL_ID IS NULL
   ORDER BY ANIMAL_ID;
   

2. 있었는데요 없었습니다

   SELECT I.ANIMAL_ID, I.NAME
   FROM ANIMAL_INS I, ANIMAL_OUTS O
   WHERE I.ANIMAL_ID = O.ANIMAL_ID
   AND O.DATETIME < I.DATETIME
   ORDER BY I.DATETIME ASC;
   

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

   SELECT I.NAME, I.DATETIME
   FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
   WHERE O.ANIMAL_ID IS NULL
   ORDER BY I.DATETIME
   LIMIT 3;
   

4. 보호소에서 중성화한 동물

   SELECT O.ANIMAL_ID, O.ANIMAL_TYPE, O.NAME
   FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
   WHERE O.ANIMAL_ID IS NOT NULL
   AND I.SEX_UPON_INTAKE LIKE 'Intact %'
   AND (O.SEX_UPON_OUTCOME LIKE 'Spayed %'
       OR O.SEX_UPON_OUTCOME LIKE 'Neutered %')
   ORDER BY O.ANIMAL_ID ASC;
   

[String, Date]

1. 루시와 엘라 찾기

   SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
   FROM ANIMAL_INS
   WHERE NAME IN ('Ella', 'Lucy', 'Pickle', 'Rogan', 'Sabrina', 'Mitty');
   

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

   SELECT ANIMAL_ID, NAME
   FROM ANIMAL_INS
   WHERE NAME LIKE '%el%'
   AND ANIMAL_TYPE = 'Dog'
   ORDER BY NAME ASC;
   

3. 중성화 여부 파악하기

   SELECT ANIMAL_ID, NAME, 
    	CASE WHEN SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%'
       THEN 'O'
       ELSE 'X'
       END
       AS '중성화'
   FROM ANIMAL_INS
   ORDER BY ANIMAL_ID ASC;
   

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

   SELECT O.ANIMAL_ID, O.NAME
   FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
   WHERE O.ANIMAL_ID IS NOT NULL
   ORDER BY O.DATETIME - I.DATETIME DESC
   LIMIT 2;
   

5. DATETIME에서 DATE로 형 변환

   SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS '날짜'
   FROM ANIMAL_INS
   ORDER BY ANIMAL_ID;
   

   
   

[중첩질의]

1. 우유와 요거트가 담긴 장바구니

   SELECT DISTINCT(CART_ID)
   FROM CART_PRODUCTS
   WHERE CART_ID IN (
       SELECT CART_ID
       FROM CART_PRODUCTS
       WHERE NAME = 'Milk'
   )
   AND NAME = 'Yogurt'
   ORDER BY CART_ID ASC;