SELECT文実行順序
operations.sql
SELECT -- 5
FROM -- 1
WHERE -- 2
GROUP BY -- 3
HAVING -- 4
ORDER BY -- 6
LIMIT -- 7
演算子と関数
operations.sql
-- 比較演算子
=
>
>=
<=
<>, !=
IN
NOT IN
IS NULL
IS NOT NULL
LIKE -- LIKE ワイルドカード文字
BETWEEN ... AND ...
-- 論理演算子
AND
OR
-- 算術演算子
+
-
*
/
% -- num % 2 = 1 <=> mod(num,2) = 1
functions.sql
-- 算術関数
SUM(expr)
AVG(expr) -- AVG(10,null,20) = AVG(10,20) = 15
MIN(expr)
MAX(expr)
COUNT(expr)
COUNT(DISTINCT expr)
ABS(expr)
ROUND(X[,D])
-- 文字列を処理する関数
IFNULL(expr, string)-- NULL値を他の文言に置き換えられる
CONCAT(expr1, expr2[,...])
-- 日付と時刻の関数
DATEDIFF(expr1, expr2) -- substract two dates
-- DATEDIFF('2022-06-09', date) < 31
-- <=> date BETWEEN '2022-05-10' AND '2022-06-09'
CURRENT_DATE() -- 現在の日付
CURRENT_DATE() + interval 3 day -- 3日後の日付
CURRENT_TIMESTAMP() -- 現在の時刻
CURRENT_TIMESTAMP() - interval 6 hour -- ６時間前の時刻
EXTRACT(unit FROM date) -- EXTRACT(YEAR_MONTH FROM '2022-06-12')
DATE_FORMAT(date,format) -- DATE_FORMAT('2022-06-12', '%Y%m')
結合
- JOIN
- 共通部分
- LEFT JOIN
- 左側をマスタする
- 右側にないものはnullとして表示する
- RIGHT JOIN
- 右側をマスタする
- UNION
- 合併
- 重複行は削除される
LeetCode175. Combine Two Tables (LEFT JOIN) ↓
combine_two_tables.sql
/*
Table: Person
+-------------+---------+
| 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.
Table: Address
+-------------+---------+
| 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.
Q: 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 FirstName, LastName, City, State
FROM Person p LEFT JOIN Address a
ON p.PersonId = a.PersonId;
LeetCode607. Sales Person(three tables, JOIN, NOT IN) ↓
sales_person.sql
/*
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.
Q: 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.
*/
SELECT s.name
FROM SalesPerson s
WHERE s.sales_id NOT IN (SELECT o.sales_id
FROM Orders o
JOIN Company c
ON o.com_id = c.com_id AND c.name = "RED");
LeetCode1407. Top Travellers(LEFT JOIN, GROUP BY, SUM, ORDER BY, IFNULL) ↓
top_travellers.sql
/*
Table: Users
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id is the primary key for this table.
name is the name of the user.
Table: Rides
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| user_id | int |
| distance | int |
+---------------+---------+
id is the primary key for this table.
user_id is the id of the user who traveled the distance "distance".
Q: Write an SQL query to report the distance traveled by each user.
Return the result table ordered by travelled_distance in descending order,
if two or more users traveled the same distance,
order them by their name in ascending order.
*/
SELECT u.name, IFNULL(SUM(distance),0) travelled_distance
FROM Users u LEFT JOIN Rides r
ON u.id = r.user_id
GROUP BY r.user_id
ORDER BY travelled_distance DESC, name;
- 自己結合(self join)
- 同じテーブルから情報を二回取る場合
LeetCode181. Employees Earning More Than Their Managers(JOIN) ↓
employees_earning_more_than_their_managers.sql
/*
Table: Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.
Q: Write an SQL query to find the employees who earn more than their managers.
Return the result table in any order.
*/
SELECT e1.name Employee
FROM Employee e1, Employee e2
WHERE e1.managerId = e2.id AND e1.salary > e2.salary;
-- or
SELECT a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
ON a.ManagerId = b.Id AND a.Salary > b.Salary;
グループ化
LeetCode511. Game Play Analysis I(GROUP BY, MIN) ↓
game_play_analysis_1.sql
/* Table: Activity
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| session_id | int |
| activity_date | date |
| activity_type | enum |
+---------------+---------+
There is no primary key for this table, it may have duplicate rows.
The activity_type column is an ENUM of type ('open_session', 'end_session', 'scroll_down', 'send_message').
The table shows the user activities for a social media website.
Note that each session belongs to exactly one user.
Q: Write an SQL query to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively.
A user was active on someday if they made at least one activity on that day.
Return the result table in any order.
*/
SELECT player_id, MIN(event_date) first_login
FROM Activity
GROUP BY player_id
LeetCode182. Duplicate Emails (GROUP BY, HAVING) ↓
group_by_example.sql
/*
Table: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.
Q: Write an SQL query to report all the duplicate emails.
Return the result table in any order.
*/
SELECT Email, COUNT(Email) num
FROM Person
GROUP BY Email; -- count the times each email exists
SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1; -- report all the duplicate emails
LeetCode1050. Actors and Directors Who Cooperated At Least Three Times(GROUP BY, HAVING, COUNT) ↓
actors_and_directors_who_cooperated_at_least_three_times.sql
/*
Table: ActorDirector
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| actor_id | int |
| director_id | int |
| timestamp | int |
+-------------+---------+
timestamp is the primary key column for this table.
Q: Write a SQL query for a report that provides the pairs (actor_id, director_id)
where the actor has cooperated with the director at least three times.
Return the result table in any order.
*/
SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(timestamp) >= 3; -- COUNT(*), COUNT(1) also works here
LeetCode1084. Sales Analysis III(GROUP BY, HAVING, MIN, MAX) ↓
sales_analysis_3.sql
/* Table: Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
product_id is the primary key of this table.
Each row of this table indicates the name and the price of each product.
Table: Sales
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
+-------------+---------+
This table has no primary key, it can have repeated rows.
product_id is a foreign key to the Product table.
Each row of this table contains some information about one sale.
Q: Write an SQL query that reports the products that were only sold in the first quarter of 2019.
That is, between 2019-01-01 and 2019-03-31 inclusive.
Return the result table in any order.
*/
SELECT product_id, product_name
FROM Product
WHERE product_id IN (SELECT product_id
FROM Sales
GROUP BY product_id
HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31');
並び替え
LeetCode1148. Article Views I(ORDER BY, DISTINCT) ↓
article_views_1.sql
/*
Table: Views
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
+---------------+---------+
There is no primary key for this table, it may have duplicate rows.
Each row of this table indicates that some viewer viewed an article (written by some author) on some date.
Note that equal author_id and viewer_id indicate the same person.
Q: Write an SQL query to find all the authors that viewed at least one of their own articles.
Return the result table sorted by id in ascending order.
*/
SELECT DISTINCT author_id id
FROM Views
WHERE author_id = viewer_id
ORDER BY id
LeetCode176. Second Highest Salary(DISTINCT, ORDER BY, DESC, LIMIT, OFFSET)
second_highest_salary.sql
/*
Table: Employee
+-------------+------+
| 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.
Q: 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 DISTINCT Salary AS SecondHighestSalary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1; -- report " " when there is no second highest result
SELECT
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) AS SecondHighestSalary; -- report "null" when there is no second highest result
CASE...WHEN
case_when.sql
CASE value
WHEN [compare_value] THEN result
[WHEN [compare_value] THEN result ...]
[ELSE result]
END;
-- or
CASE WHEN [condition] THEN result
[WHEN [condition] THEN result ...]
[ELSE result]
END;
LeetCode627. Swap Salary(UPDATE, CASE...WHEN) ↓
swap_salary.sql
/* Table: Salary
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| name | varchar |
| sex | ENUM |
| salary | int |
+-------------+----------+
id is the primary key for this table.
The sex column is ENUM value of type ('m', 'f').
The table contains information about an employee.
Q: Write an SQL query to swap all 'f' and 'm' values
(i.e., change all 'f' values to 'm' and vice versa)
with a single update statement and no intermediate temporary tables.
*/
UPDATE Salary
SET sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;
LeetCode1179. Reformat Department Table(CASE...WHEN, SUM, GROUP BY) ↓
reformat_department_table.sql
/*
Table: Department
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+-------------+---------+
(id, month) is the primary key of this table.
The table has information about the revenue of each department per month.
The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].
Q: Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.
Return the result table in any order.
The query result format is in the following example.
Input:
Department table:
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+
Output:
+------+-------------+-------------+-------------+-----+-------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1 | 8000 | 7000 | 6000 | ... | null |
| 2 | 9000 | null | null | ... | null |
| 3 | null | 10000 | null | ... | null |
+------+-------------+-------------+-------------+-----+-------------+
Explanation: The revenue from Apr to Dec is null.
Note that the result table has 13 columns (1 for the department id + 12 for the months).
*/
SELECT id,
SUM(CASE month WHEN 'Jan' THEN revenue END) Jan_Revenue,
SUM(CASE month WHEN 'Feb' THEN revenue END) Feb_Revenue,
SUM(CASE month WHEN 'Mar' THEN revenue END) Mar_Revenue,
SUM(CASE month WHEN 'Apr' THEN revenue END) Apr_Revenue,
SUM(CASE month WHEN 'May' THEN revenue END) May_Revenue,
SUM(CASE month WHEN 'Jun' THEN revenue END) Jun_Revenue,
SUM(CASE month WHEN 'Jul' THEN revenue END) Jul_Revenue,
SUM(CASE month WHEN 'Aug' THEN revenue END) Aug_Revenue,
SUM(CASE month WHEN 'Sep' THEN revenue END) Sep_Revenue,
SUM(CASE month WHEN 'Oct' THEN revenue END) Oct_Revenue,
SUM(CASE month WHEN 'Nov' THEN revenue END) Nov_Revenue,
SUM(CASE month WHEN 'Dec' THEN revenue END) Dec_Revenue
FROM Department
GROUP BY id
ORDER BY id;