LC 1075 & LC 1083 & LC 1098

SQL-Basic
SQL
Publish Date:   2022-04-10

The following basic-level SQL problems.

LeetCode 1075

Project Employees I (Easy) [link]

SELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project p
LEFT JOIN Employee e
ON p.employee_id = e.employee_id
GROUP BY p.project_id;

LeetCode 1083

Sales Analysis II (Easy) [link]

SELECT s.buyer_id
FROM Sales s
LEFT JOIN Product p
ON s.product_id = p.product_id
GROUP BY s.buyer_id
HAVING SUM(IF(p.product_name = 'S8', 1, 0)) >= 1 
    AND SUM(IF(p.product_name = 'iPhone', 1, 0)) = 0;

LeetCode 1098

Unpopular Books (Medium) [link]

Note that if we don’t use IFNULL in HAVING we would ignore those book having 0 quantity. Alternative way to compare dates WHERE datediff('2019-06-23', available_from) > 30 .

SELECT b.book_id, name
FROM books b 
LEFT JOIN orders o
ON b.book_id = o.book_id AND dispatch_date >= '2018-06-23'
WHERE available_from < '2019-05-23'
GROUP BY b.book_id
HAVING ifnull(sum(quantity), 0) < 10;
SQL-Basic
  TOC