LC 196 & LC 627 & LC 1873

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

The following intro-level SQL problems are about ‘ordering (ORDER BY)’ and ‘editing (CASE WHEN)’. More: DELETE and UPDATE.

LeetCode 196

Delete Duplicate Emails (Easy) [link]

Self Join:

DELETE p1
FROM Person p1, Person p2
WHERE p1.email = p2.email AND p1.id > p2.id;

The following code is selecting what we want to delete. For each row in p1, check whether the WHERE is satisfied. The satisfied ones are emails having larger id.

SELECT p1.*
FROM Person p1, Person p2
WHERE p1.email = p2.email AND p1.id > p2.id;

LeetCode 627

Swap Salary (Easy) [link]

UPDATE Salary
SET sex = CASE sex WHEN 'f' THEN 'm' ELSE 'f' END;

UPDATE Salary
SET sex = IF(sex = 'f', 'm', 'f');

LeetCode 1873

Calculate Special Bonus (Easy) [link]

Using regular expression and CASE WHEN

SELECT 
    employee_id,
    CASE WHEN MOD(employee_id, 2) = 1 AND name NOT rlike '^M' THEN salary ELSE 0
END AS bonus
FROM Employees
ORDER BY employee_id;

Using LEFT and CASE WHEN

SELECT 
    employee_id,
    CASE WHEN MOD(employee_id, 2) = 1 AND LEFT(name, 1) != 'M' THEN salary ELSE 0
END AS bonus
FROM Employees
ORDER BY employee_id;

Using IF

SELECT 
    employee_id,
    IF (MOD(employee_id, 2) = 1 AND LEFT(name, 1) != 'M', salary, 0) AS bonus
FROM Employees
ORDER BY employee_id;
SQL-Intro
  TOC