LeetCode中的DataBase面试题

1. Rank Scores

   SELECT s.Score, COUNT(t.Score) AS Rank 
   FROM Scores s, (SELECT DISTINCT Score FROM Scores) t
   WHERE s.Score<=t.Score 
   GROUP BY s.Id, s.Score ORDER BY s.Score DESC; 

   SELECT s.Score, 
   (SELECT COUNT(DISTINCT Score) FROM Scores WHERE Score>=s.Score) AS Rank 
   FROM Scores s ORDER BY s.Score DESC; 

2. Second Highest Salary

   SELECT max(Salary) FROM Employee
   where Salary < (SELECT MAX(Salary) FROM Employee);

   SELECT (SELECT DISTINCT Salary 
    FROM Employee ORDER BY Salary DESC LIMIT 1,1);

3. Rising Temperature

   SELECT a.Id FROM Weather AS a, Weather AS b 
   WHERE DATEDIFF(a.Date, b.Date)=1 AND a.Temperature > b.Temperature;

4. Delete Duplicate Emails

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

   DELETE FROM Person 
   WHERE Id not in 
   (SELECT t.Id FROM (SELECT MIN(Id) AS Id FROM Person GROUP BY Email) t);

5. Duplicat Emails

   SELECT Email FROM Person GROUP BY Email HAVING COUNT(*) > 1;

6. Department Highest Salary

   SELECT d.Name, e.Name, e.Salary 
   From 
   (SELECT DepartmentId, MAX(Salary) AS Salary FROM Employee GROUP BY DepartmentId) t, 
   Department d, Employee e 
   WHERE d.Id=e.DepartmentId 
   and e.Salary=t.Salary 
   and t.DepartmentId=e.DepartmentId;

   SELECT D.Name,A.Name,A.Salary 
   FROM 
   Employee A,
   Department D   
   WHERE A.DepartmentId = D.Id 
   AND NOT EXISTS 
   (SELECT 1 FROM Employee B 
     WHERE B.Salary > A.Salary AND A.DepartmentId = B.DepartmentId)

7. Department Top Three Salaries

   SELECT d.Name, e.Name, e.Salary 
   FROM Department d, Employee e 
   WHERE 3 > (SELECT COUNT(DISTINCT(Salary)) FROM Employee 
    WHERE Salary>e.Salary 
    and e.DepartmentId=DepartmentId) 
   and d.Id=e.DepartmentId 
   ORDER BY e.DepartmentId, e.Salary DESC;

8. Customers Who Never Order

   SELECT c.Name AS Customers 
   FROM Customers c 
   WHERE NOT EXISTS (SELECT 1 FROM Orders WHERE c.Id=CustomerId);

   SELECT c.Name AS Customers 
   FROM Customers c 
   LEFT JOIN Orders o ON c.Id=o.CustomerId WHERE o.Id IS NULL;

9. Employees Earning More Than Their Managers

   SELECT e.Name FROM Employee e, Employee e1 
   WHERE e1.Id=e.ManagerId AND e.Salary>e1.Salary;

10. Consecutive Numbers

    SELECT DISTINCT l.Num FROM `Logs` l, `Logs` l1, `Logs` l2 
    WHERE l1.Id=l.Id+1 AND l2.Id=l.Id+2 AND l1.Num=l.Num AND l2.Num=l.Num;