[데이터 분석 5일차] SQL 코드카타 (Lv.1 -Lv.2 26번 까지)

1. 중복제거하기

동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성해주세요. 이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.

SELECT count(distinct name) Count
FROM ANIMAL_INS

DISTINCT의 경우에는 SELECT DISTINCT {컬럼명} FROM {테이블명} {조건절} 의 형태로 쓰면 되고,

SELECT CITY FROM TABLE WHERE CITY_CODE = 02 GROUP BY CITY ;

GROUP BY의 경우에는 SELECT {컬럼명} FROM {테이블명} {조건절} GROUP BY {테이블명} 의 형태로 쓰면 된다.

 

• DISTINCT : 중복을 없애주지만 정렬을 해주지 않는다.
• GROUP BY : 중복을 없애주고 정렬도 해준다.

이 때 GROUP BY는 그루핑 뿐만 아니라 정렬까지 수행하기 때문에 속도면에서 DISTINCT가 빠를 수 있다.

 

 

2. 중복값 구하기  (동명 동물 수 찾기)

동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.

 

SELECT name,
               count(name) count
from animal_ins
where name is not null
group by 1
having count(name)>1
order by name 

 

3. 아픈 동물 찾기

동물 보호소에 들어온 동물 중 아픈 동물1의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.

 

SELECT animal_ID,
              name
from animal_ins
where intake_condition = 'Sick'
order by 1

 

4, 상위 N개 레코드

동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요.

 

SELECT name
from animal_ins
order by datetime limit 1

 

5. 최솟값 구하기

동물 보호소에 가장 먼저 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.

 

SELECT datetime '시간'
from animal_ins
order by datetime limit 1

 

5. 어린 동물 찾기

동물 보호소에 들어온 동물 중 젊은 동물1의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.

 

SELECT animal_id,
              name
from animal_ins
where intake_condition <> 'aged'
order by 1

 

** where intake_condition in ('sick', 'normal') 은 오답인 이유는..?

 

 

7. 여러 기준으로 정렬

동물 보호소에 들어온 모든 동물의 아이디와 이름, 보호 시작일을 이름 순으로 조회하는 SQL문을 작성해주세요. 단, 이름이 같은 동물 중에서는 보호를 나중에 시작한 동물을 먼저 보여줘야 합니다.

 

SELECT animal_id,
       name,
       datetime
from animal_ins
order by name asc, datetime desc

 

8. 이름에 'el' 들어가는 '개' 찾기

보호소에 돌아가신 할머니가 기르던 개를 찾는 사람이 찾아왔습니다. 이 사람이 말하길 할머니가 기르던 개는 이름에 'el'이 들어간다고 합니다. 동물 보호소에 들어온 동물 이름 중, 이름에 "EL"이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 이름 순으로 조회해주세요. 단, 이름의 대소문자는 구분하지 않습니다.

 

SELECT animal_id,
              name
from animal_ins
where animal_type = "dog" and name like '%el%'
order by name

 

 

9. 나이 정보가 없는 회원 수 구하기

USER_INFO 테이블에서 나이 정보가 없는 회원이 몇 명인지 출력하는 SQL문을 작성해주세요. 이때 컬럼명은 USERS로 지정해주세요.

 

SELECT count(*) USERS
from user_info
Where age is null

 

**  Select count (age) USERS 안 되는 이유는..?

 

10. NULL 처리하기  (이름이 없는 개는 No name으로 표시하기)

입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요.

 

SELECT ANIMAL_TYPE,
       IF(NAME IS NULL, "No name", name) NAME,
       SEX_UPON_INTAKE
FROM ANIMAL_INS

 

 

 

11. 경기도에 위치한 식품창고 목록 출력

FOOD_WAREHOUSE 테이블에서 경기도에 위치한 창고의 ID, 이름, 주소, 냉동시설 여부를 조회하는 SQL문을 작성해주세요. 이때 냉동시설 여부가 NULL인 경우, 'N'으로 출력시켜 주시고 결과는 창고 ID를 기준으로 오름차순 정렬해주세요.

 

SELECT warehouse_id,
               warehouse_name,
               address,
               if(freezer_yn is null, 'N', freezer_yn)
from food_warehouse
where address like "%경기%"
order by 1

 

 

 

17. 강원도에 위치한 생산공장 목록 출력하기

FOOD_FACTORY 테이블에서 강원도에 위치한 식품공장의 공장 ID, 공장 이름, 주소를 조회하는 SQL문을 작성해주세요. 이때 결과는 공장 ID를 기준으로 오름차순 정렬해주세요.

SELECT factory_id,
       factory_name,
       address
from food_factory
where address like "%강원도%"
order by 1

 

18. DATETIME에서 DATE로 형 변환

ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜1를 조회하는 SQL문을 작성해주세요. 이때 결과는 아이디 순으로 조회해야 합니다.

 

SELECT animal_ID,
       name,
       date_format(date(datetime),'%Y-%m-%d') '날짜'
from animal_ins
order by 1

 

 

19. 흉부외과 또는 일반외과 의사 목록 출력하기

DOCTOR 테이블에서 진료과가 흉부외과(CS)이거나 일반외과(GS)인 의사의 이름, 의사ID, 진료과, 고용일자를 조회하는 SQL문을 작성해주세요. 이때 결과는 고용일자를 기준으로 내림차순 정렬하고, 고용일자가 같다면 이름을 기준으로 오름차순 정렬해주세요.

 

SELECT DR_NAME,
       DR_ID,
       MCDP_CD,
       date_format(date(HIRE_YMD),'%Y-%m-%d') HIRE_YMD
from doctor
where MCDP_CD='CS' or MCDP_CD='GS'
order by hire_ymd desc, dr_name

 

 

20. 가격이 제일 비싼 식품의 정보 출력하기

FOOD_PRODUCT 테이블에서 가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회하는 SQL문을 작성해주세요.

SELECT PRODUCT_ID,
       PRODUCT_NAME,
       PRODUCT_CD,
       CATEGORY,
       PRICE
from food_product
ORDER BY PRICE DESC LIMIT 1;

 

 

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

동물 보호소에 들어온 동물 중, 이름이 없는 채로 들어온 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.

 

SELECT animal_id
FROM animal_ins
where name is null
order by animal_id

 

22. 조건에 맞는 회원수 구하기

USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원이 몇 명인지 출력하는 SQL문을 작성해주세요.

 

SELECT COUNT(user_id) as users
FROM user_info
WHERE YEAR(joined) = '2021' AND age >= 20 AND age <= 29

 

23. 중성화 여부 파악하기

보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

 

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

 

 

 

24. 카테고리 별 상품 개수 구하기

PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

 

SELECT substr(product_code,1,2) as CATEGORY,
              count(*)
from product
group by CATEGORY
order by CATEGORY

 

 

 

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

동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.

 

SELECT animal_type,
               count(*) count
from animal_ins
where datetime is not null
group by 1
order by 1

 

 

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

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

 

SELECT hour(datetime) hour,
              count(datetime) count
from animal_outs
where hour(datetime) >= 9 and hour(datetime) <=19
group by hour
order by hour