Shulou(Shulou.com)06/01 Report--

Let's talk about the subquery of mysql data. The secret of the text is that it is close to the topic. So, no gossip, let's just read the following, I believe you will benefit from reading this article of mysql data subquery.

# 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. Name 'FROM employees eWHERE e.salary= (SELECT MAX (salary) FROM employees)

);

For the above mysql data sub-query related content, is there anything you do not understand? Or if you want to know more about it, you can continue to follow our industry information section.

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.