[mysql][프로그래머스]SQL 고득점 kit 답!!

 

 

select

 

 

3월에 태어난 여성 회원 목록 출력하기 

select member_id, member_name, gender, date_format(date_of_birth, '%Y-%m-%d') as date_of_birth
from member_profile
where gender = 'w'
and tlno is not null
and month(date_of_birth) = 3;

select 하는 date는 date_format을 이용해서 포맷을 변경해줘야함 

특정 달을 선택하는 것은 날짜를 month로 씌워서 비교하면 된다. 

 

date_format에서 대소문자에 따라 어떤 format인지가 다르다.

Y가 대문자면 1992 와 같은 형식이 나오고 y라면 92가 나온다.

M이라면 march 이런 식으로 영어로 달이 나오고 m이라면 숫자로 나온다.

D라면 23th 이런 식으로 영어로  나오도 d라면 숫자로 나온다. 

 

 

 

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

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

 

 

서울에 위치한 식당 목록 출력하기 

SELECT A.rest_id, A.rest_name, 
A.food_type, A.favorites, 
A.address,round(avg(B.review_score) ,2) as score
from rest_info A right join rest_review B on A.rest_id = B.rest_id
group by A.rest_id
having A.address like '서울%'
order by score desc , A.favorites desc

별점이 있는 친구만 출력해야하므로 rest_review를 오른쪽으로 해서 right join해야한다.

반올림은 round를 이용한다. 

right join 대신 inner join을 사용해도 정답이다. 

select A.rest_id, A.rest_name, A.food_type, A.favorites, A.address, round(avg(B.review_score),2) as avgScore
from rest_info A inner join rest_review B on A.rest_id = B.rest_id
group by rest_id 
having address like '서울%'
order by avgScore desc, A.favorites desc

 where가 아닌 having을 사용해야한다. 해당 그룹으로 묶은 다음 address를 서울로 시작하는 컬럼을 찾기 때문이다.

 

 

 

 

재구매가 일어난 상품과 회원 리스트 구하기

SELECT user_id, product_id 
from online_sale
group by user_id, product_id
having count(*) >=2 
order by user_id, product_id desc

group 을 여러개 열으로  만들 수 있구나!

 

 

 

 

모든 레코드 조회하기

select * from ANIMAL_INS

 

역순 정렬하기

SELECT NAME, DATETIME from ANIMAL_INS order by ANIMAL_ID desc;

 

아픈 동물 찾기 

 

SELECT ANIMAL_ID, NAME from ANIMAL_INS where INTAKE_CONDITION='sick';

 

어린 동물 찾기

 

SELECT animal_id, name from animal_ins where intake_condition!='aged' order by animal_id;

 

동물의 아이디와 이름

 

select animal_id, name from animal_ins order by animal_id

 

여러 기준으로 정렬하기

 

select animal_id, name, datetime from animal_ins order by name , datetime desc

 

 

상위 n개 레코드

 

SELECT name from animal_ins order by datetime limit 1

 

 

 

 

---

 

 

 

 

SUM, MAX, MIN

 

 

가장 비싼 상품 구하기

SELECT max(price) as MAX_PRICE
from product

 

 

 

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

SELECT product_id, product_name, product_cd,category, price
from food_product 
where price in (select max(price) from food_product)

in 을 이용하면 서브쿼리를 이용할 수 있다. 

 

 

 

 

 

최댓값 구하기 

 

SELECT datetime from animal_ins order by datetime desc limit 1

 

또는 

 

select max(datetime) from animal_ins

 

 

최솟값 구하기

 

 

select min(datetime) from animal_ins

 

동물 수 구하기

 

SELECT count(*) from animal_ins

 

 

중복 제거하기

 

SELECT count(DISTINCT name) from animal_ins

distinct를 하면 중복을 제거할 수 있다. 

 

 

DISTINCT를 추가하여  NULL 값이 아니고, 중복을 제거한 개수를 가져올 수 있다.

또는 

 

select count(distinct name) from animal_ins where name is not null

이렇게 not null 조건을 붙일 수 있지만 count에서 알아서 해준다. 

 

 

참고 블로그 :

https://velog.io/@petit-prince/MySQL-%EB%AC%B8%EB%B2%95-COUNT-%EC%99%80-COUNTColName%EC%9D%98-%EC%B0%A8%EC%9D%B4

MySQL 문법 - COUNT() 함수

https://blog.naver.com/loleego/221621697252

[mysql] #33 행 개수 구하기 (count, null값, distinct로 중복제거)

 

 

 

 

 

---

 

 

 

 

GROUP BY

 

count는 NULL인 데이터는 제외하고 계산한다.

name에 null값이 있을 때 count(*)을 하면 그 null도 포함되어 개수가 나온다.

그러나 만약 count(name)을 하면 name이 null인 컬럼은 포함되지 않고 개수를 세어 반환한다. 

 

having은 group by로 그룹화된 새로운 테이블에 조건을 준다.

where은 모든 필드에 조건을 줄 수있다. 

 

 

 

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

 

SELECT animal_type, COUNT(animal_type) from animal_ins GROUP BY animal_type order by animal_type

 

동명 동물 수 찾기

 

SELECT NAME, count(name) as COUNT 
from animal_ins  
group by name  having count(name) >= 2 
order by name

또는 

select name, count(*) 
from animal_ins
group by name
having count(*)>=2 and name is not null
order by name

count(*)을 하면 null인것도 개수를 세기 때문에 having 조건에 not null을 붙여줘야한다. 

count(name)을 하면 알아서 null 인 것은 제외하고 개수를 구한다. 

 

 

 

입양 시각 구하기(1)

 

SELECT hour(DATETIME) as HOUR, count(hour(DATETIME)) as COUNT from animal_outs group by hour(DATETIME) having 9<=HOUR and 20>HOUR order by hour(DATETIME)

 

 

 

 

😈즐겨찾기가  가장 많은 식당 정보 출력하기 

select a.food_type, a.rest_id, a.rest_name, a.favorites
from (select food_type, max(favorites) as mx
        from rest_info 
        group by food_type) as b right join rest_info as a 
on a.food_type = b.food_type
where a.favorites = b.mx
order by a.food_type desc

group by한 select 문으로 테이블을 만들어 원래 테이블에  join을 시킨다.

어떻게 이런 생각을 하지...?

 

select food_type ,max(favorites) as MX
from rest_info
group by food_type

으로 food_type별로 묶어서 food_type 별 최대 즐겨찾기 수를 구한다. 

이 테이블과 join을 해서 MX의 값을 가진 원래 테이블 음식점을 조회한다. 

 

조금 다른 코드 :

select A.food_type, A.rest_id, A.rest_name, A.favorites
from rest_info A right join (select food_type ,max(favorites) as MX
                            from rest_info
                            group by food_type) B 
on A.food_type = B.food_type
where A.favorites = B.MX
order by food_type desc

 

조금 다른 코드 : 

select A.food_type, A.rest_id, A.rest_name, A.favorites
from rest_info A inner join (select max(favorites) as MAX from rest_info group by food_type) B 
on A.favorites = B.MAX
group by food_type
order by food_type desc

 

 

 

---

 

 

 

 

IS NULL

 

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

SELECT warehouse_id, warehouse_name, address, ifnull(freezer_yn,'N')
from food_warehouse 
where address like '경기%'
order by warehouse_id

 

 

이름이 없는 동물의 아이디

 

SELECT animal_id from animal_ins where name is null

 

 

이름이 있는 동물의 아이디

 

SELECT animal_id from animal_ins where name is not null order by animal_id

 

 

NULL 처리하기

 

SELECT animal_type, ifnull(name,"No name"), sex_upon_intake from animal_ins

ifnull (column_name, '대체할 값')

:  해당 컬럼이 null이라면 '대체할 값'으로 대체한다. 

 

 

 

 

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

select count(*)  as USERS
from user_info
where age is null

 

 

 

---

 

 

 

 

JOIN

 

😈그룹별 조건에 맞는 식당 목록 출력하기 

select x.MEMBER_NAME, y.REVIEW_TEXT, date_format(y.REVIEW_DATE, "%Y-%m-%d") review_date
from member_profile x inner join rest_review y
on x.member_id = y.member_id
where (y.member_id) in 
    -- ------------------ max 회원 찾기
    (select member_id
    from rest_review
    group by member_id
    having count(member_id) =
        -- -------------------- max 찾기
        (select max(cnt)
        from (
        select count(member_id) cnt
        from rest_review
        group by member_id) a
        )
        -- --------------------
    )
    -- ------------------
order by review_date

1. member_id별로 묶어서 그 개수를 select하는 테이블을 만든다.

2. 그 테이블으로부터 max값을 select 한다.

3. 그  max값이 개수인 member_id를 select한다.

4. 그  member_id와 동일한 member_id를 가진 리뷰를 출력한다.

 

 

having은 '='으로 비교가 가능하고 where에서는 서브쿼리 쓸 때 in써야한다. 

아닌가? 여러 개 중에서 비교할 때는 in을 쓰고 딱 해당 값이랑 맞을 때 = 을 쓰는건가?🤔🤔

 

 

없어진 기록 찾기

 

select outs.animal_id, outs.name 
from animal_ins ins right join animal_outs outs on ins.animal_id = outs.animal_id 
where ins.animal_id is null

 또는

 

select outs.animal_id, outs.name 
from animal_outs outs left join animal_ins ins on ins.animal_id = outs.animal_id 
where ins.animal_id is null

 

 

있었는데요 없었습니다.

 

SELECT ins.animal_id, ins.name 
from animal_ins ins left join animal_outs outs on ins.animal_id = outs.animal_id 
where ins.datetime > outs.datetime 
order by ins.datetime

 

 

오랜 기간 보호한 동물(1)

 

SELECT ins.name, ins.datetime 
from animal_ins ins left join animal_outs outs on ins.animal_id = outs.animal_id
where outs.animal_id is null 
order by datetime 
limit 3

 

 

보호소에서 중성화한 동물

 

SELECT outs.animal_id,  outs.animal_type, outs.name 
from animal_ins ins right join animal_outs outs on outs.animal_id = ins.animal_id
where ins.sex_upon_intake like 'Intact%' 
and (outs.sex_upon_outcome like 'Spayed%' or outs.sex_upon_outcome like 'Neutered%')

특정 단어로 시작하는 단어를 찾고 싶으면 like를 이용한다. 

like '특정단어%'

 

특정 단어로 끝나는 단어를 찾고 싶으면 특정단어를 끝에 쓴다.

like '%특정단어'

 

참고 : 

https://bactoria.tistory.com/22

MySQL like문 (특정 문자 포함되어있는지 검색)

 

 

상품 별 오프라인 매출 구하기 

SELECT p.product_code, (p.price * sum(off.sales_amount)) as SALES
from product p left join offline_sale off on off.product_id = p.product_id
group by p.product_code
having SALES is not null
order by SALES desc, p.product_code

 

 

---

 

 

String , Date

 

 

루시와 엘라 찾기 

SELECT animal_id, name, sex_upon_intake 
from animal_ins where name = 'Lucy' or name = 'Ella' or
name = 'Pickle' or name = 'Rogan' or name = 'Sabrina' or name = 'Mitty'

 

 

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

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

 

 

중성화 여부 파악하기 

 

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

 

 

 

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."