单行子查询返回多个行_sql多表查询练习，习题总结

组函数(聚合函数)：组函数操作行集，给出每组的结果。组函数不象单行函数，组函数对行的集合进行操作，对每组给出一个结果。这些集合可能是整个表或者是表分成的组。

组函数和单行函数的区别：

单行函数对查询到每个结果集做处理，而组函数只对分组数据做处理。

单行函数对每个结果集返回一个结果，而组函数对每个分组返回一个结果。

组函数的类型

1. AVG 平均值 ---->对封装数据做平均值运算
2. COUNT 计数
3. MAX 最大值
4. MIN 最小值
5. SUM 合计

所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数

计算有佣金的员工的佣金平均值。

select avg(e.commission_pct) from employees e;

计算所有员工的佣金的平均值。

select avg(nvl(e.commission_pct),0) from employees e;

GROUP BY (group by) 子句语法

GROUP BY 子句可以把表中的行划分为组。然后可以用组函数返回每一组的摘要信息。

1. 如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句中。
2. 在 GROUP BY 子句中必须包含列。
3. 在 GROUP BY 子句中你不能用列别名
4. group by 分组后不能再用where 对条件过滤 select出现的列要在 group by 句子 中
5. having --- group by 分组后可以用having 进行条件过滤

注意：在 SELECT 列表中的任何列必须在 GROUP BY 子句中。

求每个部门的平均薪水。

select avg(e.salary) from employees e group by e.department_id;

显示在每个部门中付给每个工作岗位的合计薪水的报告。

select e.job_id,sum(e.salary) from employees e group by e.job_id;

GROUP BY 子句的执行顺序

先进行数据查询，在对数据进行分组，然后执行组函数。

约束分组结果 having

分组后不能使用where 可以用having 进行条件过滤

显示那些最高薪水大于 $10,000 的部门的部门号和最高薪水。

select e.department_id,max(e.salary) from employees e group by e.department_id having max(e.salary)>10000;

嵌套组函数

在使用组函数时我们也可以根据需要来做组函数的嵌套使用。

显示部门中的最大平均薪水。

select max(avg(e.salary)) from employees e group by e.department_id;

子查询

子查询是一个 SELECT 语句，它是嵌在另一个 SELECT 语句中的子句。

• 子查询 (内查询) 在主查询之前执行一次
• 子查询的结果被用于主查询 (外查询)

使用子查询的原则

• 子查询放在圆括号中。
• 将子查询放在比较条件的右边。
• 在单行子查询中用单行运算符，在多行子查询中用多行运算符。

谁的薪水比 Abel 高。

select em.last_name from employees em where em.salary>(select e.salary from employees e where e.last_name='Abel');

多行子查询

• 返回多于一行
• 使用多行比较符

in 等于列表中的任何成员

any 比较子查询返回的每个值 满足其中一个条件即可

<any 小于其中任意值，相当于小于其中最大值返回结果，

>any 大于其中任意值，相当于大于其中最小值就返回结果

all 比较子查询的全部值 满足全部条件

<all 小于最小值

>all 大于最大值

查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID。

select em.last_name,em.salary,em.department_id from employees em where em.salary in (select min(e.salary) from employees e group by e.department_id);

显示工作岗位不是 IT_ PROG 的雇员，并且这些雇员的的薪水少于 IT_PROG 工作岗位的雇员的 ID、名字、工作岗位和薪水。

select e.employee_id,e.last_name,e.job_id,e.salary from employees e where e.job_id <> 'IT_PROG' and e.salary < any (select em.salary from employees em where em.job_id = 'IT_PROG');

显示那些薪水低于工作岗位 IT_ PROG 的最低薪水，并且工作岗位不是 IT_PROG 的所有雇员。

select em.last_name,em.salary from employees em where em.job_id<>'IT_PROG' and em.salary<(select min(e.salary) from employees e where e.job_id='IT_PROG');

练习

自连接

显示雇员的 last name 和 employee number 连同他们的经理的 last name 和 manager number。列标签分别为Employee、Emp#、Manager 和 Mgr#。（注意：雇员manager_id 和 经理的employee_id 相等）

select e.last_name "Employss",e.employee_id "Emp#",m.last_name "Manager",m.employee_id "Mgr#" from employees e , employees m where e.manager_id=m.employee_id;

查询所有雇员的经理包括 King，他没有经理。显示雇员的名字、雇员 ID、经理名、经理 ID、用雇员号排序结果。

select e.last_name,e.employee_id,m.last_name,e.manager_id from employees e left join employees m on (e.manager_id=m.employee_id) order by e.employee_id;

创建一个查询显示那些在雇员 Davies 之后入本公司工作的雇员的 name 和 hire date（先找到Davies 的入职信息在判断）

select e.last_name,e.hire_date from employees e,employees em where e.last_name='Davies' and e.hire_date<em.hire_date;

显示所有雇员的 names 和 hire dates，他们在他们的经理之前进入本公司，连同他们的经理的名字和受雇日期一起显示。列标签分别为 Employee、Emp Hired、Manager 和Mgr Hired。（思路，建立连接：雇员manager_id 和 经理的employee_id 相等，日期越后越大）

select e.last_name "Employee",e.hire_date "Emp Hired",ma.last_name "Manager",ma.hire_date "Mgr Hired" from employees e,employees ma  where e.manager_id=ma.employee_id and e.hire_date< ma.hire_date;

组函数 （group by）

写一个查询显示每个部门的名字、地点城市、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。（）

select e.department_id,l.city,count(e.last_name),round(avg(e.salary),2) from employees e,departments d,locations l where e.department_id=d.department_id and d.location_id=l.location_id group by e.department_id,l.city;

谁的薪水比 Abel 高。（1.子查询，2.内连接）

1.select d.last_name from employees d where d.salary>(select e.salary from employees e where e.last_name='Abel');
2.select e.last_name from employees e,employees d where d.last_name='Abel' and e.salary>d.salary;

显示那些 job ID 与雇员 141 相同的雇员的名字与 job ID。（内连接查询）

select e.last_name,e.employee_id from employees e,employees d where d.employee_id='141' and e.job_id=d.job_id;

显示那些 job ID 与雇员 141 相同的雇员的名字与 job ID。（子查询）

select d.last_name,d.employee_id from employees d where d.job_id=(select e.job_id from employees e where e.employee_id='141');

显示 job ID 与雇员 141 相同，并且薪水 高于雇员 143 的那些雇员。(子查询)

select d.last_name,d.employee_id,d.salary from employees d where d.job_id=(select e.job_id from employees e where e.employee_id='141') and d.salary>(select e.salary from employees e where e.employee_id='141');

显示所有其最低薪水小于 部门 50 的最低薪水的部门号和最低薪水。

select e.last_name,e.job_id,e.salary from employees e where e.salary=(select min(salary)from employees);

显示所有其最低薪水大于 部门 50 的最低薪水的部门号和最低薪水。

显示那些薪水低于工作岗位 IT_ PROG 的最低薪水，并且工作岗位不是 IT_PROG 的所有雇员。

select em.last_name from employees em where em.job_id <>'IT_PROG' and em.salary<(select min(e.salary) from employees e where e.job_id='IT_PROG');

---<all

select em.last_name from employees em where em.job_id <>'IT_PROG' and em.salary< all(select e.salary from employees e where e.job_id='IT_PROG');

写一个查询显示与 Zlotkey 在同一部门的雇员的 last name和 hire date（1.子查询 2.内连接）

1.select em.last_name,em.hire_date from employees em where em.department_id  = (select e.department_id from employees e where e.last_name='Zlotkey');
2.select e.last_name,e.department_id from employees e,employees em where em.last_name='Zlotkey' and em.department_id=e.department_id;

写一个查询显示与 Zlotkey 在同一部门的雇员的 last name和 hire date，结果中不包括 Zlotkey。

select em.last_name,em.hire_date from employees em,(select  e.last_name,e.department_id from employees e where e.last_name='Zlotkey') emp where em.department_id  =emp.department_id and em.last_name<>emp.last_name ;

创建一个查询显示所有其薪水高于平均薪水的雇员的雇员号和名字。按薪水的升序排序。

select e.department_id,e.last_name from employees e where e.salary> (select avg(em.salary) from employees em) order by e.salary asc;

写一个查询显示所有工作在有任一雇员的名字中包含一个 u的部门中的雇员的雇员号和名字。

select em.employee_id,em.last_name from employees em where em.department_id in (select distinct e.department_id from employees e where e.last_name like '%u%');

显示所有部门地点号 (department location ID ) 是 1700的雇员的 last name、department number 和 job ID。（1.内连接2.子查询）

1.select e.last_name,d.department_id,e.job_id from employees e,departments d where d.location_id=1700 and d.department_id=e.department_id;
2.select e.last_name,e.department_id,e.job_id from employees e where e.department_id in (select d.department_id from departments d where d.location_id=1700);

显示经理 King 下的雇员的名字和薪水。（1.子查询 2.内连接）

1.select m.last_name,m.salary from employees m where m.manager_id in (select e.employee_id from employees e where e.last_name='King');
2.select e.last_name,e.salary from employees e,employees em where em.last_name='King' and em.employee_id=e.manager_id;

显 示 在 Executive 部 门 的 每 个 雇 员 的 departmentnumber、last name 和 job ID。（1.子链接2.内连接）

1.select em.department_id,em.last_name,em.job_id from employees em where em.department_id=(select d.department_id from departments d where d.department_name='Executive');
2.select e.department_id,e.last_name,e.job_id from employees e,departments d where d.department_name='Executive' and d.department_id=e.department_id;

查询显示所有收入高于平均薪水并且工作在有任一雇员的名字中带有一个 u 的部门的雇员的 employee numbers、lastnames 和 salaries。（1.子查询 2.内连接）

1.select e.employee_id,e.last_name,e.salary from employees e where e.salary > (select avg(salary) from employees) and e.department_id in (select emp.department_id from employees emp where emp.last_name like ('%u%'));
2.select distinct e.employee_id,e.last_name,e.salary from employees e,employees em where e.department_id=em.department_id and em.last_name like '%u%' and e.salary>(select avg(salary) from employees );

显示那些雇员低于他们部门最高薪水的雇员的名字，薪水，部门号和他们部门最高的薪水

select e.last_name,e.salary,e.department_id,ma.aa from employees e ,(select max(e1.salary) aa,e1.department_id from employees e1 group by e1.department_id ) ma where  e.salary < ma.aa and e.department_id=ma.department_id;