해커랭크 - SQL

Weather Observation 8

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].*[aeiou]$')

• 정규표현식 사용
  - ^: 개행으로 나뉜 문자열의 시작 지점
  ex) ^The => The로 시작하는 문자열

  • $: 개행으로 나뉜 문자열의 끝 지점 ex) ing$ => ing로 끝나는 문자열
  • .*: 첫 글자와 마지막 글자 사이에는 어떤 문자와도 상관없다는 의미

  Weather Observation 9

  Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

  select distinct city
  from station
  where city regexp '^[^aeiou]'

• []는 지정 문자 여부 검사해준다. ^를 붙이면 NOT의 의미.

  Type of Triangle

  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.

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

• CASE 문은 위에서부터 아래로 조건을 확인하며, 가장 먼저 참(True)이 되는 조건의 결과값을 반환하고 종료.
  => 그렇게 때문에 삼각형 성립 조건을 먼저 설정한 다음, 나머지 조건 설정하는 것이 좋다.
  ex) 2 + 2 <= 5이므로 실제로는 삼각형이 될 수 없습니다.
  하지만 쿼리에서는 a=b 조건이 먼저 걸려버려 'Isosceles'라고 오답 출력 (삼각형 성립 조건 마지막으로 설정했을 경우)

The Blunder

Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's 0 key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.

Write a query calculating the amount of error (i.e.: actual - miscalculated average monthly salaries), and round it up to the next integer.

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

• 0을 지운다는 것을 replace문 활용해서 0을 ''로 대체하는 것을 생각못함.

The report

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.
Write a query to help Eve.

SELECT case when g.grade < 8 then null
            when g.grade >= 8 then s.name
       end as name,
       g.grade,
       s.marks
from students as s
join grades as g on s.marks between g.min_mark and g.max_mark
order by g.grade desc, name asc

=> join할 때, 특정 범위 안에 들어가는 경우도 가능한지 몰랐음.