SQL 작성순서
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
SELECT
문제1 모든 레코드 조회하기
동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요.
-- 코드를 입력하세요
SELECT * FROM ANIMAL_INS ORDER BY ANIMAL_ID
문제 2 역순 정렬하기
동물 보호소에 들어온 모든 동물의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 ANIMAL_ID 역순으로 보여주세요. SQL을 실행하면 다음과 같이 출력되어야 합니다.
-- 코드를 입력하세요
SELECT NAME, DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESC문제 3 아픈 동물 찾기
동물 보호소에 들어온 동물 중 아픈 동물1의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.
-- 코드를 입력하세요
SELECT ANIMAL_ID , NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION = "Sick" ORDER BY ANIMAL_ID
문제 4 어린 동물 찾기
동물 보호소에 들어온 동물 중 젊은 동물1의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.
INTAKE_CONDITION이 Aged가 아닌 경우를 뜻함
-- 코드를 입력하세요
SELECT ANIMAL_ID , NAME FROM ANIMAL_INS WHERE NOT INTAKE_CONDITION = "Aged" ORDER BY ANIMAL_ID문제 5 동물 이름 조회하기
동물 보호소에 들어온 모든 동물의 아이디와 이름을 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요. SQL을 실행하면 다음과 같이 출력되어야 합니다.
-- 코드를 입력하세요
SELECT ANIMAL_ID , NAME FROM ANIMAL_INS ORDER BY ANIMAL_ID문제 6 여러 기준으로 정렬하기
동물 보호소에 들어온 모든 동물의 아이디와 이름, 보호 시작일을 이름 순으로 조회하는 SQL문을 작성해주세요. 단, 이름이 같은 동물 중에서는 보호를 나중에 시작한 동물을 먼저 보여줘야 합니다.
-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS ORDER BY NAME, DATETIME DESC문제 7 상위 n개 레코드
동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요.
-- 코드를 입력하세요
SELECT NAME FROM ANIMAL_INS ORDER BY DATETIME LIMIT 1SUM, MAX, MIN
문제 1 최대값 구하기
가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.
-- 코드를 입력하세요
SELECT MAX(DATETIME) FROM ANIMAL_INS
문제 2 최솟값 구하기
동물 보호소에 가장 먼저 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.
-- 코드를 입력하세요
SELECT MIN(DATETIME) FROM ANIMAL_INS
문제 3 동물수 구하기
동물 보호소에 동물이 몇 마리 들어왔는지 조회하는 SQL 문을 작성해주세요.
-- 코드를 입력하세요
SELECT COUNT(ANIMAL_ID) FROM ANIMAL_INS문제 4 중복 제거하기
동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성해주세요. 이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.
-- 코드를 입력하세요
SELECT COUNT(DISTINCT NAME) FROM ANIMAL_INS WHERE NAME IS NOT NULLGROUP BY
문제 1 고양이와 개는 몇 마리 있을까
동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.
-- 코드를 입력하세요
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS count FROM ANIMAL_INS GROUP BY ANIMAL_TYPEAS로 이름 바꿔줘야 한다! -- 왜 틀렸다고 나올까....?
고양이를 개보다 먼저 조회하여야 한다조건을 고려하지 않아서 틀렸다고 나왔다.
-- 코드를 입력하세요
SELECT ANIMAL_TYPE , COUNT(ANIMAL_TYPE) AS count FROM ANIMAL_INS GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE Order By가 들어가야 하는 것이다.
문제 2 동명 동물 수 찾기
동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.
-- 코드를 입력하세요
SELECT NAME, COUNT(NAME) FROM ANIMAL_INS GROUP BY NAME HAVING COUNT(NAME) >= 2 ORDER BY NAME
- 집계된 컬럼에 대해서 조건을 지정할 때는
HAVING으로 지정
문제 3 입양 시각 구하기(1)
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
-- 코드를 입력하세요
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) FROM ANIMAL_OUTS
GROUP BY hour(DATETIME) ORDER BY HOUR
- 이렇게 입력을 하니 틀렸다고 나왔다.
- 자세히 살펴보니 시간제한이 09:00 부터였다.
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) as COUNT
FROM ANIMAL_OUTS GROUP BY HOUR(DATETIME) HAVING HOUR(DATETIME)
BETWEEN 9 AND 19 ORDER BY HOUR- 다시 이렇게 구했는데 에러가 나왔다.
- 아마 이미 HOUR로 바뀐 자료들에 다시 HOUR를 적용해서 에러가 발생한 것 같았다.
-- 코드를 입력하세요
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) as COUNT FROM ANIMAL_OUTS
GROUP BY HOUR HAVING HOUR BETWEEN 9 AND 19 ORDER BY HOUR
- 지정한 alias에 맞춰서 GROUP BY 와 HAVING절 조건을 바꿔주니 통과했다.
- 이미 alias로 만들어진 HOUR에 대해서 datetime 명령어와 혼동이 발생해서 발생된 문제인 것 같다.
- 실행순서의 문제인 것 같다.
참고 : https://stackoverflow.com/questions/14845981/datetime-group-by-date-and-hour/34674081
문제 4 입양 시각 구하기(2)
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다. - 입양이 없는 경우에는 0 으로 표시!
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS GROUP BY HOUR ORDER BY HOUR- 처음 이렇게 작성을 했을 때는 입양이 존재한 경우에만 표시된다.
SET @HOUR = -1;
SELECT (@HOUR := @HOUR +1) AS HOUR,
(SELECT COUNT(HOUR(DATETIME))
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME)=@HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23;-
이 문제같은 경우 풀이를 참고했다. SQL에서도 변수를 지정할 수 있는데 SET으로 가능하다
-
처음 SET으로 변수를 지정하고 첫번째 컬럼(HOUR)에는 +1씩 하는 loop를 돌리고
HOUR라는 이름으로 넣어준다. -
그리고 WHERE절에서 23시까지 더해져야 하니 23보다 적은 경우만 가져온다.
IS NULL
이름이 없는 동물의 아이디
동물 보호소에 들어온 동물 중, 이름이 없는 채로 들어온 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.
-- 코드를 입력하세요
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID
이름이 있는 동물의 아이디
동물 보호소에 들어온 동물 중, 이름이 있는 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.
-- 코드를 입력하세요
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL ORDER BY ANIMAL_IDNULL 처리하기
입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요. 마지막 줄의 개는 이름이 없기 때문에, 이 개의 이름은 "No name"으로 표시합니다. 따라서 SQL문을 실행하면 다음과 같이 나와야 합니다.
SELECT ANIMAL_TYPE, COALESCE(NAME, "No name") AS NAME , SEX_UPON_INTAKE FROM ANIMAL_INS ORDER BY ANIMAL_ID- 모든 자료를 표시하되 NULL인 값은
no name으로 바꿔줘야 하니Coalesce이용 - COALESCE(val1, val2, val3....) : 첫번째로 NULL이 아닌 값을 반환
JOIN
없어진 기록 찾기
ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다. 천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.
# -- 코드를 입력하세요
SELECT ANIMAL_ID, NAME FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (SELECT a.ANIMAL_ID FROM ANIMAL_OUTS as a
INNER JOIN ANIMAL_INS as b
ON a.ANIMAL_ID = b.ANIMAL_ID)
ORDER BY ANIMAL_ID- 결론적으로
ANIMAL_OUTS에는 존재하지만ANIMAL_INS ANIMAL_OUTS의 교집합에는 없는 것이어야 한다. - mysql 에서는 minus를 버전에 따라 지원하는 곳이 있고 지원하지 않는 곳이 있다. 따라서 정석은 서브쿼리로 구하는 것이다.
- 우선
JOIN을 통해 둘의교집합의 ANIMALID만 가져오고 해당ANIMALID의 속하지 않는 데이터를ANIMAL_OUTS에서 찾으면 된다.
있었는데요 없었습니다.
ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다. 관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.
-- 코드를 입력하세요
SELECT INS.ANIMAL_ID, INS.NAME FROM ANIMAL_INS AS INS
JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME- 두 집합의
DATETIME을 쉽게 비교하기 위해서JOIN으로 집합을 합치고 각 집합의 DATETIME을 비교하여 답을 구한다.
오랜 기간 보호한 동물(1)
ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다. 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.
-- 코드를 입력하세요
SELECT NAME, DATETIME FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN (SELECT a.ANIMAL_ID FROM ANIMAL_OUTS as a
INNER JOIN ANIMAL_INS as b
ON a.ANIMAL_ID = b.ANIMAL_ID)
ORDER BY DATETIME ASC
LIMIT 3
- 없어진 기록 찾기 문제와 동일한 접근
- 보호기간이 가장 긴 동물만 찾아야 하니 ANIMAL_OUTS와 ANIMAL_INS의 교집합에 포함되지 않는 동물 찾기
- 가장 기간이 긴 동물을 3명 데려와야 하니
DATETIME ASC로 정렬하고LIMIT 3
보호소에서 중성화한 동물
ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다. 보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화1되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.
String, Date
루시와 엘라 찾기
동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성해주세요.
SELECT ANIMAL_ID, NAME,SEX_UPON_INTAKE from animal_ins
WHERE NAME IN ("LUCY","ELLA","pickle","ROGAN","SABRINA","MITTY")
ORDER BY ANIMAL_ID ASC- 집합 안에 한가지 포함은 WHERE IN 절 사용
이름에 el이 들어가는 동물 찾기
보호소에 돌아가신 할머니가 기르던 개를 찾는 사람이 찾아왔습니다. 이 사람이 말하길 할머니가 기르던 개는 이름에 'el'이 들어간다고 합니다. 동물 보호소에 들어온 동물 이름 중, 이름에 "EL"이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 이름 순으로 조회해주세요. 단, 이름의 대소문자는 구분하지 않습니다.
-- 코드를 입력하세요
SELECT ANIMAL_ID ,NAME FROM ANIMAL_INS
WHERE NAME LIKE "%EL%" AND ANIMAL_TYPE ="DOG"
ORDER BY NAME ASC- LIKE "%"를 사용하면 포함되는 단어를 구할 수 있음
- LIKE "EL%" el로 시작하는 단어
- LIKE "%EL" el로 끝나는 단어
- LIKE "%EL%" el이 포함만 되면 다 조회
중성화 여부 파악하기
보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.
-- 코드를 입력하세요
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- 중성화 여부에 따라 표시되는 형태가 달라져야 해서
CASE WHEN THEN사용 CASE절에서 구한 값을 표시할 컬럼의 이름은END AS로 표현
오랜 기간 보호한 동물(2)
ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다. 입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.
SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME FROM ANIMAL_INS
JOIN ANIMAL_OUTS
ON ANIMAL_OUTS.ANIMAL_ID = ANIMAL_INS.ANIMAL_ID
order by DATEDIFF(ANIMAL_OUTS.DATETIME, ANIMAL_INS.DATETIME) DESC
limit 2;
- 기간차이는
DateDiff로 구할 수 있음 - JOIN을 해서 입양간 친구들을 찾고 그 친구들을 DATEDIFF 기준으로 정렬을 해주면 오랜 기간 보호소에 있던 친구순서로 정렬
- 상위 2개만 가져오기 위해서 limit 2
DATETIME에서 DATE로 형 변환
ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜를 조회하는 SQL문을 작성해주세요. 이때 결과는 아이디 순으로 조회해야 합니다.
- 날짜 : 시각(시-분-초)을 제외한 날짜(년-월-일)만 보여주세요.
-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, "%Y-%m-%d")AS 날짜 FROM ANIMAL_INS
ORDER BY ANIMAL_IDDate_format을 사용해서 데이터 날짜 형태 변경
DATE_FORMAT(DATETIME, "%Y-%M-%D")
%Y: 2020%M: August%D: 6th
DATE_FORMAT(DATETIME, "%y-%m-%d")
%y: 20%m: 08%d: 6
.jpeg)