LC 619 & LC 1112 & LC 1398 & LC 1607 & LC 1440

The following basic-level SQL problems are about ‘linking’.

LeetCode 619

Biggest Single Number (Easy) [link]

SELECT(
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(num) = 1
    ORDER BY num DESC
    LIMIT 1
) AS num;

# Note that the following code does not return NULL if there is.
# When the table is NULL, in order to return NULL, we have to SELECT that table

# SELECT num
# FROM MyNumbers
# GROUP BY num
# HAVING COUNT(num) = 1
# ORDER BY num DESC
# LIMIT 0,1

Alternative way:

SELECT MAX(t.num) AS num
FROM (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(num) = 1
) AS t;

Note: When the table is NULL, in order to return NULL, we have to SELECT that table. LIMIT, WHERE, and HAVING cannot give new NULL. SUM, AVG, MAX, MIN can give new NULL. For example, the following code gives four NULL.

SELECT SUM(num) sum_num, AVG(num) avg_num, MAX(num) max_num,MIN(num) min_num
FROM (
    SELECT num
    FROM my_numbers 
    GROUP BY num
    HAVING COUNT(num)=1
    ORDER BY num DESC
    LIMIT 0,1
) t1

LeetCode 1112

Highest Grade For Each Student (Medium) [link]

Using subquery

SELECT student_id, MIN(course_id) AS course_id, grade
FROM Enrollments
WHERE (student_id, grade) IN(
    SELECT student_id, MAX(grade)
    FROM Enrollments
    GROUP BY student_id
)
GROUP BY student_id
ORDER BY student_id;

Using window function

SELECT student_id, course_id, grade
FROM (
    SELECT *,
        dense_rank() OVER(PARTITION BY student_id ORDER BY grade DESC, course_id) AS rnk
    FROM Enrollments
) AS t
WHERE t.rnk = 1;

LeetCode 1398

Customers Who Bought Products A and B but Not C (Medium) [link]

SELECT O.customer_id, C.customer_name
FROM Orders O
LEFT JOIN Customers C
ON O.customer_id = C.customer_id
GROUP BY customer_id
HAVING 
    SUM(product_name = 'A') > 0 AND
    SUM(product_name = 'B') > 0 AND
    SUM(product_name = 'C') = 0;

LeetCode 1607

Sellers With No Sales (Easy) [link]

Using NOT IN

SELECT seller_name
FROM Seller
WHERE seller_id NOT IN(
    SELECT seller_id
    FROM Orders
    WHERE YEAR(sale_date) = '2020'
      # WHERE DATE_FORMAT(sale_date, "%Y") = 2020)
)
ORDER BY seller_name;

Using LEFT JOIN

SELECT S.seller_name
FROM Seller S
LEFT JOIN Orders O
ON S.seller_id = O.seller_id AND YEAR(O.sale_date) = '2020'
WHERE O.order_id IS NULL
ORDER BY seller_name;

LeetCode 1440

Evaluate Boolean Expression (Medium) [link]

SELECT E.left_operand, E.operator, E.right_operand, (
    CASE WHEN (E.operator = '>' AND v1.value > v2.value) OR
            (E.operator = '<' AND v1.value < v2.value) OR
            (E.operator = '=' AND v1.value = v2.value) THEN 'true' ELSE 'false'
    END
) AS value
FROM Expressions E
LEFT JOIN Variables v1
ON E.left_operand = v1.name
LEFT JOIN Variables v2
ON E.right_operand = v2.name;