개발 학습 블로그

<MySQL & Oracle>

SELECT A.FOOD_TYPE
     , A.REST_ID
     , A.REST_NAME
     , A.FAVORITES
  FROM REST_INFO A
 INNER JOIN (
              SELECT FOOD_TYPE
                   , MAX(FAVORITES) AS FAVORITES
                FROM REST_INFO
            GROUP BY FOOD_TYPE
            ) B
    ON A.FOOD_TYPE = B.FOOD_TYPE
   AND A.FAVORITES = B.FAVORITES
 ORDER BY A.FOOD_TYPE DESC

프로그래머스 즐겨찾기가 가장 많은 식당 정보 출력하기 SQL

SELECT C.ID, B.AGE, A.MIN_COINS_NEEDED, A.POWER
  FROM (SELECT CODE, MIN(COINS_NEEDED) AS MIN_COINS_NEEDED, POWER FROM WANDS GROUP BY CODE, POWER) A
     , WANDS_PROPERTY B
     , WANDS C
 WHERE A.CODE = B.CODE
   AND A.CODE = C.CODE
   AND A.MIN_COINS_NEEDED = C.COINS_NEEDED
   AND A.POWER = C.POWER
   AND B.IS_EVIL = 0
 ORDER BY A.POWER DESC, B.AGE DESC

해커랭크 Ollivander's Inventory SQL

STEP 1. SUBMISSION_DATE와 ID, NM의 출력은 간단하다.

SELECT MST.SUBMISSION_DATE
     , (SELECT HACKER_ID
          FROM SUBMISSIONS
         WHERE SUBMISSION_DATE = MST.SUBMISSION_DATE
      GROUP BY HACKER_ID
      ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS ID
     , (SELECT NAME
          FROM HACKERS
         WHERE HACKER_ID = ID) AS NM
  FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) MST
 GROUP BY MST.SUBMISSION_DATE

STEP 2. 아래 쿼리를 이해하자

SELECT COUNT(DISTINCT HACKER_ID)  
  FROM SUBMISSIONS S2  
 WHERE S2.SUBMISSION_DATE = '2016-03-03'
   AND (SELECT COUNT(DISTINCT S3.SUBMISSION_DATE) 
          FROM SUBMISSIONS S3 
         WHERE S3.HACKER_ID = S2.HACKER_ID 
           AND S3.SUBMISSION_DATE < '2016-03-03') = DATEDIFF('2016-03-03', '2016-03-01')

STEP 3. 두 개의 쿼리문을 합치면

SELECT MST.SUBMISSION_DATE
     , (SELECT COUNT(DISTINCT HACKER_ID)  
          FROM SUBMISSIONS S2  
         WHERE S2.SUBMISSION_DATE = MST.SUBMISSION_DATE
           AND (SELECT COUNT(DISTINCT S3.SUBMISSION_DATE) 
                  FROM SUBMISSIONS S3 
                 WHERE S3.HACKER_ID = S2.HACKER_ID 
                   AND S3.SUBMISSION_DATE < MST.SUBMISSION_DATE) = DATEDIFF(MST.SUBMISSION_DATE, '2016-03-01')) AS CNT
     , (SELECT HACKER_ID
          FROM SUBMISSIONS
         WHERE SUBMISSION_DATE = MST.SUBMISSION_DATE
      GROUP BY HACKER_ID
      ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS ID
     , (SELECT NAME
          FROM HACKERS
         WHERE HACKER_ID = ID) AS NM
  FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) MST
 GROUP BY MST.SUBMISSION_DATE

해커랭크 15 Days of Learning SQL

<MySQL>

SQL_MODE 확인

SHOW VARIABLES LIKE 'SQL_MODE'

SQL_MODE 초기화

SET SQL_MODE = '';

SQL_MODE = ONLY_FULL_GROUP_BY 확인

(GROUP BY절에 포함되지 않은 컬럼을 집합함수로 감싸야만 사용할 수 있게 해줌)

SHOW VARIABLES LIKE 'SQL_MODE'

SQL_MODE를 확인해보자

SQL_MODE 초기화 & 조회

SET SQL_MODE = '';
SELECT A.START_DATE, B.END_DATE
  FROM (SELECT START_DATE FROM PROJECTS WHERE START_DATE NOT IN (SELECT END_DATE FROM PROJECTS)) A,
       (SELECT END_DATE FROM PROJECTS WHERE END_DATE NOT IN (SELECT START_DATE FROM PROJECTS)) B
 WHERE A.START_DATE < B.END_DATE
 GROUP BY A.START_DATE
 ORDER BY DATEDIFF(B.END_DATE, A.START_DATE), A.START_DATE

해커랭크 SQL Project Planning SQL

SET @변수명 = 초기값 (:= 가능)

SELECT @변수명

또는

SELECT @변수명 := @변수명 + 더해줄 값

등으로 사용 가능

SELECT T.HACKER_ID
     , T.NAME
  FROM (SELECT A.HACKER_ID
             , D.NAME
             , (CASE WHEN A.SCORE = C.SCORE THEN 'PASS' ELSE 'FAIL' END) AS RESULT
          FROM SUBMISSIONS A, CHALLENGES B, DIFFICULTY C, HACKERS D
         WHERE A.CHALLENGE_ID = B.CHALLENGE_ID
           AND A.HACKER_ID = D.HACKER_ID
           AND B.DIFFICULTY_LEVEL = C.DIFFICULTY_LEVEL
      ORDER BY A.HACKER_ID) T
 WHERE T.RESULT = 'PASS'
 GROUP BY T.HACKER_ID, T.NAME
HAVING COUNT(T.RESULT) > 1
 ORDER BY COUNT(T.RESULT) DESC, T.HACKER_ID

RESULT 값의 집계함수를 조건으로 사용할 것이므로

WHERE절이 아닌 HAVING절에 쓴다.

HAVING절은 GROUP BY와 ORDER BY 사이에 위치한다.

해커랭크 Top Competitors SQL

SELECT (CASE WHEN B.GRADE >= 8 THEN A.NAME ELSE NULL END) AS NAME
     , B.GRADE
     , A.MARKS
  FROM STUDENTS A, GRADES B
 WHERE A.MARKS <= B.MAX_MARK
   AND A.MARKS >= B.MIN_MARK
 ORDER BY B.GRADE DESC, NAME, A.MARKS

모든 데이터는 등급을 기준으로 내림차순 정렬되어야 하며,
GRADE가 8 이상인 것들 '이름'을 기준으로 오름차순 정렬,
GRADE가 8 미만인 것들은 '점수'를 기준으로 오름차순 정렬되어야 한다.

(CASE WHEN B.GRADE >= 8 THEN A.NAME ELSE NULL END) AS NAME에서
GRADE가 8 미만인 것들은 NAME이 NULL로 조회되기 때문에
이 경우, 같은 등급 내에서 ORDER BY NAME에 영향을 받지 않게 된다.
결과적으로, GRADE가 8 이상인 것들은 같은 등급 내에서 '이름'을 기준으로 오름차순 정렬이,
GRADE가 8 미만인 것들은 같은 등급 내에서  '점수'를 기준으로 오름차순 정렬이 된다.

해커랭크 The Report SQL