[2] 프로그래머스 - SQL

Q1. 

동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요.

# RANK() OVER

# LIMIT

# ORDER

 

1. MIN(DATETIME) 활용

(오답)

-- '가장 먼저'의 정의
SELECT NAME
FROM(
	SELECT
    	NAME,
        DATETIME,
        MIN(DATETIME)
 	FROM
    	ANIMAL_INS
 ) AS TEMP;
 
 > sugar(오답)

→ MIN을 같이 SELECT하는 것일 뿐, MIN인 NAME을 찾을 수 없음

 

(정답)

SELECT NAME
FROM ANIMAL_INS
WHERE DATETIME = (SELECT MIN(DATETIME) FROM ANIMAL_INS)

2. RANK() OVER, WHERE 조건절 활용

WITH TEMP AS(
	SELECT
		NAME,
    	RANK() OVER(
    	ORDER BY DATETIME ASC) AS rnk
    FROM ANIMAL_INS)
SELECT NAME
FROM TEMP
WHERE rnk=1;

---

(주의) '정렬'과 '조건' 순서

(X)

WHERE 조건절 → ORDER BY() 정렬

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
WHERE ROWNUM = 1

 

→ WHERE 조건절이 정렬보다 먼저 적용됨

→ 서브쿼리로 정렬 후 조건절을 걸면 된다.

 

(O)

ORDER BY() 정렬 → WHERE 조건절

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

 

→  서브쿼리 내에서 적용된 후 조건절이 제대로 적용됨

 

※ ROWNUM은 Oracle에만 있고, 동일한 기능을 하는 함수로는 MySQL에는 LIMIT이 있다. 

 

ORDER BY() 정렬→ LIMIT조건절

(O)

SELECT
NAME
FROM ANIMAL_INS
ORDER BY DATETIME ASC
LIMIT 1;

---

Q2.

DOCTOR 테이블에서 진료과가 흉부외과(CS)이거나 일반외과(GS)인 의사의 이름, 의사ID, 진료과, 고용일자를 조회하는 SQL문을 작성해주세요. 이때 결과는 고용일자를 기준으로 내림차순 정렬하고, 고용일자가 같다면 이름을 기준으로 오름차순 정렬해주세요.

# DATE_FORMAT

# IN ()

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' # = IN ('CS','GS')
ORDER BY HIRE_YMD DESC, DR_NAME ASC;

 

※ 참고로 날짜 형식에서 소문자y는 2021에서 21로, 2자리만 나옴

 

---

Q3.

상반기 아이스크림 총주문량(T1)이 3,000보다 높으면서 아이스크림의 주 성분(T2)이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성해주세요.

# JOIN

# ORDER

총 주문량이 있는 FIRST_HALF

아이스크림 주 성분이 있는 ICECREAM_INFO

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;

 

---

Q4.

아이템의 희귀도가 'RARE'인 아이템(T1)들의 모든 다음 업그레이드 아이템(T2)의 아이템 ID, 아이템 명, 아이템의 희귀도를 출력하는 SQL 문을 작성해 주세요. 이때 결과는 아이템 ID를 기준으로 내림차순 정렬해주세요.

• PARENT_ITEM: ITEM A가 B로 업그레이드 되었으면, B의 PARENT_ITEM은 A이다. (업그레이드 되기 이전 아이템)
• ROOT_ITEM: 업그레이드가 시작되는 아이템으로, 이전 아이템이 존재하지 않는(PARENT_ITEM이 존재하지 않는) 아이템
• 즉, T2에서 부모 아이템이 RARE인 아이템을 T1에서 조회하는 문제 

# JOIN

# IN()

ITEM_INFO (T1)

ITEM_TREE (T2)

 

JOIN~WHERE~IN()

SELECT
	B.ITEM_ID,
	A.ITEM_NAME,
	A.RARITY 
FROM
	ITEM_INFO A
JOIN
	ITEM_TREE B
	ON A.ITEM_ID = B.ITEM_ID
WHERE
	B.PARENT_ITEM_ID IN (
    SELECT ITEM_ID 
    FROM ITEM_INFO
    WHERE RARITY='RARE')
ORDER BY
	A.ITEM_ID DESC;

 

JOIN 2번 활용

• 1번 조인으로 T1에 T2의 PARENT_ITEM_ID 정보를 가져옴
• 2번 조인으로 추가한 T1의 PARENT_ITEM_ID가 T2의 ITEM_ID와 동일하고, RARITY=''RARE"인 값 필터링

SELECT 
	A.ITEM_ID,
	A.ITEM_NAME,
	A.RARITY
FROM
	ITEM_INFO AS A
JOIN
	ITEM_TREE AS B
	ON A.ITEM_ID = B.ITEM_ID
JOIN
	ITEM_INFO AS C
	ON C.ITEM_ID = B.PARENT_ITEM_ID
WHERE
	C.RARITY='RARE'
ORDER BY
	A.ITEM_ID DESC;

 

JOIN 없이 WHERE조건절 & 서브쿼리 활용

SELECT
    ITEM_ID,
    ITEM_NAME,
    RARITY
FROM ITEM_INFO
WHERE ITEM_ID IN(
    SELECT ITEM_ID
    FROM ITEM_TREE
    WHERE PARENT_ITEM_ID IN (
        SELECT ITEM_ID
        FROM ITEM_INFO
        WHERE RARITY='RARE'))
ORDER BY ITEM_ID DESC;

---

Q6.

DEVELOPERS 테이블에서 Python이나 C# 스킬을 가진 개발자의 정보를 조회하려 합니다. 조건에 맞는 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해주세요. 결과는 ID를 기준으로 오름차순 정렬해 주세요.

# 비트 연산자

# JOIN~ON~&

DEVELOPERS (T1)

SKILL_CODE (T2)

• T1에서Jerami의 경우, SKILL_CODE가 400 = 256 + 128 + 16 으로 Python, Java, JavaScript 스킬을 보유하고 있는 것
• T1의 SKILL_CODE는 T2의 CODE의 합계로 이루어져 있기에, 단순 조인

SELECT
A.ID, A.EMAIL, A.FIRST_NAME, A.LAST_NAME
FROM DEVELOPERS A
JOIN SKILLCODES B
ON A.SKILL_CODE & B.CODE
WHERE B.NAME IN ('C#','Python')
ORDER BY A.ID ASC;

 

해석

• Python은 128 = 2^7 = 10000000
• C#은 2 = 2^1 = 00000010
• 두 값을 합산하면 130 = 10000010이 된다.
• A.SKILL_CODE는 스킬을 2진수로 표현했을 때의 합을, B_CODE는 2진수로 표현된 하나의 비트 값을 의미한다.
•  A.SKILL_CODE & B.CODE로 SKILL_CODE에 특정 스킬(B.CODE)이 포함되어 있는지 확인
• C#혹은 Python이 SKILL_CODE 에 포함되어 있으면 0이 아닌 값을 반환하기에, WHERE절에서 조건이 True로 평가

---

✅ 비트 연산자(Bitwise Operator)

먼저 10진수와 2진수의 개념에 대해 알아야 한다.

1. 10진수 : 우리가 일상에서 사용하는 숫자 체계(0~9)로, 각 자리의 값은 10의 제곱수로 증가함
   • 321 = 3 * 10^2 + 2 * 10 + 1 = 321
2. 2진수 : 컴퓨터가 사용하는 숫자 체계 (0,1)로, 각 자리의 값은 2의 제곱수로 증가함
   • 1011 = 1*2^3 + 0 + 1*2 + 1 = 11

Operator	Description	Example
&	AND 연산, 둘다 참일 때만 만족	a & b = 12	0000 1100
|	OR 연산, 둘 중 하나만 참이여도 만족	a | b = 61	0011 1101
^	XOR 연산, 둘 중 하나만 참일 때 만족	a^b = 49	0011 0001
~	보수 연산	~a = -61	1100 0011
<<	변수의 값을 왼쪽으로 지정된 비트 수만큼 이동	a << 2 = 240	1111 0000
>>	변수의 값을 오른쪽으로 지정된 비트 수만큼 이동	a >> 2 = 15	0000 1111

---