Autumn move script A

Exercise 1: the highest paid employees in each department

Create an Employee table containing all Employee information. Each Employee has its corresponding Id, salary and department Id.

create table Employee
( Id integer(4) not null,
  employee_name varchar(128) not null,
	salary integer(128) not null,
	departmentid integer(4)  not null,
	primary key(ID) 
);

insert into Employee values(1,'Joe',70000,1);
insert into Employee values(2,'Henry',80000,2);
insert into Employee values(3,'Sam',60000,2);
insert into Employee values(4,'Max',90000,1);


Create a Department table that contains information about all departments of the company

create table department
(Id integer(4) not null,
 department_name varchar(128) not null,
 primary key(Id)
);

insert into department values(1,'IT');
insert into department values(2,'Sales');


Write an SQL query to find out 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.

Problem solving ideas:

  • According to the consistency between the department Id in the employee table and the Id in the department table, the associated sub query is adopted.
  • Since the fields after the group by statement must be consistent with those after the select statement (except for the fields in the aggregate function), employee as is introduced
    p2 is used as a new query table to obtain employee_name information, and limit the queried records through the maximum salary.

Note: I don't think this method is the simplest one.

select p1.department_name as Department,
			 p2.employee_name as Employee,
			 p3.max_salary as Salary
from department as p1, employee as p2,
		 (select max(salary)as max_salary,departmentid
		 from employee
		 group by departmentid)as p3
where p3.departmentid = p1.Id
	and p2.salary = p3.max_salary;

Exercise 2: change seats

Xiaomei is an information technology teacher in a m id dle school. She has a seat table, which is usually used to store students' names and their corresponding seat IDs.
The column id is incremented continuously
Xiaomei wants to change the seats of two adjacent students.
Can you help her write an SQL query to output the results Xiaomei wants?

reference resources: [LeetCode] 626. Change seats
To create a seat table:

create table seat
(id integer(4) not null,
 student varchar(128) not null,
 primary key(id));
 
insert into seat values(1,'Abbot');
insert into seat values(2,'Doris');
insert into seat values(3,'Emerson');
insert into seat values(4,'Green');
insert into seat values(5,'Jemes');

Problem solving ideas:

  1. Move all even rows forward 1
  2. Move all odd rows back 1
  3. The last line is an odd line and does not move
select p.id ,p.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 id = (select count(*) from seat)
    )as p
order by id;

Exercise 3: score ranking

Write an SQL query to achieve score ranking. If the two scores are the same, the two score rankings (Rank) are the 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.
Create the following score table:

create table score
(Id integer(4) not null,
 Score float(64,2) not null,
 primary key(Id));
 
insert into score values(1,3.50);
insert into score values(2,3.65);
insert into score values(3,4.00);
insert into score values(4,3.85);
insert into score values(5,4.00);
insert into score values(6,3.65);

Problem solving ideas:

  1. According to the sorting, the repeated order is not skipped, so the special window function of deny_rank is used
  2. Sort by score in descending order
select Score, DENSE_RANK() OVER (ORDER BY Score desc) AS Rank1
from score

Exercise 4: consecutive numbers

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

create table log
(Id integer(4) not null,
 Num integer(4) not null,
 primary key(Id));
 
insert into log values(1, 1);
insert into log values(2, 1);
insert into log values(3, 1);
insert into log values(4, 2);
insert into log values(5, 1);
insert into log values(6, 2);
insert into log values(7, 2);

Problem solving ideas:

  1. Since it is necessary to query the numbers that appear three times in a row, the first idea is to use the window function.
  2. First query the current position num value, and then successively query the current position plus 1 value Num2 and the current position plus 2 value Num3.
  3. For comparison, when Num=Num2=Num3, take out the equal num value at this time, that is, the number that appears at least three times in a row.
    Supplement the knowledge of window functions: MySQL operation practice (II): window function
select p.Num as ConsecutiveNums
from (select Id,Num,
						 lead(Num,1) over ()as Num2,
						 lead(Num,2) over ()as Num3
			from log)as p
where p.Num = p.Num2
	and p.Num = p.Num3;

Exercise 5: tree nodes

For the tree table, ID is the ID of the tree node, and p_id is the ID of its parent node.
Create table tree:

create table tree
(id integer(4) not null,
 p_id integer(4),
 primary key(id));
 
insert into tree values(1,null);
insert into tree values(2,1);
insert into tree values(3,1);
insert into tree values(4,2);
insert into tree values(5,2);

Problem solving ideas:

  1. The query results are divided into three cases: root, inner and leaf, so the case query statement is used
  2. When the parent node is empty, the node is the root node
  3. When a node has no child nodes, that is, a node that is not in the p_id field, it is a leaf node
  4. Other nodes are intermediate nodes

Note:
not in usage supplement
If not in is followed by a subquery, as long as the subquery contains a null return value, the whole not in sentence will return a null value, and the query will not return any results.
For details, refer to: Reasons why Mysql does not return any results with not in and Solutions

select id,
	case when p_id is null then 'Root' 
		 when id not in(select p_id from tree as p1 where p_id is not null) then 'Leaf'	
		 else 'Inner'		
    end as Type
from tree

Exercise 6: managers with at least five direct reports

The Employee table contains information about all employees and their superiors. Each Employee has an Id and a corresponding supervisor's Id (ManagerId).
Create employee information table employee5:

create table employee5
(id integer(4) not null,
 employee_name varchar(128) not null,
 department varchar(4) not null,
 managerid integer(4),
 primary key(id));

insert into employee5 values(101,'John','A',null);
insert into employee5 values(102,'Dan','A',101);
insert into employee5 values(103,'James','A',101);
insert into employee5 values(104,'Amy','A',101);
insert into employee5 values(105,'Anne','A',101);
insert into employee5 values(106,'Ron','B',101);

For the Employee table, write an SQL statement to find out the supervisor with five subordinates

Problem solving ideas:

  1. The aggregation function count is used to count the number of subordinates corresponding to each supervisor
  2. Use the associated sub query to lock the supervisor information if the result of count statistics is greater than or equal to 5
select p1.employee_name
from employee5 as p1,
		 (select managerid,count(managerid) as managerid_count 
		  from employee5 
		  group by managerid)as p3		 
where p1.id = p3.managerid
 and  p3.managerid_count >=5;

Exercise 7: score ranking

The score table of exercise 3 realizes the ranking function, but the ranking needs to be discontinuous, as follows:

Problem solving ideas:

  1. The sorting is discontinuous, so RANK special window function is used
  2. Sort by score in descending order
select Score, RANK() OVER (ORDER BY Score desc) AS Rank1
from score

Exercise 8: query the questions with the highest response rate

Find the question with the highest response rate in the survey_log table. The fields of the table include uid, action, question_id, answer_id, q_num and timestamp.
uid is the user ID; the value of action is: "show", "answer", "skip"; when action is "answer", answer_id is not empty; on the contrary, when action is "show" and "skip", it is empty (null); q_num is the numerical sequence number of the problem.
Write an sql statement to find the question with the highest response rate.

To create a survey_log table:

create table survey_log
(uid integer(4) not null,
 action varchar(128) not null,
 question_id integer(4) not null,
 answer_id integer(64),
 q_num integer(4) not null,
 timestamp integer(4),
 primary key(uid,action,question_id));
 
 insert into survey_log values(5,'show',285,null,1,123);
 insert into survey_log values(5,'answer',285,124124,1,124);
 insert into survey_log values(5,'show',369,null,2,125);
 insert into survey_log values(5,'skip',369,null,2,126);

Problem solving ideas:

  1. Own ideas:
    The first reaction to getting the questions is to group them, count the number of action categories of each question, and then calculate the response rate and sort them.
    Low efficiency and redundant code. Therefore, the following programming ideas are used for reference:
  2. SQL learning notes - task06: SQL comprehensive exercise
    Group by question_id, then calculate the answers of each question (Group), sort in descending order, and output the first line of records, that is, the highest answer rate.

Note:
Response rate = the number of actions (answer) divided by the number of actions (show).

select question_id as survey_log
from survey_log
group by question_id
order by count(answer_id)/count(action='show')desc
limit 1;

Exercise 9: Top 3 high paid employees in each department

Empty the employee table in Item 7 and re insert the following data (actually insert two more rows 5 and 6):
Recreate the employee table:

create table employee7
(id integer(4) not null,
 name varchar(128) not null,
 salary integer(64) not null,
 departmentid integer(4) not null,
 primary key(id));
 
insert into employee7 values(1,'Joe',70000,1);
insert into employee7 values(2,'Henry',80000,1);
insert into employee7 values(3,'Sam',60000,1);
insert into employee7 values(4,'Max',90000,1);
insert into employee7 values(5,'Janet',69000,1);
insert into employee7 values(6,'Randy',85000,1);

Problem solving ideas:

  1. Query the reverse sorting results of employees' wages in each department
  2. Take the records of the top three wages of each department and get the results

Note: you can also use the join function. Since you are not familiar with it here, you can supplement it later.

(select p2.department_name as Department,
			 p1.name as Employee,
			 p1.salary as Salary
from employee7 as p1,
		 department as p2
where p1.departmentid = 1
	and	p1.departmentid = p2.Id
order by Salary desc
limit 3)

union 

(select p5.department_name as Department,
			 p4.name as Employee,
			 p4.salary as Salary
from employee7 as p4,
		 department as p5
where p4.departmentid = 2
	and	p4.departmentid = p5.Id
order by Salary desc
limit 3)

tips:

  1. When two sets are combined (union), there can only be one order by and write it at the end, otherwise an error will be reported, but this problem does not exist after the sets before and after the union are enclosed in parentheses.
  2. The limit function is the same as above.

Exercise 10: the nearest distance on the plane

The point_2d table contains the coordinate values (x, y) of some points (more than two) in a plane.

Write a query statement to find the shortest distance among these points and keep 2 decimal places.
To create a point_2d table:

create table point_2d
(x integer(8) not null,
 y integer(8) not null,
 primary key(x,y));

insert into point_2d values(-1,-1);
insert into point_2d values(0,0);
insert into point_2d values(-1,-2);

The shortest distance is 1, from point (- 1, - 1) to point (- 1, - 2). Therefore, the output result is: 1

Problem solving ideas:

  1. Calculate the distance between any two points (using two identical tables), using the round,sqrt,power functions
  2. Distance calculation is not required for the same points
  3. Sort the calculation results to find the point with the shortest distance
    The code is as follows:
select p1.x,p1.y,p2.x,p2.y,
			 round(sqrt(power(p1.x-p2.x,2)+power(p1.y-p2.y,2)),2) as shortest
from point_2d as p1,point_2d as p2
where p1.x != p2.x
or   p1.y != p2.y
order by shortest

The operation results are as follows:

analysis:
The distance between two points in this method will be calculated twice, which is inefficient. Solution: Supplement later when you have time.

Exercise 11: itinerary and users

Trips table stores the travel information of all taxis. Each stroke has a unique key Id, Client_Id and Driver_Id is the Users in the Users table_ Foreign key for Id. Status is an enumeration type, and the enumeration members are ('completed ',' cancelled_by_driver ',' cancelled_by_client ').
Create table trips:

create table trips
(id integer(4) not null,
 client_id integer(4) not null,
 driver_id integer(4) not null,
 city_id integer(4) not null,
 status varchar(64) not null,
 request_at date,
 primary key(id));

insert into trips values(1,1,10,1,'completed',20131001);
insert into trips values(2,2,11,1,'cancelled_by_driver',20131001);
insert into trips values(3,3,12,6,'completed',20131001);
insert into trips values(4,4,13,6,'cancelled_by_client',20131001);
insert into trips values(5,1,10,1,'completed',20131002);
insert into trips values(6,2,11,6,'completed',20131002);
insert into trips values(7,3,12,6,'completed',20131002);
insert into trips values(8,2,12,12,'completed',20131003);
insert into trips values(9,3,10,12,'completed',20131003);
insert into trips values(10,4,13,12,'cancelled_by_driver',20131003);

Create user table users:

drop table if exists users;
create table users
(users_id integer(4) not null,
 banned varchar(4) not null,
 role varchar(32) not null,
 primary key(users_id));

insert into users values(1,'No','client');
insert into users values(2,'Yes','client');
insert into users values(3,'No','client');
insert into users values(4,'No','client');
insert into users values(10,'No','driver');
insert into users values(11,'No','driver');
insert into users values(12,'No','driver');
insert into users values(13,'No','driver');

Write an SQL statement to find out the Cancellation Rate of non prohibited users from October 1, 2013 to October 3, 2013. Based on the above table, your SQL statement should return the following results, with two decimal places reserved for the Cancellation Rate.

Problem solving ideas:

  1. Table trips is associated with table users to query the taxi trip information of all non prohibited users
  2. Pass request_ Group at
  3. Count the number of orders cancelled by non prohibited users on each date and the total number of orders on each date through the count(if()) statement, and calculate the ratio with two decimal places.
    SQL statement:
select request_at,
			 round((count(if(p.status='cancelled_by_driver',true,null))+count(if(p.status='cancelled_by_client',true,null)))/count(p.status),2)as 'Cancellation Rate' 
from (select t.id,t.client_id,t.driver_id,t.status,t.request_at,u.banned_users_id
			from trips as t,(select users_id as banned_users_id from users where banned='Yes')as u
			where t.client_id != u.banned_users_id
			and   t.driver_id != u.banned_users_id) as p
group by request_at

Supplement:
Introduction to data types in MYSQL
MySQL operation practice (II): window function
Reasons why Mysql does not return any results with not in and Solutions

For a detailed list of topics, see: DataWhale team learning

Tags: Database MySQL SQL

Posted on Sun, 26 Sep 2021 22:22:18 -0400 by cuvaibhav