175. 組合兩個表
表1: Person
+-------------+---------+
| 列名 | 類型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是上表主鍵
表2: Address
+-------------+---------+
| 列名 | 類型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId 是上表主鍵
編寫一個 SQL 查詢轻庆，滿足條件：無論 person 是否有地址信息鸽心，都需要基于上述兩表提供 person 的以下信息：
FirstName, LastName, City, State
select p.FirstName,p.LastName,a.City,a.State from person p left outer join Address a on(p.personId = a.personId);
176. 第二高的薪水
編寫一個 SQL 查詢虹统，獲取 Employee 表中第二高的薪水（Salary） 璧亚。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表望蜡，SQL查詢應(yīng)該返回 200 作為第二高的薪水境氢。如果不存在第二高的薪水畦浓，那么查詢應(yīng)返回 null直奋。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
select ifnull((select distinct Salary as SecondHighestSalary from Employee order by Salary desc limit 1,1),null) as SecondHighestSalary;
184. 部門工資最高的員工
難度中等270
Employee 表包含所有員工信息，每個員工有其對應(yīng)的 Id, salary 和 department Id九杂。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部門的信息颁湖。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
編寫一個 SQL 查詢，找出每個部門工資最高的員工例隆。對于上述表甥捺，您的 SQL 查詢應(yīng)返回以下行（行的順序無關(guān)緊要）。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解釋：
Max 和 Jim 在 IT 部門的工資都是最高的镀层，Henry 在銷售部的工資最高镰禾。
select a.Department,a.Employee,a.Salary from (select d.Name as Department,e.Name as Employee,e.Salary as Salary from Employee e right outer join Department d on(e.DepartmentId = d.Id)) as a,(select max(e.Salary) as maxSalary, d.Name as Department from Employee e,Department d where e.DepartmentId = d.Id group by d.Name) as b where a.Department = b.Department and a.Salary = b.maxSalary;
