[SELECT] SQL 고득점 키트 문제 풀이 모음

 

 

풀 때마다 업데이트 할 예정

---

 

 

[LV1]

 

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

-- 자동차 종류가 'SUV'
-- 평균 일일 대여 요금을 출력 (AVERAGE_FEE)
-- 소수 첫 번째 자리에서 반올림

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

 

 

• 반올림 : ROUND(숫자, 반올림할 위치-1)
  • 1을 적을 경우 둘째자리에서 반올림, 아무것도 적지 않으면 첫번째자리에서 반올림
• 올림 : CEIL(숫자)
• 내림 : FLOOR(숫자)
• 버림 : TRUNCATE(숫자, 버림할 위치-1)
• GROUP BY에 쓰인 열의 조건은 HAVING에 적어야한다. WHERE에 적으면 작동하지 않는다.

 

 

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

-- 2021년에 출판
-- '인문' 카테고리
-- BOOK_ID, PUBLISHED_DATE 출력
-- 출판일을 기준으로 오름차순

SELECT BOOK_ID, TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD')
FROM BOOK
WHERE EXTRACT(YEAR FROM PUBLISHED_DATE) = 2021
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;

 

• 날짜 정보 추출 : EXTRACT(날짜요소 FROM 컬럼명)
  • 날짜요소 년도 : YEAR
  • 날짜요소 월    : MONTH
  • 날짜요소 일    : DAY
  • 날짜요소 시간 : HOUR
  • 날짜요소 분    : MINUTE
  • 날짜요소 초    : SECOND
  • 현재시각 기준 : EXTRACT(YEAR, SYSTIMESTAMP) AS YEAR

 

• 날짜 포맷 지정 : TO_CHAR(숫자, 포맷)
  • 포맷 년도 : YYYY
  • 포맷 월    : MM
  • 포맷 일    : DD
  • 포맷 시간 : HH24 or HH
  • 포맷 분    : MI
  • 포맷 초    : SS

 

 

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

-- 12세 이하
-- 여자환자
-- 환자이름, 환자번호, 성별코드, 나이, 전화번호 출력
-- 전화번호가 없는 경우 'NONE'
-- 나이 기준 내림차순 정렬, 환자이름 기준 오름차순 정렬

SELECT PT_NAME, PT_NO, GEND_CD, AGE, 
CASE 
    WHEN TLNO IS NULL THEN 'NONE'
    ELSE TLNO
END AS TLNO
FROM PATIENT
WHERE AGE <= 12
AND GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME;

 

• 오라클 조건 : CASE [WHEN THEN ELSE] END 

 

 

4. 조건에 부합하는 중고거래 댓글 조회하기

-- 2022년 10월에 작성된 게시글
-- 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 출력
-- 댓글 작성일을 기준으로 오름차순 정렬, 게시글 제목을 기준으로 오름차순 정렬

SELECT org.TITLE, org.BOARD_ID, reply.REPLY_ID, reply.WRITER_ID, reply.CONTENTS
,TO_CHAR(reply.CREATED_DATE, 'YYYY-MM-DD') AS CREATED_DATE

FROM USED_GOODS_BOARD org JOIN USED_GOODS_REPLY reply
ON org.BOARD_ID = reply.BOARD_ID

WHERE EXTRACT(YEAR FROM org.CREATED_DATE) = 2022
AND EXTRACT(MONTH FROM org.CREATED_DATE) = 10

ORDER BY reply.CREATED_DATE, org.TITLE

 

 

 

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

-- 흉부외과(CS)이거나 일반외과(GS)
-- 이름, 의사ID, 진료과, 고용일자 출력
-- 고용일자를 기준으로 내림차순 정렬, 이름을 기준으로 오름차순 정렬

SELECT DR_NAME, DR_ID, MCDP_CD, TO_CHAR(HIRE_YMD, 'YYYY-MM-DD') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME

 

 

 

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

-- 총주문량이 3,000보다 높음
-- 주 성분이 과일(fruit_based)
-- 아이스크림의 맛을 총주문량이 큰 순서대로 조회

SELECT DISTINCT sale.FLAVOR
FROM FIRST_HALF sale JOIN ICECREAM_INFO info ON sale.FLAVOR = info.FLAVOR
WHERE INGREDIENT_TYPE = 'fruit_based'
AND TOTAL_ORDER > 3000
ORDER BY TOTAL_ORDER DESC

 

• 만약 'Orange'의 값이 여러 행일 경우 1개만 출력하기 위해 DISTINCT를 사용

 

 

7.  인기있는 아이스크림

-- 아이스크림의 맛 출력
-- 총주문량을 기준으로 내림차순 정렬, 출하 번호를 기준으로 오름차순 정렬

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID

 

 

 

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

-- 강원도에 위치
-- 공장 ID, 공장 이름, 주소를 조회
-- 공장 ID를 기준으로 오름차순 정렬

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

 

 

9.  모든 레코드 조회하기

-- 모든 동물의 정보
-- ANIMAL_ID순

SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

 

10.  역순 정렬하기

-- 이름과 보호 시작일을 조회
-- ANIMAL_ID 역순

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

 

 

11.  아픈 동물 찾기

-- 아픈 동물(Sick)의 아이디와 이름을 조회
-- 아이디 순으로 조회

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

 

 

12.  어린 동물 찾기

-- 젊은 동물(Aged X)의 아이디와 이름을 조회
-- 결과는 아이디 순으로 조회

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

 

 

13.  동물의 아이디와 이름

-- 동물의 아이디와 이름 출력
-- ANIMAL_ID순

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

 

14.  여러 기준으로 정렬하기

-- 아이디, 이름, 보호 시작일 출력
-- 이름 순, 보호를 나중에 시작한 동물을 먼저 보여줘야 합니다.

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

 

 

15.  상위 n개 레코드

-- 가장 먼저 들어온 동물
-- 이름을 조회

SELECT *
FROM (SELECT NAME FROM ANIMAL_INS ORDER BY datetime)
WHERE ROWNUM = 1

 

• mysql에서는 LIMIT 1로 원하는 행 수를 출력할 수 있었다.
• 하지만 오라클에서는 ROWNUM으로 조절할 수 있다.
• 인라인 뷰로 연산 후 ROWNUM을 처리해줘야 정확한 값이 나온다.

 

 

16.  조건에 맞는 회원 수 구하기

-- 2021년에 가입한 회원
-- 나이가 20세 이상 29세 이하
-- 몇 명인지 출력

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

 

 

 

17.  Python 개발자 찾기

-- Python 스킬을 가진 개발자
-- ID, 이메일, 이름, 성을 조회
-- ID를 기준으로 오름차순 정렬

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPER_INFOS
WHERE SKILL_1 = 'Python' OR SKILL_2 = 'Python' OR SKILL_3 = 'Python'
ORDER BY ID

 

 

18. 잔챙이 잡은 수 구하기

-- 길이가 10cm 이하 (NULL)
-- 물고기의 수를 출력
-- 물고기의 수를 나타내는 컬럼 명은 FISH_COUNT

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

 

 

19. 가장 큰 물고기 10마리 구하기

-- 가장 큰 물고기 10마리
-- ID, 길이를 출력
-- 길이를 기준으로 내림차순 정렬, ID에 대해 오름차순 정렬
-- 가장 큰 물고기 10마리 중 길이가 10cm 이하인 경우는 없습니다.

SELECT ID, LENGTH
FROM FISH_INFO
WHERE LENGTH IS NOT NULL
ORDER BY LENGTH DESC, ID
LIMIT 10 -- 오라클의 경우 where절에 ROWNUM=10 추가

 

 

 

20. 특정 형질을 가지는 대장균 찾기

-- 2(0010 = 2)번 형질을 보유하지 않음
-- 1(0001 = 1)번이나 3(0100 = 4)번 형질을 보유
-- 대장균 개체의 수(COUNT)를 출력

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

 

• GENOTYPE&1 (0001) -> 1이 있는 자리에 1이 있으면 true

 

 

[LV2]

 

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

-- 생일 3월
-- 여성 회원
-- ID, 이름, 성별, 생년월일을 조회
-- 전화번호가 NULL인 경우는 출력대상에서 제외
-- 회원ID를 기준으로 오름차순 정렬

SELECT MEMBER_ID, MEMBER_NAME, GENDER, TO_CHAR(DATE_OF_BIRTH, 'YYYY-MM-DD') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE EXTRACT(MONTH FROM DATE_OF_BIRTH) = 3
AND gender = 'W'
AND TLNO IS NOT NULL
ORDER BY MEMBER_ID

 

 

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

-- 동일한 회원이 동일한 상품을 재구매
-- 재구매한 회원 ID, 재구매한 상품 ID 출력
-- 회원 ID 기준 오름차순 정렬, 상품 ID 기준 내림차순 정렬

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*)>=2
ORDER BY USER_ID, PRODUCT_ID DESC

 

 

3. 업그레이드 된 아이템 구하기

-- 희귀도가 'RARE'
-- 다음 업그레이드 아이템의 ITEM_ID, ITEM_NAME, RARITY를 출력
-- 아이템 ID를 기준으로 내림차순 정렬

SELECT I.ITEM_ID, I.ITEM_NAME, I.RARITY
FROM ITEM_INFO I JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID
                 JOIN ITEM_INFO I_2 ON T.PARENT_ITEM_ID = I_2.ITEM_ID
WHERE I_2.RARITY = 'RARE' 
ORDER BY I.ITEM_ID DESC

 

*해설

SELECT I.ITEM_ID, I.ITEM_NAME, I.RARITY, T.PARENT_ITEM_ID
FROM ITEM_INFO I JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID
ORDER BY I.ITEM_ID DESC

 

• ITEM_INFO 테이블에 ITEM_TREE를 JOIN하면 각 아이템 별 부모ID를 확인할 수 있다.
• 부모ID가 있다는 것은 그 부모의 업그레이드 아이템이 자신이라는 뜻이다.

 

SELECT I.ITEM_ID, I.ITEM_NAME, I.RARITY, T.PARENT_ITEM_ID, I_2.ITEM_NAME
FROM ITEM_INFO I JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID
                 JOIN ITEM_INFO I_2 ON T.PARENT_ITEM_ID = I_2.ITEM_ID
ORDER BY I.ITEM_ID DESC

 

• 위 테이블의 부모의 이름을 알아내기 위해 또 한 번 ITEM_INFO 테이블을 JOIN해준다.
• 이 과정에서 PARENT_ITEM_ID가 ROOT인 요소가 사라진다.
• 즉, 위 결과는 모두 누군가의 업그레이드 아이템만 남았다는 소리다.
• 위 쿼리에 RARE 조건을 추가하고, 출력을 원하는 속성만 남기면 답이 된다.

 

 

4. 조건에 맞는 개발자 찾기

-- Python이나 C# 스킬
-- ID, 이메일, 이름, 성을 조회
-- ID를 기준으로 오름차순 정렬

SELECT DISTINCT dev.ID, dev.EMAIL, dev.FIRST_NAME, dev.LAST_NAME
FROM DEVELOPERS dev LEFT JOIN SKILLCODES skill ON skill.CODE & dev.SKILL_CODE
WHERE skill.NAME = 'Python' OR skill.NAME = 'C#'
ORDER BY ID

 

• 400(110010000)의 능력을 가지고 있는 개발자라면 256(100000000)과, 128(10000000), 16(10000)에 해당하는 스킬을 가졌다. 이는 비트 연산자 &로 비교군과 연산결과가 같으면 출력하도록 했다.

 

 

5. 특정 물고기를 잡은 총 수 구하기

-- BASS와 SNAPPER의 수를 출력
-- 컬럼명은 'FISH_COUNT`

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO info JOIN FISH_NAME_INFO name ON info.FISH_TYPE = name.FISH_TYPE
WHERE name.FISH_NAME = 'BASS' OR name.FISH_NAME = 'SNAPPER'

 

 

6. 부모의 형질을  모두 가지는 대장균 찾기

-- 부모의 형질을 모두 보유
-- ID, GENOTYPE, PARENT_GENOTYPE 출력
-- ID에 대해 오름차순

SELECT child.ID, child.GENOTYPE, parent.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA child JOIN ECOLI_DATA parent ON child.PARENT_ID = parent.ID
WHERE child.GENOTYPE | parent.GENOTYPE = child.GENOTYPE
ORDER BY child.ID

 

• 자식 7의 부모는 2라고 가정하자
• 자식 7의 형질은 5(101)이고, 부모 2의 형질은 1(1)이다.
• 자식의 형질 중 부모가 가진 형질이 모두 존재하기 위해서는 [101 | 001 = 101] 즉, [자식 형질 | 부모 형질 = 자식 형질]이어야 한다. 

 

 

[LV3]

 

1. 없어진 기록 찾기

-- 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물
-- ID, 이름 조회
-- ID 순으로 조회

SELECT outs.ANIMAL_ID, outs.NAME
FROM ANIMAL_OUTS outs LEFT OUTER JOIN ANIMAL_INS ins
ON outs.ANIMAL_ID = ins.ANIMAL_ID
WHERE ins.ANIMAL_ID IS NULL
ORDER BY outs.ANIMAL_ID

 

 

---

 

레벨 1 문제가 왜이리 많노?