SQL - Leetcode-02

SQL Schema

For Reference: Create Employees Table

Create table If Not Exists Employees (employee_id int, name varchar(30), salary int)
Truncate table Employees
insert into Employees (employee_id, name, salary) values ('2', 'Meir', '3000')
insert into Employees (employee_id, name, salary) values ('3', 'Michael', '3800')
insert into Employees (employee_id, name, salary) values ('7', 'Addilyn', '7400')
insert into Employees (employee_id, name, salary) values ('8', 'Juan', '6100')
insert into Employees (employee_id, name, salary) values ('9', 'Kannon', '7700')

Question 1

Write a query to to calculate the bonus of each employee.

The bonus of an employee is 100% of their salary if the ID of the employee is an odd number and the employee name does not start with the character 'M'.

The bonus of an employee is 0 otherwise.

To check for Odd number we can use either of the methods:

Checking Odd or Even:

-- Using Mod
mod(employee_id, 2) <> 0
-- Using %
employee_id%2 <>0

Solutions

Using a CASE Statement:

Method 1:

SELECT employee_id,
CASE
    WHEN mod(employee_id, 2) <> 0 AND name NOT LIKE 'M%' THEN salary
    ELSE 0
END AS bonus FROM Employees ORDER BY employee_id;

Using a UNION Statement:

Method 2:

SELECT employee_id, salary AS bonus FROM Employees
WHERE employee_id%2 <> 0 and name NOT LIKE 'M%'
UNION
SELECT employee_id, 0 AS bonus FROM Employees
WHERE employee_id%2 = 0 or name LIKE 'M%' ORDER BY employee_id;

More Simplified Version:

Method 3:

SELECT employee_id, salary * ( employee_id%2 ) * ( name not like 'M%') as bonus
FROM Employees ORDER BY employee_id;

SQL Schema

For Reference: Create Salary Table

Create table If Not Exists Salary (id int, name varchar(100), sex char(1), salary int)
Truncate table Salary
insert into Salary (id, name, sex, salary) values ('1', 'A', 'm', '2500')
insert into Salary (id, name, sex, salary) values ('2', 'B', 'f', '1500')
insert into Salary (id, name, sex, salary) values ('3', 'C', 'm', '5500')
insert into Salary (id, name, sex, salary) values ('4', 'D', 'f', '500')

Question 2

Write a query to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables.

Note that you must write a single update statement, do not write any select statement for this problem.

Solution:

UPDATE salary
SET sex  = (CASE WHEN sex = 'm' THEN  'f' ELSE 'm' END)

Question 3

Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one.

For Reference: Create Person Table

Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (id, email) values ('1', 'john@example.com')
insert into Person (id, email) values ('2', 'bob@example.com')
insert into Person (id, email) values ('3', 'john@example.com')

Deleting with condition, we take 2 records A and B to compare according to the criteria.

DELETE A
FROM Person A, Person B
WHERE A.email=B.email AND A.id>B.id;