过年好久没有刷题,手都生疏了,sad…这几天正好闲的没事,准备把leetcode刷刷。Algorithm部分太多了,慢慢总结。先把Database部分总结一下吧～

Database篇,按难度来总结:

Combine Two Tables (Easy)

Description:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Table: Person
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table
Table: Address
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

Q:Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

A:

1
select FirstName,LastName,Address.City,Address.State from Person left join Address on Person.PersonId = Address.PersonId;

Customers Who Never Order (Easy)

Description:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Table: Customers.
+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Table: Orders.
+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Q:Write a SQL query to find all customers who never order anything.
A:

1
2
# Write your MySQL query statement below
select Name from Customers where Customers.Id Not in (select CustomerId from Orders);

Duplicate Emails (Easy)

Description:

1
2
3
4
5
6
7
8
Table: Person
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

Q:Write a SQL query to find all duplicate emails in a table named Person.
A:

1
2
3
4
# Write your MySQL query statement below
#way1: SELECT Email FROM Person GROUP BY Email Having COUNT(Email)>1;
#way2:
SELECT DISTINCT a.Email FROM Person a JOIN Person b ON (a.Email=b.Email and a.Id<>b.Id);

Employees Earning More Than Their Managers (Easy)

Description:

1
2
3
4
5
6
7
8
9
10
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
Table: Employee
+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

Q: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.
A:

1
2
3
4
# Write your MySQL query statement below
#way1: SELECT a.Name FROM Employee a ,Employee b WHERE a.ManagerId = b.Id AND a.Salary>b.Salary;
#way2:
select a.Name from Employee a inner join Employee b on a.ManagerId=b.Id where a.Salary>b.Salary;

Second Highest Salary (Easy)

Description:

1
2
3
4
5
6
7
8
Table: Employee
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

Q:Write a SQL query to get the second highest salary from the Employee table.
A:

1
2
3
# Write your MySQL query statement below

SELECT max(a.Salary) FROM Employee a where a.Salary < (select max(b.Salary) from Employee b) ;

Consecutive Numbers (Medium)

Description:

1
2
3
4
5
6
7
8
9
10
11
12
Table: Logs
+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

Q:Write a SQL query to find all numbers that appear at least three times consecutively.
A:

1
2
3
4
5
6
7
8
9
10
11
# Write your MySQL query statement below

select distinct Num from
(select Num,
    case
        when @pre=Num then @count:=@count+1
        when @pre:=Num then @count:=1
        when @count:=1 then @count
    end as total 
    from Logs ,(select @count:=0,@pre:=null) r
) a where a.total>=3;

Department Highest Salary (Medium)

Description:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.
Table: Employee
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
The Department table holds all departments of the company.
Table: Department
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Q:Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.
A:

1
2
3
4
5
6
7
# Write your MySQL query statement below

select b.Name,a.Name,a.Salary from Department b ,Employee a,
(select DepartmentId,Name,max(Salary) as maxs from Employee group by DepartmentId) c
where a.DepartmentId = c.DepartmentId and
    a.Salary = c.maxs and
    a.DepartmentId = b.Id;

Nth Highest Salary (Medium)

Description:

1
2
3
4
5
6
7
8
Table: Employee
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

Q:Write a SQL query to get the nth highest salary from the Employee table.
A:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
    select Salary from
    (select Salary,
    case 
        when @Salary=Salary then @rank 
        when @Salary:=Salary then @rank:=@rank+1 
        when @rank:=@rank+1 then @rank 
    end as Rank From Employee b,(select @rank:=0,@Salary:=null)a 
    order by Salary desc)c where c.Rank=N limit 1
  );
END

Rank Scores (Medium)

Description:

1
2
3
4
5
6
7
8
9
10
11
Table: Scores
+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

Q:Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.
A:

1
2
3
4
5
6
7
8
# Write your MySQL query statement below
select Score,
case
    when @score=Score then @rank
    when @score:=Score  then @rank:=@rank+1
    when @rank:=@rank+1 then @rank
end as Rank From Scores ,(select @rank:=0,@score:=NULL) a
order by Score desc;

Department Top Three Salaries (Hard)

Description:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.
Table : Employee
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+
The Department table holds all departments of the company.
Table : Department
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Q:Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.
A:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# amazing= =..不知道怎么乱搞就accept了。。。
# Write your MySQL query statement below
select c.Name,b.Name,Salary from Department c, (
select Name,DepartmentId,Salary,
case
    when @salary=Salary and @depid=DepartmentId then @rank
    when @salary=Salary and @depid<>@depid:=DepartmentId then @rank:=1
    when @salary:=Salary then
    case
        when @depid=DepartmentId then @rank:=@rank+1
        when @depid:=DepartmentId then @rank:=1
    end
    when @depid=DepartmentId then @rank:=@rank+1
    when @depid:=DepartmentId then @rank:=1
end as rank from Employee ,(select @rank:=0,@salary:=null,@depid:=null)a
order by DepartmentId asc,Salary desc)b where b.DepartmentId=c.Id and b.rank<=3