<1>Duplicate Emails
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 | +---------+
Solution:
# 找到person表中重复的Email
# 方案一:
# SELECT Email FROM Person GROUP BY Email WHERE count(*)>1 #由于合计函数 GROUP BY不能和 WHERE 同时使用所以引入HAVING。
SELECT Email
FROM Person
GROUP BY Email
HAVING count(*)>1
# 方案二
#使用JOIN ON
SELECT DISTINCT a.Email FROM Person aINNER JOIN Person
b ON (a.Email = b.Email) WHERE a.Id != b.Id
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<2>Combine Two Tables
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
Solution:
# Write your MySQL query statement below
SELECT Person.FirstName,Person.LastName,Address.City,Address.StateFROM PersonLEFT
JOIN Address ON Person.personId = Address.PersonId;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<3>Employees Earning More Than Their Managers
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 | +----------+
Solution:
SELECT e1.NAME as Employee FROM Employee e1 ,Employee
e2 where e1.ManagerId = e2.Idand e1.Salary>e2.Salary
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<4>Second Highest Salary
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
.
Solution:
SELECT
IFNULL( (SELECT
DISTINCT Salary
FROM Employee
ORDER
BY Salary
DESC
LIMIT 1,1) ,NULL)
AS SecondHighestSalary
<5>Nth Highest Salary
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
.
Solution:
CREATE
FUNCTION getNthHighestSalary(N INT)
RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
RETURN (
# Write your MySQL query statement below.
SELECT
IFNULL((SELECT
DISTINCT Salary
FROM Employee
ORDER
BY Salary DESC LIMIT M,1),NULL)
AS NHighestSalary
);
END