[SQL 고득점 Kit] SQL 코테 준비를 해봅시다 - 1일차

https://school.programmers.co.kr/learn/courses/30/lessons/151136

프로그래머스

-- 코드를 입력하세요
SELECT round(avg(daily_fee), 0) as AVERAGE_FEE from car_rental_company_car
group by CAR_TYPE having CAR_TYPE = 'SUV';

# select * from  car_rental_company_car where car_type = 'SUV';

 

round (숫자, 0) = 첫 째 자리에서 반올림

round (숫자, 1) = 둘 째 자리에서 반올림

 

https://school.programmers.co.kr/learn/courses/30/lessons/131536

프로그래머스

 

-- 코드를 입력하세요
SELECT USER_ID, PRODUCT_ID from online_sale
group by user_id,product_id having count(*) >= 2 order by user_id, product_id desc;

 

count는 group by가 있으면, 그룹핑 된 것들의 갯수를 센다

 

https://school.programmers.co.kr/learn/courses/30/lessons/59035

프로그래머스

-- 코드를 입력하세요
SELECT NAME, DATETIME from animal_ins
order by animal_id desc;

 

https://school.programmers.co.kr/learn/courses/30/lessons/59036#fn1

프로그래머스

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME from ANIMAL_INS
where INTAKE_CONDITION = 'Sick' order by ANIMAL_ID;

https://school.programmers.co.kr/learn/courses/30/lessons/59037

프로그래머스

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME from ANIMAL_INS
where INTAKE_CONDITION not in('Aged') order by ANIMAL_ID;

 

https://school.programmers.co.kr/learn/courses/30/lessons/59403

프로그래머스

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME from ANIMAL_INS
order by ANIMAL_ID;

 

https://school.programmers.co.kr/learn/courses/30/lessons/59404

프로그래머스

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME, DATETIME from ANIMAL_INS
order by name, datetime desc;

 

https://school.programmers.co.kr/learn/courses/30/lessons/59405

프로그래머스

-- 코드를 입력하세요
SELECT name from ANIMAL_INS
order by datetime
limit 1 offset 0;

 

limit은 몇 개 출력할지

offset은 어디부터 시작할지

 

https://school.programmers.co.kr/learn/courses/30/lessons/131535

프로그래머스

-- 코드를 입력하세요
SELECT count(*) as USERS from USER_INFO
where JOINED >= '2021-01-01' and JOINED <= '2021-12-31' and age >= 20 and age <= 29;

 

Date 타입은 포맷만 맞으면 연산자, between같은 함수 모두 사용 가능

 

https://school.programmers.co.kr/learn/courses/30/lessons/133024

프로그래머스

 

-- 코드를 입력하세요
SELECT FLAVOR from FIRST_HALF
order by TOTAL_ORDER desc, SHIPMENT_ID ASC;

https://school.programmers.co.kr/learn/courses/30/lessons/144853

프로그래머스

-- 코드를 입력하세요
SELECT BOOK_ID, date_format(PUBLISHED_DATE,'%Y-%m-%d') from book
where published_date >= '2021-01-01' and published_date <= '2021-12-31' and category = '인문'
order by PUBLISHED_DATE;

# SELECT * from book;

date_format(?, '%y-%m-%d)

 

Y는 네 자리 y는 두 자리

h는 시간 i는 분 s는 초

 

https://school.programmers.co.kr/learn/courses/30/lessons/132201

프로그래머스

 

-- 코드를 입력하세요
SELECT PT_NAME, PT_NO, GEND_CD, AGE, TLNO from PATIENT
where TLNO is not null and age <= 12 and GEND_CD = 'W'
union
SELECT PT_NAME, PT_NO, GEND_CD, AGE, 'NONE' from PATIENT
where TLNO is null and age <= 12 and GEND_CD = 'W'
order by age desc, PT_NAME;

 

CASE 컬럼명
    WHEN 값1 THEN 결과1
    WHEN 값2 THEN 결과2
    ELSE 기본결과
END

 

도 사용 가능

 

https://school.programmers.co.kr/learn/courses/30/lessons/131120

프로그래머스

-- 코드를 입력하세요
SELECT MEMBER_ID, MEMBER_NAME, GENDER, date_format(DATE_OF_BIRTH, '%Y-%m-%d') from MEMBER_PROFILE
where month(date_of_birth) = '03' and gender = 'W' and TLNO is not null
order by MEMBER_ID;

 

https://school.programmers.co.kr/learn/courses/30/lessons/273711

프로그래머스

select info2.ITEM_ID as ITEM_ID, info2.ITEM_NAME as ITEM_NAME, info2.RARITY as RARITY
from ITEM_INFO as info join ITEM_TREE as tree on info.ITEM_ID = tree.PARENT_ITEM_ID
join ITEM_INFO as info2 on tree.ITEM_ID = info2.ITEM_ID
where info.RARITY = 'RARE'
order by info2.ITEM_ID desc;