181. Employees Earning More Than Their Managers
https://leetcode.com/problems/employees-earning-more-than-their-managers/#/description
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+
Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.
+----------+ | Employee | +----------+ | Joe | +----------+
SELECT
a.Name AS 'Employee'
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
;
Actually, JOIN is a more common and efficient way to link tables together, and we can use ON to specify some conditions.
SELECT
a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary
;
SELECT Name As Employee
FROM Employee A
WHERE EXISTS(
SELECT Name
FROM Employee B
WHERE Id=A.ManagerId AND Salary<=A.Salary
)
196. Delete Duplicate Emails
Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Id is the primary key column for this table.
For example, after running your query, the above Person table should have the following rows:
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+
DELETE P
FROM Person P,Person P1
WHERE P.Id>P1.Id AND P.Email=P1.Email;
197. Rising Temperature
Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
+---------+------------+------------------+ | Id(INT) | Date(DATE) | Temperature(INT) | +---------+------------+------------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +---------+------------+------------------+For example, return the following Ids for the above Weather table:
+----+ | Id | +----+ | 2 | | 4 | +----+
# Write your MySQL query statement below
SELECT W1.Id
FROM Weather w1,Weather w2
Where DATEDIFF(w1.Date,w2.Date)=1 AND W1.Temperature>W2.Temperature;
SELECT wt1.Id
FROM Weather wt1, Weather wt2
WHERE wt1.Temperature > wt2.Temperature AND TO_DAYS(wt1.DATE)-TO_DAYS(wt2.DATE)=1;
182. Duplicate Emails
Write a SQL query to find all duplicate emails in a table named Person.
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
For example, your query should return the following for the above table:
+---------+ | Email | +---------+ | a@b.com | +---------+
Note: All emails are in lowercase.
SELECT Distinct(a.Email)
FROM Person a,Person b
WHERE a.Id<>b.Id and a.Email=b.Email;