175.Combine Two Tables

Problem

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.
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:
FirstName, LastName, City, State

Answer

使用外部联结，显示Person表中的所有行

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

176.Second Highest Salary

Problem

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

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the second highest salary is 200.If there is no second highest salary, then the query should return null.

Answer

利用子查询和过滤检索，排除掉最大的Salary

Select max(Salary) as SecondHighestSalary
from Employee
where Salary<(Select max(Salary) from Employee);

177.Nth Highest Salary

Problem

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

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

Answer

利用降序排序以及对输出结果的限制求解。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT ;
SET M=N-1;
RETURN(
Select Dinstict Salary from Employee
order by Salary desc
limit M,1
); 
END

178. Rank Scores

Problem

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.

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

Answer

利用聚集函数count()计算比自己分数高的作为rank

select Scores.Score,count(Ranking.Score) as Rank
from Scores,(Select distinct Score from Scores)Ranking
where Scores.Score<=Ranking.Scores 
group by Scores.Id
order by Scores.Score desc;

180. Consecutive Numbers

Problem

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

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

Answer

利用自联结在一个表中进行查询三个连续的Num相同的Num。

Select distinct l1.Num as ConsecutiveNums
from Logs l1,Logs l2,Logs l3
where l1.Id=l2.Id-1 and l2.Id=l3.Id-1
and l1.Num=l2.Num and l3.Num=l2.Num;

181. Employees Earning More Than Their Managers

Problem

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    |
+----------+

Answer

依旧利用自联结在一个表中进行查询

Select e1.Name as Employee
from Employee e1,Employee e2
where e1.ManagerId=e2.ManagerId and e1.Salary>e2.Salary;

182. Duplicate Emails

Problem

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.

Answer

依旧是使用自联结

select distinct p1.Email
from Person p1,Person p2
where p1.Email=p2.Email and p1.Id!=p2.Id;