연습장
1. SQL 문제 본문
1. 동물수 구하기
기본적인 Select 문에 count(*) 을 활용하여 테이블의 모든 Row 수를 알 수 있다.
SELECT count(*) FROM ANIMAL_INS;
2. 중복 제거하기
Select NAME from ANIMAL_INS ; -> ANIMAL_INS 테이블에서 NAME 컬럼만 추출할 수 있고
SELECT count(distinct(NAME)) from ANIMAL_INS ; distinct 를 통해 중복제거 및 count 를 활용하여 수를 셀 수 있다.
3. 최솟값 구하기
가장 먼저 들어온 동물의 들어온 시간을 구하는 문제.
테이블에서 날짜를 조회 후 가장먼저 == 최소값 을 조회
SELECT MIN(DATETIME) from ANIMAL_INS;
4. 동명 동물 수 찾기
동물의 이름이 각각 몇번 쓰였는지 확인하는 문제
다음과 같이 그룹으로 묶게되면 각 이름이 몇번 쓰였는지 확인가능하며
SELECT NAME, count(*) from ANIMAL_INS group by(NAME);
문제에서 원하는 조건대로 2번이상 쓰인 이름만 알고싶다면 having 절을 활용하여 조건 추가
SELECT NAME, count(NAME)
from ANIMAL_INS
group by(NAME) having count(NAME) >= 2
order by NAME;
5. 이름에 el이 들어가는 동물 찾기
이름에 el 이 들어간다. -- NAME Like "%EL%"
개다. -- ANIMAL_TYPE = 'Dog'
결과는 이름\순으로 조회 -- order by NAME
SELECT ANIMAL_ID, NAME
from ANIMAL_INS
where (ANIMAL_TYPE = 'Dog') and (NAME Like "%EL%") order by NAME ;
6. NULL 처리하기
NULL 은 아예 없는 빈값으로 이를 처리하는 방법은 여러가지가 있다.
NAME 이 없는 동물에 대해서 No name 으로 표시되도록 하는 방법은 다음과 같다.
SELECT ANIMAL_TYPE, IFNULL(NAME, "No name"),SEX_UPON_INTAKE from ANIMAL_INS ;
7. DATETIME 에서 DATE로 형 변환
DATETIME 의 데이터를 변환하는 문제이다.
YYYY-MM-DD hh:mm:ss -> YYYY-MM-DD 로 바꾸면된다.
SELECT ANIMAL_ID, NAME , DATE_FORMAT(DATETIME, '%Y-%m-%d') as '날짜' from ANIMAL_INS ;
format 이 다양하므로 확인이 필요하다
8. 가격이 제일 비싼 식품의 정보 출력하기
테이블에서 가격이 제일 비싼 가격은 다음과 같다.
SELECT MAX(PRICE) from FOOD_PRODUCT;
where 의 조건으로 PRICE 가 MAX(PRICE) 와 같다 라고 서브쿼리로 사용하면 다음과 같이 원하는 답을 구할 수 있다.
select * from FOOD_PRODUCT where PRICE = (SELECT MAX(PRICE) from FOOD_PRODUCT) ;
9. 카테고리 별 상품 개수 구하기
PRODCUT_CODE 의 2자리를 잘라내기 위해서 substr(PRODUCT_CODE,1,2) 를 사용하며
잘라낸 2자리를 group 으로 묶어서 원하는 결과를 얻는다.
SELECT substr(PRODUCT_CODE,1,2) as CATEGORY, count(PRODUCT_CODE) as PRODUCTS
from PRODUCT group by substr(PRODUCT_CODE,1,2) ;
10. 중성화 여부 파악하기
SEX_UPON_INTAKE 컬럼에서 Neutered , Spayed 의 단어가 있을 경우 중성화 된것으로 O 표시, 아니면 X 표시
if 문을 사용해서 해결할수있다.
if ( 조건, 참, 거짓)
select ANIMAL_ID , NAME , if(SEX_UPON_INTAKE like '%Neutered%' or SEX_UPON_INTAKE like '%Spayed%', "O","X") as '중성화'
from ANIMAL_INS ;
11. 고양이와 개는 몇 마리 있을까
고양이 수와 개의 수를 각각 나타내는 문제
group by 를 통해서 동물 타입을 묶어서 해결 할 수 있다.
SELECT ANIMAL_TYPE, count(*) from ANIMAL_INS group by ANIMAL_TYPE order by ANIMAL_TYPE;
12. 진료과별 총 예약 횟수 출력하기
22년 5월에 예약한 환자 수를
진료과 코드별로 조회하는 SQL 문 작성
정렬은 환자수 오름차순 같으면 진료과 코드 기준으로 오름차순
SELECT MCDP_CD as '진료과코드', count(*) as '5월예약건수' from APPOINTMENT
where DATE_FORMAT(APNT_YMD, '%m') = '05'
group by(MCDP_CD) order by count(*) asc, MCDP_CD asc;
13. 입양 시각 구하기(1)
시간별로 입양된 건 수 를 구하는 문제
date_format(DATETIME, '%H') 를 이용하면 입양된 시간대를 알 수 있다.
09시에서 19시 사이 -> between 09 and 19
SELECT date_format(DATETIME, '%H') as HOUR, count(*) as COUNT from ANIMAL_OUTS
group by (date_format(DATETIME, '%H')) having HOUR between 09 and 19
order by HOUR;
여기서 의문인 점은 파란색 처럼 쓰면 오류가 나는 점이다.
SELECT date_format(DATETIME, '%H') as HOUR, count(*) as COUNT from ANIMAL_OUTS
group by (date_format(DATETIME, '%H')) having (date_format(DATETIME, '%H')) between 09 and 19
order by HOUR;
having 절은 group by 에서 사용한 컬럼 , 집계함수를 사용해야되서 안되는데 HOUR 이 되는 이유는 이상하다.
SQL 실행순서에 따르면
From - where - group by - having - select - order by 순서이고
select 절의 별칭은 having 절에서 사용할 수 없어야되는데 ...
-> 찾아보니까 mysql 에서는 select 절의 as 별칭을 group by having order by 에서 사용 할 수 있다고 한다.
그럼 .. 이렇게 코드를 짜도 될것같다.
SELECT date_format(DATETIME, '%H') as HOUR, count(*) as COUNT from ANIMAL_OUTS
group by HOUR having HOUR between 09 and 19
order by HOUR;
14. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기
'통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차 종류별 대수
옵션은 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 로
시트가 총 3 종류이다.
따라서 '시트' 글자를 포함하도록 하면 된다.
SELECT CAR_TYPE, count(*) as CARS from CAR_RENTAL_COMPANY_CAR
where OPTIONS like '%시트%'
group by CAR_TYPE
order by CAR_TYPE;
15. 상품 별 오프라인 매출 구하기
OFFLINE_SALE 테이블에서 PRODUCT_ID 별로 팔린 SALES_AMOUNT 를 구하고
해당 PRODUCT_ID 에 맞는 PRICE 를 구해서 곱하면 판매량을 알 수 있다 .
2 테이블을 써야되므로 join 을 이용한다.
select * from OFFLINE_SALE as A left join PRODUCT as B on A.PRODUCT_ID = B.PRODUCT_ID;
컬럼수를 조절하여 간단하게 보면
select A.product_id, sales_amount, price from OFFLINE_SALE as A left join PRODUCT as B on A.PRODUCT_ID = B.PRODUCT_ID;
상품 ID , 판매량 , 가격 으로 볼 수 있다.
다음과 같이 매출액을 추가할 수 있으며
select A.product_id, sales_amount, price, sales_amount*price as 매출액 from OFFLINE_SALE as A left join PRODUCT as B on A.PRODUCT_ID = B.PRODUCT_ID;
제출용 정답으로 만드려면 이제 묶어서 다듬으면된다.
select B.PRODUCT_CODE, SUM(A.sales_amount*B.price) as SALES
from OFFLINE_SALE as A left join PRODUCT as B on A.PRODUCT_ID = B.PRODUCT_ID
group by B.PRODUCT_CODE
order by SALES desc, B.PRODUCT_CODE asc;
16. 조건에 맞는 도서와 저자리스트 출력
테이블 2개 이상 사용하므로 join 을 고려해야된다.
SELECT BOOK_ID, AUTHOR_NAME, DATE_format(PUBLISHED_DATE, '%Y-%m-%d') as PUBLISHED_DATE
from BOOK as A left join AUTHOR as B on A.AUTHOR_ID = B.AUTHOR_ID
where A.CATEGORY = '경제'
order by PUBLISHED_DATE;
17. 성분으로 구성한 아이스크림 총 주문량
결과 예시
FLAVOR 을 기준으로 join 할 수 있으며 INGREDIENT_TYPE 에 따라서 group by 한뒤 TOTAL_ORDER 을 더하면된다.
SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) as TOTAL_ORDER
from FIRST_HALF as A left join ICECREAM_INFO as B on A.FLAVOR = B.FLAVOR
group by (INGREDIENT_TYPE)
order by TOTAL_ORDER asc;
18. 루시와 엘라 찾기
in 연산자를 쓰면 쉽게 구할 수 있다.
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE from ANIMAL_INS
where NAME in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty') ;
19. 3월에 태어난 여성 회원 목록 출력하기
조건 : 생일이 3월 / 여성 / 전화번호가 NULL 이 아님
정렬 : 회원 ID 오름차순
SELECT MEMBER_ID, MEMBER_NAME,GENDER, date_format(DATE_OF_BIRTH, '%Y-%m-%d') as DATE_OF_BIRTH
from MEMBER_PROFILE
where date_format(DATE_OF_BIRTH, '%m') = '03' and GENDER='W' and (TLNO is not null)
order by MEMBER_ID asc;
20. 가격대 별 상품 개수 구하기
SELECT PRICE_GROUP, count(*)
from (
select case
when PRICE between 0 and 10000-1 then 0
when PRICE between 10000 and 20000-1 then 10000
when PRICE between 20000 and 30000-1 then 20000
when PRICE between 30000 and 40000-1 then 30000
when PRICE between 40000 and 50000-1 then 40000
when PRICE between 50000 and 60000-1 then 50000
when PRICE between 60000 and 70000-1 then 60000
when PRICE between 70000 and 80000-1 then 70000
when PRICE between 80000 and 90000-1 then 80000
else '기타'
end as PRICE_GROUP
from PRODUCT) as A
group by PRICE_GROUP
order by PRICE_GROUP;
너무 비효율적인거 같아서 다른 풀이를 보니
다음과 같이 72000 -> 7 -> 70000 으로 바꿔서 푸는 방법이 있었다.
select *, Truncate(price/10000,0) from PRODUCT ;
select Truncate(price/10000,0)*10000 as PRICE_GROUP, count(*) from PRODUCT
group by PRICE_GROUP
order by PRICE_GROUP;
위와 같이 짧은 코드로 해결이 된다.
21. 재구매가 일어난 상품과 회원 리스트 구하기
다음과 같이 중복해서 다시 구매하는 경우가 있다면
따로 표시하라는 문제이다.
concat(USER_ID,'-',PRODUCT_ID) 을 이용하여 user_id 와 product_id 를 합쳐서 하나의 컬럼 만들고
이를 group by 에 사용하였다.
SELECT USER_ID,PRODUCT_ID,concat(USER_ID,'-',PRODUCT_ID), count(*)
from ONLINE_SALE
group by(concat(USER_ID,'-',PRODUCT_ID))
다음과 같은 느낌으로 나오며 이제 count(*) 이 2 이상인 것만 걸러낸다.
SELECT USER_ID,PRODUCT_ID,concat(USER_ID,'-',PRODUCT_ID), count(*)
from ONLINE_SALE
group by(concat(USER_ID,'-',PRODUCT_ID))
having count(*) >= 2
정답 제출용 출력으로 컬럼과 정렬을 조절하여 만들어준다.
SELECT USER_ID,PRODUCT_ID
from ONLINE_SALE
group by(concat(USER_ID,'-',PRODUCT_ID))
having count(*) >= 2
order by USER_ID asc, PRODUCT_ID desc;
22. 조건에 부합하는 중고거래 상태 조회하기
조건 1 : 2022년 10월 5일에 등록된
조건 2: 거래 상태에 따른 분류 출력
정렬 : 게시글 ID 기준 내림차순 정렬
조건1 인 해당 날짜
SELECT * from USED_GOODS_BOARD where date_format(CREATED_DATE, '%Y-%m-%d') = '2022-10-05';
조건2를 하기위해선 case when then 을 사용하는게 편할꺼같다.
SELECT BOARD_ID , WRITER_ID, TITLE, PRICE,
(case
when STATUS = 'SALE' then '판매중'
when STATUS = 'RESERVED' then '예약중'
when STATUS = 'DONE' then '거래완료'
end
) as STATUS
from USED_GOODS_BOARD
where date_format(CREATED_DATE, '%Y-%m-%d') = '2022-10-05'
order by BOARD_ID desc;
23. 자동차 평균 대여 기간 구하기
대여기간을 구하는 과정에서 DATEDIFF 는 뭔가 오류가 있어서 TIMESTAMPDIFF 를 사용했다.
select car_id,START_DATE,END_DATE, DATEDIFF(END_DATE+1, START_DATE), TIMESTAMPDIFF(DAY,START_DATE, END_DATE)+1
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
SELECT car_id, round(avg(TIMESTAMPDIFF(DAY,START_DATE, END_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
2단계의 SQL 문제를 모두 풀어봤다.
'프로그래머스 > 2단계' 카테고리의 다른 글
다음 큰 숫자 (0) | 2024.04.30 |
---|---|
숫자의 표현 (0) | 2024.04.30 |
4. 이진 변환 반복하기 (1) | 2024.03.24 |
3. 올바른 괄호 (1) | 2024.03.05 |
2. 최솟값 만들기 (1) | 2024.03.05 |