Programmers SQL 고득점 Kit 풀기(3)

 

 

이전 글

https://fladi.tistory.com/441

 

 

 

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

-- 3조인 풀이
SELECT tree.ITEM_ID,
    info1.ITEM_NAME,
    info1.RARITY
FROM ITEM_TREE tree
INNER JOIN ITEM_INFO info1
    ON tree.ITEM_ID = info1.ITEM_ID
INNER JOIN ITEM_INFO info2
    ON tree.PARENT_ITEM_ID = info2.ITEM_ID
WHERE info2.RARITY = 'RARE'
ORDER BY tree.ITEM_ID DESC;

SELECT tree.ITEM_ID,
    info.ITEM_NAME,
    info.RARITY
FROM ITEM_INFO AS info
INNER JOIN ITEM_TREE  tree
ON info.ITEM_ID = tree.ITEM_ID
WHERE tree.PARENT_ITEM_ID IN (SELECT ITEM_ID FROM ITEM_INFO WHERE RARITY='RARE')
ORDER BY tree.ITEM_ID DESC;

• inner join 3개로 구현할 수도 있고, IN을 사용할 수도 있었다
• IN의 경우, 레어인 애들을 먼저 찾고, PARENT_ITEM_ID가 여기에 포함된 애들을 찾은 후, 조인조건을 만족하는 데이터만 가져오는 inner join을 사용하였다

 

 

2. 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;

• 1번 문제에 비하면 너무 쉬운 문제다

 

 

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

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'Python') > 0
    OR SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'C#') > 0
ORDER BY ID ASC;

• 비트마스킹이 필요한 문제이다
• & 라는 비트 AND 연산으로 해당 skill을 가지고 있는지 판단할 수 있다
• >0을 하지 않아도 정답처리가 되는 걸 봤을 때, 1이상인 값을 true로 판단하는 것 같았다
  • 실제로 테스트해보니 WHERE 0인 경우 아무것도 출력되지 않고, WHERE 1인 경우 모든 레코드 값이 출력되었다
  • 하지만 > 0을 붙여주는 게 마음이 편해서 그렇게 해줬다

 

 

4. 잔챙이 잡은 수 구하기

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

• IS NULL만 알면 된다

 

 

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

SELECT ID, LENGTH FROM FISH_INFO 
ORDER BY LENGTH DESC, ID ASC
LIMIT 10;

• ORACLE은 rownum을 이용한다는 걸 잊지말자!

 

 

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

SELECT COUNT(*) as FISH_COUNT
FROM FISH_INFO
WHERE FISH_TYPE in 
    (SELECT FISH_TYPE FROM FISH_NAME_INFO WHERE FISH_NAME = 'BASS' OR FISH_NAME = 'SNAPPER');

 

 

7. 대장균들의 자식의 수 구하기

SELECT A.ID, COUNT(B.ID) AS CHILD_COUNT
FROM ECOLI_DATA AS A
LEFT JOIN ECOLI_DATA AS B
ON A.ID = B.PARENT_ID
GROUP BY A.ID
ORDER BY A.ID ASC;

• join과 group by를 활용하는 문제다

 

 

8. 대장균의 크기에 따라 분류하기 1

SELECT ID, 
    CASE 
        WHEN SIZE_OF_COLONY > 1000 THEN 'HIGH'
        WHEN SIZE_OF_COLONY > 100 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS SIZE
FROM ECOLI_DATA
ORDER BY ID;

• CASE WHEN THEN END를 알아야 풀 수 있는 문제다
• 프로그래밍에서 IF문을 사용하는 거랑 다름없다

 

 

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

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

• BIT AND 연산을 안다면 어렵지 않은 문제다

 

 

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

SELECT CHILD.ID as ID, CHILD.GENOTYPE as GENOTYPE, PARENT.GENOTYPE as PARENT_GENOTYPE
FROM ECOLI_DATA CHILD
INNER JOIN  ECOLI_DATA PARENT
ON CHILD.PARENT_ID = PARENT.ID
WHERE CHILD.GENOTYPE & PARENT.GENOTYPE = PARENT.GENOTYPE
ORDER BY CHILD.ID;

• bit and 연산의 특징만 알면 어렵지 않다

 

 

11. 대장균의 크기에 따라 분류하기 2

-- 코드를 작성해주세요
SELECT ID, CASE
        WHEN PERCENT > 0.75 THEN 'LOW'
        WHEN PERCENT > 0.50 THEN 'MEDIUM'
        WHEN PERCENT > 0.25 THEN 'HIGH'
        ELSE 'CRITICAL'
    END as COLONY_NAME
FROM (
    SELECT ID, PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS PERCENT
    FROM ECOLI_DATA
) as P
ORDER BY ID;

• PERCENT_RANK 라는 함수로 해당 ID가 테이블에서 몇 순위인지 퍼센트로 확인이 가능하다

 

 

12. 특정 세대의 대장균 찾기

SELECT THREE.ID as ID FROM ECOLI_DATA THREE
INNER JOIN 
(
    SELECT TWO.ID as ID FROM ECOLI_DATA TWO
    INNER JOIN 
    (
        SELECT ID FROM ECOLI_DATA
        WHERE PARENT_ID IS NULL
    ) AS ANCESTOR
    ON TWO.PARENT_ID = ANCESTOR.ID
) as TWO
ON THREE.PARENT_ID = TWO.ID
ORDER BY THREE.ID;

• 3조인으로 해결했다
• 조상을 먼저 찾고, 그 조상의 자식을 찾고, 그 자식의 자식을 찾는 식으로 쿼리를 만들었다