SQL 연습 + 개념 정리

김상우·2022년 3월 18일

ref : 프로그래머스 SQL Kit

RDB (Relational DataBase) 란

RDB (관계형 데이터베이스) : 데이터베이스를 테이블 형태로 저장하는 것을 관계형 데이터베이스 라고 한다.

행 = Row = Record = Tuple

열 = Col = Field = 속성

스키마 : 데이터베이스의 구조와 제약조건에 관해 명세한 것.

INSERT INTO

customer 테이블에 [ id = 1, name = "Elice", birthday = "2010-01-15", mileage = 100 ] 인 데이터 삽입하기
INSERT INTO customers (id, name, birthday, mileage)
VALUES (1, "Elice", "2010-01-15", 100);
포인트

이름과 날짜 데이터에 붙은 큰따옴표

쿼리문 마지막의 세미콜론

UPDATE SET WHERE

UPDATE my_table SET name = '김상우', country = '대한민국' WHERE id = 1996;

DELETE FROM WHERE

DELETE FROM my_table WHERE ID = "ksw"

SELECT, ORDER BY

NAME 으로 오름차순 정렬, TIME 으로 내림 차순 정렬해서 상위 3개 데이터의 ID 출력하기
SELECT ID FROM my_table
ORDER BY NAME, TIME DESC
LIMIT 3

NULL 처리

NAME, BIRTHDAY 를 출력하되, NULL 인 경우는 "No name"으로 출력하기
SELECT IFNULL(NAME, "No name") AS NAME, BIRTHDAY FROM my_table
IFNULL(타입, 대체물) AS 타입

MIN, MAX

SELECT MIN(ID) FROM my_table

SELECT MAX(ID) FROM my_table

중복 제거, COUNT

NAME 의 개수를 출력하되, 중복 되는 이름은 하나로 간주한다. 그리고 NULL 값인 경우도 카운트하지 않는다.
SELECT COUNT(DISTICT NAME) FROM my_table
WHERE NAME IS NOT NULL

GROUP BY, HAVING

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
SELECT HOUR(DATETIME), COUNT(HOUR(DATETIME)) FROM my_table
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) < 20
# WHERE HOUR(DATETIME) BETWEEN 9 AND 19 랑 같은 구문.
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)
동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.
SELECT NAME, COUNT(NAME) FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME
포인트 : Group 으로 묶은 다음에는 WHERE 이 아닌 HAVING 을 사용한다.

String 다루기, LIKE

-- 이름에 EL이 들어가는 개 찾기
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS
WHERE ANIMAL_TYPE = "Dog" AND NAME LIKE "%EL%"
ORDER BY NAME

"%EL%" = 앞 뒤로 얼마나 추가될지는 모르겠지만 EL 이 포함된 문자열

"EL_ _" = 뒤에 2개가 추가되는 문자열

-- 오랜 기간 보호한 동물
SELECT A.ANIMAL_ID, A.NAME FROM ANIMAL_INS AS A, ANIMAL_OUTS AS B
WHERE A.ANIMAL_ID = B.ANIMAL_ID 
ORDER BY B.DATETIME - A.DATETIME DESC
LIMIT 2

포인트 : FROM table1 AS A, table2 AS B

IF + LIKE

보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.
-- 3 중성화 여부 파악하기 - IF 문 사용
SELECT ANIMAL_ID, NAME, IF(SEX_UPON_INTAKE LIKE "%Neutered%" OR SEX_UPON_INTAKE LIKE "%Spayed%", "O", "X") AS 중성화 
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

SET - 변수 활용

프로그래머스 Lv4 : https://programmers.co.kr/learn/courses/30/lessons/59413
SET @HOUR_LIST = -1;
SELECT (@HOUR_LIST := @HOUR_LIST + 1) AS "HOUR",
(SELECT COUNT(DATETIME) FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) = @HOUR_LIST) AS "COUNT"
FROM ANIMAL_OUTS
WHERE @HOUR_LIST < 23;
SET @변수명 = 값;

@변수명 := @변수명 + 1

포인트

SET 키워드에서만 '=' 이 대입 연산자로 쓰인다. 일반적으로는 '=' 은 비교 연산자고, ':=' 를 사용해야 대입이 가능하다.

SET 뒤에 세미콜론 (;) 을 붙여줘야 된다 .

JOIN

ref: https://admm.tistory.com/40

LEFT OUTER JOIN

연습 문제 1 : https://programmers.co.kr/learn/courses/30/lessons/59042
-- 1 없어진 기록 찾기
SELECT OUTS.ANIMAL_ID, OUTS.NAME FROM ANIMAL_OUTS AS OUTS
LEFT OUTER JOIN ANIMAL_INS AS INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID
-> 포인트 : ON 구문을 까먹지말고 적어야 한다.

연습 문제 2 : https://programmers.co.kr/learn/courses/30/lessons/59044
-- 오랜 기간 보호한 동물 (1)
SELECT INS.NAME, INS.DATETIME FROM ANIMAL_INS AS INS
LEFT OUTER JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY INS.DATETIME
LIMIT 3
-> 여기서 join 을 꼭 해야하는 이유 : INS 에는 속하고, OUTS 에는 속하지 않는 데이터를 찾아야 하기 때문.
-> LEFT OUTER JOIN 을 한 테이블을 C 라고 하면, C에는 있지만 OUTS 에는 없는 데이터가 생기기 때문이다.
-> LIKE 에서 풀었던 두 번째 예제와는 상황이 다르다.

연습 문제 3 : https://programmers.co.kr/learn/courses/30/lessons/59045#fnref1
-- 보호소에서 중성화한 동물
SELECT OUTS.ANIMAL_ID, OUTS.ANIMAL_TYPE, OUTS.NAME FROM ANIMAL_OUTS AS OUTS
LEFT OUTER JOIN ANIMAL_INS AS INS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE (OUTS.SEX_UPON_OUTCOME LIKE "%Spayed%" or OUTS.SEX_UPON_OUTCOME LIKE "%Neutered%") AND (INS.SEX_UPON_INTAKE LIKE "%Intact%")
ORDER BY OUTS.ANIMAL_ID
INNER JOIN

연습 문제 : https://programmers.co.kr/learn/courses/30/lessons/59043
-- 2. 있었는데요 없었습니다 ! - 이 문제는 LEFT OUTER JOIN 으로도 풀림.
SELECT INS.ANIMAL_ID, INS.NAME FROM ANIMAL_INS AS INS
INNER JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME
포인트 : ON 구문을 까먹지말고 적어야 한다.