leetcode
175.
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
答案：
左连接： 从左表那里返回所有的行，即使在右表中没有匹配的行。
SELECT FirstName, LastName, City, State FROM Person LEFT JOIN Address ON Person.PersonId = Address.PersonId
176.
编写一个 SQL 查询语句，获取Employee表中第二高的薪水（Salary）。
答案：
此题可以分为两块考虑，第一块是最高的薪水，第二个是比最高薪水小的最高的薪水
select Max(Salary) SecondHighestSalary from Employee where (select Max(Salary)from Employee) > Salary
Max(Salary) SecondHighestSalary(在结果中，用SecondHighestSalary替代Max(Salary)作为COLUMN NAME)
181.
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 | +----------+
答案：
select E.Name Employee from Employee E, Employee H where E.ManagerId = H.Id and E.Salary > H.Salary
182.
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.
答案：
使用group by 和having子句
select Email from Person group by Email having count(Email)>1
183.
Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.
Table: Customers.
+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+
Table: Orders.
+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+
Using the above tables as example, return the following:
+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
答案：
select Name Customers from Customers left join Orders on Customers.Id = Orders.CustomerId where Orders.Id is null