안녕하세요, 오늘은 지난 포스팅에 이어서 SQL 관련 개념을 학습하고 실제 문제에 적용해보겠습니다. 처음 공부하시는 분들은 지난 포스팅에서 SQL 기초 문법에 대해 설명해놓았으니 읽어보시기를 추천드립니다.
[SQL] 프로그래머스 코딩 테스트 MySQL 연습문제 (1) SELECT, ORDER BY, GROUP BY, HAVING, 서브 쿼리, 집계 함수
[SQL] 프로그래머스 코딩테스트 MySQL 연습문제 (SQL 고득점 Kit) (1)
안녕하세요, 이번 포스팅은 SQL 문법을 처음 접하는 분들도 관련 개념을 학습하면서 실제 문제에 적용할 수 있게끔 심혈을 기울여 작성하였습니다. 부디 도움이 되기를 바라며 포스팅을 시작하
codest.tistory.com
모든 문제의 저작권은 프로그래머스에게 있음을 밝힙니다.
테이블 구조
NULL의 처리
1. IS NULL
WHERE절 뒤에 사용하며 특정 필드의 값이 NULL일 경우 True, NULL이 아닐 경우 False를 반환합니다. 특정 필드의 값이 NULL일 경우의 데이터만 SELECT 가능합니다.
SELECT [필드명]
FROM [테이블명]
WHERE [특정필드명] IS NULL
2. IS NOT NULL
WHERE절 뒤에 사용하며 특정 필드의 값이 NULL이 아닐 경우 True, NULL일 경우 False를 반환합니다. 특정 필드의 값이 NULL이 아닐 경우의 데이터만 SELECT 가능합니다.
SELECT [필드명]
FROM [테이블명]
WHERE [특정필드명] IS NOT NULL
IS NULL > 이름이 없는 동물의 아이디
동물 보호소에 들어온 동물 중, 이름이 없는 채로 들어온 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_IDWHERE NAME IS NULL을 사용하여 NAME의 값이 NULL인 경우만 ANIMAL_ID를 SELECT 하였습니다.
IFNULL 함수
어떤 필드의 값이 NULL이라면 그 값을 무엇으로 대체할지 지정하는 함수입니다.
기본 문법은 다음과 같습니다.
SELECT IFNULL([필드명],[대체값])
FROM [테이블명]
IS NULL > NULL 처리하기
입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요.
SELECT ANIMAL_TYPE, IFNULL(NAME,"No name"), SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_IDIFNULL 함수를 사용하여 NAME의 값이 NULL인 경우 "No name"으로 대체했습니다.
데이터베이스의 테이블은 중복과 공간 낭비를 피하기 위해 여러 개의 테이블로 분리하여 저장합니다. 그리고 그 분리된 테이블은 서로 관계를 맺고 있습니다. 우리는 그것을 RDB(관계형 데이터베이스)라고 부릅니다. RDB의 가장 큰 특징으로는 테이블 내의 유일한 식별 키(Primary Key)를 통해 다른 테이블을 참조할 수 있다는 점입니다.
밑에 사진을 보시면 회원 테이블의 PK(Primary Key)와 구매 테이블의 FK(Foreign Key)가 1:N 관계로 연결되어 있는 것을 볼 수 있습니다. 회원 테이블의 아이디는 유일하고 구매 테이블의 아이디는 N개입니다.
이때 KBS라는 아이디를 가진 사람이 구매한 물품의 총액과 그 사람의 회원정보를 같이 조회하고 싶다면 어떻게 할까요? 두 테이블을 합쳐야 합니다. 이때 사용되는 것이 JOIN입니다.
JOIN 이란, 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만드는 것을 말합니다.
JOIN의 종류는 밑의 사진에서 볼 수 있고 주로 INNER JOIN(=JOIN)을 사용합니다.
INNER JOIN의 문법은 다음과 같습니다.
SELECT [열1, 열2, 열3 ...]
FROM [첫번째 테이블]
INNER JOIN [두번째 테이블]
ON [조인 조건]
WHERE [검색 조건]
JOIN의 종류는 다음과 같습니다.
JOIN > 없어진 기록 찾기
천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.
SELECT *
FROM ANIMAL_OUTS
INNER JOIN ANIMAL_INS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
우선 테이블 정보를 한눈에 보기 위해 단순히 INS 테이블과 OUTS 테이블을 JOIN 한 후 모든 정보를 조회합니다. INNER JOIN을 하게 되면 ON 조건에 따라 ANIMAL_ID가 같은 정보들만 조회됩니다. 하지만 INNER JOIN을 사용하게 되면 OUTS 테이블에는 정보가 존재하지만 INS 테이블에는 정보가 존재하지 않는 Row들은 조회할 수 없습니다.
그럼 LEFT JOIN을 하게 되면 어떤 결과가 발생할까요?
SELECT *
FROM ANIMAL_OUTS
LEFT JOIN ANIMAL_INS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
ANIMAL_ID가 같은 정보들이 조회되고 OUTS 테이블에만 있는 정보도 함께 조회됩니다.
여기서 왼쪽에 ANIMAL_ID부터는 OUTS 테이블의 정보이고 오른쪽에 ANIMAL_ID부터는 INS 테이블의 정보입니다. 결과 테이블을 보면 ANIMAL_ID가 같지 않더라도 OUTS 테이블에 있는 정보는 조회가 되는 것 을 볼 수 있습니다. 우리는 이 정보들을 조회해야 합니다.
우선 SELECT 절을 보면 ANIMAL_ID와 NAME을 조회할 때 어떤 테이블의 Column인지 정확히 명시해주었습니다. 단순히 ANIMAL_ID를 조회하게 되면 INS 테이블의 Column인지 OUTS 테이블의 Column인지 알 수 없기 때문에 ambigious error가 발생합니다. 그리고 WHERE절에 검색 조건으로 ANIMAL_INS.ANIMAL_ID가 없는 경우를 추가해주었습니다. 위에서 보았던 JOIN 종류 중에 ANTI LEFT JOIN을 사용한 것입니다.
SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_OUTS
LEFT JOIN ANIMAL_INS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID
최종 출력 테이블은 다음과 같습니다.
JOIN > 있었는데요 없었습니다
관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야 합니다.
SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS
JOIN ANIMAL_OUTS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.DATETIME > ANIMAL_OUTS.DATETIME
ORDER BY ANIMAL_INS.DATETIME
INNER JOIN(=JOIN)을 사용했고 조인 조건은 ANIMAL_ID가 같은 경우, 검색 조건은 들어온 날짜가 나간 날짜보다 뒤에 있을 때입니다. 여기서 가장 중요한 것은 조인 조건에 DATETIME 비교를 넣는 것이 아니라 조인 조건에는 Primary Key인 ANIMAL_INS.ANIMAL_ID와 Foreign Key인 ANIMAL_OUTS.ANIMAL_ID를 사용하고 검색 조건에 DATETIME 대소 비교를 사용해야 한다는 점입니다.
JOIN > 오랜 기간 보호한 동물(1)
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.
SELECT ANIMAL_INS.NAME, ANIMAL_INS.DATETIME
FROM ANIMAL_INS
LEFT 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
JOIN > 없어진 기록 찾기 문제와 매우 유사합니다. 우선 INNER JOIN을 사용하게 되면 INS 테이블에는 있지만 OUTS 테이블에 없는 동물의 데이터를 조회할 수 없기 때문에 LEFT JOIN을 사용합니다. 그리고, 검색 조건에 입양 간 기록이 없는 경우를 추가해줍니다. ORDER BY로 DATETIME을 정렬하게 되면 시간이 이른 순서대로 조회되기 때문에 오래 머무른 순으로 조회됩니다. 그리고 LIMIT 3으로 3개의 데이터만 제한하였습니다. 이 문제 또한 ANTI LEFT JOIN을 사용했습니다.
JOIN을 사용하지 않고 NOT IN과 서브 쿼리를 이용하여 푸는 방법도 있습니다.
SELECT NAME, DATETIME
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN
(SELECT ANIMAL_ID
FROM ANIMAL_OUTS)
ORDER BY DATETIME
LIMIT 3
검색 조건 안에 서브 쿼리를 사용하여 ANIMAL_INS의 ANIMAL_ID는 존재하지만 ANIMAL_OUTS의 ANIMAL_ID가 존재하지 않는 경우 WHERE절에서 TRUE를 반환하기 때문에 원하는 데이터만 조회할 수 있습니다.
LIKE 연산자는 WHERE절 뒤에 사용하며 특정 문자가 포함되어 있는 데이터를 조회할 때 사용합니다.
1. 특정 문자로 시작하는 데이터 조회
SELECT [필드명]
FROM [테이블명]
WHERE [필드명] LIKE '특정 문자열%'
2. 특정 문자로 끝나는 데이터 조회
SELECT [필드명]
FROM [테이블명]
WHERE [필드명] LIKE '%특정 문자열'
3. 특정 문자를 포함하는 데이터 조회
SELECT [필드명]
FROM [테이블명]
WHERE [필드명] LIKE '%특정문자열%'
또한 AND와 OR 연산자를 사용하여 여러 조건을 추가할 수 있습니다.
예를 들어, Height 필드에서 Tall을 포함한 경우와 Wealth 필드에서 Rich를 포함한 경우의 데이터를 얻고 싶다면 다음과 같이 조회할 수 있습니다.
SELECT [필드명]
FROM [테이블명]
WHERE Height LIKE '%Tall%'
OR Wealth LIKE '%Rich%'
위에 경우에는 다른 필드에서 조회하는 경우이고 동일한 필드에서 여러 개의 문자열을 포함한 데이터를 조회하고 싶을 때는 REGEXP 함수를 사용하여 필드명과 OR 연산자의 중복을 피할 수 있습니다.
4. 복수개의 특정 문자를 포함하는 데이터 검색 (특정 문자열을 '|'를 기준으로 나눕니다)
SELECT [필드명]
FROM [테이블명]
WHERE [특정 필드명]
REGEXP '[특정 문자열1]|[특정 문자열2]'
JOIN > 보호소에서 중성화한 동물
보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.
이때 중성화되지 않는 동물은 성별 및 중성화 여부에 Intact, 중성화된 동물은 Sprayed 또는 Neutered라고 표시되어 있습니다.
SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.ANIMAL_TYPE, ANIMAL_INS.NAME
FROM ANIMAL_INS
JOIN ANIMAL_OUTS
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.SEX_UPON_INTAKE LIKE '%Intact%'
AND ANIMAL_OUTS.SEX_UPON_OUTCOME NOT LIKE '%Intact%'
ORDER BY ANIMAL_INS.ANIMAL_ID
WHERE절 앞 까지는 일반적인 INNER JOIN 구문입니다.
WHERE 절에는 INS 테이블의 SEX_UPON_INTAKE에 Intact가 포함된 경우(중성화되지 않는 상태) 그리고 동시에 OUTS 테이블의 SEX_UPON_OUTCOME에 Intact가 포함되지 않은 경우(중성화된 상태)를 조건에 추가했습니다.
어려운 개념인 JOIN까지 학습해 보았습니다. 남은 문제는 마지막 포스팅에서 마무리하도록 하겠습니다. 도움이 되셨다면 구독과 하트 부탁드립니다. 감사합니다.
Reference
모든 문제는 programmers 사이트를 참고하였습니다.
코딩테스트 연습
기초부터 차근차근, 직접 코드를 작성해 보세요.
programmers.co.kr
Youtube > 한빛 미디어 MySQL 입문 강의 > SQL 고급 > 조인 개념과 Inner Join
Take your SQL from Good to Great: Part 3
Time to join the JOIN movement.
towardsdatascience.com
What is RDBMS - javatpoint
What is RDBMS with DBMS Overview, DBMS vs Files System, DBMS Architecture, Three schema Architecture, DBMS Language, DBMS Keys, DBMS Generalization, DBMS Specialization, Relational Model concept, SQL Introduction, Advantage of SQL, DBMS Normalization, Func
www.javatpoint.com