Mysql trainees: six novice road projects

Connect Mysql

d: 
cd D:\mysql-5.7.27-winx64\mysql-5.7.27-winx64\bin
net start mysql
mysql -u root -p

Item 10: Employees with the highest salaries in all departments

Create an Employee table that contains all employee information, each employee has 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 |
±—±------±-------±-------------+
Create a Department table that contains information about 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 given table, 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 |
±-----------±---------±-------+

Create a database

CREATE Database DataWhaletwo;
use DataWhaletwo;

Create tables

//Create Employee tables
DROP TABLE IF EXISTS Employee;

CREATE TABLE Employee(
Id INT PRIMARY KEY,
Name VARCHAR (255),
Salary INT,
DepartmentId INT);
//insert values
INSERT INTO Employee VALUES
('1','Joe','70000','1'),('2','Henry','80000','2'),('3','Sam','60000','2'),('4','Max','90000','1');

//Create Department tables
DROP TABLE IF EXISTS Department;

CREATE TABLE Department(
Id INT PRIMARY KEY,
Name VARCHAR(255)
);
//insert values
INSERT INTO Department VALUES 
('1','IT'),('2','Sales');
SELECT Department.Name AS Department, Employee.Name AS Employee, Employee.Salary AS Salary
 FROM Department
 JOIN Employee
 WHERE Department.Id = Employee.DepartmentId
 GROUP BY Department.Name
HAVING MAX(Salary);

Item 11: Seat change

Xiao Mei is an IT teacher in a middle school. She has a seat table, which is usually used to store the names of students and their corresponding seat ids.
Where the id of the column is continuously increasing
Xiao Mei wants to change the seats of two students next to her.
Can you help her write an SQL query to output Xiaomei's desired results?
Create the seat table as shown below:
Examples:
±--------±--------+
| id | student |
±--------±--------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
±--------±--------+
If the data input is the table above, the output results are as follows:
±--------±--------+
| id | student |
±--------±--------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
±--------±--------+
Be careful:
If the number of students is odd, there is no need to change the seat of the last student.

//Create a seating table
CREATE TABLE seat(
id INT NOT NULL PRIMARY KEY,
student VARCHAR(20)
);
//insert values
INSERT INTO seat
VALUES
(1,'Abbot'),
(2,'Doris'),
(3,'Emerson'),
(4,'Green'),
(5,'Jeames');
select a.id,a.student
from(
select id+1 as id,student
FROM seat 
where id mod 2 !=0 and id !=(select max(id) from seat)
UNION 
select id-1 as id, student
FROM seat
where id mod 2=0
UNION
select id,student
FROM seat
where id mod 2!=0 and id =(select max(id) from seat)
) as a
ORDER BY a.id

Item 12: Score Ranking

Write an SQL query to achieve score ranking. If the two scores are the same, the two Rank s are the same. Note that the next place after the split should be the next consecutive integer value. In other words, there should be no "gap" between rankings.
Create the following score table:
±—±------+
| 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 be returned (ranked from high to low in scores):
±------±-----+
| Score | Rank |
±------±-----+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
±------±-----+

CREATE TABLE score(
Id int,
Score Float
);
INSERT INTO score
VALUES
(1,3.50),
(2,3.65),
(3,4.00),
(4,3.85),
(5,4.00),
(6,3.65);	

select @rank_tmp:=0,@pre_score:=NULL;
SELECT Id, Score
	, CASE 
		WHEN @pre_score = score THEN @rank_tmp
		WHEN @pre_score := score THEN @rank_tmp := @rank_tmp + 1
	END AS 'rank'
FROM score, (
		SELECT @rank_tmp := 0, @pre_score := NULL
	) tmp
ORDER BY score.score DESC;

Item 13: Continuous figures

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 appears at least three times in a row.

±----------------+
| ConsecutiveNums |
±----------------+
| 1 |
±----------------+

Create table If Not Exists Logs (Id int,Num int);
Truncate table Logs;
INSERT INTO Logs (Id, Num) 
values ('1','1'), ('2','1'),('3', '1'), ('4','2'),('5','1'),('6','2'), ('7','2');

SELECT DISTINCT l1.Num as ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3
WHERE (l1.id = l2.id - 1
AND l2.id = l3.id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num);

Item 14: Tree Nodes

For tree tables, ID is the identity of the tree node, and p_id is the ID of its parent node.

±—±-----+
| id | p_id |
±—±-----+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
±—±-----+
Each node is one of three types:
Leaf: If the node is the root node.
Root: If the node is a leaf node.
Inner: If a node is neither a root node nor a leaf node.

Write a query statement to print the node id and the corresponding node type. Sort by node id. The corresponding results of the above example are as follows:
±—±-----+
| id | Type |
±—±-----+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
±—±-----+
Explain
Node''1'is the root node, because its parent node is NULL, and it has'2' and'3'sub-nodes.
Node'2'is an internal node because its parent node is' 1', and it has child nodes'4' and'5'.
Nodes'3','4','5' are leaf nodes because they have parent nodes but no child nodes.

The following is the graph of the tree:

Item 15: At least five direct managers

The Employee table contains information about all employees and their supervisors. Each employee has an Id and a Manager Id corresponding to the supervisor.

DROP TABLE employee
CREATE TABLE employee(
Id INT PRIMARY KEY,
Name VARCHAR (255),
Department VARCHAR (255)
ManagerId INT
);
INSERT INTO employee VALUES
('101', 'John', 'A', 'null'),
('102', 'Dan', 'A', '101 '),
('103', 'James', 'A', '101'),
('104', 'Amy', 'A', '101'),
('105', 'Anne', 'A', '101'),
('106', 'Ron', 'B', '101');

SELECT Name
FROM (
SELECT ManagerId, COUNT(Id) AS lalala
FROM employee
GROUP BY ManagerId
) m, employee e
WHERE m.ManagerId = e.Id
AND lalala >= 5;

Tags: MySQL SQL Database supervisor

Posted on Tue, 13 Aug 2019 05:24:07 -0700 by yaba