LC 176 & LC 608 & LC 1795 & LC 1965

SQL-Intro
SQL
Publish Date:   2022-03-13

The following intro-level SQL problems are about ‘combining (UNION) and querying’ and ‘selecting (HAVING)’. More: LIMIT, OFFSET, UNION ALL.

LeetCode 176

Second Highest Salary (Medium) [link]

The solution below will not pass all tests, because sometimes there is no second highest salary.

SELECT DISTINCT Salary AS SecondHighestSalary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

To address the problem above, we use it as a temporary table or use IFNULL

SELECT (
    SELECT DISTINCT Salary 
    FROM Employee
    ORDER BY Salary DESC
    LIMIT 1 OFFSET 1
) AS SecondHighestSalary;

SELECT IFNULL(
    (
        SELECT DISTINCT Salary
        FROM Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1
    ) , NULL) AS SecondHighestSalary;

LeetCode 608

Tree Node (Medium) [link]

Using UNION

SELECT id, 'Root' AS type
FROM Tree
WHERE p_id IS NULL

UNION

SELECT id, 'Leaf' AS type
FROM Tree
WHERE id NOT IN(
    SELECT p_id
    FROM Tree
    WHERE p_id IS NOT NULL
) AND p_id IS NOT NULL

UNION

SELECT id, 'Inner' AS type
FROM Tree
WHERE id IN(
    SELECT p_id
    FROM Tree
    WHERE p_id IS NOT NULL
) AND p_id IS NOT NULL

ORDER BY id;

Using CASE WHEN

SELECT id, 
    CASE 
        WHEN id IN(
            SELECT id
            FROM Tree
            WHERE p_id IS NULL
        ) THEN 'Root' 
        WHEN id IN(
            SELECT p_id
            FROM Tree
        ) THEN 'Inner' ELSE 'Leaf'
    END AS type
FROM Tree;

Using IF

SELECT id, 
    IF(ISNULL(p_id), 'Root', IF(id IN (SELECT p_id FROM Tree), 'Inner', 'Leaf')) AS type
FROM Tree
ORDER BY id;

LeetCode 1795

Rearrange Products Table (Easy) [link]

SELECT product_id, 'store1' AS store, store1 AS price
FROM Products
WHERE store1 IS NOT NULL
UNION ALL
SELECT product_id, 'store2' AS store, store2 AS price
FROM Products
WHERE store2 IS NOT NULL
UNION ALL
SELECT product_id, 'store3' AS store, store3 AS price
FROM Products
WHERE store3 IS NOT NULL;

LeetCode 1965

Employees with Missing Information (Easy) [link]

SELECT employee_id
FROM(
    SELECT employee_id FROM Employees
    UNION ALL
    SELECT employee_id FROM Salaries
) AS t
GROUP BY employee_id
HAVING COUNT(employee_id) = 1
ORDER BY employee_id;
SQL-Intro
  TOC