🌡️ Rising Temperature
Table: Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id is the primary key for this table.
This table contains information about the temperature on a certain day.
Write an SQL query to find all dates' Id with higher temperatures compared to its previous dates (yesterday).
Return the result table in any order.
The query result format is in the following example.
Example 1:
Input:
Weather table:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
Output:
+----+
| id |
+----+
| 2 |
| 4 |
+----+
Explanation:
In 2015-01-02, the temperature was higher than the previous day (10 -> 25).
In 2015-01-04, the temperature was higher than the previous day (20 -> 30).✅ 해답
여기서는
어제와 비교하여 기온이 높은 id를 반환하는 쿼리를 작성하면 된다.
하지만 문제는 테이블이 하나 밖에 없다는 것이다!!
그렇다면 어제보다 더 높은 기온인지는 어떻게 알 수 있을까?
이 때 SELF JOIN을 하면 된다.
SELF JOIN은 따로 JOIN 구문을 칠 필요 없이
FROM절에 한번에 alias와 같이 적으면 구현된다.
그리고 하루 전을 구현하기위해 날짜를 계산하는 쿼리가 따로 필요한데
밑에서 간략하게 적어보도록 하겠다.
# 문제에서 어제와 비교하여 기온이 더 높은 것의 id를 가져오라고 하였으니
SELECT a.id
# 두개의 동일한 Weather 테이블을 가져와 self join한 뒤
FROM Weather AS a , Weather AS b
# 어제의 기온이 높은 것을 부등호로 표현하고
WHERE a.Temperature > b.Temperature
# 날짜 차이가 1인 즉, 어제인 것으로 조건을 걸어준다.
AND DATEDIFF(a.recordDate , b.recordDate) = 1;🗓️ MySQL에서의 날짜 계산
- 날짜 더하기 / 빼기
- DATE_ADD(기준 날짜, INTERVAL N SECOND / MINUTE / HOUR / DAY / MONTH /YEAR)
- DATE_SUB(기준 날짜, INTERVAL N SECOND / MINUTE / HOUR / DAY / MONTH /YEAR)
- 날짜끼리 빼기
- DATEDIFF(날짜 1, 날짜 2) = N
➡️ 날짜 2에서 날짜 1을 뺀 일의 값을 정수로 반환한다.
🧑🏻💼 Sales Person
Table: SalesPerson
+-----------------+---------+
| Column Name | Type |
+-----------------+---------+
| sales_id | int |
| name | varchar |
| salary | int |
| commission_rate | int |
| hire_date | date |
+-----------------+---------+
sales_id is the primary key column for this table.
Each row of this table indicates the name and the ID of a salesperson alongside their salary, commission rate, and hire date.
Table: Company
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| com_id | int |
| name | varchar |
| city | varchar |
+-------------+---------+
com_id is the primary key column for this table.
Each row of this table indicates the name and the ID of a company and the city in which the company is located.
Table: Orders
+-------------+------+
| Column Name | Type |
+-------------+------+
| order_id | int |
| order_date | date |
| com_id | int |
| sales_id | int |
| amount | int |
+-------------+------+
order_id is the primary key column for this table.
com_id is a foreign key to com_id from the Company table.
sales_id is a foreign key to sales_id from the SalesPerson table.
Each row of this table contains information about one order. This includes the ID of the company, the ID of the salesperson, the date of the order, and the amount paid.
Write an SQL query to report the names of all the salespersons who did not have any orders related to the company with the name "RED".
Return the result table in any order.
The query result format is in the following example.
Example 1:
Input:
SalesPerson table:
+----------+------+--------+-----------------+------------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+------------+
| 1 | John | 100000 | 6 | 4/1/2006 |
| 2 | Amy | 12000 | 5 | 5/1/2010 |
| 3 | Mark | 65000 | 12 | 12/25/2008 |
| 4 | Pam | 25000 | 25 | 1/1/2005 |
| 5 | Alex | 5000 | 10 | 2/3/2007 |
+----------+------+--------+-----------------+------------+
Company table:
+--------+--------+----------+
| com_id | name | city |
+--------+--------+----------+
| 1 | RED | Boston |
| 2 | ORANGE | New York |
| 3 | YELLOW | Boston |
| 4 | GREEN | Austin |
+--------+--------+----------+
Orders table:
+----------+------------+--------+----------+--------+
| order_id | order_date | com_id | sales_id | amount |
+----------+------------+--------+----------+--------+
| 1 | 1/1/2014 | 3 | 4 | 10000 |
| 2 | 2/1/2014 | 4 | 5 | 5000 |
| 3 | 3/1/2014 | 1 | 1 | 50000 |
| 4 | 4/1/2014 | 1 | 4 | 25000 |
+----------+------------+--------+----------+--------+
Output:
+------+
| name |
+------+
| Amy |
| Mark |
| Alex |
+------+
Explanation:
According to orders 3 and 4 in the Orders table, it is easy to tell that only salesperson John and Pam have sales to company RED, so we report all the other names in the table salesperson.✅ 해답
여기서는
RED라는 이름을 가진 회사와 아무 연관이 없는 판매원들의 이름들 반환하는 쿼리를 작성 하면 된다.
하지만 위의 문제와 다르게 이 문제는 테이블이 너무 많다는 것이 문제다.
차례로 정리해보도록 하겠다.
1. RED 값이 있는 테이블은 Company 테이블이다.
2, 여기서 com_id를 가지고 있는 테이블은 Orders 테이블이다.
3. 그리고 이름을 구하기 위해서는 SalesPerson 테이블과 Orders 테이블도 서로 JOIN되어야 한다.
하지만 이 많은 테이블들을 다 JOIN시키기에는 너무 힘든 작업이기 때문에 서브쿼리를 통해 이 문제를 해결해보고자 하였다.
# SalesPerson 테이블에서 name을 가져온다.
SELECT name
FROM SalesPerson
# 그 조건은 sales_id에 무엇이 포함되어있지 않은 것인데
WHERE sales_id NOT IN (
# Orders 테이블의 sales_id를 가져오는데
SELECT a.sales_id
FROM Orders a
# Orders와 Company를 com_id를 기준으로 JOIN하는데
LEFT JOIN Company b
on a.com_id = b.com_id
# Company name이 RED인 것에 제한한다.
WHERE b.name = 'RED'
)
;이렇게 되면 서브쿼리의 값은 Orders 테이블에 com_id가 1인 값 두개를 가져오게 되고 그것의 sales_id는 1과 4가 된다.
그 값이 sales_id에 포함되지 않아야 하니 SalesPerson의 테이블에서
sales_id가 2, 3, 5가 최종적으로 해당되어
Amy, Mark, Alex가 반환된다.
추가로 위에서 언급했던 JOIN들을 계속 시도하는 답안도 Discussion에 나와있어
한번 소개해보겠다.
★ Discussion 해답
SELECT salesperson.name
FROM orders o JOIN company c
ON (o.com_id = c.com_id and c.name = 'RED')
RIGHT JOIN salesperson ON salesperson.sales_id = o.sales_id
WHERE o.sales_id IS NULL
;보기만 해도 복잡하다...
다음에는 Day 7 문제들에 대하여 알아보도록 하자.
