https://leetcode.com/problemset/database/

175. Combine Two Tables

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| personId    | int     |
| lastName    | varchar |
| firstName   | varchar |
+-------------+---------+
personId is the primary key column for this table.
This table contains information about the ID of some persons 
and their first and last names.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| addressId   | int     |
| personId    | int     |
| city        | varchar |
| state       | varchar |
+-------------+---------+
addressId is the primary key column for this table.
Each row of this table contains information about the city 
and state of one person with ID = PersonId.

Write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.

Return the result table in any order.

정답

select A.firstName, A.lastName, B.city, B.state 
  from Person A
  left join Address B
  on A.personId = B.personID

간단한 left join

176. Second Highest Salary

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.

내 답안

select *
/*
case 
    when cnt = 0 
    then 'null' 
    -- else CONVERT(SecondHighestSalary, signed integer) 
    when cnt != 0
    then SUBSTRING(sub_1.SecondHighestSalary,0,-1)
end as SecondHighestSalary
*/

 from (
    select salary as SecondHighestSalary -- , count(*) as cnt
      from Employee
     group
        by SecondHighestSalary
     order
        by SecondHighestSalary desc
     limit 2
    ) sub_1
order by sub_1.SecondHighestSalary asc
limit 1

틀린 이유 : limit 2 한 지점에서, row가 1개밖에 없다면 오류 발생

원래 group by 한 뒤, 내림차순으로 정렬 후, row 개수따라 1개이면 null을 리턴하도록
하려고 했으나 뭔가 아닌거 같고 자꾸 꼬이는거 같아서 문제의 정답을 확인함.

정답

Window 함수를 쓰는 방법

Using window function 
(RANK, DENSE_RANK, ROW_NUMBER, MAX(), MIN())
to append ranks, 
and the select records with values equal to rank n. 
(use dense_rank if more than one employee has the maximum salary

WITH CTE AS
			(SELECT Salary, RANK () OVER (ORDER BY Salary desc) AS RANK_desc
			   FROM Employee)
SELECT MAX(salary) AS SecondHighestSalary
  FROM CTE
 WHERE RANK_desc = 2

기존에 오라클 서버에서 사용하는 row_numbers() over ( ~~ ) 구문을 자주 썼었고, mysql의 경우 이와 같은 윈도우 함수가 없을까 찾아보니 rank가 그런 것 같다.

Limit 1,1 : limit은 n개만 가져오는건줄 알았는데? 아니었다.

ORDER BY value, LIMIT n-1 OFFSET n or use TOP n 
(can also find the 2nd by using TOP 2 + SELECT MIN())

SELECT IFNULL((SELECT DISTINCT Salary
	             FROM Employee 
				 ORDER BY Salary DESC 
				 LIMIT 1,1),NULL) AS SecondHighestSalary

177. Nth Highest Salary (스킵)

178. Rank Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
id is the primary key for this table.
Each row of this table contains the score of a game. Score is a floating point value with two decimal places.

답안

select  score, (dense_rank() over(order by score desc)) as 'rank'
from    Scores
order by 'score' desc

dense rank 라는게 있다는걸 알게 됐다.