Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "instance usage of mysql database subquery". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

# query the names and salaries of employees in the same department as Zlotkey

SELECT last_name,salaryFROM employeesWHERE department_id= (SELECT department_id FROM employeesWHERE last_name='Zlotkey')

# query the employee number, name and salary of employees whose salary is higher than the average salary of the company

SELECT employee_id,last_name,salaryFROM employeesWHERE salary > (SELECT AVG (salary) FROM employees)

# query the employee number, name and salary of each department whose salary is higher than the average salary of this department

Note: connect the employee table and the average payroll table, and then filter

SELECT employee_id,last_name,salaryFROM (SELECT AVG (salary) aINNER JOIN employees eON a.department_id=e.department_idWHERE e.salary (salary) FROM employees GROUP BY department_id) aINNER JOIN employees eON a.department_id=e.department_idWHERE e.salary > s

# query and the name of the employee with the letter u in the same department's employee number and name

First query the department id that contains u employees, and then check id for the same situation.

SELECT employee_id,last_nameFROM employeesWHERE department_id IN (SELECT DISTINCT department_id FROM employeesWHERE last_name LIKE'% u%')

# query the employee number of an employee working in a department whose location_id is 1700

Note: first query the department number where location_id is equal to 1700, before querying the employee number.

SELECT employee_idFROM employees eWHERE e.`department _ id`IN (SELECT department_id FROM departments d WHERE d.`location _ id` = 1700)

# query the name and salary of the employee whose manager is K_ing. There are two K_ing

SELECT last_name,salaryFROM employees eWHERE e.manager_id IN (SELECT `employee_ id` FROM employees m WHERE `last_ name` = 'kicking')

# query the name of the highest-paid employee and require first_ame and last_name to be shown as one column with the first name as last name. Name

# aliases have special characters that need to be enclosed in quotation marks

SELECT CONCAT (first_name,last_name) 'surname. The name 'FROM employees eWHERE e. Salt = (SELECT MAX (salary) FROM employees); "instance usage of mysql database subquery" ends here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.