(programmers) SQL 문제풀이

오성인·2023년 3월 25일
0

SQL

목록 보기
4/4

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

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

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

SELECT count(*) from animal_ins

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

SELECT datetime from animal_ins
order by datetime
limit 1

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

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

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

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

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

  SELECT ifnull(animal_type, 'No name'), 
         ifnull(name, 'No name'), 
         ifnull(SEX_UPON_INTAKE, 'No name') 
from animal_ins

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

SELECT ANIMAL_ID, NAME, substring(DATETIME, 1, 10) as 날짜 
from animal_ins
order by ANIMAL_ID

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

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

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

SELECT ANIMAL_TYPE, count(*) from animal_ins
where ANIMAL_TYPE = 'Dog' or ANIMAL_TYPE = 'Cat'
group by animal_type
order by ANIMAL_TYPE

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

SELECT 
    animal_id, 
    name,
    case
        when SEX_UPON_INTAKE LIKE 'Neutered%'
        OR SEX_UPON_INTAKE LIKE 'Spayed%'
        then 'O'
        else 'X'
        end as SEX_UPON_INTAKE
from animal_ins
order by animal_id

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

SELECT substring(datetime, 12,2) as HOUR, count(*) as COUNt from animal_outs
group by substring(datetime, 12,2)
having hour between 9 and 20
order by hour

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

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

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

SELECT MCDP_CD as 진료과코드,count(*) as 5월예약건수
from appointment
where substring(apnt_ymd, 6, 2) = '05'
group by MCDP_CD
order by 5월예약건수, 진료과코드

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

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'

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

SELECT p.product_code, sum(p.price * o.sales_amount) as sales 
from PRODUCT p join offline_sale o on p.product_id = o.product_id
group by p.product_code
order by sales desc, p.product_code

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

SELECT MEMBER_ID, MEMBER_NAME, GENDER, substring(DATE_OF_BIRTH, 1, 10) as DATE_OF_BIRTH from member_profile
where   substring(date_of_birth,6, 2) = 3 
        and gender = 'w' 
        and tlno is not null
order by member_id

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

SELECT car_type, count(*) as cars
from CAR_RENTAL_COMPANY_CAR
where options like '%통풍시트%' or options like '%열선시트%' or options like '%가죽시트%'
group by car_type
order by car_type

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

SELECT BOOK_ID, AUTHOR_NAME, substring(PUBLISHED_DATE, 1, 10) as PUBLISHED_DATE
from book b join author a on b.author_id = a.author_id
where category = '경제'
order by published_date

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

SELECT INGREDIENT_TYPE, sum(total_order) as TOTAL_ORDER
from first_half f join icecream_info i on f.flavor = i.flavor 
group by INGREDIENT_TYPE

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

SELECT floor(price / 10000) * 10000 as price_group, count(*) as product
from product
group by price_group
order by price_group

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(*) > 1

order by user_id, product_id desc

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

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, 
    case when status = 'sale' then '판매중'
         when status = 'reserved' then '예약중'
         else '거래완료' 
    end as STATUS
from USED_GOODS_BOARD
where created_date like '2022-10-05%'
order by board_id desc

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

SELECT car_id, round(avg(DATEDIFF(END_DATE, START_DATE)+1), 1) as AVERAGE_DURATION
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by car_id
having AVERAGE_DURATION >= 7
order by AVERAGE_DURATION desc, car_id desc
profile
기여하는 개발자

0개의 댓글