HackerRank中SQL练习_简单

Catalogue

1. 1. 简单
   1. 1.1. Weather Observation Station 3
      1. 1.1.1. 题目
      2. 1.1.2. 解题
   2. 1.2. Weather Observation Station 6
      1. 1.2.1. 题目
      2. 1.2.2. 解题
   3. 1.3. Weather Observation Station 7
      1. 1.3.1. 题目
      2. 1.3.2. 解题
      3. 1.3.3. 类似题目
   4. 1.4. Weather Observation Station 9
      1. 1.4.1. 题目
      2. 1.4.2. 解题
   5. 1.5. Higher Than 75 Marks
      1. 1.5.1. 题目
      2. 1.5.2. 解题
   6. 1.6. Weather Observation Station 15
      1. 1.6.1. 题目
      2. 1.6.2. 解题
   7. 1.7. Weather Observation Station 5
      1. 1.7.1. 题目
      2. 1.7.2. 解题
   8. 1.8. Type of Triangle
      1. 1.8.1. 题目
      2. 1.8.2. 解题
   9. 1.9. Average Population
      1. 1.9.1. 题目
      2. 1.9.2. 解题
   10. 1.10. The Blunder
       1. 1.10.1. 题目
       2. 1.10.2. 解题
   11. 1.11. Top Earners
       1. 1.11.1. 题目
       2. 1.11.2. 解题

简单

题目来源于 Hackerrake easy 部分，解题部分的注释为问题拆解过程，收录原因是这些题目是基础的 SELECT 中常用的基本函数的使用场景，故记录下解题思路和解法，以备翻阅，欢迎交流，如有侵权，望告知，即删~

Weather Observation Station 3

题目

Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.
The STATION table is described as follows:

解题

-- Query CITY names 
-- from STATION 
-- for cities that have an even ID number.
-- in any order, but exclude duplicates from the answer.

SELECT DISTINCT CITY
FROM STATION
WHERE MOD(ID,2) = 0;

• 考察点：MOD函数，匹配奇/偶数行
  • 以此类推，奇数行为：MOD(ID，2) = 1

Weather Observation Station 6

题目

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

解题

-- from STATION.
-- starting with vowels (i.e., a, e, i, o, or u) from STATION. 
-- Query the list of CITY names 
-- Your result cannot contain duplicates.

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[aeiou].*';

• 考察点：正则匹配，以什么开头
  • MYSQL正则表达式

Weather Observation Station 7

题目

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

解题

-- from STATION. 
-- Query the list of CITY names ending with vowels (a, e, i, o, u)
-- Your result cannot contain duplicates.

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '[aeiou]$';

• 考察点：正则匹配，以什么结尾
  • 正则匹配时，不需要像 LIKE 函数规定一个位置，只需要条件正确就能匹配成功

类似题目

# 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.

-- from STATION 
-- which have vowels (i.e., a, e, i, o, and u) as both their first and last characters
-- Query the list of CITY names
-- cannot contain duplicates.

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[aeiou].*[aeiou]$';

• 考察点：正则匹配
  • 在首尾都需要进行匹配时，需要空出位置

Weather Observation Station 9

题目

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

解题

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

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[^aeiou]';

# 另一种写法
SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT REGEXP '^[aeiou]';

• 考察点：正则匹配
  • NOT 函数等同于反向选择

Higher Than 75 Marks

题目

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.

解题

-- in STUDENTS 
-- Query the Name of any student
-- who scored higher than  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 
-- secondary sort them by ascending ID.

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

• 考察点：字符串截取，LEFT / RIGHT 函数
  • LEFT(str,length)
  • RIGHT(str,length)

Weather Observation Station 15

题目

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345 . Round your answer to 4 decimal places.

解题

-- Query the Western Longitude (LONG_W) 
-- for the largest Northern Latitude (LAT_N)
-- that is less than 137.2345
-- Round your answer to 4 decimal places.

SELECT ROUND(LONG_W,4)
FROM STATION
WHERE LAT_N < 137.2345
ORDER BY LAT_N DESC
LIMIT 1;

• 考察点：子查询 / 排序
  • 这个查询使用排序获得最大的LAT_N，进而获得相对应的LONG_W，问题在于如果存在相同的最大LAT_N，那么LONG_W根据题意要求使用LIMIT 1就是错误答案，所以使用子查询是最正确的姿势

Weather Observation Station 5

题目

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

解题

-- in STATION
-- Query the two cities with the shortest and longest CITY names,
-- as well as their respective lengths 
-- If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

-- SELECT CITY,LENGTH(CITY)
-- FROM STATION
-- GROUP BY 2
-- HAVING LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION) 
-- OR LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION)
-- ORDER BY 1
-- LIMIT 1;

-- SELECT MIN(LENGTH(CITY)) FROM STATION;

# 解法一
(SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY 2 DESC,1 ASC LIMIT 1)
UNION
(SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY 2 ASC, 1 ASC LIMIT 1);

# 解法二
select t2.city , t2.t  
from  
(  
    select t1.city , t1.t , row_number() over (partition by t1.t order by t1.city) as ro 
    from
        ( select city , length(city)as t 
          from station 
        ) t1
    group by t1.city,t1.t
    having 
        t1.t = (select min(length(city)) from station )
                           or 
        t1.t = (select max(length(city)) from station)
) t2
where t2.ro = 1;    

• 考察点：UNION / 子查询
  • 解法二是在网上找的，其实解题思路是一致的，求出CITY的最大最小长度，然后通过分组并匹配长度，再通过排序得出最终结果
  • 能理解，但…写不出，其实写出来了一部分，卡在排序，即解法二的ROW_NUMBER，需要多加练习

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.

解题

-- Write a query identifying the type of each record in the TRIANGLES table using its three side lengths.
-- 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 AND A + C > B AND B + C > A THEN
                CASE 
                    WHEN A = B AND B = C THEN 'Equilateral'
                    WHEN A = B OR B = C OR C = A THEN 'Isosceles'
                    ELSE 'Scalene' 
                END
            ELSE 'Not A Triangle' 
        END
FROM TRIANGLES;

• 考察点：三角形判定条件 + CASE WHEN + CASE WHEN 嵌套
  • 嵌套的性能需要验证，只是该题目可以这样处理

Average Population

题目

Query the average population for all cities in CITY, rounded down to the nearest integer.

解题

-- in CITY
-- Query the average population for all cities 
-- rounded down to the nearest integer.

-- SELECT ROUND(AVG(POPULATION))
-- FROM CITY;

SELECT FLOOR(AVG(POPULATION))
FROM CITY;

• 考察点：AVG 函数 + 小数取整
  • ROUND(X,D)，默认D=0
  • FLOOR(number)：向下取整，参数必须为数字类型

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 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.

解题

-- Write a query calculating the amount of error (i.e.:  average monthly salaries)
-- fix monthly salaries --> before average monthly salaries - fix average monthly salaries
-- round it up to the next integer.

SELECT CEIL(AVG(Salary) - AVG(REPLACE(Salary,'0',''))) AS diff
FROM EMPLOYEES;

• 考察点：REPLACE 函数 + 向上取整
  • CEIL(X)：返回最小整数值，但不能小于X
  • REPLACE(str,old_string,new_string)

Top Earners

题目

We define an employee’s total earnings to be their monthly salary X months worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as 2 space-separated integers.

解题

-- define an employee's total earnings
-- Write a query to find the maximum total earnings for all employees 
-- the total number of employees who have maximum total earnings. 
-- these values as  space-separated integers.

SELECT salary*months AS earnings , COUNT(*)
FROM Employee
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1;

• 考察点：排序获得最大值 + COUNT 函数 + LIMIT 函数