제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit입니다.
업그레이드 된 아이템 구하기
SELECT
E.ITEM_ID,
E.ITEM_NAME,
E.RARITY
FROM ITEM_INFO E JOIN (
SELECT
T.ITEM_ID
FROM ITEM_TREE T JOIN (
SELECT
*
FROM ITEM_INFO
WHERE RARITY = 'RARE'
) I
ON T.PARENT_ITEM_ID = I.ITEM_ID
) R
ON E.ITEM_ID = R.ITEM_ID
ORDER BY R.ITEM_ID DESC
- ITEM_INFO 테이블에서 ITEM이 RARE인 ITEM의 ITEM_ID와 ITEM_TREE에서 PARRENT_ITEM_ID를 기준으로 조인합니다.
FROM ITEM_TREE T JOIN (
SELECT
*
FROM ITEM_INFO
WHERE RARITY = 'RARE'
) I
ON T.PARENT_ITEM_ID = I.ITEM_ID
- 다시 ITEM_INFO와 ITEM_ID를 기준으로 조인합니다. 자식 아이템의 RARITY를 확인하기 위함입니다.
FROM ITEM_INFO E JOIN (
SELECT
T.ITEM_ID
FROM ITEM_TREE T JOIN (
SELECT
*
FROM ITEM_INFO
WHERE RARITY = 'RARE'
) I
ON T.PARENT_ITEM_ID = I.ITEM_ID
) R
ON E.ITEM_ID = R.ITEM_ID
- ITEM_ID를 기준으로 내림차순 정렬합니다.
Python 개발자 찾기
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 ASC
- SKILL이 Python인 개발자를 찾고 ID를 기준으로 오름차순 정렬합니다.
(WHERE SKILL_1 = 'Python' OR SKILL_2 = 'Python' OR SKILL_3 = 'Python')
조건에 맞는 개발자 찾기
SELECT
DISTINCT D.ID,
D.EMAIL,
D.FIRST_NAME,
D.LAST_NAME
FROM DEVELOPERS D
JOIN SKILLCODES S ON (D.SKILL_CODE & S.CODE) > 0
WHERE S.NAME IN ('Python', 'C#')
ORDER BY D.ID ASC;
- SKILLCODES에 NAME이 Python과 C#인 레코드를 조회합니다.
(WHERE S.NAME IN ('Python', 'C#'))
- DEVELOPERS의 SKILL_CODE와 S의 CODE를 비트연산(&)을 통해 공통된 비트가 있는 레코드를 조인합니다.
(FROM DEVELOPERS D JOIN SKILLCODES S ON (D.SKILL_CODE & S.CODE) > 0)
- ID를 기준으로 오름차순 정렬합니다.
잔챙이 잡은 수 구하기
SELECT
COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE LENGTH is null;
- 10 cm 이하인 물고기를 조회합니다.
(WHERE LENGTH is null;)
가장 큰 물고기 10마리 구하기
SELECT
ID,
IFNULL(LENGTH, 10) AS LENGTH
FROM (
SELECT
ID,
LENGTH,
ROW_NUMBER() OVER (ORDER BY LENGTH DESC, ID ASC) AS ROWNUM
FROM FISH_INFO
) AS temp
WHERE ROWNUM <= 10
ORDER BY LENGTH DESC, ID ASC;
- 생선의 길이와 아이디를 기준으로 ROW_NUMBER를 조회합니다.
SELECT
ID,
LENGTH,
ROW_NUMBER() OVER (ORDER BY LENGTH DESC, ID ASC) AS ROWNUM
FROM FISH_INFO
- ROWNUM이 10보다 작은 레코드를 조회합니다.
(WHERE ROWNUM <= 10)
- LENGTH가 null이면 10으로 치환합니다.
(IFNULL(LENGTH, 10) AS LENGTH)
- 생선 길이(내림차순)와 아이디(오름차순)를 기준으로 정렬합니다.
특정 물고기를 잡은 총 수 구하기
SELECT
COUNT(*) AS FISH_COUNT
FROM FISH_INFO AS I JOIN FISH_NAME_INFO AS N
ON I.FISH_TYPE = N.FISH_TYPE
WHERE N.FISH_NAME = 'BASS' OR N.FISH_NAME = 'SNAPPER';
- FISH_TYPE을 기준으로 조인합니다.
(FROM FISH_INFO AS I JOIN FISH_NAME_INFO AS N ON I.FISH_TYPE = N.FISH_TYPE)
- 이름이 BASS 또는 SNAPPER인 생선을 조회합니다.
(WHERE N.FISH_NAME = 'BASS' OR N.FISH_NAME = 'SNAPPER';)
대장균들의 자식의 수 구하기
SELECT
E1.ID,
IFNULL(COUNT(E2.PARENT_ID), 0) AS CHILD_COUNT
FROM ECOLI_DATA E1 LEFT JOIN ECOLI_DATA E2
ON E1.ID = E2.PARENT_ID
GROUP BY E1.ID
ORDER BY E1.ID ASC;
- E1의 ID와 E2의 PARENT_ID를 기준으로 LEFT JOIN합니다.
(FROM ECOLI_DATA E1 LEFT JOIN ECOLI_DATA E2 ON E1.ID = E2.PARENT_ID)
- E1.ID를 기준으로 GROUP BY합니다.
(GROUP BY E1.ID)
- 만약 PARENT_ID가 없으면 0으로 처리합니다.
(IFNULL(COUNT(E2.PARENT_ID), 0) AS CHILD_COUNT)
- ID를 기준으로 오름차순 정렬합니다.
대장균의 크기에 따라 분류하기 1
SELECT
ID,
CASE
WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
WHEN SIZE_OF_COLONY <= 1000 THEN 'MEDIUM'
ELSE 'HIGH'
END AS SIZE
FROM ECOLI_DATA
ORDER BY ID asc
- CASE WHEN 절을 이용하여 사이즈에 따라 분류합니다.
- ID를 기준으로 오름차순 정렬합니다.
특정 형질을 가지는 대장균 찾기
SELECT
COUNT(*) AS COUNT
FROM ECOLI_DATA
WHERE GENOTYPE & 2 = 0 AND (GENOTYPE & 4 > 0 OR GENOTYPE & 1 > 0)
- 2번 형질을 보유하지 않으면서 1, 3번 형질을 보듀하고 있는 대장균의 갯수를 조회합니다.
(WHERE GENOTYPE & 2 = 0 AND (GENOTYPE & 4 > 0 OR GENOTYPE & 1 > 0))
부모의 형질을 모두 가지는 대장균 찾기
SELECT
E1.ID,
E1.GENOTYPE AS GENOTYPE,
E2.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA E1 JOIN ECOLI_DATA E2
ON E1.PARENT_ID = E2.ID
WHERE (E1.GENOTYPE & E2.GENOTYPE) = E2.GENOTYPE
ORDER BY E1.ID ASC;
- 자식과 부모를 연결합니다.
(FROM ECOLI_DATA E1 JOIN ECOLI_DATA E2 ON E1.PARENT_ID = E2.ID)
- 자식의 유전자형이 부모의 유전자형을 포함하고 있는지 확인합니다.
(WHERE (E1.GENOTYPE & E2.GENOTYPE) = E2.GENOTYPE)
- 자식의 ID를 기준으로 오름차순으로 정렬됩니다.
