안녕하세요, 이번 포스팅은 SQL 문법을 처음 접하는 분들도 관련 개념을 학습하면서 실제 문제에 적용할 수 있게끔 심혈을 기울여 작성하였습니다. SQL 개념에 대해 모르시는 분들은 RDBMS와 SQL 부분을 참고해주세요.
[SQL] 데이터베이스와 친해지기 (DB, DBMS, RDBMS, SQL, ORM)
[SQL] 데이터베이스와 친해지기 (DB, DBMS, RDBMS, SQL, ORM)
안녕하세요! 오늘은 들어는 봤지만 이해하기 힘들었던 데이터베이스 관련 용어들과 친해지는 시간입니다. 복잡한 개념인 만큼 이해를 돕기 위해 어려운 용어나 세부적인 내용은 생략했으니 참
codest.tistory.com
부디 도움이 되기를 바라며 포스팅을 시작하겠습니다.
모든 문제의 저작권은 프로그래머스에게 있음을 밝힙니다.
테이블 구조
SELECT 절은 테이블이 이미 생성되어있는 상태에서 원하는 Column을 조회하는 데 사용합니다.
-- SELECT 절의 구조
SELECT 필드명
FROM 테이블명
-- 테이블 전체 조회
SELECT *
FROM 테이블명
-- 원하는 필드만 조회
SELECT 필드명1, 필드명2
FROM 테이블명
MySQL 대소문자 구분 규칙
MySQL에서 키워드와 구문, 문자열은 대소문자를 구분하지 않습니다.
하지만 테이블 명과 필드의 이름은 대소문자를 구분하므로, 주의해서 사용해야 합니다.
SELECT > 어린 동물 찾기
동물 보호소에 들어온 동물 중 젊은 동물의 아이디와 이름을 조회하는 SQL 문을 작성해주세요.
이때 결과는 아이디 순으로 조회해주세요. 젊은 동물이란 INTAKE_CONDITION이 Aged가 아닌 경우를 말합니다.
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != "Aged"
ORDER BY ANIMAL_ID
WHERE 절은 Python 같은 언어의 if 문(조건문)이라고 생각하시면 됩니다.
ORDER BY 절은 로우를 정렬할 때 사용합니다. ASC는 생략 가능합니다.
| 데이터 형태 | ASC (오름차순) | DESC (내림차순) |
| 숫자 | 작은 값부터 정렬 | 큰 값부터 정렬 |
| 문자 | 사전 순으로 정렬 | 사전 역순으로 정렬 |
| 날짜 | 빠른 날짜부터 정렬 | 늦은 날짜부터 정렬 |
| NULL | 가장 첫번째 로우에 표시 | 가장 마지막 로우에 표시 |
SELECT > 여러 기준으로 정렬하기
동물 보호소에 들어온 모든 동물의 아이디와 이름, 보호 시작일을 이름 순으로 조회하는 SQL문을
작성해주세요. 단, 이름이 같은 동물 중에 보호를 나중에 시작한 동물을 먼저 보여줘야 합니다.
SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME ASC, DATETIME DESC
여기서 ORDER BY 문은 첫 번째 조건(현재 코드에서 NAME)을 기준으로 오름차순 정렬하되, 첫 번째 조건이 동일한(=이름이 같은) 로우들을 만나게 될 경우 그다음 조건을 기준으로 해당 로우들을 내림차순 정렬합니다. 여기서 ASC는 생략이 가능합니다. (ORDER BY문은 Default 값이 ASC)
집계 함수란? 입력값이 여러 개의 로우이고 출력 값이 하나인 함수
자주 쓰이는 집계 함수로는 SUM, MAX, MIN, AVG, COUNT 함수가 있습니다.
1. SUM 함수
특정 필드의 수치형 데이터(숫자)를 모두 더하여 반환하는 함수
NULL값은 무시됩니다.
SELECT SUM(필드명) FROM 테이블명
2. AVG 함수
특정 필드의 수치형 데이터(숫자)의 평균을 반환하는 함수
NULL값은 무시됩니다.
SELECT AVG(필드명) FROM 테이블명
3. MAX, MIN 함수
특정 필드의 최댓값, 최솟값을 반환하는 함수
수치형 데이터뿐만 아니라 문자열, 날짜형, 시간형에서도 사용 가능하고 NULL값은 무시됩니다.
SELECT MAX(필드명) FROM 테이블명
SELECT MIN(필드명) FROM 테이블명
4. COUNT 함수
전체 로우의 개수나 특정 필드의 로우의 개수를 반환하는 함수
NULL값은 무시됩니다.
SELECT COUNT(* OR 필드명) FROM 테이블명
추가) DISTINCT 함수
단순히 중복된 데이터를 제외하고 계산합니다.
SELECT DISTINCT 필드명 FROM 테이블명
COUNT와 DISTINCT를 같이 사용할 경우는 NULL값과 중복 값이 모두 제외됩니다.
SELECT COUNT(DISTINCT 필드명) FROM 테이블명SUM, MAX, MIN > 최댓값 구하기
가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.
- 직관적인 풀이
들어온 시간 역순으로 정렬한 후 1개의 데이터만 출력하는 풀이입니다.
SELECT DATETIME AS "시간"
FROM ANIMAL_INS
ORDER BY DATETIME DESC
LIMIT 1
- MAX 함수를 이용한 풀이
코드가 짧고 동작성능이 더 좋습니다.
SELECT MAX(DATETIME) AS "시간"
FROM ANIMAL_INS
SUM, MAX, MIN > 동물 수 구하기
동물 보호소에 동물이 몇 마리 들어왔는지 조회하는 SQL 문을 작성해주세요.
SELECT COUNT(ANIMAL_ID)
FROM ANIMAL_INSANIMAL_ID가 ANIMAL_INS 테이블에서 유일하게 식별할 수 있는 Primary Key 이기 때문에 ID를 기준으로 COUNT 해야 합니다. NAME으로 COUNT 하게 되면 이름이 같은 동물은 카운트되지 않습니다.
SUM, MAX, MIN > 중복 제거하기
동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성해주세요.
이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.
SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
COUNT 함수 자체에서 NULL값을 제외하고 계산하기 때문에 따로 NULL값을 제외할 필요는 없습니다.
GROUP BY 절은 데이터들을 원하는 그룹으로 나누어 줍니다. 이때 중복된 로우들을 제외하여 데이터가 출력되는데 이는 DISTINCT 함수와 같은 기능을 합니다. 그렇다면 둘의 차이점은 무엇일까요? DISTINCT 함수는 단순히 중복된 데이터를 제외할 때 사용하고 GROUP BY 절은 집계 함수를 사용하여 ~~ 별 데이터를 구하고 싶을 때 사용합니다.
예를 들어, 밑에 사진처럼 장르별 책의 개수를 구하고 싶다면 GRUOP BY와 SUM 함수를 사용하면 됩니다.
합계를 구하기 위한 코드는 다음과 같습니다.
SELECT genre, SUM(qty) AS "total"
FROM BOOKS
GROUP BY genreGROUP BY > 고양이와 개는 몇 마리 있을까
동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요.
이때 고양이를 개보다 먼저 조회해주세요.
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE
ANIMAL_TYPE으로 그룹화하여 Cat과 Dog의 수를 묶어서 카운트했고 제한 조건에 고양이를 개보다 먼저 조회해야 한다고 했으므로 ORDER BY절을 사용해 사전 순으로 정렬했습니다. 이때 사전 순 정렬을 하지 않아도 C는 D보다 앞에 있기 때문에 결괏값은 같지만 채점 결과는 오답으로 처리됩니다.
GROUP BY > 동명 동물 수 찾기
동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.
SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) > 1
ORDER BY NAME ASC
HAVING 절은 GROUP BY 절과 함께 사용하며 집계 함수를 이용해 출력된 데이터를 바탕으로 조건비교를 할 때 사용합니다. 앞에서 사용했던 WHERE 절의 기능과 같지만 WHERE 절은 HAVING 절과 달리 집계 함수와 함께 사용할 수 없다는 차이점이 있습니다. 그 이유는 집계 함수를 사용할 수 있는 GROUP BY 문보다 WHERE 문이 먼저 수행되기 때문입니다.
결론적으로, HAVING 절은 그룹화 또는 집계가 발생한 후의 데이터를 필터링하는 데 사용하고 WHERE 절은 그룹화 또는 집계가 발생하기 전의 데이터를 필터링하는 데 사용합니다.
날짜 데이터 추출 함수
1. YEAR 함수
YEAR(DATE)와 같이 사용하며 DATE 안에 연도를 추출합니다.
2. MONTH 함수
MONTH(DATE)와 같이 사용하며 DATE 안에 월을 추출합니다.
3. DAY 함수
DAY(DATE)와 같이 사용하며 DATE 안에 일을 추출합니다.
4. HOUR 함수
HOUR(DATE)와 같이 사용하며 DATE 안에 시를 추출합니다.
5. MINUTE 함수
MINUTE(DATE)와 같이 사용하며 DATE 안에 분을 추출합니다.
6. SECOND 함수
SECOND(DATE)와 같이 사용하며 DATE 안에 초를 추출합니다.
여기서 시각인 17을 추출하고 싶다면 다음과 같이 사용하면 됩니다.
SELECT HOUR(2016-03-05 17:50:00)
GROUP BY > 입양 시각 구하기 (1)
이 문제부터는 ANIMAL_OUTS 테이블도 사용합니다.
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
SELECT HOUR(DATETIME), COUNT(HOUR(DATETIME))
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 9 AND 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)
HOUR 함수를 이용하여 DATETIME의 시를 추출했고 그룹화 전에 데이터를 필터링하기 위해 WHERE 절을 사용하여 9시부터 19시까지의 HOUR(DATETIME)만을 필터링했습니다. 또한 입양 시각별로 입양 건수를 나누어야 하기 때문에 GROUP BY를 사용했습니다.
GROUP BY > 입양 시각 구하기 (2)
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다. 단, 입양이 되지 않은 시각의 입양 건수도 0으로 테이블에 포함되어야 합니다.
SELECT HOUR(DATETIME), COUNT(HOUR(DATETIME))
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 0 AND 23
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)
요구 조건에 맞게 0시 ~ 23시의 입양 건수를 입양 시각별로 그룹화하여 작성하였습니다.
하지만 결과는 원하는 대로 나오지 않았습니다. 그 이유는 입양이 발생하지 않는 DATETIME은 입양 건수가 COUNT 되지 않기 때문에 NULL값으로 존재하게 됩니다. 그래서 NULL인 부분을 0으로 표시하게끔 처리해주어야 합니다.
그러기 위해서 사용자 정의 변수를 선언하는 문법인 SET과 서브 쿼리를 이용하여 문제를 풀어보겠습니다.
사용자 정의 변수 선언 방법에는 두 가지가 있습니다.
1. SET @변수명 = 대입값;
2. SELECT @변수명 := 대입값;
우선 SET을 이용해 HOUR Column을 새롭게 정의해주겠습니다. @HOUR을 -1로 선언해주고 @HOUR := @HOUR + 1 문법을 사용하여 0부터 23까지의 정수를 SELECT 해주었습니다.
SET @HOUR = -1;
SELECT @HOUR := @HOUR + 1 AS HOUR
FROM ANIMAL_OUTS
WHERE @HOUR < 23
이러면 입양 시각에 대한 테이블은 완성이 됐고 출력 결과는 다음과 같습니다.
위의 문법이 이해가 안 되신다면 파이썬의 변수 선언과 반복문을 생각하시면 됩니다. hour의 초기값을 -1로 설정해주고 1부터 24까지 더해준다면 0부터 23의 값을 얻을 수 있습니다.
for n in range(1,25):
hour = -1
hour += n
print(hour)
이제 서브 쿼리를 추가하여 입양 시각별 입양 건수를 COUNT 해보겠습니다.
SET @HOUR = -1;
SELECT @HOUR := @HOUR + 1 AS HOUR,
(SELECT COUNT(HOUR(DATETIME))
FROM ANIMAL_OUTS
WHERE @HOUR = HOUR(DATETIME)) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23
이렇게 서브 쿼리를 이용하는 이유는 HOUR이 0부터 23으로 SELECT 되어있는 상태에서 COUNT를 해야 하기 때문입니다.
다음 문제부터는 두 번째 포스팅에서 이어서 학습해보도록 하겠습니다. 감사합니다.
Reference
모든 문제는 programmers 사이트를 참고하였습니다.
코딩테스트 연습
기초부터 차근차근, 직접 코드를 작성해 보세요.
programmers.co.kr
GROUP BY (上) : 개념과 실제 사용 방법
SQL 공부를 시작하면 얼마 되지 않아 GROUP BY 라는 개념을 배우게 됩니다. 데이터를 그룹화하는 것, 즉 ㄷ이터를 집계하는 것은 데이터베이스 상에서 상당히 중요한 개념입니다. 이 글에서는, 실
kimsyoung.tistory.com
'Algorithm > SQL' 카테고리의 다른 글
| [SQL] 프로그래머스 코딩테스트 MySQL 연습문제 (3) IN 연산자, REPLACE, DATEDIFF와 TIMESTAMPDIFF, DATE_FORMAT (0) | 2022.07.10 |
|---|---|
| [SQL] 프로그래머스 코딩테스트 MySQL 연습문제 (2) NULL처리, INNER JOIN과 LEFT JOIN, LIKE 연산자, REGEXP (0) | 2022.07.10 |