SQL 테스트 대비

기본 연산

• [ic]SUM[/ic], [ic]COUNT[/ic], [ic]AVG[/ic] : 합, 개수, 평균
• [ic]ROUND[/ic](숫자, 반올림할 자릿수) : 자릿수를 기준으로 반올림
• [ic]TRUNCATE[/ic](숫자, 버림할 자릿수) : 자릿수를 기준으로 버림
• [ic]CEIL[/ic], [ic]FLOOR[/ic] : 정수화(올림, 버림)
• [ic]POW[/ic], [ic]SQRT[/ic] : 거듭 제곱, 제곱근

The Blunder | HackerRank

SELECT CEIL(AVG(SALARY) - AVG(REPLACE(CAST(SALARY AS CHAR), "0", ""))) FROM EMPLOYEES;

 

조건문

• [ic]IF[/ic](조건문, 참일 경우 값, 거짓일 경우 값)
• [ic]CASE WHEN[/ic] (조건) THEN (만족 경우 값)
• [ic]IF NULL[/ic](NULL 확인할 컬럼, "대체 값")

GROUP BY + HAVING

• [ic]HAVING[/ic] : [ic]GROUP BY[/ic]로 묶은 후 조건 제시

Top Competitors | HackerRank

SELECT H.HACKER_ID, H.NAME FROM HACKERS AS H
    INNER JOIN SUBMISSIONS AS S ON H.HACKER_ID = S.HACKER_ID
    INNER JOIN CHALLENGES AS C ON C.CHALLENGE_ID = S.CHALLENGE_ID
    INNER JOIN DIFFICULTY AS D ON D.DIFFICULTY_LEVEL = C.DIFFICULTY_LEVEL
    WHERE S.SCORE = D.SCORE
    GROUP BY H.HACKER_ID, H.NAME
    -- GROUP BY 조건 설정
    HAVING COUNT(H.HACKER_ID) > 1
    ORDER BY COUNT(H.HACKER_ID) DESC, H.HACKER_ID ASC;

 

서브 쿼리

• 중첩 서브쿼리(Nested Subquery) : [ic]WHERE[/ic] 절에 나오는 서브 쿼리

Weather Observation Station 17 | HackerRank

SELECT ROUND(LONG_W, 4) FROM STATION
    WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);

• 인라인 뷰(Inline View) : [ic]FROM[/ic] 절에 나오는 서브쿼리

Ollivander's Inventory | HackerRank

SELECT W.ID, P.AGE, W.COINS_NEEDED, W.POWER
FROM
-- 최소 가격 조건 만족 위해 GROUP BY 진행
    (SELECT CODE, POWER, MIN(COINS_NEEDED) AS COINS_NEEDED
    FROM WANDS
    GROUP BY CODE, POWER) AS M
INNER JOIN WANDS AS W ON W.CODE = M.CODE and W.COINS_NEEDED = M.COINS_NEEDED
INNER JOIN WANDS_PROPERTY AS P ON P.CODE = M.CODE
WHERE P.IS_EVIL = 0
ORDER BY M.POWER DESC, P.AGE DESC;

 

사용자 선언 변수

• [ic]SET @변수명 = 값;[/ic]으로 선언
• [ic]@ 변수명[/ic] 형태로 사용해야 함
• 값 할당 시엔 [ic]@ 변수명:=@변수명 + 값[/ic]

Weather Observation Station 20 | HackerRank

SET @idx = -1;

SELECT ROUND(LAT_N, 4) FROM (SELECT @idx := @idx + 1 as ROW_NUM, LAT_N FROM STATION ORDER BY LAT_N) AS SUB
    WHERE SUB.ROW_NUM in (floor(@idx / 2), ceil(@idx / 2));

JOIN

• [ic]ON B.ID BETWEEN A.MIN AND A.MAX[/ic] : 결합 조건에 범위 추가 방법

The Report | HackerRank

SELECT IF(GRADES.GRADE < 8, NULL, STUDENTS.NAME), GRADES.GRADE, STUDENTS.MARKS  FROM STUDENTS
    INNER JOIN GRADES ON STUDENTS.MARKS BETWEEN GRADES.MIN_MARK AND GRADES.MAX_MARK
    ORDER BY GRADES.GRADE DESC, STUDENTS.NAME ASC, STUDENTS.MARKS ASC;

---

정규 표현식 활용

• [ic]REGEXP[/ic] 사용
• 정규 표현식 쓰지 않으면 LIKE 반복 필요

Weather Observation Station 6 | HackerRank

• Weather Observation Station 6 ~ 12 까지 모두 정규 표현식 활용해서 해결 가능

SELECT DISTINCT CITY FROM STATION
    WHERE CITY REGEXP "^[aeiou]"

SELECT DISTINCT CITY
FROM STATION
WHERE (CITY LIKE 'a%'
       OR CITY LIKE 'e%'
       OR CITY LIKE 'i%'
       OR CITY LIKE 'o%'
       OR CITY LIKE 'u%')