MySQL练习题集
Source
Edit
History

HackerRank中SQL练习非简单部分

Catalogue
  1. 1. NO Easy
    1. 1.1. The PADS
      1. 1.1.1. 题目
      2. 1.1.2. 解题
    2. 1.2. Weather Observation Station 18
      1. 1.2.1. 题目
      2. 1.2.2. 解题
    3. 1.3. Binary Tree Nodes
      1. 1.3.1. 题目
      2. 1.3.2. 解题
    4. 1.4. New Companies
      1. 1.4.1. 题目
      2. 1.4.2. 解题
    5. 1.5. *Weather Observation Station 20
      1. 1.5.1. 题目
      2. 1.5.2. 解题
    6. 1.6. The Report
      1. 1.6.1. 题目
      2. 1.6.2. 解题
    7. 1.7. Top Competitors
      1. 1.7.1. 题目
      2. 1.7.2. 解题
    8. 1.8. Ollivander’s Inventory
      1. 1.8.1. 题目
      2. 1.8.2. 解题
    9. 1.9. Challenges
      1. 1.9.1. 题目
      2. 1.9.2. 解题

NO Easy

The PADS

题目

Generate the following two result sets:

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

  2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

    1
    There are a total of [occupation_count] [occupation]s.

    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.

    the_pads

解题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- in OCCUPATIONS
-- all names 
-- Query an alphabetically ordered list of 
-- 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).

SELECT CONCAT(Name,'(',LEFT(Occupation,1),')')
FROM OCCUPATIONS
ORDER BY Name ASC;

-- Query the number of ocurrences of each occupation in OCCUPATIONS. 
-- output format: There are a total of [occupation_count] [occupation]s.
-- Sort the occurrences in ascending order
-- where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS 
-- [occupation] is the lowercase occupation name. 
-- If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

SELECT CONCAT('There are a total of ',COUNT(Occupation),' ',LOWER(Occupation),'s.')
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation) ASC,Occupation ASC;
  • 考察点：字符串拼接 & 分组 + 聚合 + 字符串拼接
  • 注意点：不要使用 UNION / UNION ALL ，因为排序，不要问我为什么知道

Weather Observation Station 18

题目

Consider P1(a,b) and P2(c,d) to be two points on a 2D plane.

  • a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
  • b happens to equal the minimum value in Western Longitude (LONG_W in STATION).
  • c happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
  • d happens to equal the maximum value in Western Longitude (LONG_W in STATION).

Query the Manhattan Distance between points P1 and P2 and round it to a scale of decimal places.

Station

解题

1
2
3
4
5
6
7
8
9
10
11
-- Query the Manhattan Distance between points P1 and P2
-- round it to a scale of  decimal places.
-- P1(a,b) and P2(c,d)
-- a : the minimum value in Northern Latitude (LAT_N in STATION).
-- b : the minimum value in Western Longitude (LONG_W in STATION).
-- c : the maximum value in Northern Latitude (LAT_N in STATION).
-- d : the maximum value in Western Longitude (LONG_W in STATION).
-- P1 - P2 Manhattan Distance: |a-c|+|b-d|

SELECT ROUND(ABS(MIN(LAT_N)-MAX(LAT_N)) + ABS(MIN(LONG_W)-MAX(LONG_W)),4) AS M_D
FROM STATION;

Binary Tree Nodes

题目

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Tree_Nodes

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

  • Root: If node is root node.
  • Leaf: If node is leaf node.
  • Inner: If node is neither root nor leaf node.

解题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- where N represents the value of a node in Binary Tree, and P is the parent of N.
-- Root: If node is root node.
-- Leaf: If node is leaf node.
-- Inner: If node is neither root nor leaf node.


SELECT CASE 
            WHEN P IS NULL THEN CONCAT(N,' Root')
            WHEN N IN (SELECT DISTINCT P FROM BST) THEN CONCAT(N,' Inner')
            ELSE CONCAT(N,' Leaf')
        END
FROM BST
ORDER BY N;

SELECT N,
        CASE
            WHEN P IS NULL THEN 'Root'
            WHEN N IN (SELECT DISTINCT P FROM BST) THEN 'Inner'
            ELSE 'Leaf'
        END
FROM BST
ORDER BY 1;
  • 考察点：CASE WHEN + 逻辑判断
    • Root 节点：没有父节点
    • Inner 节点：既有父节点也有子节点
    • Leaf 节点：ELSE / 没有子节点的
    • CONCAT 基于题目要求 OUTPUT 一列还是两列

New Companies

题目

Amber’s conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy：Founder -> Lead Manager -> Senior Manager -> Manager -> Employee

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

Note:

  • The tables may contain duplicate records.
  • The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

解题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Founder -> Lead Manager -> Senior Manager -> Manager -> Employee
-- write a query to print the company_code, founder name, 
-- total number of lead managers, 
-- total number of senior managers,
-- total number of managers, 
-- total number of employees. 
-- Order by ascending company_code.
-- The tables may contain duplicate records.
-- The company_code is string, so the sorting should not be numeric. 
-- For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

SELECT c.company_code,c.founder,
    COUNT(DISTINCT l.lead_manager_code),
    COUNT(DISTINCT s.senior_manager_code),
    COUNT(DISTINCT m.manager_code),
    COUNT(DISTINCT e.employee_code)
FROM Company c ,Lead_Manager l ,Senior_Manager s ,Manager m ,Employee e
WHERE c.company_code = l.company_code 
    AND l.lead_manager_code = s.lead_manager_code
    AND s.senior_manager_code = m.senior_manager_code
    AND m.manager_code = e.manager_code
GROUP BY 1,2
ORDER BY 1;
  • 考察点：多表联结 + 逻辑
  • 问题一：为什么要五张表都联结，而不是直接Company联结Employee？
    • 如果存在 Lead_Manager手下没有Senior_Manager，或者Senior_Manager没有Manager，所以还是对数据的准确性的判断，当然如果能确保每一级都存在下一级的数据，且保证数据收录在表中是可以尝试两表
  • 拓展：排序
    • 题中表明排序字段为字符串，所以排序顺序为C_1, C_10, and C_2，如果需要C_1, C_2, and C_10 ，思路为：截取 C_ 右边的字符，并转换为数字类型，再排序

*Weather Observation Station 20

题目

A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to decimal places.

Station

解题

1
2
3
4
5
6
7
8
9
10
11
12
-- from STATION
-- Query the median of the Northern Latitudes (LAT_N) 
-- round your answer to 4 decimal places.
-- A median is defined as a number separating the higher half of a data set from the lower half.

SELECT ROUND(s.LAT_N,4) median
FROM STATION s
WHERE (SELECT COUNT(LAT_N) FROM STATION WHERE LAT_N < s.LAT_N) = (SELECT COUNT(LAT_N) FROM STATION WHERE LAT_N > s.LAT_N);

SELECT ROUND(s.LAT_N,4)  
FROM STATION s 
WHERE (SELECT ROUND(COUNT(s.ID)/2)-1 FROM STATION) = (SELECT COUNT(s1.ID) FROM STATION s1 WHERE s1.LAT_N > s.LAT_N);
  • 考察点：中位数概念 & 子查询 + ROUND + COUNT
    • Oracle 有 MEDIAN 函数
    • 中位数概念：指将数据按大小顺序排列起来，形成一个数列，居于数列中间位置的那个数据
      • 数据行数为奇数：排序后，该列位于(行数+1)/2
      • 数据行数为偶数：排序后，行数/2和行数/2+1两数的平均值
    • 中位数概念：按顺序排列的一组数据中居于中间位置的数，代表一个样本、种群或概率分布中的一个数值，其可将数值集合划分为相等的上下两部分

The Report

题目

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn’t want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade – i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use “NULL” as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

Students:

Report_1

Grades:

Report_2

解题

1
2
3
4
5
6
7
8
9
10
11
-- report containing three columns: Name, Grade and Mark
-- doesn't want the NAMES of those students who received a grade lower than 8
-- the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order
-- descending order by grade DESC
-- same grade order by their name
-- more than one student with the same grade (1-7),order by marks

SELECT IF(Grade < 8 ,NULL,Name) ,Grade,Marks
FROM Students JOIN Grades
WHERE Marks BETWEEN Min_Mark AND Max_Mark
ORDER BY 2 DESC , 1;
  • 考察点：IF 函数 & 表联结 + 排序逻辑
    • 判断 grade < 8 ，可以使用 CASE WHEN

Top Competitors

题目

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

解题

1
2
3
4
5
6
7
8
9
10
11
12
13
-- print the respective hacker_id and name of hackers 
-- who achieved full scores for more than one challenge. 
-- order by the total number of challenges in which the hacker earned a full score. 
-- more than one hacker received full scores in same number of challenges, then sort by ascending hacker_id.

SELECT s.hacker_id , h.name
FROM Hackers AS h JOIN Submissions AS s ON h.hacker_id = s.hacker_id 
JOIN Challenges AS c ON s.challenge_id = c.challenge_id 
JOIN Difficulty AS d ON c.difficulty_level = d.difficulty_level
WHERE s.score = d.score AND c.difficulty_level = d.difficulty_level
GROUP BY s.hacker_id,h.name
HAVING COUNT(s.challenge_id) > 1
ORDER BY COUNT(s.challenge_id) DESC, 1;
  • 考察点：表联结 & 分组聚合
    • 多注意题目限制：who achieved full scores for more than one challenge

Ollivander’s Inventory

题目

Harry Potter and his friends are at Ollivander’s with Ron, finally replacing Charlie’s old broken wand.

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron’s interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.

解题

1
2
3
4
5
6
7
8
9
10
11
12
13
-- choose is by determining the minimum number of gold galleons 
-- needed to buy each non-evil wand of high power and age.
-- print the id, age, coins_needed, and power of the wands
-- sorted in order of descending power. 
-- more than one wand has same power, sort by descending age.

SELECT w.id , p.age , t.coins_needed , t.power 
FROM
(SELECT code , power , MIN(coins_needed) AS coins_needed FROM Wands GROUP BY code , power) AS t
JOIN Wands AS w ON t.code = w.code AND t.power = w.power AND t.coins_needed = w.coins_needed
JOIN Wands_Property AS p ON w.code = p.code
WHERE p.is_evil = 0
ORDER BY 4 DESC , 2 DESC ;

  • 考察点：GROUP BY & 多表联结

    • 重点还是读题

      Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age.

      表达的意思是：以年龄和威力进行分组，找到最少购买金币，且邪恶值为0

Challenges

题目

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

解题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- print the hacker_id, name, and the total number of challenges created by each student. 
-- Sort by the total number of challenges in descending order. 
-- If more than one student created the same number of challenges, then sort by hacker_id. 
-- If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

SELECT DISTINCT c.hacker_id , h.name , COUNT(c.challenge_id) AS challenges_created
FROM Challenges AS c JOIN Hackers AS h ON c.hacker_id = h.hacker_id
GROUP BY 1 , 2
HAVING challenges_created = (SELECT MAX(t.c_challenges) FROM (SELECT hacker_id ,COUNT(challenge_id) AS c_challenges FROM Challenges GROUP BY 1) AS t) OR challenges_created IN (SELECT t1.cnt FROM
(SELECT hacker_id , COUNT(challenge_id) AS cnt
FROM Challenges GROUP BY hacker_id) t1 
GROUP BY t1.cnt
HAVING COUNT(t1.cnt) = 1)
ORDER BY 3 DESC , 1;

  • 考察点：HAVING & 子查询

    • 逻辑分解

      If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

      原义为：如果多个学生创建了相同数量的挑战并且计数小于创建的最大挑战数，则将这些学生排除在结果之外。

      等同于：创建的最大挑战数的所有学生id + 创建挑战数的数量为 1 的学生 id