✈️ SQL Programmers Level 1 - 27 Solved

001. 평균 일일 대여 요금 구하기 / 002. 흉부외과 또는 일반외과 의사 목록 출력하기

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

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

 

✈️

(1) SELECT) 일일 대여 요금 평균값을 가져오므로 AVG(DAILY_FEE)를 SELECT. 소수 첫번째 자리 반올림이므로 ROUND(AVG(DAILY_FEE, 0) 

(2) FROM) WHERE)  CAR_RENTAL_COMPANY_CAR라는 표에서 정보를 가져오므로 FROM CAR_RENTAL_COMPANY_CAR

(3) 자동차 종류가 'SUV'여야 하므로 WHERE문 조건은 CAR_TYPE = 'SUV'

 

✈️

(1) SELECT) 주어진 4개의 칼럼을 SELECT 한 다음 HIRE_YMD 칼럼 데이터 포맷을 %Y-%m-%d. y가 아니라 Y인 이유는 2020년이면 20이 아니라 2020으로 표현. %M은 영어로 March, %m은 03. %D는 영어로 1st, %d는 01. 

(2) ORDER BY) 고용일자 내림차순 정렬 후 이름 오름차순 정렬 진행. 고용일자 내림차순 먼저 진행하므로 ORDER BY HIRE_YMD DESC, 그 다음 이어서 DR_NAME. 이어서 작성하면 첫 번째 정렬 후 기준 같을 때 그 다음 기준으로 정렬된다

(3) WHERE) OR로 앞뒤 MCDP_CD = 'CS', 'GS' 각각 조건 나열

---

003. 과일로 만든 아이스크림 고르기 / 004. 조건에 부합하는 중고거래 댓글 조회하기

SELECT A.FLAVOR
FROM FIRST_HALF A
JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
WHERE (A.TOTAL_ORDER > 3000) AND (B.INGREDIENT_TYPE = 'fruit_based')
ORDER BY A.TOTAL_ORDER DESC

SELECT A.TITLE, A.BOARD_ID, B.REPLY_ID, B.WRITER_ID, B.CONTENTS, DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD A
JOIN USED_GOODS_REPLY B ON A.BOARD_ID = B.BOARD_ID
WHERE SUBSTR(A.CREATED_DATE, 1, 7) = '2022-10'
ORDER BY B.CREATED_DATE ASC, A.TITLE ASC

 

✈️

(1) SELECT) FIRST_HALF라는 첫번째 테이블에서의 TOTAL_ORDER를 구하므로 A.FLAVOR

(2) FROM) FIRST_HALF 별칭 A 설정

(3) JOIN) 두 테이블 연결해야 하므로 연결한 두 번째 테이블 ICECREAM_INFO 입력한 다음 ON 사용해서 두 테이블 연결 위한 A.FLAVOR = B.FLAVOR

(4) WHERE) A와 B 각각 조건 AND로 서로 연결

(5) ORDER BY) TOTAL_ORDER 내림차순이므로 DESC

 

✈️

(1) SELECT) B.CREATED_DATE인지 A.CREATED_DATE인지 확인 필수. DATE_FORMAT에 맞게 %Y-%m-%d 형식 맞추기

(2) JOIN) 조건 A.BOARD_ID = B.BOARD_ID. WRITER_ID는 각각 게시물 작성자, 댓글 작성자이므로 다름. BOARD_ID로 맞추기

(3) WHERE) DATE 형식의 TYPE은 문자열로, 문자열 앞의 '2022-10' 부분만 조건으로 만들려면 SUBSTR(A.CREATED_DATE, 1, 7)로 SUBSTR() 함수 사용

(4) ORDER BY) ASC는 디폴트로 안 써도 상관 X

---

005. 인기있는 아이스크림 / 006. 강원도에 위치한 생산공장 목록 출력하기

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE "강원도%"
ORDER BY FACTORY_ID ASC

 

✈️ 첫번째 문제 스킵

 

✈️

(1) WHERE) 강원도로 시작하는 조건은 LIKE를 쓰고 '강원도%' 사용. %는 0개, 1개 또는 2개 이상의 문자 모음을 뜻하며, _(underscore)는 단순히 1개의 글자를 뜻한다. 즉 '강원도%'는 강원도로 시작하는 모든 문자열

---

007. 12세 이하인 여자 환자 목록 출력하기 / 008. 조건에 맞는 도서 리스트 출력하기

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 BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE PUBLISHED_DATE LIKE '2021%' AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE ASC

 

✈️

(1) SELECT) TLNO가 없다면 NONE으로 표시하기 위해 ifnull() 함수 사용

---

009. 모든 레코드 조회하기 / 010. 역순 정렬하기

SELECT ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

---

011. 아픈 동물 찾기 / 012. 어린 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION LIKE 'sick'
ORDER BY ANIMAL_ID ASC

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'

 

✈️ 12번)

(1) WHERE) '같지 않다'라는 조건은 != 사용

---

013. 동물의 아이디와 이름 / 014. 여러 기준으로 정렬하기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS

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

---

015. 상위 n개 레코드 / 016. 조건에 맞는 회원수 구하기

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE YEAR(JOINED) = '2021' AND 20 <= AGE AND AGE <= 29

 

✈️ 딱 1개 데이터만 가져오려면 마지막 LIMIT 1

 

✈️ 개수를 구하므로 COUNT(*) 사용 / YEAR()로 DATETIME의 2021 확인

---

017. Python 개발자 찾기 / 018. 잔챙이 잡은 수 구하기

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPER_INFOS
WHERE 'Python' IN (SKILL_1, SKILL_2, SKILL_3)
ORDER BY ID

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE LENGTH IS NULL

 

✈️ IN () 활용해서 원하는 문자열이 () 안의 column에 있는 지 확인

 

✈️ WHERE 조건에 LENGTH IS NULL 조건 넣어서 NULL인 칸이 들어간 레코드 개수 COUNT(*)로 구할 수 있다.

---

019. 가장 큰 물고기 10마리 구하기 / 020. 특정 형질을 가지는 대장균 찾기

SELECT ID, LENGTH 
FROM FISH_INFO 
ORDER BY LENGTH, ID
LIMIT 10

SELECT
    COUNT(*) AS COUNT
FROM
    ECOLI_DATA
WHERE
    GENOTYPE & 2 = 0 AND (GENOTYPE & 1 = 1 OR GENOTYPE & 4 = 4)

 

✈️ LENGTH, ID 순 정렬했기에 LIMIT 10으로 상위 10개 레코드 출력

 

✈️ 이진법의 특정 자리의 숫자가 1인지 0인지 아는 방법은 & 연산자와 2의 해당 자리의 지수승을 활용하면 된다.

 

: 위 그림과 같이 구하고자 하는 자리수에 1이 있는 지 확인하기 위해 & 연산자와 해당 2의 지수승을 연산으로 진행. 그래도 해당 2의 지수승이 결과로 나온다면 해당 자리수에 1이 존재한다는 걸 알 수 있다.

---

021. 가장 비싼 상품 구하기 / 022. 최댓값 구하기

SELECT MAX(PRICE) AS MAX_PRICE
FROM PRODUCT

SELECT MAX(DATETIME) AS 시간
FROM ANIMAL_INS

 

✈️ PRICE 칼럼에서 최댓값 MAX() 함수 활용. MAX_PRICE로 지정이므로 AS MAX_PRICE

 

✈️ DATETIME 형식의 최댓값은 가장 최근 시간이므로 최댓값 구하는 MAX() 함수 활용.

---

023. 잡은 물고기 중 가장 큰 물고기의 길이 구하기 / 024. 경기도에 위치한 식품 창고 목록 출력하기

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

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, 'N')
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID ASC

 

✈️ CONCAT 함수 활용해서 최댓값에 'cm' 붙이기

 

✈️ IFNULL() 함수 / 경기도로 시작하는 주소는 LIKE '경기도%'

---

025. 이름이 없는 동물의 아이디 / 026. 이름이 있는 동물의 아이디

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID ASC

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID ASC

---

023. 나이 정보가 없는 회원 수 구하기 / 024. 잡은 물고기의 평균 길이 구하기

SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE AGE IS NULL

SELECT ROUND(AVG(IFNULL(LENGTH,10)),2) AS AVERAGE_LENGTH
FROM FISH_INFO

 

✈️ IFNULL() 함수 활용해서 NULL인 length는 모두 10으로 바꿈. AVG() 함수로 평균 구하고, ROUND() 함수로 소수 둘째 자리까지 나타내기

 

※ AVG() 안에 IFNULL(LENGTH, 10)을 넣어서 LENGTH의 평균 모두 구하기

---

025. 특정 옵션이 포함된 자동차 리스트 구하기 / 026. 자동차 대여 기록에서 장기/단기 대여 구분하기

SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC

SELECT  HISTORY_ID,
        CAR_ID,
        DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE,
        DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE,
        CASE
        WHEN DATEDIFF(END_DATE,START_DATE) + 1 >= 30
        THEN "장기 대여"
        ELSE "단기 대여"
        END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN '2022-09-01' AND '2022-09-30'
ORDER BY HISTORY_ID DESC

 

✈️

(1) DATE_FORMAT으로 원하는 형식 (%Y-%m-%d)으로 바꾸어서 표현

 

(2) CASE WHEN THEN ELSE END 구문(SQL에서의 if문)을 활용해 RENT_TYPE 내용 결정

 

" CASE

WHEN 조건1

THEN 실행1

ELSE 실행2

END AS A"

: 조건1을 충족할 때 A 칼럼의 내용은 실행 1로, 조건 1을 충족하지 않는다면 A칼럼의 내용은 실행2로 만들겠다는 구문

---

027. 한 해에 잡은 물고기 수 구하기

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE DATE_FORMAT(TIME, "%Y") = "2021"

 

✈️ DATE_FORMAT() 함수 결과 TIME 칼럼의 %Y 즉 연도가 2021인 조건은 WHERE DATE_FORMAT(TIME, "%Y") = "2021"로 만들 수 있다.

---