SQL 코딩연습 (프로그래머스)

<1. SELECT>

1)모든레코드 조회하기

SELECT * FROM ANIMAL_INS;

2)역순 정렬하기

SELECT NAME,  DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESC

3)아픈동물 찾기

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
WHERE INTAKE_CONDITION = 'Sick' 
ORDER BY ANIMAL_ID

4)어린동물 찾기

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != "Aged"
ORDER BY ANIMAL_ID;

5)동물의 아이디와 이름

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

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

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

7)상위 n개 레코드

SELECT NAME
FROM ANIMAL_INS
WHERE DATETIME=(SELECT MIN(DATETIME) FROM ANIMAL_INS);

 

<2. SUM, MAX, MIN>

1)최댓값 구하기

SELECT DATETIME
FROM ANIMAL_INS
WHERE DATETIME=(SELECT MAX(DATETIME) FROM ANIMAL_INS);

2)최솟값 구하기

SELECT DATETIME
FROM ANIMAL_INS
WHERE DATETIME = (SELECT MIN(DATETIME) FROM ANIMAL_INS);

3)동물 수 구하기

SELECT COUNT(ANIMAL_ID)
FROM ANIMAL_INS;

4)중복 제거하기

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME != "NULL";

 

<3. GROUP BY>

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

SELECT ANIMAL_TYPE, COUNT(ANIMAL_ID)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE;

2)동명 동물 수 찾기

SELECT NAME , COUNT(NAME) AS C_NAME
FROM ANIMAL_INS
WHERE NAME != "NULL"
GROUP BY NAME
HAVING C_NAME >=2;

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

SELECT HOUR(DATETIME) AS HOUR, COUNT(HOUR(DATETIME)) AS COUNT
FROM ANIMAL_OUTS
WHERE 9 <= HOUR(DATETIME) AND HOUR(DATETIME) <= 19
GROUP BY HOUR(DATETIME);

4)입양시각 구하기(2)

SELECT HOUR(DATETIME) AS HOUR, COUNT(HOUR(DATETIME)) AS COUNT, DATETIME
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME);

#효율성 미해결...#

 

<4. JOIN>

1)없어진 기록 찾기

SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_OUTS
LEFT OUTER JOIN ANIMAL_INS
ON ANIMAL_INS.ANIMAL_ID=ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.ANIMAL_ID IS NULL;

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

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

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

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

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

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