1. Second Highest Salary

SELECT MAX(Salary)FROM EmployeeWHERE Salary < (SELECT max(Salary) FROM Employee)

1. Nth Highest Salary

CREATE FUNCTION getNthHightestSalary(N INT) RETURNS INTBEGINDECLARE M INT;SET M=N-1; RETURN ( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1(LIMIT M, 1 是LIMIT 1 OFFSET M的简写) OFFSET M表示从表的M+1行(0-based开始查找 );END

1. Shortest Distance in a Line
   每个点与其他所有的点都做差比较，最后返回最小值
   Self Join: table joined with itself
   SELECT column_name(s) FROM table T1, table T2 WHERE condition

SELECT MIN(ABS(P1.x - P2.x)) AS shortest FROM point AS P1JOIN point AS P2 ON P1.x > P2.x(等价于P1.x <> P2.x <>符号表示不相等)

SELECT MIN(ABS(P1.x - P2.x)) AS shortest FROM point P1, point P2 WHERE P1.x > P2.x

KEYWORD LIST

LIMIT: Specify the number of records to return in the result set
LEFT JOIN: Returns all rows from the left table, and the matching rows from the right table
NOT NULL: enforces a column to not accept NULL values CREATE TABLE Persons( ID int NOT NULL, Name varchar(25) NOT NULL);
ROUND(number, decimals):: number: Required The number to be rounded decimals: Optional, the number of decimal places to round number it. 默认是整型
CASE FUNCTION
语法:

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE resultEND;

IF FUNCTION

WHERE OR条件 与 UNION表的效率差别

1. Friend Requests I: Overall Acceptance Rate
   必须找到unique 的acceptance 以及 request

SELECT IFNULL( (SELECT ROUND(COUNT(DISTINCT requester_id, accepter_id)/COUNT(DISTINCT sender_id, send_to_id), 2)FROM request_accepted, friend_request), 0) AS accept_rate; 

1. Tree Node

Each node in the tree can be one of three types:
Leaf / Root / Inner

通过Query找出每个node的Type

Query之间的 连接组合可以使用的SQL语法:

• CASE WHEN关键字
• IF关键字
• UNION关键字

SELECT id, ‘Root‘ AS TypeFROM tree WHERE p_id IS NULLUNIONSELECT id, ‘Leaf‘ AS TypeFROM treeWHERE id NOT IN (SELECT DISTINCT p_id FROM tree WHERE p_id IS NOT NULL)AND p_id IS NOT NULLUNIONSELECT id, ‘Inner‘ AS TypeFROM treeWHERE id IN (SELECT DISTINCT p_id FROM tree WHERE p_id IS NOT NULL) AND p_id IS NOT NULLORDER BY id;

--使用CASE关键词SELECT id AS `Id`, (CASE WHEN p_id is null THEN ‘ROOT‘ WHEN id IN (SELECT p_id FROM tree WHERE p_id IS NOT NULL) THEN ‘Inner‘ ELSE ‘Leaf‘ END) as TypeFROM treeGROUP BY id; 

1. Rank Scores