프로그래머스 사이트에서 제공하는 SQL 고득점 Kit 문제 풀이 기록입니다.
- SQL 고득점 Kit
- Update : 2023-02-05, 신규 추가 문제 풀이 중
SELECT *
from ANIMAL_INS
order by ANIMAL_ID ASC
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 1
SELECT
animal_id,
name
from animal_ins
order by 1 ASC
SELECT
animal_id,
name,
Datetime
from animal_ins
order by 2 ASC, 3 DESC
SELECT name
from animal_ins
order by datetime ASC
limit 1
SELECT round(AVG(DAILY_FEE), 0) as AVERAGE_FEE
from CAR_RENTAL_COMPANY_CAR
where 1=1
and CAR_TYPE = "SUV"
SELECT USER_ID, PRODUCT_ID
from ONLINE_SALE
group by 1, 2
having count(user_ID) >= 2
order by 1, 2 desc
SELECT MEMBER_ID, MEMBER_NAME
, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') as DATE_OF_BIRTH
from MEMBER_PROFILE
where 1=1
and TLNO is not null
and EXTRACT(MONTH from DATE_OF_BIRTH) = 3
and GENDER = "W"
order by member_id
SELECT FLAVOR
from FIRST_HALF
group by 1
order by sum(TOTAL_ORDER) desc, SHIPMENT_ID
SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') as PUBLISHED_DATE
from book
where 1=1
and CATEGORY = "인문"
and PUBLISHED_DATE like "2021-%"
order by PUBLISHED_DATE
# SELECT FIRST_HALF.flavor
# from FIRST_HALF
# inner join (
# select *
# from ICECREAM_INFO
# where INGREDIENT_TYPE = "fruit_based"
# ) ice_if on ice_if.flavor = FIRST_HALF.flavor
# group by 1
# having sum(FIRST_HALF.TOTAL_ORDER) > 3000
# order by sum(FIRST_HALF.TOTAL_ORDER) desc
SELECT FIRST_HALF.flavor
from FIRST_HALF
inner join ICECREAM_INFO on ICECREAM_INFO.flavor = FIRST_HALF.flavor
where INGREDIENT_TYPE = "fruit_based"
group by 1
having sum(FIRST_HALF.TOTAL_ORDER) > 3000
order by sum(FIRST_HALF.TOTAL_ORDER) desc
SELECT max(Datetime)
from animal_ins
SELECT DATETIME as "시간"
from ANIMAL_INS
order by DATETIME
limit 1
SELECT count(*)
from animal_ins
--or
SELECT count(1)
from animal_ins
SELECT count(distinct name)
from animal_ins
SELECT animal_type, count(animal_type)
from animal_ins
group by animal_type
SELECT name, count(name)
from animal_ins
group by name
having count(name) >= 2
SELECT
HOUR(datetime) as HOUR,
count(HOUR(datetime)) as COUNT
from animal_outs
where HOUR(DATETIME) between 9 and 19
group by 1
set @rownum:=-1;
select
HOUR_table.HOUR,
IFNULL(COUNT, 0) as COUNT
from (
select @rownum:=@rownum+1 as HOUR
from animal_outs
limit 24
) as HOUR_table
left join (
SELECT extract(hour from DATETIME) as HOUR, count(1) as COUNT
from animal_outs
group by 1
) extract_hour on HOUR_table.HOUR = extract_hour.HOUR
--------
set @rownum:=-1;
select HOUR_table.HOUR, IFNULL(COUNT, 0) as COUNT
from (
select @rownum:=@rownum+1 as HOUR
from animal_outs
limit 24
) as HOUR_table
left join (
SELECT HOUR(DATETIME) as HOUR, count(1) as COUNT
from animal_outs
group by 1
) extract_hour on HOUR_table.HOUR = extract_hour.HOUR
SELECT ANIMAL_ID
from ANIMAL_INS
where NAME is null
SELECT ANIMAL_ID
from ANIMAL_INS
where NAME is not null
order by 1 ASC
SELECT
ANIMAL_TYPE,
CASE
when NAME is null then "No name"
else NAME
end as NAME,
SEX_UPON_INTAKE
from ANIMAL_INS
--------
SELECT
ANIMAL_TYPE,
COALESCE(NAME, "No name") as NAME,
SEX_UPON_INTAKE
from ANIMAL_INS
--------
SELECT
ANIMAL_TYPE,
IFNULL(NAME, "No name") as NAME,
SEX_UPON_INTAKE
from ANIMAL_INS
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, "N") as FREEZER_YN
from FOOD_WAREHOUSE
where 1=1
and WAREHOUSE_NAME like "%_경기%"
SELECT ao.animal_id, ao.name
from animal_outs as ao
left join animal_ins as ai on ao.animal_id = ai.animal_id
where ai.animal_id is null
SELECT ai.animal_id, ai.name
from animal_ins as ai
join animal_outs as ao on ao.animal_id = ai.animal_id
where ao.datetime < ai.datetime
order by ai.datetime
SELECT ai.name, ai.datetime
from animal_ins as ai
left join animal_outs as ao on ai.animal_id = ao.animal_id
where ao.animal_id is null
order by ai.datetime
limit 3
SELECT ai.animal_id, ai.animal_type, ai.name
from animal_ins as ai
inner join animal_outs as ao on ao.animal_id = ai.animal_id
where ai.sex_upon_intake in ('Intact Male', "Intact Female")
and ao.sex_upon_outcome in ('Neutered Male', 'Spayed Female')
SELECT animal_id, name, SEX_UPON_INTAKE
from animal_ins
where name in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
group by 1, 2, 3
SELECT
animal_id,
name
from animal_ins
where name like "%el%" and animal_type = 'dog'
order by name
----- regexp
SELECT
animal_id,
name
from animal_ins
where name regexp 'el' and animal_type = 'dog'
order by name
SELECT
animal_id,
name,
case
when SEX_UPON_INTAKE regexp 'Neutered' then 'O'
when SEX_UPON_INTAKE regexp 'Spayed' then 'O'
else 'X'
end as '중성화'
from animal_ins
select
animal_id,
name
from (
SELECT
animal_ins.animal_id as animal_id,
animal_ins.name as name,
datediff(animal_outs.datetime, animal_ins.datetime) as date_diff
from animal_ins
join animal_outs on animal_outs.animal_id = animal_ins.animal_id
group by 1, 2
) as new_table
order by date_diff DESC
limit 2
SELECT
animal_id,
name,
date_format(datetime, "%Y-%m-%d") as "날짜"
from animal_ins