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 문제를 모두 풀어봤다.