프로그래머스 level 2 sql 문제들

- 동물 수 구하기

SELECT count(*) from ANIMAL_INS;

- 최솟값 구하기

SELECT DATETIME from ANIMAL_INS order by DATETIME limit 1;

- 동명 동물 수 찾기

SELECT NAME, count(*) as COUNT
from ANIMAL_INS 
where NAME IS NOT NULL
group by NAME
having count(*) > 1
order by NAME;

- 중복 제거하기

SELECT count(distinct NAME)
from ANIMAL_INS
where NAME is not null

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

SELECT ANIMAL_ID, NAME
from ANIMAL_INS 
where ANIMAL_TYPE = 'Dog' and LOWER(NAME) like '%el%'
order by NAME

- NULL 처리하기

SELECT ANIMAL_TYPE, ifnull(NAME, 'No name'), SEX_UPON_INTAKE
from ANIMAL_INS 
order by ANIMAL_ID

- DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, date_format(DATETIME, "%Y-%m-%d") as 날짜
from ANIMAL_INS

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

SELECT *
from FOOD_PRODUCT 
order by PRICE desc
limit 1

- 중성화 여부 파악하기

SELECT ANIMAL_ID, NAME, if(SEX_UPON_INTAKE like 'Neutered%' or SEX_UPON_INTAKE like 'Spayed%', 'O', 'X') as 중성화
from ANIMAL_INS

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

SELECT ANIMAL_TYPE, count(*)
from ANIMAL_INS 
group by ANIMAL_TYPE
order by ANIMAL_TYPE

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

SELECT LEFT(PRODUCT_CODE, 2) as CATEGORY, count(*) as PRODUCTS
from PRODUCT 
group by LEFT(PRODUCT_CODE, 2)

- 입양 시각 구하기(1)

SELECT HOUR(DATETIME) as HOUR, count(*)
from ANIMAL_OUTS
where HOUR(DATETIME) between 9 and 19
group by HOUR
order by HOUR

- 진료과별 총 예약 횟수 출력하기

SELECT MCDP_CD as 진료과코드, count(*) as 5월예약건수
from APPOINTMENT 
where APNT_YMD like '2022-05%'
group by MCDP_CD
order by count(*), MCDP_CD

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

SELECT PRODUCT_CODE, sum(SALES_AMOUNT) * price as SALES
from PRODUCT, OFFLINE_SALE
where PRODUCT.PRODUCT_ID = OFFLINE_SALE.PRODUCT_ID
group by PRODUCT.PRODUCT_ID
order by sum(SALES_AMOUNT) * price desc, PRODUCT_CODE

- 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

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

- 조건에 맞는 도서와 저자 리스트 출력하기

SELECT BOOK_ID, AUTHOR_NAME, date_format(PUBLISHED_DATE, '%Y-%m-%d')
from BOOK, AUTHOR
where BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID and CATEGORY = '경제'
order by PUBLISHED_DATE

- 루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
from ANIMAL_INS 
where NAME in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')

- 성분으로 구분한 아이스크림 총 주문량

SELECT INGREDIENT_TYPE, sum(TOTAL_ORDER) as TOTAL_ORDER
from FIRST_HALF, ICECREAM_INFO
where FIRST_HALF.FLAVOR = ICECREAM_INFO.FLAVOR
group by INGREDIENT_TYPE

- 가격대 별 상품 개수 구하기

SELECT floor(PRICE / 10000)*10000 as PRICE_GROUP, count(PRODUCT_ID) as PRODUCTS
from PRODUCT 
group by floor(PRICE / 10000)*10000
order by floor(PRICE / 10000)*10000

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

SELECT MEMBER_ID, MEMBER_NAME, GENDER, date_format(DATE_OF_BIRTH, '%Y-%m-%d')
from MEMBER_PROFILE 
where TLNO is not null and DATE_OF_BIRTH like '%-03-%' and GENDER = 'W'
order by MEMBER_ID

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

SELECT USER_ID, PRODUCT_ID
from ONLINE_SALE
group by USER_ID, PRODUCT_ID
having count(*) > 1
order by USER_ID, PRODUCT_ID desc

- 조건에 부합하는 중고거래 상태 조회하기

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, 
case STATUS
when 'SALE'
    then '판매중'
when 'RESERVED'
    then '예약중'
when 'DONE'
    then '거래완료'
end
    as STATUS
from USED_GOODS_BOARD
where CREATED_DATE like '2022-10-05'
order by BOARD_ID desc

- 자동차 평균 대여 기간 구하기

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

- 조건에 맞는 아이템들의 가격의 총합 구하기

select SUM(PRICE) as TOTAL_PRICE from ITEM_INFO where RARITY = 'LEGEND';

- ROOT 아이템 구하기

select ITEM_INFO.ITEM_ID, ITEM_INFO.ITEM_NAME from ITEM_INFO, ITEM_TREE
where ITEM_INFO.ITEM_ID = ITEM_TREE.ITEM_ID and PARENT_ITEM_ID is null order by ITEM_INFO.ITEM_ID

- 조건에 맞는 사원 정보 조회하기

select sum(SCORE) as SCORE, HR_EMPLOYEES.EMP_NO, EMP_NAME, POSITION, EMAIL
from HR_EMPLOYEES, HR_GRADE
where HR_EMPLOYEES.EMP_NO = HR_GRADE.EMP_NO and HR_EMPLOYEES.EMP_NO = (select HR_GRADE.EMP_NO from HR_GRADE group by HR_GRADE.EMP_NO order by sum(SCORE) desc limit 1)
group by HR_EMPLOYEES.EMP_NO

- 연도 별 평균 미세먼지 농도 조회하기

select year(YM) as YEAR, ROUND(avg(PM_VAL1), 2) as PM10, ROUND(avg(PM_VAL2), 2) as 'PM2.5'
from AIR_POLLUTION
where LOCATION1 = '경기도' and LOCATION2 = '수원'
group by YEAR
order by YEAR;

- 업그레이드 된 아이템 구하기

select ITEM_TREE.ITEM_ID, ITEM_NAME, RARITY
from ITEM_INFO, ITEM_TREE
where ITEM_INFO.ITEM_ID = ITEM_TREE.ITEM_ID and PARENT_ITEM_ID is not NULL and PARENT_ITEM_ID in (select ITEM_ID from ITEM_INFO where RARITY = 'RARE')
order by ITEM_TREE.ITEM_ID desc

- 2개 이하로 다른 비트

def solution(numbers):
    for i in range(len(numbers)):
        if numbers[i] % 2 == 0:
            numbers[i] += 1
        else:
            numbers[i] += 2**((bin(numbers[i])[2:][::-1]+'0').find('0')-1)
            
    return numbers