[LeetCode] SQL Study Plan_Day 2

#1873 Calculate Special Bonus

Notes

• Usage of CASE:
  When multiple possibilities of conditions, we can use the CASE statement

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

• Usage of NOT LIKE with Wildcards in SQL:
  In this case we are looking for words that do not start with a ‘M.’ Therefore we should use ‘M%’ with the NOT LIKE to include them.

SQL Wildcards

• Usage of remainders:
  In this case we are looking for odd numbers, which means it has a remainder of 1 if divided by 2. There are several ways to indicate a remainder statement:
  1. MOD statement:

     SELECT MOD(9, 2);   // 1, odd number
     

  2. x % y

     SELECT MOD(employee_id, 2) != 0
     

---

#627 Swap Salary

Notes

• Usage of UPDATE statement:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

In this case, we use CASE...WHEN to set the column:

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

---

#196 Delete Duplicate Emails

Notes

In this case the solution is to join the table with itself, and filter out the duplicates to be deleted. Ordered by id, we can indicate that the duplicate column will have a bigger id number than the first one, and a same email.

DELETE p1 FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id;

You can also join tables without the JOIN statement like this: FROM Person p1, Person p2. What we deleted here is the left half of the two tables.