# Analysis of the actual operation of the database SQL of Niuke network (1-10)

Posted by Soumen on Tue, 07 Apr 2020 15:46:24 +0200

# 1. Find all information about the latest employee

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

Solution: sort order by... Desc according to the entry time in reverse order, and then take another record, that is, the latest employee.

select * from employees
order by hire_date DESC limit 1;

There is a problem in this way. Hire date is a date type. It is likely that multiple records are employed at the same time, so the time type is still more precise with a timestamp.

For this problem, you can use the MAX() function, and then use a subquery.

select * from employees
where hire_date = (select MAX(hire_date) FROM employees);

MAX() first finds out the latest entry time, and then finds out all the employees who joined at the latest time.

# 2. Find all the information of the employees whose time ranking is the third from the bottom

Find all the information of the employee whose time rank is the third from the bottom

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

Solution: to query the last three entry time, you need to sort them first, and then take three records. The result sets in MySQL are arranged in positive order by default, DESC is used for reverse order, and limit is used for the third.

select * from employees
order by hire_date DESC
limit 2,1;

# 3. Find the current salary details and department No

Find the current salary details of the leaders of each department and their corresponding department number Dept

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

Solution idea: This is a question of equivalent connection, which can be related with employee number EMP No.

select s.*, d.dept_no
from salaries s
join dept_manager d on d.emp_no = s.emp_no
where d.to_date='9999-01-01';

One of the optimization points of the above SQL is that you can use s.To date ='9999-01-01 'to de lock again. The modified SQL is as follows:

select s.*, d.dept_no
from salaries s
join dept_manager d on d.emp_no = s.emp_no
where d.to_date='9999-01-01'  and s.to_date='9999-01-01';

Tucao: the ox dept_manager make complaints about the two letter alias dm.

# 4. Find the last name, first name and Dept no of all employees who have been assigned departments

Find the last name, first name, and Dept no of all employees who have assigned departments

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

Solution id ea: this problem began to think of using a sub query, first find out all the Department IDS, as a condition to query the employee table. Then you can see that you need to query Dept. no in the Department table, so it's more appropriate to use external connection, and use Dept. EMP table as the main table to query.

select e.last_name, e.first_name, d.dept_no
from dept_emp  d
left join employees e on d.emp_no=e.emp_no;

# 5. Find the last name and first name of all employees and the corresponding department No

Find the last name and first name of all employees and the corresponding department number Dept. No., including displaying the employees without specific department assignment.

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

Solution idea: This is the same as the previous question, but in this question, if there is no department number, you need to find out, that is, query all employees, and take the employee table as the main table.

select e.last_name, e.first_name, d.dept_no
from employees e
left join dept_emp  d on d.emp_no=e.emp_no;

# 6. Find the salary of all employees at the time of entry

Find the salary of all employees at the time of entry, give EMP no and salary, and reverse the order according to EMP No.

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

Solution idea: this question should take into account the salary of the employee at the time of entry, that is, the salary of the employee in the first month of entry, that is, the employees. Hire_date = salary. from_date.

select e.emp_no, s.salary
from employees e
join salaries s on e.emp_no=s.emp_no and e.hire_date = s.from_date
order by e.emp_no DESC;

# 7. Find the employee number emp_no whose salary has increased more than 15 times and the corresponding increase times t

Find the employee number emp_no whose salary has increased more than 15 times and the corresponding increase times t

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`)
);

Solution: the first reaction to this problem is that it is too complex. Because the salary increase needs to be compared with the salary of two months before and after. If the salary decreases, it cannot be regarded as the increase. So, with the idea of solving the problem, I don't think the problem will be so complicated, so I boldly assume that it has been paid by default.

From the perspective of test taking, I think it is to investigate group by...having..., that is to say, first group by emp_no, and then filter the records with more than 15 grouped records.

select emp_no, count(emp_no) as t
from salaries
group by emp_no
having t > 15;

It is. Don't worry about the meaning of this question. Just know that the inspection point is group by...having.

MySQL having clause

# 8. Find out the salary status of all employees

Find out the current salary status of all employees (to ﹐ date = (9999-01-01)). For the same salary, display it only once and in reverse order

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

Solution idea: as mentioned in this question, the same salary can only be displayed once, that is, let's use DISTINCT to duplicate.

select DISTINCT salary
from salaries
where to_date='9999-01-01'
order by salary DESC;

# 9. Obtain the current salary status of the current manager of all departments, and give the Dept. no, EMP. No and salary. The current value is to date = 9999-01-01 '

Obtain the current salary status of the current manager of all departments, and give the Dept. no, EMP. No and salary. The current value is to date =. 9999-01-01 '

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

Solution idea: first, look at the table structure. EMP no and Dept no are the primary keys of the dept manager table, which means that a department may have multiple leaders, and then dept manager can query the salary by associating the salary table. The restriction is to query the salary of the day s.To date = (9999-01-01 ');.

select d.dept_no, d.emp_no, s.salary
from dept_manager d
join salaries s on d.emp_no = s.emp_no
where s.to_date='9999-01-01';

But when I write like this, it turns out that AC fails. After importing the data, I find that the query result is like this, which means that a leader of a department will have multiple salaries on the same day??

d001	10002	72527
d001	10002	72527
d001	10002	72527
d001	10002	72527
d001	10002	72527
d001	10002	72527
d004	10004	40054
d004	10004	42283
d004	10004	42542
d004	10004	46065
d004	10004	48271
d004	10004	50594
d004	10004	52119
d004	10004	54693
d004	10004	58326

To add a query condition D.to date = (9999-01-01), it means the current manager.

select d.dept_no, d.emp_no, s.salary
from dept_manager d
join salaries s on d.emp_no = s.emp_no
where s.to_date='9999-01-01' and d.to_date='9999-01-01';

# 10. Obtain EMP ﹣ no of all non manager employees

Get all non manager employees EMP? No

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

Solution idea: find the employee who is not the manager, that is, after the employees left connect Dept. manager, no record employee is found in the Dept. manager table. So after association, judge d.dept.no is null; that is, the employee without data in the Dept.Manager table.

select e.emp_no
from employees e
left join dept_manager d on e.emp_no = d.emp_no
where d.dept_no IS NULL;

Topics: MySQL SQL