[HackerRank | SQL] Advanced Select
What type of Triangle
https://www.hackerrank.com/challenges/what-type-of-triangle/problem?isFullScreen=true
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 B+C<=A or C+A<=B THEN "Not A Triangle"
WHEN A=B and B=C and C=A THEN "Equilateral"
WHEN A=B or B=C or C=A THEN "Isosceles"
ELSE "Scalene"
END
FROM TRIANGLES;CASE 문.
A+B+C-max(A,B,C) <= max(A,B,C) 면 삼각형 아닌걸로 생각했는데
일단 MYSQL은 max의 피연산자가 두 개여야 하고, row의 최댓값이 아니라 그 컬럼의 최댓값을 찾아준다.
연산의 방향 !
The PADs
https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true&h_r=next-challenge&h_v=zen
Generate the following two result sets:
- Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses).
For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S). - Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:
where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically. - There are a total of [occupation_count] [occupation]s.
Note: There will be at least two entries in the table for each type of occupation.
- () 붙여서 문자열 합성 하되 직업의 첫번째 글자 따와야 하고, 직업 알파벳 순으로 정렬
- 직업 별 인원 수 체크해서 ascending order로 정렬
- 문자열 합성 : CONCAT( , , )
- 부분 글자 가져오기 : LEFT("abcde", 2)
- 정렬 : SELECT * FROM table ORDER BY ___ [ASC] (내림차순)
- 그룹화 : SELECT NAME FROM table GROUP BY OCCUPATION
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);추가!
GROUP BY
조건 처리 후에 컬럼 그룹화
SELECT 컬럼 FROM 테이블 WHERE 조건식 GROUP BY 그룹화할 컬럼;컬럼 그룹화 후에 조건 처리
SELECT 컬럼 FROM 테이블 GROUP BY 그룹화할 컬럼 HAVING 조건식;조건 처리 후에 컬럼 그룹화 후에 조건 처리
SELECT 컬럼 FROM 테이블 WHERE 조건식 GROUP BY 그룹화할 컬럼 HAVING 조건식;
SELECT OCCUPATION, Name FROM OCCUPATIONS GROUP BY OCCUPATION;ERROR 1055 (42000) at line 6: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'OCCUPATIONS.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
→ OCCUPATIONS.Name이 GROUP BY로 묶인 컬럼에 의존적이지 않은 (나올수가 없는) 상태.
Aggregate function
- an aggregate function or aggregation function is a function where the values of multiple rows are grouped together to form a single summary value.
- 여러 행으로부터 하나의 결괏값을 반환하는 함수이다. SELECT 구문에서만 사용되며, 이전에 다룬 기본 함수들이 행(row)끼리 연산을 수행했다면, 집계 함수는 열(column)끼리 연산을 수행한다.
주로 평균, 합, 최대, 최소 등을 구하는 데 사용된다.
sql_mode
- MySQL에 저장될 데이터에 대한 유효성 검사(validation check)범위를 설정하는 시스템 변수
- only_full_group_by
Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
GROUP BY로 그룹화시킨 컬럼이 아닌 다른 컬럼으로 HAVING, ORDER BY 하는 쿼리를 거절
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by