[打卡]xgl 子查询

SELECT max(salary),min(salary),avg(salary),sum(salary)
from employees;

SELECT job_id,max(salary),min(salary),avg(salary),sum(salary)
from employees
GROUP BY job_id;

SELECT job_id,COUNT(job_id)
from employees
GROUP BY job_id;

SELECT max(salary),min(salary),max(salary)-min(salary) cj
from employees;

SELECT manager_id,min(salary)
from employees
where manager_id is NOT NULL
GROUP BY manager_id
HAVING min(salary)>=6000;

SELECT department_name,location_id,COUNT(department_name),avg(salary)
from emp[loyees e RIGHT JOIN departments d
on e.depaartment_id=d.department_id
GROUP BY department_name,location_id
ORDER BY avg(salary) desc;

SELECT employee_id,salary,department_name,location_id
from employees e RIGHT JOIN departments d
on d.department_id=e.department_id;

#子查询
#需求：谁的工资比ABEL的高
SELECT salary
FROM employees
where last_name='Abel';

SELECT last_name,salary
from employees
where salary>11000;

#方式二 自连接
SELECT e2.last_name,e2.salary
from employees e1,employees e2
where e2.salary>e1.salary #链接条件
and e1.last_name='Abel'

#方式三 子查询

SELECT last_name,salary
from employees
where salary>(
SELECT salary
FROM employees
where last_name='Abel'
);

#称谓的规范 外查询，内查询

#外查询


#内查询

/*
子查询在主查询之前一次执行完成
子查询的结果
子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行对应多行子查询


#子查询的分类
角度1：从内查询返回的结果条目数
单行子查询 VS 多行子查询
/*
相关子查询VS 不相关子查询