[ SQL ] HackerRank Basic - MySQL 답안 정리(3)

Higher than 75 marks

SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY SUBSTRING(NAME,-3,3),ID

개념 : Substring
활용 : Substring(문자열, 시작할 자리위치, 가져올개수)

 

Employee Names, Salaries

// Names
SELECT NAME FROM EMPLOYEE ORDER BY NAME;

// Salaries
SELECT NAME FROM EMPLOYEE 
WHERE SALARY > 2000 AND MONTHS < 10
ORDER BY EMPLOYEE_ID;

 

Type of Triangle

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

주의 : 등호때문에 오답처리 될 수 있음
개념 : CASE WHEN ~ THEN ~ ELSE ~ END (AS 지정할이름) 조건에 따라 다른 표시 결과처리

 

The PADS

SELECT CONCAT(NAME,"(",SUBSTRING(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);

개념 : concat(문자열1, 문자열2, 문자열3 ... ) => 문자열을 합쳐줌
개념 : substring(문자열, 시작위치, 가져올개수) => 시작 위치에서부터 개수만큼 문자열을 슬라이싱해줌
개념 : Lower(문자열) => 문자열을 소문자로 바꿔서 출력해줌

 

Revising Aggregations - The Sum Function, Averages

// The Sum Function
SELECT SUM(POPULATION)
FROM CITY
WHERE DISTRICT = 'California';

// Averages
SELECT AVG(POPULATION)
FROM CITY
WHERE DISTRICT = 'California';

 

Average population, Japan Population

// Average Population
SELECT ROUND(AVG(POPULATION))
FROM CITY;

// Japan Population
SELECT ROUND(AVG(POPULATION))
FROM CITY

 

Population Density Difference

SELECT ROUND(AVG(POPULATION))
FROM CITY