SELECT

SELECT 문제 특징 및 상황

평균 일일 대여 요금 구하기

SELECT ROUND(SUM(DAILY_FEE) / COUNT(CAR_ID), 0) as AVERAGE_FEE 
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = "SUV"
GROUP BY CAR_TYPE;

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

SELECT MEMBER_ID, MEMBER_NAME, GENDER, date_format(DATE_OF_BIRTH, '%Y-%m-%d')
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 3
    AND GENDER = "W"
    AND TLNO IS NOT NULL
ORDER BY MEMBER_ID ASC

서울에 위치한 식당 목록 출력하기

SELECT MEMBER_ID, MEMBER_NAME, GENDER, date_format(DATE_OF_BIRTH, '%Y-%m-%d')
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 3
    AND GENDER = "W"
    AND TLNO IS NOT NULL
ORDER BY MEMBER_ID ASC

강원도에 위치한 생산공장 목록 출력하기

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE SUBSTRING(ADDRESS, 1, 3) = '강원도'
ORDER BY FACTORY_ID ASC

12세 이하인 여자 환자 목록 출력하기

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, "NONE") as TLNO
FROM PATIENT
WHERE AGE <= 12
    AND GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME ASC

흉부외과 또는 일반외과 의사 목록 출력하기

SELECT DR_NAME, DR_ID, MCDP_CD, date_format(HIRE_YMD, '%Y-%m-%d') 
FROM DOCTOR
WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME ASC

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

SELECT f.FLAVOR
FROM FIRST_HALF f
    RIGHT JOIN ICECREAM_INFO i 
        ON f.FLAVOR = i.FLAVOR
WHERE f.TOTAL_ORDER > 3000
    AND i.INGREDIENT_TYPE = 'fruit_based'
GROUP BY FLAVOR
ORDER BY TOTAL_ORDER DESC

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

SELECT BOOK_ID, date_format(PUBLISHED_DATE, '%Y-%m-%d') as PUBLISHED_DATE
FROM BOOK
WHERE CATEGORY = '인문'
    AND YEAR(PUBLISHED_DATE) = 2021
ORDER BY PUBLISHED_DATE ASC

인기있는 아이스크림

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC

모든 레코드 조회하기

SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

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

SELECT t.USER_ID, t.PRODUCT_ID
FROM ( SELECT USER_ID, PRODUCT_ID, COUNT(*) AS cnt
         FROM ONLINE_SALE
     GROUP BY USER_ID, PRODUCT_ID) AS t
WHERE cnt >= 2
ORDER BY t.USER_ID ASC, t.PRODUCT_ID DESC

오프라인/온라인 판매 데이터 통합하기

SELECT date_format(SALES_DATE, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE f
WHERE YEAR(SALES_DATE) = 2022 
    AND MONTH(SALES_DATE) = 3
    
UNION ALL

SELECT date_format(SALES_DATE, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE YEAR(SALES_DATE) = 2022 
    AND MONTH(SALES_DATE) = 3
    
ORDER BY SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC

역순 정렬하기

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

아픈 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'
ORDER BY ANIMAL_ID ASC

어린 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION <> 'Aged'
ORDER BY ANIMAL_ID ASC

동물의 아이디와 이름

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

여러 기준으로 정렬하기

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME ASC, DATETIME DESC

상위 n개 레코드

SELECT NAME FROM ANIMAL_INS
ORDER BY DATETIME ASC LIMIT 1

조건에 맞는 회원수 구하기

SELECT COUNT(USER_ID) USERS
FROM USER_INFO
WHERE YEAR(JOINED) = 2021
    AND AGE BETWEEN 20 AND 29