Force buckle Mysql practice

Recently in brush LeetCode SQL practice, here is a summary

175 Combine two tables

Topic describes

Given a salary table, as shown below, there are values m = male and f = female. Swap all f and m values (for example, change all f values to M and vice versa). An Update statement is required and there are no intermediate temporary tables.

Note that you must write an Update statement, not any Select statements.

Such as:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500
After running the update statement you wrote, you will get the following table:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

MySQL script:

-- ----------------------------
-- Table structure for `salary`
-- ----------------------------
DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary` (
  `id`int(11) NOT NULL,
  `name`varchar(10) NOT NULL,
  `sex`varchar(10) NOT NULL,
 `salary` int(11) NOT NULL,
  PRIMARYKEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of salary
-- ----------------------------
INSERT INTO `salary` VALUES ('1'.'A'.'m'.'2500');
INSERT INTO `salary` VALUES ('2'.'B'.'f'.'1500');
INSERT INTO `salary` VALUES ('3'.'C'.'m'.'5500');
INSERT INTO `salary` VALUES ('4'.'D'.'f'.'500');
Copy the code

The answer to this question is:

Method 1: Use the if function

IF(expr1, expr2, expr3) IF expr1 is TRUE, then IF() returns expr2; Otherwise the return value is expr3.

# Write your MySQL query statement below
UPDATE salary
SET sex = IF(sex = "f","m","f");
Copy the code

Method 2: Use case… when.. then.. else.. end

UPDATE salary 
SET sex  = (CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END);
Copy the code

176 – Second highest salary

Topic describes

Write an SQL query to get the second highest Salary in the Employee table.

Id	Salary
1	100
2	200
3	300
For example, the Employee table above, the SQL query should return 200 as the second highest salary. If no second highest salary exists, the query should return NULL.

SecondHighestSalary
200

Ontology method

Limit (1,1)

SELECT IFNULL((SELECT DISTINCT(Salary) 
FROM Employee
ORDER BY Salary DESC
LIMIT 1.1),null) AS SecondHighestSalary
Copy the code

Method 2 first query the maximum height value, and then query the maximum height less than this value.

# Write your MySQL query statement below
SELECT Max(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT Max(Salary) FROM Employee);
Copy the code

IFNULL()

IFNULL(expr1,expr2)
Copy the code

IFNULL() returns expr1 if expr1 is not NULL, otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

177 – The NTH highest salary

Topic describes

Write an SQL query to get the NTH highest Salary in the Employee table.

Id	Salary
1	100
2	200
3	300
For example, if n = 2 in the Employee table above, the second highest salary, 200, should be returned. If no NTH salary exists, the query should return NULL.

getNthHighestSalary(2)
200
MySQL script
“`sql
Create table If Not Exists Employee (Idint, Salary int);
Truncate table Employee;
insert into Employee (Id, Salary) values(‘1’, ‘100’);
insert into Employee (Id, Salary) values(‘2’, ‘200’);
insert into Employee (Id, Salary) values(‘3’, ‘300’);
` ` `

Ontology method

Search criteria:

1) Return the NTH highest salary
2) If no NTH salary exists, then the query should return NULL

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N- 1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT(
      SELECT DISTINCT Salary
      FROM Employee
      ORDER BY Salary DESC
      LIMIT 1 OFFSET N
      )
  );
END
Copy the code

Note: the LIMIT clause cannot be followed by an operation.

Or we could define a new variable x:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE x int;
  SET x = N- 1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT(
      SELECT DISTINCT Salary
      FROM Employee
      ORDER BY Salary DESC
      LIMIT 1 OFFSET x
      )
  );
END
Copy the code

178 – Score ranking

Topic describes

Write an SQL query to implement the score ranking. If two scores are the same, they Rank the same. Note that the next place after the split should be the next consecutive integer value. In other words, there should be no “gaps” between rankings.

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

For example, based on the given Scores table above, your query should return (in order of score from highest to lowest) :

Score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

MySQL script

Create table If Not Exists Scores (Id int,Score DECIMAL(3.2));
Truncate table Scores;
insert into Scores (Id, Score) values ('1'.'3.5');
insert into Scores (Id, Score) values ('2'.'3.65');
insert into Scores (Id, Score) values ('3'.'4.0');
insert into Scores (Id, Score) values ('4'.'3.85');
insert into Scores (Id, Score) values ('5'.'4.0');
insert into Scores (Id, Score) values ('6'.'3.65');
Copy the code

The answer to this question is:

Method 1

For each score, find out from the table how many non-repeating scores are greater than or equal to that score, and then arrange them in descending order.

# Write your MySQL query statement below
SELECT Score,(
    SELECT COUNT(DISTINCT s.Score)
    FROM Scores s
    WHERE s.Score > = Scores.Score) AS Rank
FROM Scores
ORDER BY Score DESC;
Copy the code

This approach is simple, but using a subquery to generate a rank in a SELECT statement is equivalent to querying the scores table once for each record, which is slow (number of records ^2).

Method 2

Create two variables in the SELECT statement where @prescore is used to record the previous score and @ranker is used to record the current ranking. Then determine if the current score is equal to the previous score @prescore, if so, the ranking value is @ranker, otherwise, the ranking value is @Ranker+1.

The CAST() function is used for type conversion. The syntax is:

CAST(field nameasType of conversion)Copy the code

The type can be:

CHAR [(N)] Specifies the character type
The DATE type DATE
DATETIME Date and time type
A DECIMAL, float type
SIGNED int
TIME TIME to type

SELECT Score,CAST(
    CASE 
    WHEN @PreScore = Score THEN @Ranker
    WHEN @PreScore := Score THEN @Ranker := @Ranker + 1
    ELSE @Ranker := @Ranker + 1
  END AS SIGNED) AS Rank
FROM Scores a,(SELECT @PreScore := NULL.@Ranker := 0) r
ORDER BY Score DESC;
Copy the code

180 – Consecutive numbers

Topic describes

Write an SQL query to find all numbers that appear at least three times in a row.

Id	Num
1	1
2	1
3	1
4	2
5	1
6	2
7	2
For example, given the Logs table above, 1 is the only number that occurs at least three times in a row.

ConsecutiveNums
1

MySQL script

Create table If Not Exists Logs (Id int,Num int);
Truncate table Logs;
insert into Logs (Id, Num) values ('1'.'1');
insert into Logs (Id, Num) values ('2'.'1');
insert into Logs (Id, Num) values ('3'.'1');
insert into Logs (Id, Num) values ('4'.'2');
insert into Logs (Id, Num) values ('5'.'1');
insert into Logs (Id, Num) values ('6'.'2');
insert into Logs (Id, Num) values ('7'.'2');
Copy the code

The answer to this question is:

Since we need to find the same number three times, we need to create three instances of the table, we can use L1 and L2, respectively, the Id of L1 and L2, the next two positions of L1 and L3, and then return the Num of the same number.

Note: SELECT DISTINCT L1. Num AS ConsecutiveNums ensures that a Num is returned when there are more than 3 consecutive numbers.

# Write your MySQL query statement below
SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1 
LEFT JOIN Logs l2
ON l1.Id = l2.Id - 1
LEFT JOIN Logs l3
ON l1.Id = l3.Id - 2
WHERE l1.Num = l2.Num AND l1.Num = l3.Num;
Copy the code

181 – Employees who earn more than their manager

Topic describes

The Employee table contains all employees, and their managers are employees. Each employee has an Id, along with a list of ids for the employee’s manager.

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 an SQL query that gets the names of employees who earn more than their managers. In the table above, Joe is the only employee who earns more than his manager.

Employee
Joe

MySQL script

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, ManagerId int);
Truncate table Employee;
insert into Employee (Id, Name, Salary, ManagerId) values ('1'.'Joe'.'70000'.'3');
insert into Employee (Id, Name, Salary, ManagerId) values ('2'.'Henry'.'80000'.'4');
insert into Employee (Id, Name, Salary, ManagerId) values ('3'.'Sam'.'60000'.null);
insert into Employee (Id, Name, Salary, ManagerId) values ('4'.'Max'.'90000'.null);
Copy the code

182 – Look for duplicate E-mail addresses

Topic describes

Write an SQL query to find all duplicate E-mail addresses in the Person table. Example:

Id	Email
1	[email protected]
2	[email protected]
3	[email protected]
Based on the above input, your query should return the following results:

Email
[email protected]

Note: All email addresses are in lowercase letters.

MySQL script

Create table If Not Exists Person (Id int,Email varchar(255));
Truncate table Person;
insert into Person (Id, Email) values ('1'.'[email protected]');
insert into Person (Id, Email) values ('2'.'[email protected]');
insert into Person (Id, Email) values ('3'.'[email protected]');
Copy the code

The answer to this question is:

Find duplicate data: Groups with more than 1 are duplicate.

# Write your MySQL query statement below
SELECT Email
FROM Person
GROUP BY Email
HAVING Count(*) > = 2;
Copy the code

183 – Customers who never order

Topic describes

A site contains two tables, the Customers table and the Orders table. Write an SQL query to find all the customers who never order anything.

Id	Name
1	Joe
2	Henry
3	Sam
4	Max
The Orders table:

Id	CustomerId
1	3
2	1
For example, given the table above, your query should return:

Customers
Henry
Max

MySQL script

Create table If Not Exists Customers (Idint, Name varchar(255));
Create table If Not Exists Orders (Id int,CustomerId int);
Truncate table Customers;
insert into Customers (Id, Name) values('1'.'Joe');
insert into Customers (Id, Name) values('2'.'Henry');
insert into Customers (Id, Name) values('3'.'Sam');
insert into Customers (Id, Name) values('4'.'Max');
Truncate table Orders;
insert into Orders (Id, CustomerId) values('1'.'3');
insert into Orders (Id, CustomerId) values('2'.'1');
Copy the code

Ontology method

Method 1 – LEFT the JOIN

Simply join the two tables to the left and find the customer whose CustomerId is Null on the right is the customer who did not place the order.

SELECT Customers.Name AS Customers
FROM Customers LEFT JOIN Orders
ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS null;
Copy the code

Method 2 – NOT IN

SELECT c.Name AS Customers
FROM Customers AS c
WHERE c.Id NOT IN (
    SELECT DISTINCT CustomerId
    FROM Orders
);
Copy the code

184 – Department’s highest salary

Topic describes

The Employee table contains information about all employees, each with its corresponding Id, Salary, and 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 contains information for all departments of the company.

Id	Name
1	IT
2	Sales
Write an SQL query to find the highest paid employees in each department. For example, according to the table given above, 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

MySQL script

Create table If Not Exists Employee (Idint, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Idint, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary,DepartmentId) values ('1'.'Joe'.'70000'.'1');
insert into Employee (Id, Name, Salary,DepartmentId) values ('2'.'Henry'.'80000'.'2');
insert into Employee (Id, Name, Salary,DepartmentId) values ('3'.'Sam'.'60000'.'2');
insert into Employee (Id, Name, Salary,DepartmentId) values ('4'.'Max'.'90000'.'1');
Truncate table Department;
insert into Department (Id, Name) values('1'.'IT');
insert into Department (Id, Name) values('2'.'Sales');
Copy the code

Ontology method

Find out the highest salary of each department through GROUP BY, and then find the corresponding employee name and department name through the join table.

# 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 e.DepartmentId = d.Id 
AND ((e.DepartmentId,e.Salary) IN (SELECT DepartmentId,Max(Salary) AS MSalary
                                   FROM Employee
                                   GROUP BY DepartmentId));
Copy the code

185 – The top three highest-paid employees in the department

Topic describes

The Employee table contains information about all employees, each with its corresponding Id, Salary, and 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 contains information for all departments of the company.

Id	Name
1	IT
2	Sales
Write an SQL query to find the top three highest-paid employees in each department. For example, based on the table given above, the query results should return:

Department	Employee	Salary
IT	Max	90000
IT	Randy	85000
IT	Joe	70000
Sales	Henry	80000
Sales	Sam	60000

MySQL script

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary, DepartmentId) values ('1'.'Joe'.'85000'.'1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('2'.'Henry'.'80000'.'2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('3'.'Sam'.'60000'.'2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('4'.'Max'.'90000'.'1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('5'.'Janet'.'69000'.'1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('6'.'Randy'.'85000'.'1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('7'.'Will'.'70000'.'1');
Truncate table Department;
insert into Department (Id, Name) values ('1'.'IT');
insert into Department (Id, Name) values ('2'.'Sales');
Copy the code

Ontology method

Method 1

SELECT d.Name AS Department,e1.Name AS Employee,e1.Salary AS Salary
FROM Employee AS e1 INNER JOIN Department AS d
ON e1.DepartmentId = d.Id
WHERE (
    SELECT COUNT(DISTINCT e2.Salary)
    FROM Employee AS e2
    WHERE e2.DepartmentId = d.Id AND e2.Salary > = e1.Salary
      ) < = 3
ORDER BY e1.DepartmentId,e1.Salary DESC;
Copy the code

Method 2: One thing to note is that the top three salaries in a department imply the same rank for the same salary
Find the top three highest-paid employees in each department. We can break down the query steps and recombine them.
Select * from each Department (Department, Employee, Salary) order by Department (up), Salary (down)

SELECT dep.Name AS Department, emp.Name AS Employee, emp.Salary
FROM Employee AS emp INNER JOIN Department AS dep 
ON emp.DepartmentId = dep.Id
ORDER BY emp.DepartmentId, emp.Salary DESC
Copy the code

Employees in each department are ranked according to salary

SELECT te.DepartmentId, te.Salary,
       CASE 
            WHEN @pre = DepartmentId THEN @rank:= @rank + 1
            WHEN @pre := DepartmentId THEN @rank:= 1
       END AS RANK
FROM (SELECT @pre:=null.@rank:=0)tt,
     (
         SELECT DepartmentId,Salary
         FROM Employee
         GROUP BY DepartmentId,Salary
         ORDER BY DepartmentId,Salary DESC
     ) te
Copy the code

conclusion

DepartmentId	Salary	RANK
1	90000	1
1	85000	2
1	70000	3
1	69000	4
2	80000	1
2	60000	2

Combine steps, turn each step into a result set (no secondary queries), and then correlate the result sets of all steps to improve performance.

SELECT dep.Name Department, emp.Name Employee, emp.Salary
FROM (
        SELECT te.DepartmentId, te.Salary,
               CASE 
                    WHEN @pre = DepartmentId THEN @rank:= @rank + 1
                    WHEN @pre := DepartmentId THEN @rank:= 1
               END AS RANK
        FROM (SELECT @pre:=null.@rank:=0)tt,
             (
                 SELECT DepartmentId,Salary
                 FROM Employee
                 GROUP BY DepartmentId,Salary
                 ORDER BY DepartmentId,Salary DESC
             )te
       )t
INNER JOIN Department dep 
ON t.DepartmentId = dep.Id
INNER JOIN Employee emp 
ON t.DepartmentId = emp.DepartmentId and t.Salary = emp.Salary and t.RANK < = 3
ORDER BY t.DepartmentId, t.Salary DESC
Copy the code

conclusion

Department	Employee	Salary
IT	Max	90000
IT	Joe	85000
IT	Randy	85000
IT	Will	70000
Sales	Henry	80000
Sales	Sam	60000

196 – Delete duplicate email addresses

Topic describes

Write an SQL query to delete all duplicate email mailboxes from the Person table, leaving only the one with the smallest Id.

Id	Email
1	[email protected]
2	[email protected]
3	[email protected]
Id is the primary key of this table.

For example, after running your query, the Person table above should return the following lines:

Id	Email
1	[email protected]
2	[email protected]

MySQL script

-- ----------------------------
-- Table structure for `person`
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
 `Id` int(11) DEFAULT NULL,
 `Email` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('1'.'[email protected]');
INSERT INTO `person` VALUES ('2'.'[email protected]');
INSERT INTO `person` VALUES ('3'.'[email protected]');
Copy the code

Ontology method

Query target: Delete a record
Query scope: Person table
Search condition: Delete all duplicate email addresses and retain only the one with the smallest Id.

Obviously, two and relation conditions can be extracted from this query condition:

Find all duplicate E-mail addresses
Delete duplicate mailboxes with large IDS
For condition (1), all duplicate Email addresses need to be identified, that is, p1.email = p2.email;
For condition (2), identify the mailbox whose Id is larger than p1.Id > p2.id

# Write your MySQL query statement below
DELETE p1
FROM Person p1,Person p2
WHERE (p1.Email = p2.Email) AND (p1.Id > p2.Id);
Copy the code

197 – Rising temperature

Topic describes

Given a Weather table, write an SQL query to find the ids of all the dates that were hotter than the previous (yesterday) date.

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

MySQL script

-- ----------------------------
-- Table structure for `weather`
-- ----------------------------
DROP TABLE IF EXISTS `weather`;
CREATE TABLE `weather` (
 `Id` int(11) DEFAULT NULL,
 `RecordDate` date DEFAULT NULL,
 `Temperature` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of weather
-- ----------------------------
INSERT INTO `weather` VALUES ('1'.'2015-01-01'.'10');
INSERT INTO `weather` VALUES ('2'.'2015-01-02'.'25');
INSERT INTO `weather` VALUES ('3'.'2015-01-03'.'20');
INSERT INTO `weather` VALUES ('4'.'2015-01-04'.'30');
Copy the code

Ontology method

We can use MySQL’s function DATEDIFF to calculate the difference between two dates. Our constraint is that the temperature is high and the date difference is 1.

# Write your MySQL query statement below
SELECT a.Id
FROM Weather a INNER JOIN Weather b
WHERE DATEDIFF(a.RecordDate,b.RecordDate) = 1 AND a.Temperature > b.Temperature;
Copy the code

262 – Itinerary and users

Ontology description

Trips table stores all taxi trip information. Each trip has a unique key Id. Client_Id and Driver_Id are the foreign keys of Users_Id in the Users table. Status is an enumeration type whose members are (‘ 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 stores all Users. Each user has a unique key, Users_Id. Banned indicates whether the user is Banned or not, and Role is an enumeration type representing (‘ 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

Mysql script

Create table If Not Exists Trips (Id int, Client_Id int, Driver_Id int, City_Id int, Status ENUM('completed'.'cancelled_by_driver'.'cancelled_by_client'), Request_at varchar(50))
Create table If Not Exists Users (Users_Id int, Banned varchar(50), Role ENUM('client'.'driver'.'partner'))
Truncate table Trips
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('1'.'1'.'10'.'1'.'completed'.'2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('2'.'2'.'11'.'1'.'cancelled_by_driver'.'2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('3'.'3'.'12'.'6'.'completed'.'2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('4'.'4'.'13'.'6'.'cancelled_by_client'.'2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('5'.'1'.'10'.'1'.'completed'.'2013-10-02')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('6'.'2'.'11'.'6'.'completed'.'2013-10-02')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('7'.'3'.'12'.'6'.'completed'.'2013-10-02')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('8'.'2'.'12'.'12'.'completed'.'2013-10-03')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('9'.'3'.'10'.'12'.'completed'.'2013-10-03')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('10'.'4'.'13'.'12'.'cancelled_by_driver'.'2013-10-03')
Truncate table Users
insert into Users (Users_Id, Banned, Role) values ('1'.'No'.'client')
insert into Users (Users_Id, Banned, Role) values ('2'.'Yes'.'client')
insert into Users (Users_Id, Banned, Role) values ('3'.'No'.'client')
insert into Users (Users_Id, Banned, Role) values ('4'.'No'.'client')
insert into Users (Users_Id, Banned, Role) values ('10'.'No'.'driver')
insert into Users (Users_Id, Banned, Role) values ('11'.'No'.'driver')
insert into Users (Users_Id, Banned, Role) values ('12'.'No'.'driver')
insert into Users (Users_Id, Banned, Role) values ('13'.'No'.'driver')
Copy the code

Write a SQL statement to find the cancellation rate of non-banned users between October 1, 2013 and October 3, 2013. Based on the table above, your SQL statement should return the following result, with the Cancellation Rate remaining two decimal places.

Cancellation rates are calculated as follows :(number of non-prohibited user-generated orders cancelled by drivers or passengers)/(total number of non-prohibited user-generated orders)

Day	Cancellation Rate
2013-10-01	0.33
2013-10-02	0.00
2013-10-03	0.50

The answer to this question is:

Method 1:
For trips table and Users table, the connection condition is that the passengers corresponding to the trip are not prohibited and the driver is not prohibited
Filter order dates between target dates
Group by date
All orders and cancelled orders were counted respectively. The cancelled orders were obtained by a bool condition to get 0 or 1, and then avG was used to calculate the mean value
Keep the order cancellation rate by two decimal places and rename the output column name

# Write your MySQL query statement below
SELECT
    request_at as 'Day', round(avg(Status! ='completed'), 2) as 'Cancellation Rate'
FROM 
    trips t JOIN users u1 ON (t.client_id = u1.users_id AND u1.banned = 'No')
    JOIN users u2 ON (t.driver_id = u2.users_id AND u2.banned = 'No')
WHERE	
    request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY 
    request_at
Copy the code

Method 2:

Non-forbidden user-generated orders mean non-forbidden passenger orders, mainly in combination with daily life, taxi orders are initiated and generated by passengers, so change the idea, GROUP, and then divide the two COUNT

SELECT
	t.Request_at 'Day',ROUND(
    1 - COUNT(IF(t.`Status` = 'completed'.1.NULL)) / COUNT(*),2) 'Cancellation Rate'
FROM	Trips t
INNER JOIN Users u ON t.Client_Id = u.Users_Id AND u.Banned = 'No' 
WHERE
  t.Request_at > = '2013-10-01' AND t.Request_at < '2013-10-04'
GROUP BY t.Request_at
Copy the code

595 – Big country

Topic describes

Here’s a World table

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 large if its area is more than 3 million square kilometers, or its population is more than 25 million.

Write an SQL query that outputs the names, population, and area of all the large countries in the table.

For example, according to the table above, we should print:

name	population	area
Afghanistan	25500100	652230
Algeria	37100000	2381741

Mysql script

-- ----------------------------
-- Table structure for `world`
-- ----------------------------
DROP TABLE IF EXISTS `world`;
CREATE TABLE `world` (
  `name`varchar(255) DEFAULT NULL,
 `continent` varchar(255) DEFAULT NULL,
  `area`int(11) DEFAULT NULL,
 `population` int(11) DEFAULT NULL,
  `gdp`varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of world
-- ----------------------------
INSERT INTO `world` VALUES ('Afghanistan'.'Asia'.'652230'.'25500100'.'20343000000');
INSERT INTO `world` VALUES ('Albania'.'Europe'.'28748'.'2831741'.'12960000000');
INSERT INTO `world` VALUES ('Algeria'.'Africa'.'2381741'.'37100000'.'188681000000');
INSERT INTO `world` VALUES ('Andorra'.'Europe'.'468'.'78115'.'3712000000');
INSERT INTO `world` VALUES ('Angola'.'Africa'.'1246700'.'20609294'.'100990000000');
Copy the code

Ontology method

One caveat: a country is large if its area is more than 3 million square kilometers, or its population is more than 25 million.

# Write your MySQL query statement below
SELECT name,population,area
FROM World
WHERE area > 3000000 OR population > 25000000;
Copy the code

596 – Lessons for more than 5 students

Topic describes

There is a table for courses (student) and classes (class).

Please list all classes with more than 5 students or equal.

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: Students should not be counted twice in each lesson.
MySQL script

-- ----------------------------
-- Table structure for `courses`
-- ----------------------------
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
 `student` varchar(255) DEFAULT NULL,
  `class`varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of courses
-- ----------------------------
INSERT INTO `courses` VALUES ('A'.'Math');
INSERT INTO `courses` VALUES ('B'.'English');
INSERT INTO `courses` VALUES ('C'.'Math');
INSERT INTO `courses` VALUES ('D'.'Biology');
INSERT INTO `courses` VALUES ('E'.'Math');
INSERT INTO `courses` VALUES ('F'.'Computer');
INSERT INTO `courses` VALUES ('G'.'Math');
INSERT INTO `courses` VALUES ('H'.'Math');
INSERT INTO `courses` VALUES ('I'.'Math');
Copy the code

The answer to this question is:

There are duplicate entries in this table, for example, student A can have multiple entries for Math, but Math is thought to have been chosen only once by student A.

# Write your MySQL query statement below
SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) > = 5;
Copy the code

601 – The flow of people in the stadium

Topic describes

A new stadium has been built in X City, and the daily visitor flow information is recorded in the three columns: ID, date and people.

Please write a query statement to find the peak period. The peak period requires three consecutive days or more and the daily traffic is not less than 100.

For example, table stadium:

id	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	date	people
5	2017-01-05	145
6	2017-01-06	1455
7	2017-01-07	199
8	2017-01-08	188

Note: There is only one record per day, and the date increases as the ID increases.
MySQL script

Create table If Not Exists stadium (id int, visit_date DATE NULL, people int);
Truncate table stadium;
insert into stadium (id, visit_date, people) values ('1'.'2017-01-01'.'10');
insert into stadium (id, visit_date, people) values ('2'.'2017-01-02'.'109');
insert into stadium (id, visit_date, people) values ('3'.'2017-01-03'.'150');
insert into stadium (id, visit_date, people) values ('4'.'2017-01-04'.'99');
insert into stadium (id, visit_date, people) values ('5'.'2017-01-05'.'145');
insert into stadium (id, visit_date, people) values ('6'.'2017-01-06'.'1455');
insert into stadium (id, visit_date, people) values ('7'.'2017-01-07'.'199');
insert into stadium (id, visit_date, people) values ('8'.'2017-01-08'.'188');
Copy the code

The answer to this question is:

For example :(29,’2017-5-29′,150),(30,’2017-6-1′,150), (30,’ 2017-5-29′,150),(30,’2017-6-1′,150), (30,’ 2017-5-29′,150),(30,’2017-6-1′,150), (30,’ 2017-5-29′,150),(30,’2017-6-1′,150), (30,’ 2017-5-29′,150)

SELECT DISTINCT t1.*
FROM stadium t1, stadium t2, stadium t3
WHERE t1.people > = 100 AND t2.people > = 100 AND t3.people > = 100
AND
(
    (t1.id - t2.id = 1 AND t1.id - t3.id = 2 AND t2.id - t3.id = 1) 
    OR
    (t2.id - t1.id = 1 AND t2.id - t3.id = 2 AND t1.id - t3.id = 1) 
    OR
    (t3.id - t2.id = 1 AND t2.id - t1.id = 1 AND t3.id - t1.id = 2))ORDER BY t1.id;
Copy the code

620 – Interesting movie

Topic describes

A new cinema opened in a city, which attracted many people to see the film. The cinema pays special attention to the user experience and has a special LED display board for movie recommendations, which posts movie reviews and descriptions.

As the information director of the cinema, you need to write an SQL query to find all movies that are described as non-boring and have an odd number of ids, ranked by rating.

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 would be:

id	movie	description	rating
5	House card	Interesting	9.1
1	War	great 3D	8.9

Their thinking
Use MySQL to determine odd and even numbers

Such as

num % 2 =1; Num is an odd number num% 2 =0; Num is evenCopy the code

Ontology method

select * from cinema 
where description <> 'boring' and id % 2 =1
order by rating desc
Copy the code

626 – Change seats

Topic describes

Mei, an information technology teacher in a middle school, has a seat chart, which she usually uses to store students’ names and seat ids corresponding to them.

The ids in the columns are continuously increasing

Mei wants to change the seats of two students next to each other.

Can you write an SQL Query for her to output the result that Mei wants?

Example:

id	student
1	Abbot
2	Doris
3	Emerson
4	Green
5	Jeames
If the data input is from the above table, the output is as follows:

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 seat of the last student.
MySQL script

Create table If Not Exists seat(id int, studentvarchar(255));
Truncate table seat;
insert into seat (id, student) values ('1'.'Abbot');
insert into seat (id, student) values ('2'.'Doris');
insert into seat (id, student) values ('3'.'Emerson');
insert into seat (id, student) values ('4'.'Green');
insert into seat (id, student) values ('5'.'Jeames');
Copy the code

The answer to this question is:

We can write it in three pieces, the first piece is the one with an even id, the one with an odd id minus 1 is the same thing as the one with an odd id, the second piece is the one with an odd ID plus 1 is the same thing as the one with an even id, and then the last piece is the last one with an odd number, and then we get the result by combining the three pieces.

Method 1:

WHERE mod(id,2) = 1 AND ID = (SELECT COUNT(*) FROM seat), ensure that the ID of the last block is not even.

# Write your MySQL query statement below
SELECT s.id,s.student
FROM (SELECT (id- 1) AS id,student
      FROM seat
      WHERE mod(id,2) = 0
      UNION
      SELECT (id+1) AS id,student
      FROM seat
      WHERE mod(id,2) = 1 AND id ! = (SELECT COUNT(*) FROM seat)
      UNION
      SELECT id,student
      FROM seat
      WHERE mod(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat)) s
ORDER BY s.id;
Copy the code

Method 2: CASE WHEN

# Write your MySQL query statement below
SELECT (CASE 
        WHEN MOD(id,2)! =0 AND id! =counts THEN id+1  
        WHEN MOD(id,2)! =0 AND id=counts THEN id  
        ELSE id- 1 END) AS id,student 
        FROM seat,(SELECT COUNT(*) AS counts FROM seat) AS seat_counts  
ORDER BY id;
Copy the code

627 – Exchange wages

Topic describes

Note that you must write an Update statement, not any Select statements.

Such as:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500
After running the update statement you wrote, you will get the following table:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

MySQL script

-- ----------------------------
-- Table structure for `salary`
-- ----------------------------
DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary` (
  `id`int(11) NOT NULL,
  `name`varchar(10) NOT NULL,
  `sex`varchar(10) NOT NULL,
 `salary` int(11) NOT NULL,
  PRIMARYKEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of salary
-- ----------------------------
INSERT INTO `salary` VALUES ('1'.'A'.'m'.'2500');
INSERT INTO `salary` VALUES ('2'.'B'.'f'.'1500');
INSERT INTO `salary` VALUES ('3'.'C'.'m'.'5500');
INSERT INTO `salary` VALUES ('4'.'D'.'f'.'500');
Copy the code

The answer to this question is:

Method 1: Use the if function

IF(expr1, expr2, expr3) IF expr1 is TRUE, then IF() returns expr2; Otherwise the return value is expr3.

# Write your MySQL query statement below
UPDATE salary
SET sex = IF(sex = "f","m","f");
Copy the code

Method 2: Use case… when.. then.. else.. end

UPDATE salary 
SET sex  = (CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END);
Copy the code

Brush force buckle database of non-member questions, most of them are some partial basic questions, forcing me to open member ya, after the time will brush algorithm, there are objections, but also hope you don’t hesitate to comment.

PS, more exciting, interested partners welcome to visit my personal station –> noheart. Cn

175 Combine two tables

The answer to this question is:

176 – Second highest salary

Ontology method

177 – The NTH highest salary

Ontology method

178 – Score ranking

The answer to this question is:

180 – Consecutive numbers

The answer to this question is:

181 – Employees who earn more than their manager

182 – Look for duplicate E-mail addresses

The answer to this question is:

183 – Customers who never order

Ontology method

184 – Department’s highest salary

Ontology method

185 – The top three highest-paid employees in the department

Ontology method

196 – Delete duplicate email addresses

Ontology method

197 – Rising temperature

Ontology method

262 – Itinerary and users

The answer to this question is:

595 – Big country

Ontology method

596 – Lessons for more than 5 students

The answer to this question is:

601 – The flow of people in the stadium

The answer to this question is:

620 – Interesting movie

Ontology method

626 – Change seats

The answer to this question is:

627 – Exchange wages

The answer to this question is:

Related Posts

Multithreaded Wait, Notify, and notifyAll

Elasticsearch learning route

Webpack – Dashboard – Webpack dashboard display