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.
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;