LeetCode Diary (daily update)

Day 1.

# Write your MySQL query statement below
SELECT FirstName, LastName, City, State
FROM Person
LEFT JOIN Address
ON Person.PersonId=Address.PersonId;
SELECT DISTINCT Salary
AS SecondHighestSalary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT DISTINCT Salary FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET N-1
);
END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
RETURN (
# Write your MySQL query statement below.
SELECT DISTINCT Salary FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET N
);
END
SELECT Score, 
DENSE_RANK() OVER(ORDER BY Score DESC) AS "Rank"
FROM Scores;
# Write your MySQL query statement below
SELECT l1.Num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l2.Id = l1.Id +1 AND l1.Num = l2.Num
JOIN Logs l3 ON l3.Id = l2.Id +1 AND l2.Num = l3.Num;
# Write your MySQL query statement below
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs AS l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id -1
AND l2.Id = l3.Id -1
AND l1.Num = l2.Num
AND l2.Num = l3.Num;

Day 2.

SELECT *
FROM Employee AS e1, Employee AS e2
;
# Write your MySQL query statement below
SELECT e1.Name AS Employee
FROM Employee AS e1,
Employee AS e2
WHERE e1.ManagerId = e2.Id AND
e1.Salary > e2.Salary;
# Write your MySQL query statement below
SELECT e1.Name AS 'Employee'
FROM Employee e1
JOIN Employee e2
ON e2.Id= e1.ManagerId
WHERE e1.Salary > e2.Salary;
# Write your MySQL query statement below
SELECT DISTINCT p1.Email AS Email
FROM Person p1,
Person p2
WHERE p1.Id <> p2.Id AND
p1.Email = p2.Email;
SELECT Email FROM
( SELECT Email, COUNT(Email) AS num
FROM Person
GROUP BY Email
) AS statistic
WHERE num > 1
;
SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1;
# Write your MySQL query statement below
SELECT Name AS Customers
FROM Customers
WHERE Customers.Id NOT IN (SELECT CustomerId FROM Orders);
# Write your MySQL query statement below
SELECT cus.Name AS Customers
FROM Customers cus
LEFT JOIN Orders ord
ON cus.Id = ord.CustomerId
WHERE ord.CustomerId IS NULL;
# Write your MySQL query statement below
SELECT Department.Name AS Department,
Employee.Name AS Employee,
Salary
FROM Employee
LEFT JOIN Department
ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId, Salary) IN (SELECT DepartmentId, Max(Salary)
FROM Employee
GROUP BY DepartmentId);
# Write your MySQL query statement below
SELECT dp.Name AS "Department",
ep.Name AS "Employee",
ep.Salary AS "Salary"
FROM Employee AS ep
INNER JOIN Department AS dp
ON ep.DepartmentId = dp.Id
WHERE (SELECT COUNT(DISTINCT Salary)
FROM Employee AS ep1
WHERE ep1.DepartmentId = ep.DepartmentId AND ep1.Salary > ep.Salary) < 3;
SELECT
d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
Employee e1
JOIN
Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
;
# Write your MySQL query statement below
SELECT Department.Name AS "Department",
e.Name AS "Employee",
e.Salary
FROM (SELECT DepartmentId, Name, Salary,
DENSE_RANK() OVER(PARTITION BY DepartmentId
ORDER BY Salary DESC)
AS r FROM Employee) As e
JOIN Department
ON e.DepartmentId = Department.Id
WHERE r <= 3;

Day 3.

# Write your MySQL query statement below
DELETE p1
FROM Person p1,
Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id;
# Write your MySQL query statement below
SELECT w2.id
FROM Weather w1
JOIN Weather w2
ON w2.Temperature > w1.Temperature AND DATEDIFF(w2.recordDate, w1.recordDate) = 1
WHERE w2.recordDate > w1.recordDate;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store