leetcode_175_Combine Two Tables
Combine Two Tables
题:
Table: Person
<span style="font-size:18px;">+-------------+---------+
| Column Name | Type |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId is the primary key column for this table.</span>
Table: Address
<span style="font-size:18px;">+-------------+---------+
| Column Name | Type |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId is the primary key column for this table.</span>
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:
<span style="font-size:18px;">FirstName, LastName, City, State</span>
解:
<span style="font-size:18px;">select p.FirstName,p.LastName,a.City,a.State from Person p left outer join Address a using(PersonId);</span>
leetcode_176_Second Highest Salary
Second Highest Salary
题:
Write a SQL query to get the second highest salary from the Employee
table.
<span style="font-size:18px;">+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+</span>
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
.
解:
1.建表;
3.使用limit选取第二大工资;
4.使用双重select max(Salary)来选取第二大工资;
leetcode_177_Nth Highest Salary
Nth Highest Salary
题:
Write a SQL query to get the nth highest salary from the Employee
table.
<span style="font-size:18px;">+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+</span>
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
.
解:
<span style="font-size:18px;">CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare N1 int;
set N1=N-1;
RETURN (
# Write your MySQL query statement below.
#select distinct Salary from Employee order by Salary desc limit N1,1
select Salary from (select distinct Salary from Employee) Ta order by Salary desc limit N1,1
);
END</span>
注意:上述代码中两种解题代码没有本质区别。
leetcode_178_Rank Scores
leetcode_178_Rank Scores
题:
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):
<span style="font-size:18px;">+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+</span>
解:
<span style="font-size:18px;">select ss1.Score , count(ss2.Score) as Rank from
(select * from Scores)ss1 left outer join
(select distinct Score from Scores)ss2
on(ss1.Score<=ss2.Score) group by ss1.Id order by ss1.Score desc;</span>
leetcode_180_Consecutive Numbers
leetcode_180_Consecutive Numbers
题:
Write a SQL query to find all numbers that appear at least three times consecutively.
<span style="font-size:18px;">+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+</span>
For example, given the above Logs
table, 1
is the only number that appears consecutively for at least three times.
解:
<span style="font-size:18px;"># Write your MySQL query statement below
select distinct T1.Num as ConsecutiveNums from
Logs T1,Logs T2,Logs T3 where
T1.Id+1=T2.Id and T1.Id+2=T3.Id and T1.Num=T2.Num and T1.Num=T3.Num;</span>
leetcode_181_ Employees Earning More Than Their Managers
181_ 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.
<span style="font-size:18px;">+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+</span>
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.
<span style="font-size:18px;"><span style="font-size:18px;">+----------+
| Employee |
+----------+
| Joe |
+----------+</span></span>
解:
<span style="font-size:18px;"><span style="font-size:18px;"># Write your MySQL query statement below
select e1.Name as Employee from Employee e1 inner join
Employee e2 on e1.ManagerId=e2.Id and e1.Salary>e2.Salary;</span></span>
leetcode_182_Duplicate Emails
leetcode_182_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:
<span style="font-size:18px;">+---------+
| Email |
+---------+
| a@b.com |
+---------+</span>
Note: All emails are in lowercase.
解:
<span style="font-size:18px;"># Write your MySQL query statement below
select Email from Person group by Email having count(8)>1;</span>
leetcode_183_Customers Who Never Order
leetcode_183_Customers Who Never Order
题:
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
.
<span style="font-size:18px;">+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+</span>
Using the above tables as example, return the following:<span style="font-size:18px;">+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+</span>
解:
<span style="font-size:18px;"># Write your MySQL query statement below
select Name as Customers from Customers left join Orders on Customers.Id=Orders.CustomerId where Orders.Id is Null;</span>
leetcode_184_Department Highest Salary
题:
The Employee
table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.
+----+-------+--------+--------------+
| 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.
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
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.
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解:
<span style="font-size:18px;"># Write your MySQL query statement below
select D.Name AS Department, E1.Name as Employee, Temp.Salary from Employee E1
join (select DepartmentId, MAX(Salary) as Salary from Employee group by DepartmentId) Temp
using(DepartmentId, Salary)join Department D on D.Id = Temp.DepartmentId;</span>
leetcode_185_Department Top Three Salaries
leetcode_185_Department Top Three Salaries
题:
The Employee
table holds all employees. Every employee has an Id, and there is also a column for the department Id.
+----+-------+--------+--------------+
| 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.
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
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.
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解:
<span style="font-size:18px;"># Write your MySQL query statement below
SELECT D.Name AS Department, E.Name AS Employee, E.Salary AS Salary
FROM Employee E, Department D
WHERE (SELECT COUNT(DISTINCT(Salary)) FROM Employee
WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary) < 3
AND E.DepartmentId = D.Id
ORDER BY E.DepartmentId, E.Salary DESC;</span>
leetcode_196_Delete Duplicate Emails
leetcode_196_Delete Duplicate Emails
题:
Write a SQL query to delete all duplicate email entries in a table named Person
, keeping only unique emails based on its smallest Id.
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id is the primary key column for this table.
For example, after running your query, the above
Person
table should have the following rows:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
解:
<span style="font-size:18px;"># Write your MySQL query statement below
delete p1 from Person p1,Person p2
where p1.Email=p2.Email and p1.Id>p2.Id;</span>
删除:结果:
leetcode_197_Rising Temperature
Rising Temperature
题:
Given a Weather
table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------+------------------+
For example, return the following Ids for the above Weather table:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
解:
<span style="font-size:18px;"># Write your MySQL query statement below
select w2.Id from Weather w1 join Weather w2
where to_days(w1.Date)+1=to_days(w2.Date) and w2.Temperature>w1.Temperature;</span>