12/19 SQL 문제

평균 일일 대여 요금 구하기

• CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 SUV인 자동차들의 평균 일일 대여 요금 출력하는 SQL문

  SELECT ROUND(AVG(DAILY_FEE), 0) AS AVERAGE_FEE FROM CAR_RENTAL_COMPANY_CAR WHERE CAR_TYPE = 'SUV';
  

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

• ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하고, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성. 회원 ID는 오름차순, 회원 ID가 같다면 상품 ID를 기준으로 내림차순으로 정렬

  SELECT USER_ID, PRODUCT_ID
  FROM ONLINE_SALE
  GROUP BY USER_ID, PRODUCT_ID
  HAVING COUNT(*) > 1
  ORDER BY USER_ID ASC, PRODUCT_ID DESC;
  

  COUNT(*)

  • 테이블 전체를 대상으로 실행하면 전체 행의 개수 출력
  • GROUP BY와 함꼐 사용할 경우, 각 그룹의 행의 개수 출력

역순 정렬하기

• 동물 보호소에 들어온 모든 동물의 이름과 보호 시작일 조회. 결과는 ANIMAL_ID 역순으로 출력

  SELECT NAME, DATETIME
  FROM ANIMAL_INS
  ORDER BY ANIMAL_ID DESC;
  

상위 n개 레코드

• 보호소에 가장 먼저 들어온 동물의 이름 조회

  LIMIT x : 위에서 x개만 조회

  SELECT NAME
  FROM ANIMAL_INS
  ORDER BY DATETIME ASC
  LIMIT 1;
  

조건에 맞는 회원수 구하기

• USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원의 수 출력

  ```mysql SELECT COUNT(*) AS USERS FROM USER_INFO WHERE AGE >= 20 AND AGE <= 29 AND JOINED LIKE “2021%”

과일로 만든 아이스크림 고르기

• 상반기 아이스크림 총 주문량이 3000보다 크고(FIRST_HALF 테이블)

  아이스크림의 주 성분이 과일인 아이스크림을(ICECREAM_INFO 테이블(FLAVOR 외래키))

  총 주문량이 큰 순서대로 조회

  1. 두 테이블 FLAVOR 필드를 기준으로 조인
  2. FIRST_HALF 테이블에서 주문량 필드가 3000보다 크다
  3. ICECREAM_INFO 테이블에서 INGREDIENT_TYPE 필드가 “fruit_based”(주성분이 과일)
  4. 주문량 내림차순으로 정렬
  5. FLAVOR 필드 조회

  SELECT FLAVOR
  FROM
    FIRST_HALF FH
      JOIN
    ICECREAM_INFO II
      ON FH.FLAVOR = II.FLAVOR
  WHERE
    FH.TOTAL_ORDER > 3000
      AND
    II.INGREDIENT_TYPE = "fruit_based"
  ORDER BY TOTAL_ORDER DESC;
  

중복 제거하기

• 동물 보호소에 들어온 동물의 이름은 몇 개인지 조회

  중복되는 이름 하나로 친다

  SELECT COUNT(DISTINCT NAME)
  FROM ANIMAL_INS;
  

  1. DISTICT 키워드
  2. COUNT()는 NULL을 무시한다

연도별 대장균 크기의 편차 구하기

ID와 분화된 연도는 기존의 필드를 이용해서 간단하게 구할 수 있다.

연도별 대장균 크기의 편차는 서브쿼리를 이용해 각 연도에서 최대 대장균 크기를 구한 뒤, 각 대장균 크기를 뺀다.

-- ID : 대장균 ID
-- PARENT_ID : 부모 개체 ID
-- SIZE_OF_COLONY : 개체의 크기
-- DIFFERENTIATION_DATE : 분화된 날짜
-- GENOTYPE : 개체의 형질

-- 문제 : 분화된 연도(YEAR), 
-- 분화된 연도별 대장균 크기의 편차(YEAR_DEV),
-- 연도별 가장 큰 대장균의 크기 - 각 대장균 크기
-- 대장균 개체의 ID(ID) 출력
-- 연도에 대해 오름차순 정렬, 같은 연도이면 대장균 편차에 오름차순으로 정렬
SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR,
(
	SELECT MAX(SIZE_OF_COLONY)
    FROM ECOLI_DATA
    WHERE YEAR(DIFFERENTIATION_DATE) = YEAR
) - SIZE_OF_COLONY AS YEAR_DEV,
ID
FROM ECOLI_DATA
ORDER BY YEAR, YEAR_DEV;

• 막힌부분

  날짜에서 연도만 빼는 방법

  연도 : YEAR(기준날짜)

  달 : MONTH(기준날짜)

  일 : DAY(기준날짜)

  시간 : HOUR(기준날짜)

  분 : MINUTE(기준날짜)

  초 : SECOND(기준날짜)

잡은 물고기 중 가장 큰 물고기의 길이 구하기

• 가장 큰 물고기의 길이에 cm를 붙여 출력

  SQL문에서는 데이터 형을 변환을 안해도 된다.

  CONCAT(TARGET1, TARGET2)

  SELECT CONCAT(MAX(LENGTH), "cm") AS MAX_LENGTH
  FROM FISH_INFO
  

물고기 종류 별 대어 찾기

• 종류별로 가장 큰 물고기 ID, 물고기 이름, 길이 출력

  1. FISH_INFO, FISH_NAME_INFO 조인
  2. FISH_TYPE별 LENGTH가 최대인 행만 선택

  -- 1.
  SELECT INFO.ID,
  		NAME.FISH_NAME,
  		INFO.LENGTH
  FROM FISH_INFO INFO
  	JOIN
  		FISH_NAME_INFO NAME
  	ON INFO.FISH_TYPE = NAME.FISH_TYPE
    
  -- 2.
  SELECT FISH_TYPE, MAX(LENGTH)
  FROM  FISH_INFO
  GROUP BY FISH_TYPE
    
  -- 3. 합침
  SELECT INFO.ID,
  		NAME.FISH_NAME,
  		INFO.LENGTH
  FROM FISH_INFO INFO
  	JOIN
  		FISH_NAME_INFO NAME
  	ON INFO.FISH_TYPE = NAME.FISH_TYPE
  WHERE (INFO.FISH_TYPE, INFO.LENGTH)
  	IN (
      	SELECT FISH_TYPE, MAX(LENGTH)
          FROM FISH_INFO
          GROUP BY FISH_TYPE
      )
  ORDER BY INFO.ID;