[HackerRank] SQL Basic (easy)(3)

1. Higher Then 75 Marks

https://www.hackerrank.com/challenges/more-than-75-marks/problem?isFullScreen=true

Higher Than 75 Marks | HackerRank

Query the Name of any student in STUDENTS who scored higher than 75 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.

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

2. Employee Names

https://www.hackerrank.com/challenges/name-of-employees/problem?isFullScreen=true

Employee Names | HackerRank

Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee table in alphabetical order.

SELECT
    name
FROM 
	Employee
ORDER BY 
	name;

3. Employee Salaries

https://www.hackerrank.com/challenges/salary-of-employees/problem?isFullScreen=true

Employee Salaries | HackerRank

Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than  $2000per month who have been employees for less than 10 months. Sort your result by ascending employee_id.

SELECT 
    NAME
FROM 
    Employee
WHERE 
    salary > 2000
    AND months < 10
ORDER BY 
    employee_id ASC;

4. Type of Triangle

https://www.hackerrank.com/challenges/what-type-of-triangle/problem?isFullScreen=true

Type of Triangle | HackerRank

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  3 sides of equal length.
• Isosceles: It's a triangle with  2 sides of equal length.
• Scalene: It's a triangle with  3 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 AS triangle_type
FROM TRIANGLES;

POINT!

CASE WHEN이 위에서부터 차례대로 조건을 검사하고 처음 만족하는 조건에서 멈춥니다

그래서 해당 쿼리에서는 하나라도 참이면 나머지 조건이 무시되기 때문에
우선 순위가 중요합니다.

① Not A Triangle(포괄적인 조건) → ② Equilateral(특수한 조건) → ③ Isosceles(그 다음 조건) → ④ Scalene (나머지)

 

5. Revising Aggregations  - The Count Function

https://www.hackerrank.com/challenges/revising-aggregations-the-count-function/problem?isFullScreen=true

Revising Aggregations - The Count Function | HackerRank

Query a count of the number of cities in CITY having a Population larger than 100,000.

SELECT 
    COUNT(*) AS CITY_COUNT
FROM 
    CITY
WHERE POPULATION > 100000;

6. Revising Aggregations - The Sum Function

https://www.hackerrank.com/challenges/revising-aggregations-sum/problem?isFullScreen=true

Revising Aggregations - The Sum Function | HackerRank

SELECT SUM(POPULATION) AS TOTAL_POPULATION
FROM CITY
WHERE DISTRICT = 'California'

7. Revising Aggregations - Averages

https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem?isFullScreen=true

Revising Aggregations - Averages | HackerRank

Query the average population of all cities in CITY where District is California.

SELECT AVG(POPULATION) AS AVG_POPULATION
FROM CITY
WHERE DISTRICT = 'California'