[MySQL]SQL 코딩 테스트 대비 문법 정리

1. HackerRank - Basic select

-- #6
SELECT DISTINCT CITY FROM STATION WHERE ID%2 = 0 

-- #7
SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION 

-- #8
SELECT MIN(LENGTH(CITY)) AND MAX(LENGTH(CITY)) FROM STATION ORDER BY DESC 

-- #11
SELECT DISTINCT(CITY) FROM STATION WHERE RIGHT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u') 

-- #12
SELECT DISTINCT(CITY) FROM STATION WHERE (RIGHT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u')) 
AND (LEFT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u')) 

-- #13
SELECT DISTINCT CITY FROM STATION WHERE LEFT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u') 

-- #14
SELECT DISTINCT CITY FROM STATION WHERE RIGHT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u') 

-- #15
SELECT DISTINCT(CITY) FROM STATION WHERE (RIGHT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u')) 
OR (LEFT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u'))

- 유일한 값: DISTINCT

- 개수: COUNT(column_name)

- 최대 값: MAX(column_name)

- 최소 값: MIN(column_name)

- 길이: LENGTH(column_name)

- 데이터 왼쪽, 오른쪽에서 n 번째까지의 글자(String type, Python list slicing과 비슷): LEFT(column_name, n), RIGHT(column_name, n)

- 데이터 포함, 미포함: IN, NOT IN

 

 

2. HackerRank - Advanced Select

-- the pads

SELECT CONCAT(NAME, '(', LEFT(OCCUPATION, 1), ')') 
FROM OCCUPATIONS 
ORDER BY NAME ASC;

SELECT CONCAT('There are a total of ', COUNT(OCCUPATION), ' ', LOWER(OCCUPATION), 's.')
FROM OCCUPATIONS 
GROUP BY OCCUPATION 
ORDER BY COUNT(OCCUPATION), OCCUPATION;

- 쿼리 여러개 사용할 때는 쿼리 문 끝에 ; 붙이기

- String 붙이기: CONCAT(str1, str2, ...)

- String 붙이기(seperator): CONCAT_WS(sep, str1, str2, ...)

- 알파벳 소문자로 바꾸기: LOWER(str)

 

 

3. HackerRank - Aggregation

-- The Blunder
SELECT CEIL(AVG(SALARY)-AVG(REPLACE(SALARY, '0', ''))) FROM EMPLOYEES

-- Top Earners
SELECT SALARY*MONTHS AS EARNINGS, COUNT(*) FROM EMPLOYEES 
GROUP BY EARNINGS ORDER BY EARNINGS DESC LIMIT 1 

- CEIL(), ROUND(), TRAUNCATE()

- AVG()

- REPLACE(column_name, 바꿀 값, 바뀔 값)

 

4. HackerRank - BasicJoin

SELECT W.ID, P.AGE, W.COINS_NEEDED, W.POWER 
FROM WANDS W
JOIN WANDS_PROPERTY P
ON W.CODE = P.CODE
WHERE P.IS_EVIL = 0
AND W.COINS_NEEDED = (
    SELECT MIN(W1.COINS_NEEDED)
    FROM WANDS W1
    JOIN WANDS_PROPERTY P1
    ON W1.CODE = P1.CODE
    WHERE W1.POWER = W.POWER
    AND P1.AGE = P.AGE
    )
ORDER BY W.POWER DESC, P.AGE DESC

- 비교: 값 = (SELECT ~)

- 변수: @variable_name := (SELECT ~)

- 변수: SET @variable_name = 값

-- The Report
SELECT IF (GRADE < 8, NULL, NAME), GRADE, MARKS
FROM STUDENTS
JOIN GRADES
WHERE MARKS BETWEEN MIN_MARK AND MAX_MARK
ORDER BY GRADE DESC, NAME

- IF (조건, 만족할 때, 만족하지 않을 떄)

SELECT H.HACKER_ID, H.NAME
FROM SUBMISSIONS S
JOIN CHALLENGES C
ON S.CHALLENGE_ID = C.CHALLENGE_ID
JOIN DIFFICULTY D
ON C.DIFFICULTY_LEVEL = D.DIFFICULTY_LEVEL
JOIN HACKERS H
ON S.HACKER_ID = H.HACKER_ID
WHERE S.SCORE = D.SCORE AND C.DIFFICULTY_LEVEL = D.DIFFICULTY_LEVEL
GROUP BY H.HACKER_ID, H.NAME
HAVING COUNT(S.HACKER_ID)>1
ORDER BY COUNT(S.HACKER_ID) DESC, S.HACKER_ID ASC 

- JOIN 여러 개 가능

- HAVING

 

 

5. HackerRank - AlternativeQueries

SET @number = 0;
SELECT REPEAT('* ', @number := @number + 1)
FROM INFORMATION_SCHEMA.TABLES
WHERE @number < 20

- REPEAT 가능(Python의 for문 처럼)

 

6. Programmers - Join

-- link: https://programmers.co.kr/learn/courses/30/lessons/59042
-- 천재지변으로 인해 일부 데이터가 유실되었습니다. 
-- 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

-- version 1: Subquery
SELECT animal_id, name 
FROM animal_outs 
WHERE animal_id 
NOT IN (SELECT animal_id FROM animal_ins) 
ORDER BY animal_id


-- version 2: Left Join(using)
SELECT o.animal_id, o.name 
FROM animal_outs o 
LEFT JOIN animal_ins i ON i.animal_id = o.animal_id 
WHERE i.animal_id is null 
ORDER BY o.animal_id


-- version 3: Left Join(on)
SELECT o.animal_id, o.name 
FROM animal_outs o 
LEFT JOIN animal_ins i USING(animal_id) 
WHERE i.animal_id is null 
ORDER BY o.animal_id

- IS NULL, IS NOT NULL

 

7. Programmers - String, Date

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
WHERE NAME LIKE '%EL%' AND ANIMAL_TYPE = 'Dog' 
ORDER BY NAME

- LIKE

 

SELECT ANIMAL_ID, NAME, CASE SEX_UPON_INTAKE 
WHEN 'Neutered Male' THEN 'O' 
WHEN 'Spayed Female' THEN 'O' ELSE 'X' 
END AS '중성화' 
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

- CASE <column-name> WHEN <condition> THEN ~ (ELSE ~) END (AS <new-column-name>)

 

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, "%Y-%m-%d") AS '날짜' 
FROM ANIMAL_INS ORDER BY ANIMAL_ID

- DATE_FORMAT(<column_name>, 변형 형태)

 

 

8. Programmers - IS NULL

SELECT animal_type, IFNULL(name, 'No name'), sex_upon_intake 
FROM animal_ins 
ORDER BY animal_id

- IFNULL(<column-name>, 변형 값)

 

 

---

문제 출처: HackerRank Sql Practice, Programmers SQL 고득점 KIT