MySQL 5-day Express - day 3

Introduction to sub query
Meaning: select statements that appear in other statements are called subqueries or intra queries
External query statements are called external queries or primary queries
Classification:
Classification by location of sub query:
After select
from back
Support table sub query
After having or where
Support scalar subquery
Column subquery
Row subquery
After exists (related sub query)

Different by the number of rows and columns in the result set
Scalar subquery (result set has only one row and one column)
Column subquery (the result set has only one column and multiple rows)
Row subquery (result set has multiple rows and columns)
Table sub query (the result set is generally multi row and multi column)

Behind where and having
Scalar subquery (single line subquery)
Column subquery (multi row subquery)
Row subquery (multi column and multi row)
Features: 1 Subqueries are enclosed in parentheses
2. Sub queries are generally placed on the right side of the criteria
3. Scalar subqueries are generally used with single line operators < > > = < = < >
Column subqueries are generally used with multi row operators
in any/some all
4. The execution of sub queries takes precedence over the main query, and the conditions of the main query use the results of the sub query

Illegal use of scalar subquery: the result of subquery is not row by column

Whose salary is higher than AB

1.query AB Salary
select  salary 
from employees
where lastname = 'AB';
2.To query employee information, you need to meet the result of 1
select *
from employees
where employees.salary >(
		select  salary 
		from employees
		where lastname = 'AB');

Return the LastName job of the employee with the lowest salary in the company_ id salary

1.Query the minimum wage in the company
select  min(salary)
from employees;
2.select  lastname ,job_id,salary
from employees
where salary = (
	select  min(salary)
	from employees);

Query the Department id and minimum wage of the Department whose minimum wage is greater than the minimum wage of No. 50 department

1.Query the salary of department No. 50
select salary
from departments
where department_id = 50
2.Query the minimum wage of each department
select department_id,min(salary)
from employees;
group by department_id;

3.Filter 1 based on 2
select department_id,min(select)
from departments
group by department_id
having min(salary)>(
	select salary
	from departments
	where department_id = 50);

Column subquery
Multi line subquery: returns multiple lines and uses multiple line comparators

Operator	meaning
in / not in	Equal to any one in the list
any / some	And a value returned by the subquery
all	And all the values returned by the subquery

Return to local_ Names of all employees in the 1400 or 1700 department in the ID

1.select department_id
from departments
where local_id in (1400,1700);

2.select lastname
from employees
where department_id in (
select department_id
from departments
where local_id in (1400,1700));

Row subquery
The result set has one row and multiple columns or multiple rows and multiple columns
Query the information of the employee with the lowest employee number and the highest salary

select *
from employees
where(employ_id,salary)=
(select min(employ_id),max(salsry)
from employees);

After select
Number of employees per department

select d.*,(
	select count(*)
	from employees e
	where e.department_id = d.department_id) number
	from departments d;

Query the employee name with employee No. 102

select lastname
from employees
where employ_id in(
	select employ_id
	from employ
	where local_id = 102);

from back
Use the subquery as a table and require aliasing
Query the work grade of the average salary of each department

select  avg(salary),department_id
from employees
group by department_id

select * from job_grade

2.Connect
select avg.*,g.grade_level
from (
	select  avg(salary) ag,department_id
	from employees
	group by department_id
) avg inner join job_grade j
on avg.ag between max(salary) and min(salary);

Related sub queries after exists

select  exists (select employees_id from employees);

Syntax: exists (complete query statement)
Result: only 1 or 0
Query the Department name with employees

select department_name
from department d
where exists(
	select *
	from employees e
	where d.department_id = e.department_id);