LeetCode中的DataBase面试题

1. Rank Scores

   SELECT s.Score, COUNT(t.Score) AS Rank FROM Scores s, (SELECT DISTINCT Score FROM Scores) tWHERE 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 Employeewhere 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;