1.

STUDENTS

Query the Name of any student in STUDENTS who scored higher than Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

• MARKS 75초과인 학생 이름을 뒤에서 3글자 기준 오름차순 출력, 모두 같다면 ID기준으로 출력

SELECT NAME FROM STUDENTS
WHERE MARKS > 75
ORDER BY RIGHT(NAME, 3), ID;

2. CASE ~ WHEN ~ THEN

TRIANGLES

Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

• Equilateral: It's a triangle with sides of equal length.
• Isosceles: It's a triangle with sides of equal length.
• Scalene: It's a triangle with sides of differing lengths.
• Not A Triangle: The given values of A, B, and C don't form a triangle.

두 변의 합이 나머지 하나 보다 클 때 (삼각형의 최소 조건을 만족할 때),
모든 변의 길이가 같다면 'Equilateral', 두 변의 길이가 같다면 'Isosceles', 아니면 'Scalene' 출력,
삼각형이 아니면 'Not A Triangle' 출력

SELECT CASE WHEN A + B > C AND B + C > A AND A + C > B THEN
                CASE WHEN A = B AND B = C THEN 'Equilateral'
                     WHEN A = B OR B = C OR C = A THEN 'Isosceles'
                ELSE 'Scalene'
                END
            ELSE 'Not A Triangle'
       END
FROM TRIANGLES;

3. CONCAT, GROUP BY 문제

1. 이름 뒤에 Occupations 첫글자를 괄호로 묶어서 출력
2. Occupation 집계 후 아래 포맷에 맞게 출력

There are a total of [occupation_count] [occupation]s.

Sample Output

Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.

풀이

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

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

4. 가장 높은 연봉과 사람 수

SELECT SALARY * MONTHS, COUNT(*)
FROM EMPLOYEE
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1