181. Employees who exceed the manager's income
Employee The table contains all employees, and their managers belong to employees. Every employee has one Id，In addition, there is a list of managers corresponding to employees Id. +----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+ given Employee Table, write a SQL Query, which can get the names of employees whose income exceeds their managers. In the table above, Joe Is the only employee who earns more than his manager. +----------+ | Employee | +----------+ | Joe | +----------+
Idea: first associate the employee manager into a table, and then filter where.
select a.Name as Employee from(
employee a
inner join
employee b
on a.managerid = b.id and a.salary > b.salary
)
182. Find duplicate email addresses
Write a SQL Query, find Person All duplicate email addresses in the table. Example: +----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+ Based on the above input, your query should return the following results: +---------+ | Email | +---------+ | a@b.com | +---------+
Idea: find the mailbox name with count greater than 1.
select person.Email from person group by person.Email having count(person.Email) > 1
183. Customers who never order
A website contains two tables, Customers Table and Orders Watch. Write a SQL Query to find all customers who never order anything. Customers Table: +----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+ Orders Table: +----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+ For example, given the above table, your query should return: +-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
Idea: first get a table with purchase records (the corresponding field of the two tables is inner join), and then use this table t to filter out all people in the customer table whose id is not in t.
select customers.name Customers from(
Customers
)where customers.id not in (select customers.id from(
customers
inner join
orders
on customers.id = orders.customerid
)
)
196. Delete duplicate e-mail addresses
Write a SQL Query to delete Person All duplicate e-mail addresses in the table are reserved in the duplicate e-mail addresses Id The smallest one. +----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Id Is the primary key of this table. For example, after running your query, the above Person The table should return the following rows: +----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+
Idea: first associate the two person s. The filtering condition is p1.id > p2.id. delete the P1 that meets the condition, and the remaining is the smallest.
delete p1 from(
person p1
inner join
person p2
on p1.email = p2.email
)where p1.id > p2.id
197. Rising temperature
surface Weather +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | recordDate | date | | temperature | int | +---------------+---------+ id Is the primary key of this table This table contains temperature information for a specific date Write a SQL Query to find all dates with higher temperatures than previous (yesterday's) dates id . The returned results do not require order. The query result format is as follows: Weather +----+------------+-------------+ | id | recordDate | Temperature | +----+------------+-------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +----+------------+-------------+ Result table: +----+ | id | +----+ | 2 | | 4 | +----+ 2015-01-02 The temperature is higher than the previous day (10 -> 25) 2015-01-04 The temperature is higher than the previous day (20 -> 30)
Idea: first associate the two weather tables, and then use the function DATEDIFF to judge the difference between the two dates (1) and the temperature of the next day is higher than that of the previous day
Finally, select the corresponding id.
select w1.Id from(
weather w1
inner join
weather w2
on DATEDIFF(w1.recordDate,w2.recordDate) = 1 and w1.temperature > w2.temperature
);
595. Large countries
Here's one World surface +-----------------+------------+------------+--------------+---------------+ | 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 | +-----------------+------------+------------+--------------+---------------+ If a country has an area of more than 3 million square kilometers or a population of more than 25 million, then the country is a big country. Write a SQL Query and output the name, population and area of all large countries in the table. For example, according to the above table, we should output: +--------------+-------------+--------------+ | name | population | area | +--------------+-------------+--------------+ | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 | +--------------+-------------+--------------+
Idea: water problem.
select name, population, area from world where area > 3000000 OR population > 25000000
596. Classes with more than five students
There is one courses Watch, yes: student (student) and class (curriculum). Please list all classes with more than or equal to 5 students. For example, 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 | +---------+
Idea: screening criteria: according to the corresponding courses with different number of students > = 5 after course group by count.
select class from courses group by class having count(distinct student) >= 5
620. Interesting movies
A new cinema has been opened in a city, which has attracted many people to watch movies. The cinema pays special attention to user experience and has a special LED The display board makes movie recommendations, which publishes movie reviews and related movie descriptions. As the director of the Information Department of the cinema, you need to write a SQL Query to find out all movies described as non boring (Not boring) And id Is an odd number of movies. Please rank the results rating Arrange. For example, the following 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 above example, the correct output is: +---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 5 | House card| Interesting| 9.1 | | 1 | War | great 3D | 8.9 | +---------+-----------+--------------+-----------+
Idea: the topic has explained the screening conditions clearly, and you can follow them.
select * from cinema where description <> 'boring' and id %2 = 1 order by rating desc
176. The second highest salary
Write a SQL Query, get Employee The second highest salary in the table( Salary) . +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ For example, the above Employee Watch, SQL The query should return 200 as the second highest salary. If there is no second highest salary, the query should return null. +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+
Idea: first sort the table in descending order of salary, then use the limit function to select the two largest ones and return them as sub table t1, and then select the one with the smallest salary in T1. Note that the special judgment when null is that the number of salary in sub table t1 is less than 2
select if(count(t1.salary)>1, min(t1.salary), null ) SecondHighestSalary from (
select distinct salary from employee
order by salary desc
limit 0,2
)t1;
177. The nth highest salary
Write a SQL Query, get Employee No. in the table n High salary( Salary). +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ For example, the above Employee Watch, n = 2 When, the second highest salary 200 should be returned. If there is no second highest salary n High salary, then the query should return null. +------------------------+ | getNthHighestSalary(2) | +------------------------+ | 200 | +------------------------+
Idea: the same as above. Only select the maximum n in t1, and then the special judgment condition is when the salary of t1 is less than n.
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
select if(count(t1.salary)>=n, min(t1.salary), null ) SecondHighestSalary from (
select distinct salary from employee
order by salary desc
limit 0,n
)t1
);
END
178. Score ranking
Write a SQL Query to achieve score ranking. If the two scores are the same, the two scores are ranked( Rank)Same. Please note that the next ranking after bisection should be the next consecutive integer value. In other words, there should be no "interval" between ranking. +----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+ For example, according to the above given Scores Table, your query should return (sorted from high to low): +-------+------+ | Score | Rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+
Idea: the output table can be regarded as two parts. The left half is arranged in descending order of scores, and the right is rank, which can be transformed into a number less than the current score (in the case of de duplication).
Now focus on solving the right part. Let's first associate the de duplicated tables S2 and S3, provided that s3.socre > s2.socre, and then count the number of occurrences of each score num according to the score group by. At this time, num+1 is Rank.
Then it is found that the maximum score is not in the associated table, so another Scores table s1 is associated with the above table. When selecting, the null value is rank=1
select s1.Score, ifnull(s4.Rank,1) 'Rank' from(
Scores s1
Left join #The s4 table is used to generate the rank field and find out how many score s are smaller than it
(select s2.score S1, count(s2.score)+1 'Rank' from(
(select distinct score from Scores) s2
inner join
(select distinct score from Scores) s3
on s3.score > s2.score
) group by s2.score) s4
on s1.score = s4.S1
)order by s4.Rank
180. Consecutive figures
Table: Logs +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id Is the primary key of this table. Write a SQL Query to find all numbers that appear at least three times in a row. The data in the returned result table can be arranged in any order. The query result format is shown in the following example: Logs Table: +----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ Result Table: +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+ 1 Is the only number that appears at least three times in a row.
Idea: associate the three logs tables, and then select the corresponding Num with three consecutive table IDs and the same Num.
select distinct a.num ConsecutiveNums from
logs a,
logs b,
logs c
where
a.id = b.id + 1
and b.id = c.id + 1
and a.num = b.num
and b.num = c.num
184. The highest paid employees in the Department
Employee The table contains all employee information, and each employee has its corresponding Id, salary and 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 | +----+-------+--------+--------------+ Department The table contains information about all departments of the company. +----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+ Write a SQL Query and find out the employees with the highest salary in each department. For the above table, your SQL The query should return the following rows (the order of the rows does not matter). +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Jim | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+
Idea: first get the maximum salary of each department, get table t1, and then use employee join t1 on the same department and salary = T1. Maximum salary to filter out employee information.
#Record the maximum value of each department according to the Department group by, and then use the inner join employee table to see who has the maximum value of the Department.
select t1.Department, e.name Employee, e.salary Salary from(
(select d.name Department, max(e.salary) Salary, e.id Id ,
e.DepartmentId Did from(
Employee e
inner join
Department d
on e.DepartmentId = d.Id
)group by Department
order by Salary desc) t1
inner join
Employee e
on e.salary = t1.salary and e.DepartmentId = t1.Did
)
626. Change seats
Xiaomei is an information technology teacher in a middle school. She has one seat The seat table is usually used to store students' names and their corresponding seats id. In which column id It is continuously increasing Xiaomei wants to change the seats of two adjacent students. Can you write one for her SQL query To output the results Xiaomei wants? Example: +---------+---------+ | id | student | +---------+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +---------+---------+ If the data input is in the above table, the output results are as follows: +---------+---------+ | id | student | +---------+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +---------+---------+
Idea: we know that all odd numbers exchange for the person with id+1 and even numbers exchange for the person with id-1. Then we can associate the two expressions, and the correlation condition is the person we are looking for. Then output the id of table t1 and the student of table t2.
Note that the last odd number does not need to be exchanged, so use the ifnull function for special judgment.
select s1.id, ifnull(s2.student, s1.student) student from(
seat s1
Left join
seat s2
on s2.id = if(s1.id%2, s1.id+1, s1.id-1)
);