summary

on must be used with join

SQL1 finds all the information of the latest employee

select*from employees order by hire_date desc limit 1;

select * from employees
    where hire_date = (select max(hire_date) from employees);

ORDER BY sorts the result set according to the specified column. By default, ORDER BY DESC is in ascending and descending order
LIMIT(m, n) take n records from line m + 1

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

It seems that sql says that the number of lines starts from 1. limit 2,1 means that one record is taken from line 2 + 1

select *from employees order by hire_date desc limit 2,1

SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 1 offset 2;

SQL3 find current salary details and department number dept_no

select salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_manager.dept_no
from dept_manager, salaries
where dept_manager.emp_no=salaries.emp_no
order by salaries.emp_no;

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

select s.emp_no, salary, s.from_date, s.to_date, dept_no
from salaries s join dept_manager dm
on s.emp_no = dm.emp_no
and dm.to_date='9999-01-01'
and s.to_date='9999-01-01'
order by s.emp_no

SQL4 finds the last of all employees who have been assigned departments_ Name and first_name and dept_no

select s.last_name,s.first_name,d.dept_no from employees as s join dept_emp as d
on s.emp_no = d.emp_no;

select s.last_name,s.first_name,d.dept_no from employees as s inner join dept_emp as d
where s.emp_no = d.emp_no;

join is omitted. where can be used, but on cannot be used

select s.last_name,s.first_name,d.dept_no from employees as s , dept_emp as d
where s.emp_no = d.emp_no;

SQL5 find last of all employees_ Name and first_name and corresponding department number dept_no

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

For internal connection, the tables on both sides have corresponding data at the same time, that is, if there is missing data on either side, it will not be displayed.
Left join, read all the data in the left data table, even if there is no corresponding number in the right table. That is, Dept in the right table d_ Even if no is NULL, all EMPs in the left table e will be read.

select
last_name,first_name,dept_no
from employees a left join dept_emp b
on a.emp_no=b.emp_no

SQL7 finds the employee number EMP of salary records more than 15 times_ No and its corresponding recording times t

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

Note that having cannot be changed to where. Having is used for filtering after grouping

SQL8 find out the current salary status of all employees

select distinct salary from salaries order by salary desc;

SQL10 get EMP of all non manager employees_ no

select emp_no from employees where employees.emp_no not in(select emp_no from dept_manager)

SELECT e.emp_no
FROM employees AS e
LEFT JOIN dept_manager AS d
ON e.emp_no=d.emp_no
WHERE dept_no IS NULL;

SQL11 get the current manager of all employees

Get all employees and their corresponding managers. If the employee is a manager, it will not be displayed

select s.emp_no ,d.emp_no as manager from dept_emp  s inner join dept_manager d
on s.dept_no=d.dept_no
and s.emp_no<>d.emp_no

SELECT e.emp_no, m.emp_no AS manager
FROM dept_emp e LEFT JOIN dept_manager m 
ON e.dept_no = m.dept_no
WHERE e.emp_no <> m.emp_no

SQL12 gets information about the highest salary of current employees in each department

Step 1: the highest salary in each department

select 
  d.dept_no,max(s.salary) salary 
from 
  dept_emp d
join 
  salaries s
on 
  d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01'
group by 
  d.dept_no;

Step 2 (integrate employees, departments and salaries in one table):

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

Step 3: (integrate t1 and t2)

select 
  t1.dept_no,t2.emp_no,t1.salary 
from 
  (surface t1) t1
join 
  (surface t2) t2
on 
  t1.dept_no=t2.dept_no and t1.salary=t2.salary
order by 
  t1.dept_no;

select 
  t1.dept_no,t2.emp_no,t1.salary 
from 
  (select 
    d.dept_no,max(s.salary) salary 
  from 
    dept_emp d
  join 
    salaries s
  on 
    d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01'
  group by 
    d.dept_no) t1
join 
  (select 
    d.emp_no,d.dept_no,s.salary 
  from 
    dept_emp d
  join 
    salaries s
  on 
    d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01') t2
on 
  t1.dept_no=t2.dept_no and t1.salary=t2.salary
order by 
  t1.dept_no;

SQL15 finding the employees table emp_no and last_name's employee information

SELECT *
FROM employees
WHERE emp_no%2=1
AND last_name NOT LIKE 'Mary'
ORDER BY hire_date DESC;

select *from employees where emp_no%2=1 and last_name!='Mary' Order by hire_date desc

select *from employees where MOD(emp_no, 2)=1 and last_name<>'Mary' Order by hire_date desc

Supplement: EMP_ No% 2 = 1 can also be changed to MOD(emp_no, 2)=1
Supplement: inequality can be expressed in three ways: < >,! = IS NOT
Note: last_name is a varchar type, so its judgment needs to be enclosed in single quotes

select *from employees where emp_no&1 and last_name<>'Mary' Order by hire_date desc

General method for querying odd numbers: as above (preferably bit operation &)
General method for querying even numbers: EMP_ no=(emp_no>>1<<1)

select *from employees where emp_no=(emp_no>>1<<1) and last_name<>'Mary' Order by hire_date desc

SQL16 calculates the average salary corresponding to the current salary of employees corresponding to each current title type

Note: AVG(*) is automatically named avg, so there is no need to rename it

SELECT t.title, AVG(salary) 
FROM titles as t
INNER JOIN salaries as s
ON t.emp_no=s.emp_no
GROUP BY t.title;

SQL17 get EMP of the employee with the second highest salary_ No and its corresponding salary salary

limit means to take several records. limit 1,1 means to cross the first record and take one record, that is, take the second record

select emp_no ,salary from  salaries order by salary desc limit 1 ,1

Considering that there may be more than one employee with the highest salary and the second highest salary, it is necessary to sort them by salary group
GROUP BY salary is to divide all the same wages into one group, and then take the second group in descending order

SELECT emp_no, salary
FROM salaries
WHERE salary = (SELECT salary
                FROM salaries                
                GROUP BY salary
                ORDER BY salary DESC LIMIT 1,1 )  

There may be more than one person with the highest salary. First get the value of the highest salary, and then check the employee number and salary by taking this value as a condition

select emp_no, salary
from salaries
where salary = 
    (select distinct salary 
        from salaries 
        order by salary desc 
        limit 1,1
    );

SQL18 get EMP of the employee with the second highest salary_ No and its corresponding salary salary

select e.emp_no,s.salary,e.last_name ,e.first_name from employees  e
inner join salaries  s 
on e.emp_no=s.emp_no
order by s.salary DESC
limit 1,1

However, order by is not allowed

Find the highest ranked salary through self connection and < = sign combined with COUNT function

SELECT e.emp_no, s.salary, e.last_name, e.first_name
FROM employees AS e
INNER JOIN salaries AS s
ON e.emp_no=s.emp_no
AND salary=(SELECT s1.salary
FROM salaries AS s1
INNER JOIN salaries AS s2
WHERE s1.salary <= s2.salary
GROUP BY s1.salary
HAVING COUNT(DISTINCT s2.salary)=2
);

SQL19 find last for all employees_ Name and first_name and corresponding dept_name

Note that all employees are checked, so we can't use the equivalent connection when connecting the table, because it will ignore the null value. I use the left connection

select  last_name ,first_name,dept_name from
(select first_name,last_name,dept_no from employees e left join dept_emp d  on  e.emp_no=d.emp_no) s1 
left join departments  d2
on s1.dept_no= d2.dept_no

Method 1: connect the departments table and Dept first_ The EMP table forms a new table b, and then left connects the employees table and the b table

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

SQL21 finds the salary increase of on-the-job employees since they joined the company

SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT e.emp_no, s.salary
FROM employees AS e
LEFT JOIN salaries AS s
ON e.emp_no=s.emp_no
WHERE s.to_date='9999-01-01') AS sCurrent
INNER JOIN (SELECT e.emp_no, s.salary
FROM employees AS e
LEFT JOIN salaries AS s
ON e.emp_no=s.emp_no
AND s.from_date=e.hire_date) AS sStart
ON sCurrent.emp_no=sStart.emp_no
ORDER BY growth ASC;

SQL22 counts the number of salary records of each department

SELECT d.dept_no, d.dept_name, count(s.salary) AS SUM
FROM departments d JOIN dept_emp de ON d.dept_no = de.dept_no
JOIN salaries s ON de.emp_no = s.emp_no
GROUP BY d.dept_no
ORDER BY d.dept_no ASC