175. Combine Two Tables [Easy]
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
複製程式碼
DROP TABLE IF EXISTS Person;
CREATE TABLE Person(
PERSONID INT,
FIRSTNAME VARCHAR(50),
LASTNAME VARCHAR(50),
PRIMARY KEY (PERSONID)
);
DROP TABLE IF EXISTS Address;
CREATE TABLE Address(
ADDRESSID INT,
PERSONID INT,
CITY VARCHAR(50),
STATE VARCHAR(50),
PRIMARY KEY (ADDRESSID)
);
insert into Person values (1, 'F1', 'L1'), (2, 'F2', 'L2'), (3, 'F3', 'L3');
insert into Address values (1, 1, 'SZ', 'FT'), (3, 2, 'GZ', 'PY');
複製程式碼
select FirstName, LastName, City, State
from Person left join Address on Person.PersonId = Address.PersonId
複製程式碼
176. Second Highest Salary [Easy]
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 |
+---------------------+
複製程式碼
DROP table if exists Employee;
create table Employee(
Id int,
Salary int,
primary key (Id)
);
insert into Employee values (1, 100), (2, 200), (3, 300);
複製程式碼
- Solution
- 因為找不到時要返回null，所以外部再加一層select
select (select DISTINCT Salary from Employee order by Salary desc limit 1, 1) SecondHighestSalary
複製程式碼
177. Nth Highest Salary [Medium]
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 |
+------------------------+
複製程式碼
DROP table if exists Employee;
create table Employee (
Id int,
Salary int,
primary key (Id)
);
insert into Employee
values (1, 100),
(2, 200),
(3, 300);
複製程式碼
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N, 1
);
END
複製程式碼
178. Rank Scores [Medium]
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 |
+-------+------+
複製程式碼
DROP table if exists Scores;
create table Scores (
Id int,
Scores double,
primary key (Id)
);
insert into Scores
values (1, 3.5),
(2, 3.65),
(3, 4.00),
(4, 3.85),
(5, 4.00),
(6, 3.65);
複製程式碼
select s1.Score, COUNT(DISTINCT s2.Score) Rank
from Scores s1
inner join Scores s2 on s1.Score <= s2.Score
group by s1.Id
order by s1.Score desc
複製程式碼
180. Consecutive Numbers [Medium]
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 |
+-----------------+
複製程式碼
DROP table if exists Logs;
create table Logs (
Id int,
Num int,
primary key (Id)
);
insert into Logs
values (1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 1),
(6, 2);
複製程式碼
- Solution
- 用3張表做連線，取id連續且num相等，然後注意需要對結果去重
解法1：
select distinct t1.Num as ConsecutiveNums
from Logs t1,
Logs t2,
Logs t3
where t1.id = t2.id - 1
and t2.id = t3.id - 1
and t1.num = t2.num
and t2.num = t3.num
解法2：
select distinct t1.Num as ConsecutiveNums
from Logs t1
join Logs t2 on t1.id = t2.id - 1
join Logs t3 on t2.id = t3.id - 1
where t1.num = t2.num and t2.num = t3.num
複製程式碼
181. Employees Earning More Than Their Managers [Easy]
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 |
+----------+
複製程式碼
DROP table if exists Employee;
create table Employee (
Id int,
Name varchar(20),
Salary int,
ManagerId int,
primary key (Id)
);
insert into Employee
values (1, 'Joe', 700, 3),
(2, 'Henry', 800, 4),
(3, 'Sam', 600, NULL),
(4, 'Max', 900, NUll);
複製程式碼
select t1.name as Employee
from Employee t1 join Employee t2 on t1.ManagerId = t2.id
where t1.Salary > t2.Salary
複製程式碼
182. Duplicate Emails [Easy]
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.
複製程式碼
DROP table if exists Person;
create table Person
(
Id int,
Email varchar(20),
primary key (Id)
);
insert into Person
values (1, 'a@b.com'),
(2, 'c@d.com'),
(3, 'a@b.com');
複製程式碼
select email
from (
select email, count(email) as times
from Person
group by email) t
where t.times > 1
複製程式碼
183. Customers Who Never Order [Easy]
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 |
+-----------+
複製程式碼
DROP table if exists Customers;
create table Customers
(
Id int,
Name varchar(20),
primary key (Id)
);
insert into Customers
values (1, 'Joe'),
(2, 'Henry'),
(3, 'Sam'),
(4, 'Max');
DROP table if exists Orders;
create table Orders
(
Id int,
CustomerId int,
primary key (Id)
);
insert into Orders
values (1, 3),
(2, 1);
複製程式碼
select c.Name as Customers
from Customers c left join Orders o
on c.id = o.CustomerId
where o.CustomerId is null
複製程式碼
184. Department Highest Salary [Medium]
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 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | 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, your SQL query should return the following rows (order of rows does not matter).
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
Explanation:
Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.
複製程式碼
DROP table if exists Employee;
create table Employee
(
Id int,
Name varchar(20),
Salary int,
DepartmentId int ,
primary key (Id)
);
insert into Employee
values (1, 'Joe', 70000, 1),
(2, 'Jim', 90000, 1),
(3, 'Henry', 80000, 2),
(4, 'Sam', 60000, 2),
(5, 'Max', 90000, 1);
DROP table if exists department;
create table Department
(
Id int,
Name varchar(20),
primary key (Id)
);
insert into Department
values (1, "IT"),
(2, "Sales");
複製程式碼
- Solution
- 先使用group by和max函式獲取每個部門的最大薪資，然後在進行一次連線查詢
select t.Name as Department, e1.Name as Employee, e1.Salary as Salary
from Employee e1
join (
select MAX(Salary) as Salary, e.DepartmentId, d.Name
from Employee e join Department d on e.DepartmentId = d.Id
group by DepartmentId) t
on e1.DepartmentId = t.DepartmentId and e1.Salary = t.Salary
複製程式碼
185.Department Top Three Salaries [Hard]
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 |
+------------+----------+--------+
複製程式碼
DROP table if exists Employee;
create table Employee
(
Id int,
Name varchar(20),
Salary int,
DepartmentId int ,
primary key (Id)
);
insert into Employee
values (1, 'Joe', 70000, 1),
(2, 'Henry', 80000, 2),
(3, 'Sam', 60000, 2),
(4, 'Max', 90000, 1),
(5, 'Janet', 69000, 1),
(6, 'Randy', 85000, 1);
DROP table if exists department;
create table Department
(
Id int,
Name varchar(20),
primary key (Id)
);
insert into Department
values (1, "IT"),
(2, "Sales");
複製程式碼
- Solution
- 內外兩張Employee連線查詢，查詢出Employee表中同部門，比自己薪水高的數目小於3的記錄，即是每個部門的前三
select d.Name as Department, e1.Name as Employee, e1.Salary
from Employee e1, Department d
where (select count(distinct e2.Salary)
from Employee e2
where e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary) < 3
and e1.DepartmentId = d.id
order by e1.DepartmentId desc, e1.Salary desc
複製程式碼
196. Delete Duplicate Emails [Easy]
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 |
+----+------------------+
Note:
Your output is the whole Person table after executing your sql. Use delete statement.
複製程式碼
DROP table if exists Person;
create table Person
(
Id int,
Email varchar(50),
primary key (Id)
);
insert into Person
values (1, 'joe@example.com'),
(2, 'bob@example.com'),
(3, 'joe@example.com');
複製程式碼
連線：
delete p1
from Person p1,
Person p2
where p1.Email = p2.Email
and p1.Id > p2.Id
子查詢：注意內部多做了一次select查詢，否則執行會報錯
delete
from Person
where Id in (
select id
from (select distinct p2.id
from Person p1 join Person p2 on p1.Email = p2.Email and p1.Id < p2.Id) as m)
複製程式碼
197. Rising Temperature [Easy]
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) | RecordDate(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 |
+----+
複製程式碼
DROP table if exists Weather;
create table Weather
(
Id int,
RecordDate Date,
Temperature int,
primary key (Id)
);
insert into Weather
values (1, '2015-01-01', 10),
(2, '2015-01-02', 25),
(3, '2015-01-03', 20),
(4, '2015-01-04', 30);
複製程式碼
- Solution
- 日期間的差距天數使用Datediff函式
select w1.id
from Weather w1 join Weather w2
on Datediff(w1.RecordDate, w2.RecordDate) = 1
and w1.Temperature > w2.Temperature
複製程式碼
262. Trips and Users [Hard]
The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2013-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
| 3 | 3 | 12 | 6 | completed |2013-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
| 5 | 1 | 10 | 1 | completed |2013-10-02|
| 6 | 2 | 11 | 6 | completed |2013-10-02|
| 7 | 3 | 12 | 6 | completed |2013-10-02|
| 8 | 2 | 12 | 12 | completed |2013-10-03|
| 9 | 3 | 10 | 12 | completed |2013-10-03|
| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
Write a SQL query to find the cancellation rate of requests made by unbanned users between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
Credits:
Special thanks to @cak1erlizhou for contributing this question, writing the problem description and adding part of the test cases.
複製程式碼
DROP table if exists Users;
create table Users
(
Users_Id int,
Banned ENUM ('YES', 'NO'),
Role ENUM ('client', 'driver'),
primary key (Users_Id)
);
insert into Users
values (1, 'No', 'client'),
(2, 'Yes', 'client'),
(3, 'No', 'client'),
(4, 'No', 'client'),
(10, 'No', 'driver'),
(11, 'No', 'driver'),
(12, 'No', 'driver'),
(13, 'No', 'driver');
Drop table if exists Trips;
create table Trips(
Id int,
Client_id int references Users(Users_Id),
Driver_Id int references Users(Users_Id),
City_Id int,
Status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'),
Request_at date,
primary key (Id)
);
insert into Trips
values (1, 1, 10, 1, 'completed', '2013-10-01'),
(2, 2, 11, 1, 'cancelled_by_driver', '2013-10-01'),
(3, 3, 12, 6, 'completed', '2013-10-01'),
(4, 4, 13, 6, 'cancelled_by_client', '2013-10-01'),
(5, 1, 10, 1, 'completed', '2013-10-02'),
(6, 2, 11, 6, 'completed', '2013-10-02'),
(7, 3, 12, 6, 'completed', '2013-10-02'),
(8, 2, 12, 12, 'completed', '2013-10-03'),
(9, 3, 10, 12, 'completed', '2013-10-03'),
(10, 4, 13, 12, 'cancelled_by_driver', '2013-10-03');
複製程式碼
- Solution
- 外層查詢出每日的行程數，round中查詢出每日取消的行程數
select a.Request_at as Day, round((
select count(*) from Trips as b left join Users as c on b.Client_Id = c.Users_Id
where (b.Status = 'cancelled_by_client' or b.Status ='cancelled_by_driver')
and c.Banned = 'No'
and b.Request_at = a.Request_at)/count(a.Status),2) as "Cancellation Rate"
from Trips as a left join Users as d
on a.Client_Id = d.Users_Id
where d.Banned = 'No' and a.Request_at >= date("2013-10-01") and a.Request_at <= date("2013-10-03")
group by a.Request_at
order by a.Request_at
複製程式碼
595. Big Countries [Easy]
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 |
+--------------+-------------+--------------+
複製程式碼
DROP table if exists World;
create table World
(
name varchar(50),
continent varchar(50),
area integer,
population integer,
gpd integer,
primary key (name)
);
insert into World
values ('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);
複製程式碼
select t.name, t.population, t.area
from World t
where t.population > 25000000 or t.area > 3000000
複製程式碼
596. Classes More Than 5 Students [Easy]
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.
複製程式碼
DROP table if exists courses;
create table courses
(
student varchar(50),
class varchar(50)
);
insert into courses
values ('A', 'Math'),
('B', 'English'),
('C', 'Math'),
('D', 'Biology'),
('E', 'Math'),
('F', 'Computer'),
('G', 'Math'),
('H', 'Math'),
('I', 'Math');
複製程式碼
- Solution
- 注意課程和學生對應關係可能有重複，需要先進行一次distinct
select t2.class
from (select t1.class, count(t1.class) as count
from (select distinct class, student from courses) t1
group by t1.class) t2
where t2.count >= 5
複製程式碼
620. Not Boring Movies [Easy]
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 |
+---------+-----------+--------------+-----------+
複製程式碼
DROP table if exists cinema;
create table cinema
(
id int,
movie varchar(50),
description varchar(100),
rating double,
primary key (id)
);
insert into cinema
values (1, 'War', 'great3D', 8.9),
(2, 'Science', 'fiction', 8.5),
(3, 'irish', 'boring', 6.2),
(4, 'Icesong', 'Fantacy', 8.6),
(5, 'Housecard', 'Interesting', 9.1);
複製程式碼
select *
from cinema t
where t.description != 'boring'
and t.id MOD 2 = 1
order by t.rating desc
複製程式碼
601. Human Traffic of Stadium [Hard]
X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, visit_date, people
Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).
For example, the table stadium:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
For the sample data above, the output is:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
Note:
Each day only have one row record, and the dates are increasing with id increasing.
複製程式碼
DROP table if exists stadium;
create table stadium
(
id int,
visit_date DATE,
people int,
primary key (id)
);
insert into stadium
values (1, '2017-01-01', 10),
(2, '2017-01-02', 109),
(3, '2017-01-03', 150),
(4, '2017-01-04', 99),
(5, '2017-01-05', 145),
(6, '2017-01-06', 1455),
(7, '2017-01-07', 199),
(8, '2017-01-08', 188);
複製程式碼
- Solution
- 3張表做連線，找出日期相鄰的3天，且每天的人流量均大於等於100，最後做排序
select distinct s1.*
from stadium s1,
stadium s2,
stadium s3
where s1.people >= 100 and s2.people >= 100 and s3.people >= 100
AND (
(s1.id + 1 = s2.id and s1.id + 2 = s3.id)
or
(s1.id - 1 = s2.id and s1.id + 1 = s3.id)
or
(s1.id - 2 = s2.id and s1.id - 1 = s3.id)
)
order by s1.id asc
複製程式碼
626. Exchange Seats [Medium]
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.
複製程式碼
DROP table if exists seat;
create table seat
(
id int,
student varchar(50),
primary key (id)
);
insert into seat
values (1, 'Abbot'),
(2, 'Doris'),
(3, 'Emerson'),
(4, 'Green'),
(5, 'Jeames');
複製程式碼
- Solution
- 使用union，偶數的id-1，奇數的id+1，排除最後1個奇數id，union後再加上最後1個奇數id排序
select s1.id-1 as id, s1.student from seat s1
where s1.id mod 2 = 0 union
select s2.id+1 as id, s2.student from seat s2
where s2.id mod 2 = 1 and s2.id != (select max(s3.id) from seat s3) union
select s4.id, s4.student from seat s4
where s4.id mod 2 = 1 and s4.id = (select max(s5.id) from seat s5)
order by id asc
複製程式碼
627. Swap Salary [Easy]
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 statement and no intermediate temp table.
Note that you must write a single update statement, DO NOT write any select statement for this problem.
Example:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
After running your update statement, 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 |
複製程式碼
DROP table if exists salary;
create table salary
(
id int,
name varchar(1),
sex varchar(1),
salary int,
primary key (id)
);
insert into salary
values (1, 'A', 'm', 2500),
(2, 'B', 'f', 1500),
(3, 'C', 'm', 5500),
(4, 'D', 'f', 500);
複製程式碼
- Solution
- 瞭解char和ascii函式以及^異或，字元異或自身為0,0異或其他字元得其他字元
update salary
set sex = CHAR( ASCII(sex) ^ ASCII('f') ^ ASCII('m'))
複製程式碼