공부흔적

문제 링크

1. 루시와 엘라 찾기

  동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별을 출력하는 문제이다.

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
    FROM ANIMAL_INS
    WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')

  where 절에서 name = '~' || '~' || ... 꼴로 작성할 수도 있지만 in 연산자를 통해 더 쉽게 구현할 수 있다.

2. 이름에 el이 들어가는 동물 찾기

  동물 보호소에 들어온 동물 중 이름에 "EL"이 들어가는 개의 아이디와 이름을 이름 순으로 출력하는 문제이다.

SELECT ANIMAL_ID, NAME
    FROM ANIMAL_INS
    WHERE ANIMAL_TYPE = 'Dog' AND
          NAME LIKE '%EL%'
    ORDER BY NAME

  name 컬럼에서 문자열 "el"이 들어간 레코드를 찾기 위해서 like 연산자와 % 기호 연산자를 사용했다.

  이름이 el로 시작하는 경우 El, 아니면 el이 들어가지만 case insensitive하기 때문에 '%EL%'로 했다.

3. 중성화 여부 파악하기

  동물 보호소의 동물의 아이디, 이름, 중성화 여부를 아이디 순으로 출력하는 문제이다.

SELECT ANIMAL_ID, NAME,
       (CASE WHEN SEX_UPON_INTAKE REGEXP 'Neutered|Spayed' THEN 'O'
             ELSE 'X' END) AS "중성화"
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

  sex_upon_intake 컬럼의 값이 주어진 내용을 포함하는 문자열인지를 확인하기 위해서 like를 사용할 수도 있으나 그럴 경우 when을 한 줄 더 작성해야 하기 때문에 정규표현식 regexp를 사용했다.

  regexp를 사용하여 찾고자 하는 부분 문자열을 '|'로 연결하여 작성한다.

  따라서 중성화 컬럼은 (CASE WHEN SEX_UPON_INTAKE REGEXP 'Neutered|Spayed' THEN 'O' ELSE 'X' END) AS "중성화"라고 작성하면 된다.

4. 오랜 기간 보호한 동물(2)

  입양을 간 동물 중 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 보호 기간이 긴 순으로 출력하는 문제이다.

SELECT O.ANIMAL_ID, O.NAME
    FROM ANIMAL_OUTS O, ANIMAL_INS I
    WHERE O.ANIMAL_ID = I.ANIMAL_ID
    ORDER BY O.DATETIME - I.DATETIME DESC
    LIMIT 2

  우선 보호 기간이라는 것이 보호소에서 나간 날짜에서 보호소에 들어온 날짜 이므로 보호소에 들어온 정보와 나간 정보를 담은 테이블을 각각 I, O라고 할 때 O.DATETIME - I.DATETIME이 큰 순서대로 2개를 출력하면 된다.

  이때 보호소에서 나가지 않은 동물의 정보는 필요하지 않으므로 inner join을 했다.

5. DATETIME에서 DATE로 형 변환

  동물 보호소에 들어온 모든 동물의 아이디, 이름 들어온 날짜를 아이디 순으로 출력하는 문제이다.

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME,'%Y-%m-%d') AS "날짜"
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

  DATETIME 형식을 DATE 형식으로 바꾸기 위해 DATE_FORMAT( )를 사용했다.

* 날짜 컬럼을 DATE(DATETIME)으로 해도 DATETIME 형식으로 나오는데 그 이유는 모르겠다.

문제 링크

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

  동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 출력하는 문제이다. (고양이, 개 순서)

  동물을 종류에 따라 묶기 위해 group by 절을 사용했다.

SELECT ANIMAL_TYPE, COUNT(*) AS count
    FROM ANIMAL_INS
    GROUP BY ANIMAL_TYPE
    ORDER BY ANIMAL_TYPE

  문제에서는 고양이와 개라고 특정 지었지만 group by 절을 사용하여 출력하니 정답으로 처리돼었다. 이방법으로 되지않았다면 where절을 사용하여 고양이와 개의 수를 각각 구한 후 union을 사용하는 방법이 있다.

SELECT ANIMAL_TYPE, COUNT(*) AS count
    FROM ANIMAL_INS
    WHERE ANIMAL_TYPE = "Cat"
UNION
SELECT ANIMAL_TYPE, COUNT(*) AS count
    FROM ANIMAL_INS
    WHERE ANIMAL_TYPE = "Dog"

2. 동명 동물 수 찾기

  동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 출력하는 문제이다. 이때 이름이 없는 동물은 집계에서 제외한다.

  group by로 그룹으로 나눈 후 그 수가 2 이상이어야 하므로 having절을 사용했다.

SELECT NAME, COUNT(NAME) AS COUNT
    FROM ANIMAL_INS
    GROUP BY NAME
    HAVING COUNT > 1
    ORDER BY NAME

  name 컬럼이 null인 레코드를 집계에서 제외하기 위해서 count( )에 컬럼명 name을 넣었다.

  그룹의 수가 2 이상인 즉 1보다 크다는 조건이 있으므로 having count > 1을 썼다.

* 이때 where count > 1이 되면 그룹을 만들기 이전에 검사하기 때문에 문제에서 원하는 결과를 낼 수 없다. 이 문제에서는 where절에서 count를 사용할 수 없기 때문에 실행조차 되지 않는다.

  ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블이다.

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

  이 문제에서는 ANIMAL_OUTS 테이블을 사용했다.

  동물 보호소에서 9시부터 19시까지 각 시간대 별로 입양이 몇 건 발생했는지 출력하는 문제이다.

SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY HOUR
    HAVING HOUR >= 9 && HOUR < 20
    ORDER BY HOUR

  날짜 형식의 입양일에서 시간만 사용하기 위해 hour( )를 사용해서 원하는 시간만 뽑아내 hour 컬럼으로 만들었다.

  이후 hour 컬럼에 대해서 그룹을 만들고 hour의 조건이 주어지기 때문에 having절을 사용했다.

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

  이 문제에서도 ANIMAL_OUTS 테이블을 사용했다.

  동물 보호소에서 0시부터 23시까지 각 시간대 별로 입양이 몇 건 발생했는지 출력하는 문제이다.

SET @HOUR_IT := -1; 
SELECT @HOUR_IT := @HOUR_IT+1 AS 'HOUR', 
    (SELECT COUNT(*) 
        FROM ANIMAL_OUTS 
        WHERE HOUR(DATETIME) = @HOUR_IT) AS 'COUNT' 
    FROM ANIMAL_OUTS 
    WHERE @HOUR_IT < 23;

  COUNT만을 사용하는 경우 0인 결과에 대해서는 출력하지 않기 때문에 SET을 사용하여 변수 HOUR_IT를 만든 이후 사용했다.

  HOUR에 HOUR_IT라는 변수를 사용하여 0부터 23까지 넣고, COUNT에는 HOUR(DATETIME)과 HOUR_IT가 일치하는 레코드의 수를 센 후 값을 넣었다.

 문제 링크

  같은 테이블을 사용한다.
 

1. 이름이 없는 동물의 아이디

  동물 보호소에 들어온 동물 중 이름이 없는 채로 들어온 동물의 ID를 출력하는 문제이다.
  name 컬럼이 비어있는지 확인하기 위해 is null을 사용하는 문제이다.

SELECT ANIMAL_ID
    FROM ANIMAL_INS
    WHERE NAME IS NULL
    ORDER BY ANIMAL_ID

 
  name 컬럼이 비어있는지 확인하기 위에 where 절에서 is null을 했다.

  * null 비교는 등호 사용이 불가능하다.
 

2. 이름이 있는 동물의 아이디

  동물 보호소에 들어온 동물 중 이름이 있는 동물의 ID를 출력하는 문제이다.
  위의 문제와 반대의 결과를 출력하면 된다.

SELECT ANIMAL_ID
    FROM ANIMAL_INS
    WHERE NAME IS NOT NULL
    ORDER BY ANIMAL_ID

 
  위의 문제와 반대로 name 컬럼이 null이 아닌 레코드를 출력하기 위해서 where 절에서 is not null을 사용한다.
 

3. NULL 처리하기

  동물 보호소에 들어온 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 출력하는 문제이다. 이때 이름이 비어있는 동물의 이름은 No name으로 출력한다.

SELECT ANIMAL_TYPE, IFNULL(NAME, "No name"), SEX_UPON_INTAKE
    FROM ANIMAL_INS

 
  name 컬럼에 대해서 값이 비어있는 경우 No name을 넣기 위해 ifnull( )을 사용했다.

문제 링크

  이 전에 포스팅한 글과 같은 테이블을 사용한다.

1. 최댓값 구하기

  동물 보호소에 가장 최근에 들어온 동물이 언제 들어왔는지 출력하는 문제이다.

  가장 최근 즉 보호 시작일이 가장 큰 동물의 보호 시작일(datetime)을 출력하면 되므로 max( )를 사용한다.

SELECT MAX(DATETIME)
    FROM ANIMAL_INS

  datetime 컬럼에 대해서 최댓값을 출력하고자 하므로 select max(datetime)을 해주면 된다.

  이전에 포스팅한 글에서처럼 내림차순으로 정렬한 후 limit 절을 사용하여 같은 답을 출력할 수 있다.

2. 최솟값 구하기

  동물 보호소에 가장 먼저 들어온 동물이 언제 들어왔는지 출력하는 문제이다.

  위의 문제와 반대로 보호 시작일이 가장 작은 동물의 보호 시작일을 출력하면 되므로 min( )을 사용한다.

SELECT MIN(DATETIME)
    FROM ANIMAL_INS

  datetime 컬럼에 대해서 최솟값을 출력하고자 하므로 select min(datetime)을 해주면 된다.

  이전에 포스팅한 글에서처럼 오름차순으로 정렬한 후 limit 절을 사용하여 같은 답을 출력할 수 있다.

3. 동물 수 구하기

  동물 보호소에 동물이 몇 마리 들어왔는지 출력하는 문제이다.

  레코드의 수를 세기 위해서 count( )를 사용한다.

SELECT COUNT(*)
    FROM ANIMAL_INS

  특정 컬럼을 고려할 필요 없으므로 select count(*)를 해주었다.

4. 중복 제거하기

  동물 보호소에 들어온 동물의 이름의 수를 NULL과 중복을 제외하여 출력하는 문제이다.

  위의 문제와 마찬가지로 count( )를 사용한다.

SELECT COUNT(DISTINCT NAME)
    FROM ANIMAL_INS

  단 중복을 이름, 즉 name 컬럼에 대해서 중복을 제거하기 위해 distinct 키워드를 사용했다.

  *을 사용했을 때와 달리 count( ) 의 인자로 컬럼을 직접 명시해줬기 때문에 NULL 값은 자동적으로 제외된다.

문제 링크

  위의 표는 동물 보호소에 들어온 동물의 정보를 담은 ANIMAL_INS 테이블의 구조를 나타낸 것으로

  각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타낸 것이다.

  아래 7개의 문제는 위 테이블에 대해 구하고자 하는 것을 구하는 것이다.

1. 모든 레코드 조회하기

  동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID 순으로 출력하는 문제이다.

  이 문제를 통해서 select와 order by를 사용했다. ( default - asc )

SELECT *
    FROM ANIMAL_INS
    ORDER BY ANIMAL_ID

  ANIMAL_INS 테이블의 모든 정보를 조회하므로 select * from animal_ins 를 해주고

  animal_id 순으로 조회하므로 order by animal_id를 해주면 된다.

2. 역순 정렬하기

  동물 보호소에 들어온 모든 동물의 이름과 보호 시작일을  ANIMAL_ID 역순으로 출력하는 문제이다.

  이 문제에서 역순으로 정렬하기 위한 order by (기준) desc를 사용했다.

SELECT NAME, DATETIME
    FROM ANIMAL_INS
    ORDER BY ANIMAL_ID DESC

  이 문제는 동물의 모든 정보가 아닌 이름과 보호 시작일을 원하기 때문에 select name, datetime 을 해준 후 animal_id에 대해서는 역순으로 출력하기 때문에 order by animal_id desc를 해주었다.

* 이문제를 풀 때 datetime을 date_format을 사용하여 포매팅하지 않아야 정답으로 인정된다.

3. 아픈 동물 찾기

  동물 보호소에 들어온 동물 중 아픈 동물의 아이디와 이름을 아이디 순으로 출력하는 SQL문을 작성하는 문제이다.

  이 문제에서는 조건을 확인하기 위해 where절을 사용했다.

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

  동물의 아이디와 이름을 출력하므로 select animal_id, name이다.

  아프다는 조건이 있으므로 intake_condition 컬럼에서 Sick인 것을 찾으면 된다. where intake_condition = "Sick"

* "Sick"가 아닌 "sick"를 입력했을 때도 정답 처리되는 것으로 보아 대소문자를 구분하지 않도록 설정이 돼있는 것으로 보인다.

4. 어린 동물 찾기

  동물 보호소에 들어온 동물 중 젊은 동물의 아이디와 이름을 아이디 순으로 출력하는 문제이다.

  이 문제에서는 where절에서 비교연산자 "!="("<>")를 사용했다.

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

  문제에서 젊은 동물은 intake_condition이 Aged가 아니라고 문제에서 제시돼있기 때문에

  where절에 intake_condition != "Aged" 또는 intake_condition <> "Aged"를 해주면 된다.

* 위 문제와 마찬가지로 "" 안의 대소문자는 답에 영향을 주지 않았다.

5. 동물의 아이디와 이름

  동물 보호소에 들어온 모든 동물의 아이디와 이름을 ANIMAL_ID 순으로 출력하는 문제이다.

SELECT ANIMAL_ID, NAME
    FROM ANIMAL_INS
    ORDER BY ANIMAL_ID

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

  동물 보호소에 들어온 모든 동물의 아이디와 이름, 보호 시작일을 이름 순, 보호일 역순으로 출력하는 문제이다. 즉 이름이 같은 경우 보호일의 역순으로 출력해야 한다.

  순서를 두 컬럼에 대해서 봐야 하기 때문에 order by 뒤에 두 컬럼을 적어준다.

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

  문제의 조건에 따라 order by 에 두 컬럼을 적어주고 datetime에 대해서는 역순이어야 하므로 desc를 붙여주었다.

* 두 컬럼에 대해서 정렬할 경우 순서에 신경 써야 한다.

7. 상위 n개의 레코드

  동물 보호소에 가장 먼저 들어온 동물의 이름을 출력하는 문제이다.

  즉 상위 1개의 레코드를 출력하는 문제이기 때문에 limit 절을 사용해서 해결했다.

SELECT NAME
    FROM ANIMAL_INS
    ORDER BY DATETIME
    LIMIT 1

  order by 절을 통해 테이블을 datetime에 대해 오름차순으로 정렬한 후 한 줄만 출력하기 위해 limit 절을 사용했다.