Day04-LeetCode-SQL启航~

花了点时间完成昨日进度，加油!菜鸟!

608. Tree Node

Example 1:

+-------------+------+| Column Name | Type |+-------------+------+| id          | int  || p_id        | int  |+-------------+------+id is the primary key column for this table.Each row of this table contains information about the id of a node and the id of its parent node in a tree.The given structure is always a valid tree.Each node in the tree can be one of three types:"Leaf": if the node is a leaf node."Root": if the node is the root of the tree."Inner": If the node is neither a leaf node nor a root node.Write an SQL query to report the type of each node in the tree.Return the result table in any order.The query result format is in the following example.Input: Tree table:+----+------+| id | p_id |+----+------+| 1  | null || 2  | 1    || 3  | 1    || 4  | 2    || 5  | 2    |+----+------+Output: +----+-------+| id | type  |+----+-------+| 1  | Root  || 2  | Inner || 3  | Leaf  || 4  | Leaf  || 5  | Leaf  |+----+-------+Explanation: Node 1 is the root node because its parent node is null and it has child nodes 2 and 3.Node 2 is an inner node because it has parent node 1 and child node 4 and 5.Nodes 3, 4, and 5 are leaf nodes because they have parent nodes and they do not have child nodes.Example 2:Input: Tree table:+----+------+| id | p_id |+----+------+| 1  | null |+----+------+Output: +----+-------+| id | type  |+----+-------+| 1  | Root  |+----+-------+Explanation: If there is only one node on the tree, you only need to output its root attributes.

这一题需要帮这个树状图做命名，
1.NULL值改成Root
2.底下有连结的为Inner
3.底下无连结的为Leaf
用一些IF THEN的逻辑就能解出来，
** 遇到的问题是('IS' 不等于 '=')，在刷题时的小记录，
还有CASE WHEN THEN END在用字上需要记一下

SELECT id,(CASE    WHEN p_id IS null THEN 'Root'    WHEN id IN (SELECT p_id FROM Tree )THEN 'Inner'    ELSE 'Leaf'    END)AS typeFROM Tree 

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.The query result format is in the following example.Example 1:Input: Employee table:+----+--------+| id | salary |+----+--------+| 1  | 100    || 2  | 200    || 3  | 300    |+----+--------+Output: +---------------------+| SecondHighestSalary |+---------------------+| 200                 |+---------------------+Example 2:Input: Employee table:+----+--------+| id | salary |+----+--------+| 1  | 100    |+----+--------+Output: +---------------------+| SecondHighestSalary |+---------------------+| null                |+---------------------+

这一题要列出第2高的薪资(salary栏位)，如果没有则回报NULL，
这几天从SQL的结构中学到一些计算都要在SELECT区块进行，
就尝试用max()找最大值再补条件，幸好他只会列第二大的值，而不是全部列出。

SELECT max(salary) AS SecondHighestSalaryFROM Employee WHERE salary < (SELECT max(salary) FROM Employee)

我就菜

1965. Employees With Missing Information

+-------------+---------+| Column Name | Type    |+-------------+---------+| employee_id | int     || name        | varchar |+-------------+---------+employee_id is the primary key for this table.Each row of this table indicates the name of the employee whose ID is employee_id.Table: Salaries+-------------+---------+| Column Name | Type    |+-------------+---------+| employee_id | int     || salary      | int     |+-------------+---------+employee_id is the primary key for this table.Each row of this table indicates the salary of the employee whose ID is employee_id.Write an SQL query to report the IDs of all the employees with missing information. The information of an employee is missing if:The employee's name is missing, orThe employee's salary is missing.Return the result table ordered by employee_id in ascending order.The query result format is in the following example.Example 1:Input: Employees table:+-------------+----------+| employee_id | name     |+-------------+----------+| 2           | Crew     || 4           | Haven    || 5           | Kristian |+-------------+----------+Salaries table:+-------------+--------+| employee_id | salary |+-------------+--------+| 5           | 76071  || 1           | 22517  || 4           | 63539  |+-------------+--------+Output: +-------------+| employee_id |+-------------+| 1           || 2           |+-------------+Explanation: Employees 1, 2, 4, and 5 are working at this company.The name of employee 1 is missing.The salary of employee 2 is missing.

这题要找出缺少资料的员工，观察Employees跟Salaries两个表，
能看出employee_id 1、2 两笔资料会缺name或salary的内容，
一开始的想法是把表合在一起，直接取只有一笔的1跟2出来，
但卡在合成一表后，没办法在最外层用DISTINCT只取出现过一次的值，
在GOOGLE后看到大神的写法，这边直接进行说明，
先比对出employee_id栏位，于Employees表中不在Salaries中值
再比对出employee_id栏位，于Salaries表中不在Employees中值
两个结果用UNION连在一起，最后ORDER BY进行排序

SELECT employee_id FROM Employees WHERE employee_id NOT IN (SELECT employee_id FROM Salaries)UNIONSELECT employee_id FROM Salaries WHERE employee_id NOT IN (SELECT employee_id FROM Employees)ORDER BY employee_id;

1795. Rearrange Products Table

+-------------+---------+| Column Name | Type    |+-------------+---------+| product_id  | int     || store1      | int     || store2      | int     || store3      | int     |+-------------+---------+product_id is the primary key for this table.Each row in this table indicates the product's price in 3 different stores: store1, store2, and store3.If the product is not available in a store, the price will be null in that store's column. Write an SQL query to rearrange the Products table so that each row has (product_id, store, price). If a product is not available in a store, do not include a row with that product_id and store combination in the result table.Return the result table in any order.The query result format is in the following example.Example 1:Input: Products table:+------------+--------+--------+--------+| product_id | store1 | store2 | store3 |+------------+--------+--------+--------+| 0          | 95     | 100    | 105    || 1          | 70     | null   | 80     |+------------+--------+--------+--------+Output: +------------+--------+-------+| product_id | store  | price |+------------+--------+-------+| 0          | store1 | 95    || 0          | store2 | 100   || 0          | store3 | 105   || 1          | store1 | 70    || 1          | store3 | 80    |+------------+--------+-------+Explanation: Product 0 is available in all three stores with prices 95, 100, and 105 respectively.Product 1 is available in store1 with price 70 and store3 with price 80. The product is not available in store2.

这题要把store1.2.3整合在一起，除了分数NULL的不列入，其他分数都写在price栏位中，
一开始想全部写一个改名的方式，但输出的样子完全不对，就先注解起来当记录，
在W3S中查询到是用UNION ALL进行连结，
要用UNION ALL是因为UNION本身会排除重複值，

/*SELECT product_id ,        'store1' AS 'store'  ,        'store2' AS 'store'  ,        'store3' AS 'store'  ,        store1 AS price  ,        store2 AS price  ,        store3 AS price         FROM Products WHERE store1 IS NOT NULL;*/SELECT product_id ,        'store1' AS 'store',         store1 AS price        FROM Products WHERE store1 IS NOT NULLUNION ALLSELECT product_id ,        'store2' AS 'store',         store2 AS price        FROM Products WHERE store2 IS NOT NULLUNION ALLSELECT product_id ,        'store3' AS 'store',         store3 AS price        FROM Products WHERE store3 IS NOT NULL;