# SQL Syntax Notes


:::info
:notebook_with_decorative_cover: **Introduction** :abc:

This document provides a comprehensive summary of SQL syntax, including detailed explanations, examples, and practical use cases. It covers various aspects of SQL, such as database table management and database operations. Additionally, it includes LeetCode practice problems to help reinforce and apply SQL concepts effectively.

本文檔提供 SQL 常用語法的連結，簡單整理解釋、範例和實際用例。附上自己刷 LeetCode Database的練習題，幫助了解 SQL 的概念與這些語法的練習~~ :smile_cat: :smile_cat:

:information_desk_person: Editor by P.Y.Huang <br>

Keeping Update till all syntax complete [~2024/11/19] ~~~
:::

## DataBase Management(資料庫管理)

✅ 已建立說明頁面
❎ 尚未建立說明頁面

### Data Definition Language (DDL)

**這用於定義資料庫的結構和模式。它包括創建、修改和刪除資料庫物件（例如表、索引和視圖）的命令**

- [CREATE TABLE](create_table.md)
用來建立新的資料表。
- [ALTER TABLE](alter_table.md)
修改現有資料表的結構。
- [DROP TABLE](drop_table.md)
刪除現有的資料表。
- [CREATE INDEX](create_index.md)
在資料表上建立索引以加快查詢速度。
- [ALTER INDEX](alter_index.md)
修改現有索引的屬性。
- [DROP INDEX](drop_index.md)
刪除現有的索引。

### Data Manipulation Language (DML)

**主要用於用於資料夾操作，例如資料庫插入、更新、刪除和檢索資料。允許使用者操作資料表中儲存的實際資料。**

- [INSERT](insert.md)
插入新記錄到資料表中。
- [UPDATE](update.md)
更新現有資料表中的記錄。
- [DELETE](delete.md)
從資料表中刪除記錄。
- [SELECT](select.md)
從資料表中查詢數據。
- [MERGE](merge.md)
根據條件合併多個資料列。

### Data Control Language (DCL)

主要處理**資料庫的權限**和**存取權限**。這些命令管理用戶角色並控制用戶可以存取的數據

- [GRANT](grant.md)
給予用戶特定的權限。
- [REVOKE](revoke.md)
收回用戶的權限。

### Transaction Control Language (TCL)

允許透過控制事務的執行方式來確保資料完整性。

- [COMMIT](commit.md)
提交當前交易，保存修改。
- [ROLLBACK](rollback.md)
撤銷當前交易，回滾至先前狀態。
- [SAVEPOINT](savepoint.md)
設定交易中的保存點，可以回滾到該點。
- [SET TRANSACTION](set_transaction.md)
設定當前交易的特性。

## Database Operations (資料庫操作)

基本上，資料庫操作遵守其運作原則，即可從大量的數據庫中寫出欲查詢的資料結果

1. **FROM**: Identifies the source table(s) and joins data if needed.
* use `CASE WHEN` in the `SELECT` clause : **to conditionally return values in your result set.**

3. **WHERE**: Filters rows based on conditions.
* use `CASE`or `IF` in the `WHERE` clause: **For conditional row filtering**
4. **GROUP BY:** Groups rows into summary rows.
5. **HAVING**: Filters groups (after grouping).
* use `CASE` or `IF` **For filtering groups after aggregation.**
7. **SELECT**: Selects and processes the columns, including calculated ones.
8. **ORDER BY**: Orders the final result set.
LIMIT/OFFSET: Reduces the number of rows in the result.
* use `CASE` or `IF` **For conditional sorting**

### Data Query Language (DQL)

是查詢資料庫以獲取資料。 `SELECT`語句是此類別的主要指令，允許有條件的複雜查詢。

- [SELECT](select_dql.md)
用來查詢數據的基本語法。
- [WHERE](https://hackmd.io/@PinYuan99957/S11KJX-60)
✅用來過濾查詢結果的條件語句。
- [ORDER BY](order_by.md)
將查詢結果按照指定欄位**排序**。
- [GROUP BY](https://hackmd.io/@PinYuan99957/HJWQ51epA)
✅根據指定欄位將結果**分組**。
- [PARITION BY](https://hackmd.io/@PinYuan99957/BJJ0-TkAC)
✅不同於GROPU BY將結果聚合，PARITION BY 將各row根據window function組成一組，並不會變更Table Row's的總數
- [HAVING](https://hackmd.io/@PinYuan99957/SyCFjklTR)
✅用來篩選分組後的資料，通常與`GROUP BY`一起使用
- [LIMIT](limit.md)
限制查詢結果的資料行數。

### Joins

使用聯接語法**串聯多個**資料表中的資料。

- [JOIN(INEER JOIN)](https://hackmd.io/@PinYuan99957/Byscqkx6R)
✅返回兩個表中有匹配的行。
- [LEFT JOIN](https://hackmd.io/@PinYuan99957/r1Ogrxl60)
✅返回左表中的所有行以及匹配的右表行。
- [RIGHT JOIN](right_join.md)
返回右表中的所有行以及匹配的左表行。
- [FULL OUTER JOIN](full_outer_join.md)
返回兩個表中所有的行，無論是否匹配。
- [CROSS JOIN](https://hackmd.io/@PinYuan99957/rkgyxvEraC)
✅返回兩個表的笛卡爾積。

### Aggregate Functions

對資料表進行**計算**

- [SUM()](sum.md)
計算數值欄位的總和。
- [AVG()](avg.md)
計算數值欄位的平均值。
- [COUNT()](https://hackmd.io/@PinYuan99957/rJiXTAwpR)
✅計算資料行數。
- [MAX()](max.md)
返回指定欄位中的最大值。
- [MIN()](min.md)
返回指定欄位中的最小值。
- [MON()](mon.md)
返回數值的餘數

### Conditional and Null Handling Functions
- [CASE & IF](https://hackmd.io/@PinYuan99957/BkNtjZtaA)✅
- [COALESCE()](https://hackmd.io/@PinYuan99957/BJDyD3kRA)`✅
- [ISNULL()](ISNULL.md)
- [NULLIF()](https://hackmd.io/@PinYuan99957/B1lEtk3a0)✅

### String Functions

操作文字資料並設定其格式。.

- [CONCAT()](https://hackmd.io/@PinYuan99957/BkDqsVK60)
✅將多個字串連接在一起。
- [GROUP_CONCAT()]()
- [SUBSTRING()](substring.md)
從字串中提取子字串。
- [LENGTH()](length.md)
返回字串的長度。
- [UPPER() | LOWER()](upper.md)
將字串轉換為大寫，小寫。
- [LIKE](like.md)
- [REGEXP](https://hackmd.io/@PinYuan99957/B1L3FHGAC)
✅正規表達條件式篩選

### Date Functions
**使用日期和時間值並設定其格式**。

- [NOW()](now.md)
返回當前的日期和時間。
- [CURDATE()](curdate.md)
返回當前的日期。
- [DATEDIFF()](datediff.md)
計算兩個日期之間的差距。
- [DATEADD() & DATESUB()](https://hackmd.io/@PinYuan99957/ry85MH-pC)
✅對指定的時間進行日期算術。
- [DATE_FORMAT()](date_format.md)
格式化日期為指定格式。

### Set Operators
**合併多個查詢**的結果。

- [UNION](union.md)
合併兩個查詢的結果，並去除重複行。
- [UNION ALL](union_all.md)
合併兩個查詢的結果，不去除重複行。
- [INTERSECT](intersect.md)
返回兩個查詢中共同的行。
- [EXCEPT](except.md)
返回在第一個查詢中存在但不在第二個查詢中的行。

### Window Function:

- [ROW_NUMBER()](ROW_NUBMER.MD)
分區內的行分配唯一的序號
- [RANK()](RANK.MD)
根據指定的順序為分區內的每一行分配排名，若兩行具有相同的值，將獲得相同的排名，且下一個排名將跳過一個數字
- [DENSE_RANK()](DENSE_RANK.MD)
DENSE_RANK() 與 RANK() 類似，但若兩行具有相同的值，將獲得相同的排名，但下一排名不跳過數字
- [LAG()](LAG.MD)
提供對同一結果集中**前一行**資料的存取。它允許您查看previous row的列值，而無需使用subquery
- [LEAD()](LEAD.MD)
與 LAG() 類似，但它提供對同一結果集中**下一行**的存取。它可用於比較row與next row
### Common Table Expression(CTE)

- [WITH AS](withas.md)

---

## Leetcode 練習整理
**[Github : Leetcode Database Practice]**

| SQL Syntax | LeetCode Problem(s) |
|-------------|------------------------- |
| `SELECT` | [584. Find Customer Referee](https://github.com/Pin99978/LeetCode-Database/blob/95fd0363d0206578df102e9dd7877cea2c8abefd/584.%20Find%20Customer%20Referee/main.sql)<br>[595. Big Countries](https://github.com/Pin99978/LeetCode-Database/blob/41d367202cb64cba4130a316e18069c3848cd3c9/595.%20Big%20Countries/main.sql)<br>[1148. Article Views I](https://github.com/Pin99978/LeetCode-Database/blob/41d367202cb64cba4130a316e18069c3848cd3c9/1148.%20Article%20Views%20I/main.sql)<br>[1757. Recyclable and Low Fat Products](https://github.com/Pin99978/LeetCode-Database/blob/e3907f74401d779699342e1d783208bde2006f1d/1757.%20Recyclable%20and%20Low%20Fat%20Products/main.sql)<br>
| `DELETE` | [196. Delete Duplicate Emails](https://github.com/Pin99978/LeetCode-Database/blob/ecfc18caa4f8eaf17eb52951da7836278be00f8e/196.%20Delete%20Duplicate%20Emails/main.sql)|
| `HAVING` | [182. Customers Who Never Order](https://github.com/Pin99978/LeetCode-Database/blob/3fc187c0775e987906699baeb9646f7de0940142/182.%20Duplicate%20Emails/main.sql) <br>[570. Managers with at Least 5 Direct Reports](https://github.com/Pin99978/LeetCode-Database/blob/a28757c9bf1fc780dd6eaf2bddf666cdddac23b0/570.%20Managers%20with%20at%20Least%205%20Direct%20Reports/main.sql)<br>[596. Classes More Than 5 Students](https://github.com/Pin99978/LeetCode-Database/blob/fd47033223c25539a9226c9748cae4dba6b492cc/596.%20Classes%20More%20Than%205%20Students/main.sql)<br>[619 . Biggest Single Number](https://github.com/Pin99978/LeetCode-Database/blob/abdcb292668be94c7e3e5c6f7be50be025a35be9/619%20Biggest%20Single%20Number/main.sql)<br>[1327. List the Products Ordered in a Period](https://github.com/Pin99978/LeetCode-Database/blob/cf73c19441c418dc465e000517a0f40371af51b4/1327.%20List%20the%20Products%20Ordered%20in%20a%20Period/mani.sql)|
| `JOIN` | [181. Employees Earning More Than Their Managers](https://github.com/Pin99978/LeetCode-Database/blob/3fc187c0775e987906699baeb9646f7de0940142/181.%20Employees%20Earning%20More%20Than%20Their%20Managers/main.sql) <br>[570. Managers with at Least 5 Direct Reports](https://github.com/Pin99978/LeetCode-Database/blob/a28757c9bf1fc780dd6eaf2bddf666cdddac23b0/570.%20Managers%20with%20at%20Least%205%20Direct%20Reports/main.sql)<br>[1068. Product Sales Analysis I](https://github.com/Pin99978/LeetCode-Database/blob/df87235d5c5076566c2717533fdaafb3ed88020c/1068.%20Product%20Sales%20Analysis%20I/main.sql)<br>[1731. The Number of Employees Which Report to Each Employee](https://github.com/Pin99978/LeetCode-Database/blob/7b37d9de7f74fd3b1126fa9ba9f23c377aae7f88/1731.%20The%20Number%20of%20Employees%20Which%20Report%20to%20Each%20Employee/main.sql) |
| `LEFT JOIN` | [175. Count Student Number in Departments](https://github.com/Pin99978/LeetCode-Database/blob/5c7ab0879db17d8a28d820c798137ef216ea5965/175.%20Combine%20Two%20Tables/main.sql)<br>[183. Customers Who Never Order](https://github.com/Pin99978/LeetCode-Database/blob/df87235d5c5076566c2717533fdaafb3ed88020c/183.Customers%20Who%20Never%20Order/main.sql) <br>[577. Employee Bonus](https://github.com/Pin99978/LeetCode-Database/blob/57741b6cd75fac141d47a6347628cbd86d7a0fc7/577.%20Employee%20Bonus/main.sql) <br> [1378. Replace Employee ID with the unique identifier](https://github.com/Pin99978/LeetCode-Database/blob/3fc187c0775e987906699baeb9646f7de0940142/1378.%20Replace%20Employee%20ID%20with%20the%20unique%20identifier/main.sql)<br>[1581. Customer who visited but did not make any transactions](https://github.com/Pin99978/LeetCode-Database/blob/217ce43cc7535856ac280fa8eddbd8c46c80ca0d/1581.%20Customer%20who%20visited%20but%20did%20not%20make%20any%20transactions/main.sql)<br>[1978. Employees Whose Manager Left the Company](https://github.com/Pin99978/LeetCode-Database/blob/f9c0ca8d95d103884fa11321dbd5523971af78fb/1978.%20Employees%20Whose%20Manager%20Left%20the%20Company/main.sql)|
|`CROSS JOIN`|[1280. Students and Examinations](https://github.com/Pin99978/LeetCode-Database/blob/a9b95e55fd169de561f53f3e3d5608a02a90b0ea/1280.%20Students%20and%20Examinations/main.sql)|
| `GROUP BY` | [182. Customers Who Never Order](https://github.com/Pin99978/LeetCode-Database/blob/3fc187c0775e987906699baeb9646f7de0940142/182.%20Duplicate%20Emails/main.sql)<br> [1141. User Activity for the Past 30 Days I](https://github.com/Pin99978/LeetCode-Database/blob/432fa211ba1acbc053e0bdfb2435d9a864cb3c65/1141.%20User%20Activity%20for%20the%20Past%2030%20Days%20I/main.sql)<br>[1729. Find Followers Count](https://github.com/Pin99978/LeetCode-Database/blob/c03ce79fbf9ccde13974c3481af66d0d13b2ca95/1729.%20Find%20Followers%20Count/main.sql) |
| `ORDER BY` | [620. Not Boring Movies](https://github.com/Pin99978/LeetCode-Database/blob/7ac132383b0a0ffef85f90238f6c44c19e72e149/620.%20Not%20Boring%20Movies/main.sql) |
|`PARTITION BY`|[1164. Product Price at a Given Date](https://github.com/Pin99978/LeetCode-Database/blob/3a2c71bb3509a429dbef9049af581ec635cc438e/1164.%20Product%20Price%20at%20a%20Given%20Date/main.sql)|
| `DATE_ADD`|[197. Rising Tempareture](https://github.com/Pin99978/LeetCode-Database/tree/cca54b998f5140f2fd350642b01ca7f0bec19c70/197.%20Rising%20Temperature)
|`COUNT()`|[1141. User Activity for the Past 30 Days I](https://github.com/Pin99978/LeetCode-Database/blob/432fa211ba1acbc053e0bdfb2435d9a864cb3c65/1141.%20User%20Activity%20for%20the%20Past%2030%20Days%20I/main.sql) <br>[1211.Queries Quality and Percentage](https://github.com/Pin99978/LeetCode-Database/blob/367cca92195c0af8a495f8d117ec041227823cc5/1211.%20Queries%20Quality%20and%20PerCentage/main.sql)<br>[1729. Find Followers Count](https://github.com/Pin99978/LeetCode-Database/blob/c03ce79fbf9ccde13974c3481af66d0d13b2ca95/1729.%20Find%20Followers%20Count/main.sql)<br>[2356.Number of Unique SubjectsTaught by Each Teacher](https://github.com/Pin99978/LeetCode-Database/blob/0f94779ea396f305ee39f68658ca68c5647d752a/2356.%20Number%20of%20Unique%20Subjects%20Taught%20by%20Each%20Teacher/main.sql)|
|`SUM()`|[1193. Monthly Transactions I](https://github.com/Pin99978/LeetCode-Database/blob/5412d88d3392f6ed3a26399da03e4ade7474659e/1193.%20Monthly%20Transactions%20I/main.sql)|
|`Union`|[1789. Primary Department for Each Employee](https://github.com/Pin99978/LeetCode-Database/blob/d95daa15551e176f2ef108361d01b08f276d005f/1789.%20Primary%20Department%20for%20Each%20Employee/main.sql)|
|`CASE THEN`|[610. Triangle Judgement](https://github.com/Pin99978/LeetCode-Database/blob/08cb26b843a1a636780a06dccfbd742073c7dcc7/610.%20Triangle%20Judgement/main.sql)<br>[626. Exchange Seats](https://github.com/Pin99978/LeetCode-Database/blob/1ee48acfb6c050c0c5dba583750309ed2c73f50e/626.%20Exchange%20Seats/main.sql)|
|`COALESCE`|[1934. Confirmation Rate](https://github.com/Pin99978/LeetCode-Database/blob/c479a49e7f925fd43f4dde4a0e354e250a3ca943/1934.%20Confirmation%20Rate/main.sql)||
|`NULLUF`|[1934. Confirmation Rate](https://github.com/Pin99978/LeetCode-Database/blob/c479a49e7f925fd43f4dde4a0e354e250a3ca943/1934.%20Confirmation%20Rate/main.sql)|
|`CONCAT`|[1667. Fix Names in a Table](https://github.com/Pin99978/LeetCode-Database/blob/3cf613a1abba3c98acfd36b8f0061f95b1fc9a46/1667.%20Fix%20Names%20in%20a%20Table/main.sql)|
| `GROUP_CONCAT`|[1484. Group Sold Products By The Date](https://github.com/Pin99978/LeetCode-Database/blob/dab43688d0e3525d5ab5d2e618363a8ae0b02549/1484.%20Group%20Sold%20Products%20By%20The%20Date/main.sql)|
|`REGEXP`|[1517. Find Users With Valid E-Mails](https://github.com/Pin99978/LeetCode-Database/blob/147826f6af81ac860baba881b499e725965bd0f2/1517.%20Find%20Users%20With%20Valid%20E-Mails/main.sql)|
|`WITH AS (CTE)`|[550. Game Play Analysis IV](https://github.com/Pin99978/LeetCode-Database/blob/314481756cdfd7ab2d59086789cd5b70a35036c2/550.%20Game%20Play%20Analysis%20IV/main.sql)<br>[619 . Biggest Single Number](https://github.com/Pin99978/LeetCode-Database/blob/abdcb292668be94c7e3e5c6f7be50be025a35be9/619%20Biggest%20Single%20Number/main.sql)<br>[1070. Product Sales Analysis III](https://github.com/Pin99978/LeetCode-Database/blob/36417b9a501f67325cc900f823193d2e068e38f4/1070.%20Product%20Sales%20Analysis%20III/main.sql)<br>[1075. Project Employees I](https://github.com/Pin99978/LeetCode-Database/blob/e96960804ca4f5ed0d4be85bf23a367ad60a7b01/1075.%20Project%20Employees%20I/main.sql)<br>[1164. Product Price at a Given Date](https://github.com/Pin99978/LeetCode-Database/blob/3a2c71bb3509a429dbef9049af581ec635cc438e/1164.%20Product%20Price%20at%20a%20Given%20Date/main.sql)<br>[1204. Last Person to Fit in the Bus](https://github.com/Pin99978/LeetCode-Database/blob/490be87adede4fd0a4162a567224618fb2220fe7/1204.%20Last%20Person%20to%20Fit%20in%20the%20Bus/main.sql)<br>[1251. Average Selling Price](https://github.com/Pin99978/LeetCode-Database/blob/056cc5ce61a6d71bb8c57898d81e13678623e569/1251.%20Average%20Selling%20Price/main.sql)<br>[1633. Percentage of Users attend a contest]()<br>[1661. Average Time of Process per Machine](https://github.com/Pin99978/LeetCode-Database/tree/6dce4ab9a3c1a27ea3abe8ca3738ac1a0e90e459/1661.%20Average%20Time%20of%20Process%20per%20Machine)<br>|
|Subquery|[585. Investments in 2016](https://github.com/Pin99978/LeetCode-Database/blob/f96698c1d0f424df644f28f5d18c41d0f919fb5d/585.%20Investments%20in%202016/main.sql)<br>[1174. Immediate Food Delivery II](https://github.com/Pin99978/LeetCode-Database/blob/fd6cd551038e2ade4fcbc680dadf757924c0c048/Immediate%20Food%20Delivery%20II/main.sql)|