[데이터분석 7일차] SQL 코드카타 (Lv.3-Lv.4진입)

 

31. 오랜기간 보호한 동물(1)

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

 

- 혼자 (RANK 함수)

select name,
       datetime
from
(
select name,
       datetime,
       rank()over (order by datetime asc) 'rn'
from
(
SELECT a.name 'name',
               a.datetime 'datetime'
from animal_outs a left join animal_ins b on a.animal_id = b.animal_id
where b.datetime is null
)a
)b
where rn<=3

 

b.datetime에서 입양일이 없는 데이터로검색했고, 보호시작일 나오게 했고, 보호시작일 랭크 3순위까지만나오게 했는데.. 왜 틀렸을까..?  이젠 블로그 검색 정답찾아 공부하자

 

홀리..내가 한 Left join 풀이에선 order by  하고 limit3  하는 간단풀이가.... 

값이 틀린 이유.. 오우씨  다시 보니 나는animal_outs 를 a 로 뒀음 -_-!!!!!!!!  animal_out를 b로 뒀어야 b.datetime (입양일) null 값이 나오는 것..   ㅠㅠㅠ

 

select a.name,
          a.datetime
from animal_ins a left join animal_outs b on a.animal_id= b.animal_id
where b.datetime is null
order by a.datetime
limit 3

 

다른풀이

풀이1  not exist 사용

: animal_ins와 animal_outs 테이블에 모두 존재하는 Animal_ID는 동물보호소에 들어와서 입양간 동물들이기 때문에 not exists 사용 입양 보내지 않은동물 구할 수 있음

 

select name,
       datetime
from animal_ins A
where not exists 
(select animal_id 
 from animal_outs B 
 where A.animal_id = b.animal_id)
 order by datetime
 limit 3

 

풀이2 not in 사용

not_exist 대신 not_in 사용해 animal_outs에 존재하지 않는 동물 (입양가지 않은 동물)을 알 수 있다

- 서브쿼리의 입양을 보낸동물 아이디에 해당하지 않는행을구하면 입양을 가지 않은 동물과 같다고 할수 있다.

 

select name,
          datetime
from animal_ins A
where animal_id not in (select animal_id from animal_outs)
order by datetime
limit 3

 

 

 

32.카테고리 별 도서 판매량집계하기

 2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력하는 SQL문을 작성해주세요.

결과는 카테고리명을 기준으로 오름차순 정렬해주세요.

 

SELECT category,
               sum(sales) 'total_sales'
from book a left join book_sales b on a.book_id=b.book_id
where b.sales_date like '2022-01%'
group by 1
order by 1

 

방법1) WHERE sales_date LIKE '2021~01%'

방법2) WHERE YEAR( sales_date )='2021' and MONTH( sales_date)='051'

방법3) WHERE DATE_FORMAT( sales_date , '%Y-%m')='2021-0'

방법4) WHERE LEFT( sales_date,7)='2021-01'

 

 

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

PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

 

SELECT product_code,
               sum(price*sales_amount) sales
from product a left join offline_sale b on a.product_id=b.product_id
group by 1
order by 2 desc, 1

 

34. 있었는데요 없었습니다

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

 

SELECT a.animal_id,
               a.name
from animal_ins a inner join animal_outs b on a.animal_id = b.animal_id
where a.datetime > b.datetime
order by a.datetime

 

35. 오랜 기간 보호한 동물(2)

입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

 

(오답!! 혼자 풀었을 때 기간 구하는 법 몰라서 datetime 더해봄 -0-)

SELECT animal_id,
               name
from
(
SELECT a.animal_id animal_id,
       a.name name,
       a.datetime + b.datetime '보호기간'
from animal_ins a inner join animal_outs b on a.animal_id=b.animal_id) a
order by 보호기간 desc
limit 2 

 

(정답찾기)

 

*** 새로운 함수 발견!!  DATEDIFF (구하고싶은 차이(구분자) ,  'Start+date', 'End_date')

 

 

(BUT,,,,datediff 함수로 구한 건 오답..!!!  결과도 다르게 나옴 -ㅁ- 뭐지..?  질문)

SELECT a.animal_id,
              a.name
from animal_ins a inner join animal_outs b on a.animal_id=b.animal_id
order by datediff(a.datetime,b.datetime) desc
limit 2

 

 

진짜 정답은 order by 뒤에 입양일- 보호시작일  desc 하고 limit 2로 자른 거)

 

SELECT a.animal_id,
               a.name
from animal_ins a inner join animal_outs b on a.animal_id=b.animal_id
order by b.datetime - a.datetime desc
limit 2

 

 

36. 보호소에서 중성화한 동물

보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화1되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.

 

(혼자) - 정답!

SELECT a.animal_id animal_id,
               a.animal_type animal_type,
               a.name name
from animal_ins a inner join animal_outs b on a.animal_id=b.animal_id
where (a.sex_upon_intake like "%intact%" and b.sex_upon_outcome like "%spayed%") or (a.sex_upon_intake like "%intact%" and b.sex_upon_outcome like "%neutered%")
order by animal_id

 

*** Where 절 더 간단히 !  %% 앞 뒤로 잘 하고, 괄호로 묶으면 됨+_+ 

  where a.sex_upon_intake like "%intact%" and (b.sex_upon_outcome like "%spayed%" or b.sex_upon_outcome like "%neutered%")

 

 

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

'경제' 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 출판일을 기준으로 오름차순 정렬해주세요.

 

(혼자 - 정답!!)

SELECT book_id 'ID',
               author_name as "저자명",
               date_format(published_date,'%Y-%m-%d') as "출판일"
from book a inner join author b on a.author_id=b.author_id
where a.category = '경제'
order by 3

 

 

38. 조건별로 부류하여 주문상태 출력하기

FOOD_ORDER 테이블에서 2022년 5월 1일을 기준으로 주문 ID, 제품 ID, 출고일자, 출고여부를 조회하는 SQL문을 작성해주세요. 출고여부는 2022년 5월 1일까지 출고완료로 이 후 날짜는 출고 대기로 미정이면 출고미정으로 출력해주시고, 결과는 주문 ID를 기준으로 오름차순 정렬해주세요.

 

** 출고여부 구하는게 어렵네..? 2022년 5월 1일까지 ..를 구하는건.. out_date<='2022-05-01' ? 넣으면 1 이런거 나오는데...

블로그 보니 case 활용하래! oh!

 

SELECT order_id,
               product_id,
               date_format(out_date,'%Y-%m-%d'),
               case when out_date <= '2022-05-01' then '출고완료'
                        when out_date > '2022-05-01' then '출고대기'
                        else '출고미정'
               end '출고여부'
from food_order
order by order_id

 

나.. date_format 쫌 마스터 한듯.. -_-b

 

 

 

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

상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총주문량이 작은 순서대로 조회하는 SQL 문을 작성해주세요. 이때 총주문량을 나타내는 컬럼명은 TOTAL_ORDER로 지정해주세요.

 

SELECT b.ingredient_type,
               sum(total_order) 'TOTAL_ORDER'
from first_half a inner join icecream_info b on a.flavor=b.flavor
group by 1
order by 2

 

40. 루시와 엘라 찾기

동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성해주세요.

 

(혼자서 정답!)

SELECT animal_id,
               name,
               sex_upon_intake
from animal_ins
where name in ('Lucy','Ella','Pickle','Rogan','Sabrina','Mitty')

 

**in 은 select 뒤에서 쓰면 안 먹음.. where절에서 조건으로 써줘야함.

 

41. 조건에 맞는 도서 리스트 출력하기

 

(혼자서 정답..! 이 정도는 이제 껌이쥐)

 

SELECT book_id,
               date_format(published_date,'%Y-%m-%d') 'Published_date'
from book
where year(published_date)='2021' and category='인문'
order by published_date

 

 

42. 평균 일일대여 요금 구하기 

CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 'SUV'인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성해주세요. 이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정해주세요.

 

SELECT round(avg(daily_fee),0) 'average_fee'
from car_rental_company_car
where car_type='SUV'

 

또 나왔다! 새로운 함수!! 이번엔 반올림함수 ROUND

소수점 첫째 자리, 소수점 둘째 자리, 정수 첫째 자리에서 반올림한 값

 

ex)  salary가 월급이라고 가정했을 때 일급을 계산

 

SELECT salary,

              salary/30 일급,

              ROUND(salary/30, 0),

              ROUND(salary/30, 1),

              ROUND(salary/30, -1) 

From employees

 

 

43. 조건에 맞는 사용자와 총 거래금액 조회하

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.

 

(혼자 정답..!)

select user_id,
       nickname,
       total_price
from
(
SELECT b.user_id 'user_id',
               b.nickname 'nickname',
              sum(a.price) 'total_price'
from used_goods_board a inner join used_goods_user b on a.writer_id=b.user_id
where status='done'
group by 1) a
where total_price >= 700000
order by 3

 

44. 가격대 별 상품 개수 구하기

PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요. 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요. 결과는 가격대를 기준으로 오름차순 정렬해주세요.

 

(훗 혼자서 정답..!)

SELECT case when price between 0 and 9999 then 0
            when price between 10000 and 19999 then 10000
            when price between 20000 and 29999 then 20000
            when price between 30000 and 39999 then 30000
            when price between 40000 and 49999 then 40000
            when price between 50000 and 59999 then 50000
            when price between 60000 and 69999 then 60000
            when price between 70000 and 79999 then 70000
            when price between 80000 and 89999 then 80000
            end 'price_group',
       count(product_id) 'products'
from product
group by 1
order by 1

 

 

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

MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.

 

(혼자했을 때.. 틀렸단다  왜지..???)

 

SELECT member_id,
       member_name,
       gender,
       date_format(date_of_birth,'%Y-%m-%d') Date_of_birth
from member_profile
where month(date_of_birth)=03 and tlno is not null
order by 1

 

** 헉. 와  '여성' 회원의...와우.

.

SELECT member_id,
       member_name,
       gender,
       date_format(date_of_birth,'%Y-%m-%d') Date_of_birth
from member_profile
where month(date_of_birth)=03 and tlno is not null and gender = 'W'
order by 1

 

 

 

46. 대여기록이 존재하는자동차 리스트 구하기 

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 자동차 종류가 '세단'인 자동차들 중 10월에 대여를 시작한 기록이 있는 자동차 ID 리스트를 출력하는 SQL문을 작성해주세요. 자동차 ID 리스트는 중복이 없어야 하며, 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

(혼자서 정답..!! 중복없애기  Count(distinct 컬럼)만 해봐서 그냥 distinct( ) 로 찍었는데 이게 되네...? 꺄) 

 

SELECT distinct(a.car_id)
from car_rental_company_car a inner join car_rental_company_rental_history b on a.car_id=b.car_id
where a.car_type ='세단' and month(b.start_date)='10'
order by 1 desc

 

47. 모든 레코드 조회하기

동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요.

 

(뭐지 갑자기 그냥 주는 문제인가)

 

SELECT *
from animal_ins

 

48. 즐겨찾기가 가장 많은 식당 정보 출력하기

REST_INFO 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.

 

(오답.. 내가 푼 쿼리)

SELECT food_type,
       rest_id,
       rest_name,
       max(favorites)
from rest_info
group by 1
order by 1 desc

 

(정답  where 조건 절 in 뒤에  서브쿼리를 시작해버리는 혁신적인 방법....) (내가..지금 뭘 본거지..+_+)

 

SELECT 

food_type, 

rest_id, 

rest_name, 

favorites
from rest_info
where (food_type, favorites) in (select food_type, max(favorites) from rest_info
group by 1)
order by 1 desc

한 바퀴 돌고 다시 풀어볼 문제 추가..!!

 

** group by 로 묶으면 가장 상단에 있는 데이터들을 임의로 가져옴. (우연의 일치로 결과물만 같은것. 엄밀히 가장 즐겨찾기가 많은 식당이 아님 - 쿼리의 진행순서 때문.

따라서, group by 후 그냥 max 해버리면 최대값이 아니라 테이블 최상단값 가져와버리므로..

조건절에서 서브쿼리로 최대 값을 먼저 따로 찾아주고 뽑아내기.

음 블로그를 봐도 정확히 in 을 왜 쓰는지는 계속 보구 비슷한 문제 풀어봐야 확실히 이해될 것 같음

 

 

49. 식품분류별 가장 비싼 식품의 정보 조회하기 

 

와. 감격....위에 꺼 활용 혼자 정답..!!!

where에서 가격이 제일 비싼! 친구 먼저 구하고 and 쓰고 카테고리 집어주기..! 오예

 

SELECT category,
               price,
               product_name
from food_product
where (category, price) in (select category, max(price) from food_product group by 1) and category in ('과자','국','김치','식용유')
order by 2 desc

 

 

50. 5월 식품들의 총매출 조회하기

FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.

 

(오답.. 혼자 푼 거. 총매출 뭐냐고..ㅠㅠ)

SELECT a.product_id,
       a.product_name,
       a.price * b.amount
from food_product a inner join food_order b on a.product_id=b.product_id
where b.produce_date like "2022-05%" and b.out_date is not null
group by 1
order by 3 desc, 1

 

(정답.. 뜨쉬.. 일단 총매출는 sum(price*amount) 였고,,  내가 혹시 몰라서 한 출고일자out_date is not null 을 없애니 정답..!!)

 

SELECT a.product_id,
       a.product_name,
       sum(a.price * b.amount)
from food_product a inner join food_order b on a.product_id=b.product_id
where b.produce_date like "2022-05%" 
group by 1
order by 3 desc, 1

 

(옹예 나도 이제 나무다 나무~~)

 

 

 

 

 

 

.