1667. Fix Names in a Table

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+
user_id is the primary key for this table.
This table contains the ID and the name of the user. The name consists of only lowercase and uppercase characters.

Write an SQL query to fix the names so that only the first character is uppercase and the rest are lowercase.

Return the result table ordered by user_id.

The query result format is in the following example.

Example 1:

Input: 
Users table:
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | aLice |
| 2       | bOB   |
+---------+-------+
Output: 
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | Alice |
| 2       | Bob   |
+---------+-------+

這一題需要把name欄位的名字改成字首大寫，其餘小寫的格式，
當下我想用title這類python在用的函式，但發現SQL沒有這個，
於是到w3s查詢UPPER、LOWER轉大寫跟轉小寫，還學到了SUBSTR、CONCAT的用法，
SUBSTR是提取字串的函式，CONCAT是連結字串的函式，
我直接註解在下面方便記錄。

SELECT user_id,
    CONCAT(UPPER(LEFT(name,1)),  #CONCAT連結((字),(字))，UPPER(LEFT(name,1)欄位name左邊第1個字大寫
    LOWER(SUBSTR(name,2))) name  #LOWER(SUBSTR(name,2))，提取name中，第2個字以後的字串並轉小寫。
FROM Users
ORDER BY user_id; #按user_id做排序

1484. Group Sold Products By The Date

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
There is no primary key for this table, it may contain duplicates.
Each row of this table contains the product name and the date it was sold in a market.
 
Write an SQL query to find for each date the number of different products sold and their names.

The sold products names for each date should be sorted lexicographically.

Return the result table ordered by sell_date.

The query result format is in the following example.

Example 1:

Input: 
Activities table:
+------------+------------+
| sell_date  | product     |
+------------+------------+
| 2020-05-30 | Headphone  |
| 2020-06-01 | Pencil     |
| 2020-06-02 | Mask       |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible      |
| 2020-06-02 | Mask       |
| 2020-05-30 | T-Shirt    |
+------------+------------+
Output: 
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+
Explanation: 
For 2020-05-30, Sold items were (Headphone, Basketball, T-shirt), we sort them lexicographically and separate them by a comma.
For 2020-06-01, Sold items were (Pencil, Bible), we sort them lexicographically and separate them by a comma.
For 2020-06-02, the Sold item is (Mask), we just return it.

這一題要將sell_date 中重複日期的商品加在一起，並把商品名擺在products欄位中，
比如2020-05-30有3筆，num_sold中加總有3，products中填上那3個品項Basketball,Headphone,T-shirt
，知道要用COUNT去做加總，用DISTINCT提取重複值，但先後順序不會寫，GOOGLE後在將註解寫上記錄。
** GROUP_CONCAT能合併多筆

SELECT  sell_date , COUNT(DISTINCT product) AS num_sold, 
                    #加總(DISTINCT product)是因為每個商品都不一樣才能這麼做
GROUP_CONCAT(DISTINCT product ORDER BY product) AS products 
                    #GROUP_CONCAT是多筆連結函式，其中發現ORDER BY product這句不寫也能過
FROM Activities
GROUP BY sell_date # GROUP BY用來分類群組，不打會6筆都加在一起
ORDER BY sell_date;

1527. Patients With a Condition

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |
+--------------+---------+
patient_id is the primary key for this table.
'conditions' contains 0 or more code separated by spaces. 
This table contains information of the patients in the hospital.
 
Write an SQL query to report the patient_id, patient_name and conditions of the patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Patients table:
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | YFEV COUGH   |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |
+------------+--------------+--------------+
Output: 
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 | 
+------------+--------------+--------------+
Explanation: Bob and George both have a condition that starts with DIAB1.

這一題是找出字串中有"DIAB1"字樣的資料並列出，
這題找字串馬上就用LIKE"%"寫出來，但忽略了小細節卡關，直接寫在註解中。

#SELECT * FROM Patients 
#WHERE conditions LIKE "%DIAB1%";<<他不會將D前面沒有字的值列入

select *
from Patients
where conditions like '% DIAB1%' or 
      conditions like 'DIAB1%' #要補上這句才能找到開頭DIAB1的資料