본문 바로가기

SQL56

[SQL] 해커랭크 Top Competitors SELECT T.HACKER_ID , T.NAME FROM (SELECT A.HACKER_ID , D.NAME , (CASE WHEN A.SCORE = C.SCORE THEN 'PASS' ELSE 'FAIL' END) AS RESULT FROM SUBMISSIONS A, CHALLENGES B, DIFFICULTY C, HACKERS D WHERE A.CHALLENGE_ID = B.CHALLENGE_ID AND A.HACKER_ID = D.HACKER_ID AND B.DIFFICULTY_LEVEL = C.DIFFICULTY_LEVEL ORDER BY A.HACKER_ID) T WHERE T.RESULT = 'PASS' GROUP BY T.HACKER_ID, T.NAME HAVING COUNT(T.RESU.. 2022. 12. 3.
[SQL] 해커랭크 The Report SELECT (CASE WHEN B.GRADE >= 8 THEN A.NAME ELSE NULL END) AS NAME , B.GRADE , A.MARKS FROM STUDENTS A, GRADES B WHERE A.MARKS = B.MIN_MARK ORDER BY B.GRADE DESC, NAME, A.MARKS 모든 데이터는 등급을 기준으로 내림차순 정렬되어야 하며, GRADE가 8 이상인 것들 '이름'을 기준으로 오름차순 정렬, GRADE가 8 미만인 것들은 '점수'를 기준으로 오름차순 정렬되어야 한다. (CASE WHEN B.GRADE >= 8 THEN A.NAME ELSE NULL END) AS NAME에서 GRADE가 8 미만인 것들은 NAME이 NULL로 조회되기 때문에 이 경우, 같은 등급 .. 2022. 12. 3.
[SQL] 해커랭크 Occupations SELECT MAX(CASE WHEN A.OCCUPATION = 'Doctor' THEN NAME END) AS 'Doctor' , MAX(CASE WHEN A.OCCUPATION = 'Professor' THEN NAME END) AS 'Professor' , MAX(CASE WHEN A.OCCUPATION = 'Singer' THEN NAME END) AS 'Singer' , MAX(CASE WHEN A.OCCUPATION = 'Actor' THEN NAME END) AS 'Actor' FROM ( SELECT NAME , OCCUPATION , ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) AS RN FROM OCCUPATIONS ) A GR.. 2022. 12. 3.
[SQL] 해커랭크 The PADS SELECT CONCAT(NAME, '(', SUBSTR(OCCUPATION, 1, 1), ')') FROM OCCUPATIONS ORDER BY NAME; SELECT CONCAT('There are a total of ', COUNT(OCCUPATION), ' ', LOWER(OCCUPATION), 's.') FROM OCCUPATIONS GROUP BY OCCUPATION ORDER BY COUNT(OCCUPATION), OCCUPATION 해커랭크 The PADS SQL 2022. 12. 2.
[SQL] 해커랭크 Type of Triangle SELECT CASE WHEN A = B AND B = C THEN 'Equilateral' WHEN A + B 2022. 12. 2.
[SQL] 해커랭크 Weather Observation Station 6~7 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%') a로 시작하는 문자열을 검색 : 'a%' a로 시작하는 3글자의 문자열 검색 : 'a__' SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[aeiou]' 정규 표현식 REGEXP : LIKE보다 복잡한 문자열 조건으로 검색 가능 ^[aeiou] : a 또는 e 또는 i 또는 o 또는 u로 시작하는 문자열 SELECT DISTINCT CITY FROM STATION WHERE (CITY LIKE '%a' OR CITY LIKE '.. 2022. 12. 2.

티스토리툴바