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

Subqueries: types, syntax, and considerations

What problems can be solved by using subqueries?

Subquery syntax:

Select select_list from table where expr operator (select select_list from table)

The subquery (inner query) is executed before the main query (outer query).

The main query uses the results of the subquery.

Location: select,where,from,having

1. Find out who has a higher salary than Abel

Select last_name, salary from employees

Where salary >

(select salary

From employees

Where last_name = 'Abel')

Considerations for using subqueries

The subquery should be contained in parentheses.

Place subqueries on the right side of the comparison condition to enhance readability (subqueries can appear on both sides of the comparison operator)

Single-line operators correspond to single-row subqueries, and multi-line operators correspond to multiple-row subqueries.

Single-line subquery:

-Group functions in subqueries

Subqueries in the-HAVING clause

Only one row is returned

Use a single-line comparison operator

Operator meaning = equal to > greater than > = greater than or equal to, 'IT_PROG'

Using the ALL operator in multiple-row subqueries

Select employee_id, last_name, job_id, salary

From employees

Where salary

< all (select salary from employees where job_id = 'IT_PROG') and job_id 'IT_PROG'; 子查询中的空值 select emp.last_name from employees emp where emp.employee_id not in (select mgr.manager_id from employees mgr); 1、HR 部门的同事想要你帮忙写一个 SQL 语句，该 SQL 语句可以传入一个值（员工的 last_name），然后返回结果是该员工同一部门同事的 last_name 和 hire_date，且要求该员工不在返回结果中。 举个例子，如果用户输入"Zlotkey"，结果就会返回除了 Zlotkey 之外的同一部门的其他同事的 last_name 和 hire_date. select last_name,hire_date from employees where department_id =(select department_id from employees where last_name= '&&enter_name') and last_name < >

'& enter_name'

2. Please find out the employee_id,last_name,salary of all employees who are higher than the average salary, and sort the final results according to the descending order of salary.

Select employee_id,last_name,salary

From employees

Where salary > (select avg (salary))

From employees)

Order by salary

3. Please write a SQL statement to find out the employee_id,last_name of those employees in the same department whose last_name contains the letter "u".

Select employee_id,last_name from employees where department_id in (select department_id from employees where last_name like'% u%')

4. Please help colleagues in the HR department to find out the last_name,department_id,job_id of all employees whose location_id is 1700.

Select last_name,department_id,job_id

From employees

Where department_id in (select department_id

From departments

Where location_id=1700)

Let the user choose to enter a location_id and then output the result.

Select last_name,department_id,job_id

From employees

Where department_id in (select department_id

From departments

Where location_id=&enter_location)

5. Please find out the last_name and salary of all employees who need to report to King

Select last_name,salary,manager_id

From employees

Where manager_id = (select employee_id

From employees

Where last_name like 'King' and manager_id is null)

6. Please find out the department_id,last_name,job_id of all employees who are Executive.

Select department_id,last_name,job_id

From employees

Where department_id in (select department_id

From departments

Where department_name like 'Executive')

7. Please find out the last_name of all employees whose salary is higher than any employee whose department_id is 60.

Select department_id,last_name,salary from employees

Where salary > any

(select salary from employees

Where department_id=60)

8. Query the employee_id,last_name,salary of all employees who are above average salary and whose last_name contains the letter "u" in the same department.

Select employee_id,last_name,salary

From employees

Where department_id in (select department_id

From employees

Where last_name like'% u%')

And salary > (select avg (salary) from employees)

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.