# LeetCode DB ~ MySQL

[TOC]

## About this article

### About LeetCode website and Codes

* To ensure code quality, I have solved all SQL questions on LeetCode and compared my solutions with others.
* The Questions on LeetCode focus on writing queries efficiently.

### About learning MySQL

* Instead of learning a **broad** range of operations in MySQL, solving questions on LeetCode allows me (and perhaps you) to take **deep dive** into writing MySQL queries.
* Learning **deeply** can truly make you indispensable. You can always refer to documentation as you need, but the skill of writing queries is not something you can simply find in the documentation.

---

## Easy Questions

### 175. Combine Two Tables

#### Key : LEFT JOIN

##### LEFT JOIN

* `SELECT ... FROM table1 LEFT JOIN table2 ON table1.col = table2.col`
* `SELECT ... FROM table1 LEFT JOIN table2 USING(col)`
>`ON {condition}` is equivalent to `USING(column_name)`
>`USING()` can be used Only when the table1 and table2 have the same column name

SELECT P.firstName, P.lastName, A.city, A.state
FROM Person P
LEFT JOIN Address A USING(personId)

### 181. Employees Earning More Than Their Managers

#### Key : INNER JOIN

##### INNER JOIN

* `SELECT ... FROM table1 INNER JOIN table2 ON table1.col = table2.col`
* `SELECT ... FROM table1 INNER JOIN table2 USING(col)`
* `SELECT ... FROM table1 JOIN table2 ON table1.col = table2.col`
* `SELECT ... FROM table1 JOIN table2 USING(col)`

>`INNER JOIN` is equivalent to `JOIN`
>`ON {condition}` is equivalent to `USING(column_name)`

SELECT E1.name AS Employee
FROM Employee E1
JOIN Employee E2
ON E1.managerId = E2.id
WHERE E1.salary > E2.salary

### 182. Duplicate Emails

#### key : INNER JOIN, SELF JOIN

SELECT DISTINCT P1.email AS Email
FROM Person P1
JOIN Person P2 USING(email)
WHERE P1.id <> P2.id

#### key : GROUP BY, HAVING

* `HAVING` can only be used after `GROUP BY`

SELECT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(email) > 1;

### 183. Customers Who Never Order

#### key : IN, subquery

##### IN with subquery

* `(col1, col2, ...) IN (SELECT col1, col2 ... FROM subTable)`

SELECT name AS Customers
FROM Customers
WHERE id NOT IN (SELECT customerId FROM Orders)

#### key : LEFT JOIN, NULL processing

SELECT C.name AS Customers
FROM Customers C
LEFT JOIN Orders O ON C.id = O.customerId
WHERE O.customerId IS NULL

### 196. Delete Duplicate Emails

#### key : DELETE

DELETE P1 FROM Person P1, Person P2
WHERE P1.email = P2.email AND P1.id > P2.id

### 197. Rising Temperature

#### key : CROSS JOIN, DATEDIFF

##### CROSS JOIN

* `SELECT ... FROM table1 CROSS JOIN table2`
* `SELECT ... FROM table1, table2`

SELECT W1.id
FROM Weather W1, Weather W2
WHERE DATEDIFF(W1.recordDate, W2.recordDate) = 1 
    AND W1.temperature > W2.temperature

#### key : INNER JOIN, DATEDIFF

SELECT W1.id
FROM Weather W1
JOIN Weather W2
    ON DATEDIFF(W1.recordDate, W2.recordDate) = 1
WHERE W1.temperature > W2.temperature

### 511. Game Play Analysis I

#### key : GROUP BY + Functions(min, max, sum, avg...)

SELECT player_id, MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id

### 577. Employee Bonus

#### key : LEFT JOIN, NULL processing

SELECT E.name, B.bonus
FROM Employee E
LEFT JOIN Bonus B
ON E.empId = B.empId
WHERE (B.bonus is NULL) OR (B.bonus < 1000)

### 584. Find Customer Referee

#### key : NULL processing

SELECT name FROM Customer
WHERE (referee_id <> 2) OR (referee_id IS NULL)

### 586. Customer Placing the Largest Number of Orders

#### key : ORDER BY + LIMIT

SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(customer_number) DESC
LIMIT 1

### 595. Big Countries

#### key : this is quite easy to solve

SELECT name, population, area FROM World
WHERE area >= 3000000 OR population >= 25000000

### 596. Classes More Than 5 Students

#### key : GROUP BY, HAVING

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(*) >= 5

### 607. Sales Person

#### key : subquery

* line 7 ~ 9 : select the company id of company 'RED'.
* line 4 ~ 10 : select the sales_id who have orders related to 'RED'.
* line 1 ~ 11 : eliminate those id.

SELECT name
FROM SalesPerson
WHERE sales_id NOT IN(
    SELECT sales_id
    FROM Orders
    WHERE com_id = (
        SELECT com_id
        FROM Company
        WHERE name = "RED"
    )
)

### 610. Triangle Judgement

#### key : CASE

##### CASE

CASE
    WHEN condition1 THEN return_value1
    WHEN condition2 THEN return_value2
    .
    .
    .
    ELSE return_value3
END

* In this problem, return value is 'column'.
* Remember the 'END'.

SELECT
    x,
    y,
    z,
    (CASE
        WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes'
        ELSE 'No'
    END) AS triangle
FROM Triangle

### 619. Biggest Single Number

#### key : subquery, GROUP BY, HAVING

SELECT MAX(num) AS num
FROM (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(*) = 1
) AS MyNumbers