SQL_해커랭크

1. 홀수/짝수 

짝수 : id % 2 = 0

홀수 : id % 2 = 1 

1. 영화의 id가 홀수인 경우만 출력 
2. description이 boring이 아닌 것만 출력 
3. 출력 순서는 rating이 높은 순서대로 출력

SELECT *
FROM cinema
WHERE description <> 'boring' and id % 2 = 1
ORDER BY rating DESC

SQL 연산자

/ 나누기
% 나머지 연산자 (나머지가 1이면 홀수, 0이면 짝수)
= 같음
<>, =! 같지 않음
!< 느낌표를 먼저 쓴다. 왼쪽 값이 오른쪽 값보다 작지 않다. 
!> 오른쪽 값이 왼쪽 값보다 작지 않다.
(==와 //는 파이썬에서 사용된다.)

---

2. MOD함수 : mod(x,y) == x%y

짝수 : WHERE MOD(ID,2) = 0 
홀수 : WHERE MOD(ID,2) = 1

 

 

ex. Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.
The STATION table is described as follows:

---

3. a,e,i,o,u로 시작하는 city를 뽑기

 

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%'

 

조금 더 효율적인 함수 사용 (like문이 아닌 regexp 사용)

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

 

REGEXP:

• SQL에서 정규 표현식을 사용하기 위한 연산자입니다.
• 'Regular Expression'의 줄임말로, 문자열 패턴 매칭을 위해 사용됩니다.

[aeiou]:

• 대괄호 []는 문자 집합을 정의합니다.
• 이 집합 안의 어떤 문자라도 일치하면 됩니다.
• 여기서는 소문자 모음 'a', 'e', 'i', 'o', 'u' 중 하나를 의미합니다.

따라서 REGEXP '^[aeiou]'는 "a, e, i, o, u 중 하나로 시작하는 문자열"과 일치합니다.

[MySQL] Weather Observation Station 6 (tistory.com)

 

---

4. a,e,i,o,u로 끝나는 city를 뽑기 → ^, $ 사용

 

SELECT DISTINCT CITY
FROM STATION 
WHERE CITY REGEXP '[aeiou]$'

 

---

5.Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

 

SELECT DISTINCT CITY
FROM STATION 
WHERE CITY REGEXP '^[aeiou]' AND CITY REGEXP '[aeiou]$'

 

---

6. aeiou로 시작하지 않는 CITY → NOT, [^] 사용

SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY NOT REGEXP '^[aeiou]'

 

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

 

1. 첫 번째 ^ (패턴의 시작):
   • 문자열의 시작을 나타냅니다.
   • 패턴 밖에 있는 이 ^는 "문자열이 이렇게 시작한다"는 의미입니다.
2. [^aeiou] (문자 집합의 부정):
   • 대괄호 [] 안에 있는 ^는 전혀 다른 의미를 가집니다.
   • 이는 "해당 문자 집합에 포함되지 않는 어떤 문자"를 의미합니다.
   • 즉, [^aeiou]는 "a, e, i, o, u가 아닌 어떤 문자"를 뜻합니다.

따라서 ^[^aeiou]의 전체 의미는: "a, e, i, o, u가 아닌 어떤 문자로 시작하는 문자열"입니다.

---

7. aeiou로 끝나지 않는 것 추출

SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY REGEXP '[^aeiou]$'

 

---

8. 삼각형 형태 알아내기

 

SELECT 
   CASE
       -- 삼각형이 되려면 두변의 합이 나머지 한변보다 커야함
       WHEN (a + b <= c) OR (a + c <= b) OR (b + c <= a) 
           THEN 'Not a Triangle'
       
       -- 세 변의 길이가 모두 같으면 정삼각형
       WHEN a = b AND b = c AND c = a 
           THEN 'Equilateral'
       
       -- 두 변의 길이만 같으면 이등변삼각형 
       WHEN (a = b AND b != c) OR (a = c AND c != b) OR (b = c AND c != a) 
           THEN 'Isosceles'
       
       -- 세 변의 길이가 모두 다르면 부등변삼각형
       ELSE 'Scalene'
   END AS shape
FROM triangles

 

select case when (a+b <= c) or (a+c <= b) or (b+c <= a) then 'Not A Triangle'
            when a = b and b = c and c = a then 'Equilateral'
            when (a=b and b!=c) or (a=c and c!=b) or (b=c and c!=a) then 'Isosceles'
            when a != b and b != c then 'Scalene'
            else 'etc' end as shape
from triangles

---

9. OCCUPATIONS / 직업별 컬럼 분류

 

• if를 사용한 쿼리 : if(조건문,참,거짓)

select 
    max(if(occupation='Doctor',name,NULL)) AS Doctor,
    max(if(occupation='Professor',name,NULL)) AS Professor,
    max(if(occupation='Singer',name,NULL)) AS Singer,
    max(if(occupation='Actor',name,NULL)) AS Actor
from(
    select *,
        row_number() over (partition by occupation order by name) as r
    from occupations
) sub
group by r;

 

 

1) with a as (~~) : 임시 테이블 형성

ROW_NUMBER() OVER (
    PARTITION BY occupation 
    ORDER BY name
) as rk

2) row_number() : 각 행에 순차적인 번호 부여

3) partition by  occupation : occupation 별로 그룹 나누기

4) order by name : 해당 그룹에서 name순으로 정렬 

 

예시

SELECT 이름, 직업,
    ROW_NUMBER() OVER (
        PARTITION BY 직업 
        ORDER BY 이름
    ) as 번호
FROM 테이블

결과값

이름    직업    번호
Jenny   의사    1    -- 의사 그룹의 첫번째
Sam     의사    2    -- 의사 그룹의 두번째
John    배우    1    -- 배우 그룹의 첫번째
Maria   배우    2    -- 배우 그룹의 두번째
Lisa    교수    1    -- 교수 그룹의 첫번째

---

10. OCCUPATIONS 

 

1. OCCUPATIONS에 있는 모든 이름의 알파벳순 목록을 쿼리하고 바로 뒤에 각 직업의 첫 글자를 괄호(즉, 괄호로 묶음)로 표시합니다. 예: , , , . AnActorName(A)ADoctorName(D)AProfessorName(P)ASingerName(S)
2. OCCUPATIONS에서 각 직업의 발생 횟수를 쿼리합니다. 발생을 오름차순으로 정렬하고 다음 형식으로 출력합니다.
   여기서 는 OCCUPATIONS의 직업 발생 횟수이고 는 소문자 직업 이름입니다. 하나 이상의 직업이 동일한 경우 알파벳순으로 정렬해야 합니다.[occupation_count][occupation][occupation_count]
3. There are a total of [occupation_count] [occupation]s.

SELECT CONCAT(name,'(',LEFT(Occupation,1),')')
from OCCUPATIONS
order by name;

select concat('There are a total of ', count(name),' ',lower(occupation),'s.')
from OCCUPATIONS
group by Occupation
order by count(name), occupation;

---

 

11. 전체 Salary 평균(actual)과 Salary에서 0을 모두 없앤 값(miscalculated)의 평균의 차이를 정수(올림)를 반환하는 쿼리를 작성하라.

 

(replace(salary,0,'') : salary 값에서 모든 0을 빈 문자열('')로 대체

select ceil(avg(salary)-avg(replace(salary,0,'')))
from EMPLOYEES

---

 

 

 

 

 

 

 

 

 

 

 

* 참고

[MySQL] Weather Observation Station 4 (tistory.com)

HackerRank(해커랭크) MySQL Occupations 문제 답