leetcode 数据库题目
595.Big Countries
There is a table World
| name | continent | area | population | gdp |
|---|---|---|---|---|
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million.
Write a SQL solution to output big countries’ name, population and area.
For example, according to the above table, we should output:
| name | population | area |
|---|---|---|
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
答案： select name,population,area from World where area>3000000 or population>25000000;
知识点：where语句中or的使用
627.Swap Salary
Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.
For example:
| id | name | sex | salary |
|---|---|---|---|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
After running your query, the above salary table should have the following rows:
| id | name | sex | salary |
|---|---|---|---|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
答案：update salary set sex=if(sex=’m’,’f’,’m’); update salary set sex=case sex when ‘m’ then ‘f’ else ‘m’ end;
知识点：if case的使用
if 使用方法： if(参数=值,返回值1，返回值2)
case使用方法：
case 参数 when 值 then 返回值
else
end
620.Not Boring Movies
X city opened a new cinema, many people would like to go to this cinema. The cinema also gives out a poster indicating the movies’ ratings and descriptions.
Please write a SQL query to output movies with an odd numbered ID and a description that is not ‘boring’. Order the result by rating.
For example, table cinema:
| id | movie | description | rating |
|---|---|---|---|
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card | Interesting | 9.1 |
For the example above, the output should be:
| id | movie | description | rating |
|---|---|---|---|
| 5 | House card | Interesting | 9.1 |
| 1 | War | great 3D | 8.9 |
答案：select * from cinema where mod(id,2)=1 and description!=’boring’ order by rating desc;
知识点：odd number的判断使用mod函数或者%:mod(id,2)=1或者id%2=1
182.Duplicate Emails
Write a SQL query to find all duplicate emails in a table named Person.
| Id | |
|---|---|
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
For example, your query should return the following for the above table:
| a@b.com |
Note: All emails are in lowercase.
答案：select Email from Person group by Email having count(Email)>1;
知识点：统计次数
175.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
答案：select p.FirstName as FirstName,p.LastName as LastName,a.City as City, a.State as State from Person p left join Address a on p.PersonId=a.PersonId;
知识点：无论是否满足某个条件，left join或者right join的使用
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.
| 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 |
答案：1.select a.Name from Employee a inner join Employee b on a.ManagerId= b.Id wherea.Salary>b.Salary;
2.select a.Name from Employee a inner join Employee b on a.ManagerId= b.Id and a.Salary>b.Salary;
知识点：inner join
对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面 所有的连接条件都必需要放在ON后面，不然前面的所有LEFT,和RIGHT关联将作为摆设，而不起任何作用。
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.
| Id | CustomerId |
|---|---|
| 1 | 3 |
| 2 | 1 |
Using the above tables as example, return the following:
| Customers |
|---|
| Henry |
| Max |
答案：select Name as Customers from Customers where id not in (select distinct CustomerId from Orders);
知识点：取反，使用not in
197.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 |
答案：1.select w1.Id from Weather w1 inner join Weather w2 on datediff(w1.Date,w2.Date)=1 and w1.Temperature>w2.Temperature;
2.select w1.Id from Weather w1 inner join Weather w2 on TO_DAYS(w1.Date)=TO_DAYS(w2.Date)+1 and w1.Temperature>w2.Temperature;
知识点：自连接，时间处理 TO_DAYS(w1.Date)=TO_DAYS(w2.Date)+1 datediff(w1.Date,w2.Date)=1
596.Classes More Than 5 Students
There is a table courses with columns: student and class
Please list out all classes which have more than or equal to 5 students.
For example, the table:
| student | class |
|---|---|
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
Should output:
| class |
|---|
| Math |
Note:
The students should not be counted duplicate in each course.
答案：select class from courses group by class having count（distinct student)>=5;
知识点：group by 的基本使用 + distinct
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 | |
|---|---|
| 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 | |
|---|---|
| 1 | john@example.com |
| 2 | bob@example.com |
答案：1.delete p1 from Person p1,Person p2 where p1.email=p2.email and p1.id>p2.id;
2.delete from Person where id not in (select id from (select min(id) as id from Person group by Email) p);
知识点：自连接//group by//实现distinct 功能，删除冗余
扩充知识点：delete from 和 delete tablename from 的区别，从单表(一个表)中删除可以不跟tablename，联查删除需要跟tablename，答案1中使用联查的方式，所以要跟删除的表名。
176. 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 query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.
| SecondHighestSalary |
|---|
| 200 |
答案：select max(Salary) as SecondHighestSalary from Employee where Salary<(select max(Salary) from Employee);
知识点：第二大，返回值为NULL
我们看到题目中“If there is no second highest salary, then the query should return null.”，如果没有符合查询条件的记录，查询结果需要返回null。
补充知识点：返回值为null和没有返回值的区别
函数的返回值一般为null，而一般的字段返回值为空
1.使用isnull()方法进行判断，如isnull(sum(cnt),0)，其中0为默认值，如果sum(cnt)的结果为null，则赋值 0；
2.使用case when else流程控制语句，如可以用”case when sum(cnt) is null then 0 else sum(cnt) end”来代替sum(cnt)，其中0同上一条的意义相同。
3.使用coalesce（）方法，返回0而不是NULL，
SELECT COALESCE(SUM(name),0) FROM person WHERE id > 0
626. Exchange Seats
DescriptionHintsSubmissionsDiscussSolution SQL Schema DiscussPick One Mary is a teacher in a middle school and she has a table seat storing students’ names and their corresponding seat ids.
The column id is continuous increment.Mary wants to change seats for the adjacent students.Can you write a SQL query to output the result for Mary?
| id | student |
|---|---|
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
For the sample input, the output is:
| id | student |
|---|---|
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
Note:
If the number of students is odd, there is no need to change the last one’s seat.
答案：select id,student from (
select (id-1) as id,student from seat where id%2=0 union
select (case id when (select count(*) from seat) then id else id+1 end) as id,student from seat where id%2=1) s
order by id asc ;
知识点：query没有要求 update
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):
| Score | Rank |
|---|---|
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
答案：select Scores.Score, count(ranking.Score) as Rank from Scores, (select distinct Score from Scores) ranking where Scores.Score <= ranking.Score group by Scores.Id,Scores.Score order by Scores.Score desc;
知识点：这一题的关键在解题思路 因为名次要重复，所以可以先把distinct的名词选出来，然后根据<=的score数量得出排名
180. Consecutive Numbers
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.
| ConsecutiveNums |
|---|
| 1 |
答案：select 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 l2.num=l3.num;
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 |
答案：select temp.Name as Department,e.name as Employee,e.salary as Salary from Employee e, (select d.id,d.name,max(e.salary) as salary from Employee e, Department d where e.departmentid=d.id group by e.departmentid) temp where e.salary=temp.salary and e.departmentid= temp.id;
知识点：同一个部门可能存在相同的最高工资，不同部门也可能有相同的最高工资
177. 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.
| getNthHighestSalary(2) |
|---|
| 200 |
答案：
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN
declare M INT;
set M=N-1;
RETURN (
# Write your MySQL query statement below.
select DISTINCT SALARY from Employee order by SALARY DESC LIMIT M,1
);
END
知识点：自定义函数简单语法：
CREATE FUNCTION 函数名称(
变量名称 类型,
变量名称 类型
)
RETURN 类型
BEGIN
– 声明， 语句要完成的操作，
RETURN 变量;
END;