多表查询-组函数-子查询

– 多表查询
select * from employees;
select * from departments;
select * from locations;
select * from job_grades;
– 查询名字为Taylor的名字和部门名称
select last_name,department_name from employees em,departments de
where em.department_id=de.department_id and last_name=‘Taylor’;

– 查询每个雇员的last_name、department_name和city（city来源于locations表）
select last_name,department_name,city from employees em,departments de,locations lo
where em.department_id=de.department_id and de.location_id=lo.location_id;

– 查询Taylor的雇员id、部门名称和工作城市(等值连接)
select employee_id,department_name,city from employees em,departments de,locations lo
where em.department_id=de.department_id and de.location_id=lo.location_id and last_name=‘Taylor’;

– 创建案例表
create table job_grades(
lowest_sal number,
highest_sal number,
gra varchar2(10)
);

– 查询所有雇员的薪水级别（非等值连接）
select e.last_name,e.salary,j.gra from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal;

– 自连接
– 查询每个雇员的经理的名字以及雇员的名字
select worker.last_name,man.last_name from employees man,employees worker
where worker.manager_id=man.employee_id;

– 左外连接
– 用左外连接查询雇员名字以及他们所在的部门名称，包含那些没有部门的雇员
select e.last_name,d.department_name from employees e left outer join departments d
on e.department_id=d.department_id;

– 用右外连接查询雇员名字以及他们所在的部门名称，包含那些没有雇员的部门
select e.last_name,d.department_name from employees e right outer join departments d
on e.department_id=d.department_id;

– 用全外连接查询雇员名字以及他们所在的部门名称，包含那些没有雇员的部门和没有部门的雇员
select e.last_name,d.department_name from employees e full outer join departments d
on e.department_id=d.department_id;

– 只能在Oracle中使用的
select e.last_name,d.department_name from employees e,departments d where e.department_id (+)= d.department_id;
select e.last_name,d.department_name from employees e,departments d where e.department_id = d.department_id(+);

– 自然连接
– 查询部门id，部门名称以及他们所在的城市
select d.department_id,d.department_name,l.city from departments d,locations l where d.location_id=l.location_id;
select d.department_id,d.department_name,l.city from departments d natural join locations l;

– 使用using子句创建连接
– 当有多个列匹配时，用using子句匹配唯一的列
– 如果某列在using中使用，那么在引用该列时不要使用表名或者别名
– natural join和using子句是相互排斥的
– 查询部门id为1800的部门名称以及他们所在的城市名称，指定location_id为连接列
select department_id,department_name,city from departments d join locations l
using(location_id) where location_id=1800 ;

– 内连接
– 查询雇员id为202的雇员名字，部门名称，以及工作的城市
– 等值连接
select e.last_name,d.department_name,l.city from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id and e.employee_id=202;
– 内连接
select e.last_name,d.department_name,l.city
from employees e inner join departments d on e.department_id=d.department_id
inner join locations l on d.location_id = l.location_id
where e.employee_id = 202;
– 多表查询小练习
– 写一个查询显示所有雇员的last_name、department_id、department_name
– 等值连接
select e.last_name,d.department_name,d.department_id from employees e,departments d
where e.department_id=d.department_id;
– 内连接
select e.last_name,d.department_name,d.department_id
from employees e inner join departments d on e.department_id=d.department_id;

– 查询部门编号80中所有工作岗位的唯一列表，在输出中包括部门编号、地点编号
select distinct e.job_id,d.location_id from employees e,departments d
where e.department_id=d.department_id and e.department_id=80;

– 写一个查询显示所有有佣金的雇员的last_name、department_name、location_id和城市
select e.last_name,d.department_id,d.location_id,l.city
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id and e.commission_pct is not null;

– 显示所有在其last_names中有一个小写的a的雇员的last_name和department_name
select e.last_name,d.department_name from employees e,departments d
where e.department_id=d.department_id and e.last_name like ‘%a%’;

– 使用内连接写一个查询显示那些工作在Toronto的所有雇员
– last_name、job、department_id和department_name
select e.last_name,e.job_id,e.department_id,d.department_name
from employees e inner join departments d
on e.department_id=d.department_id
inner join locations l on d.location_id=l.location_id
where l.city=‘Toronto’;

– 显示雇员的last_name和employee_id连同他们的经理的last_name和manager_id。
– 列标签分别为Employee、Emp#和Mgr#
select worker.last_name,worker.employee_id “Employee”,mgr.last_name “Emp#”,mgr.employee_id
from employees worker,employees mgr
where worker.manager_id=mgr.employee_id;

– 查询所有雇员的经理包括King，他没有经理。
–显示雇员的名字、雇员id、经理名、经理id、用雇员排序结果
select emp.last_name “Emp_name”,emp.employee_id “Emp_id”,emp.manager_id “Mgr_id”,mgr.last_name “Mgr_name”
from employees emp left outer join employees mgr
on emp.manager_id=mgr.employee_id order by emp.last_name;

– 创建一个查询那是所有与被指定雇员工作在同一部门的雇员的last_name、department_id
– 给每列一个适当的标签
select e.last_name,e.department_id
from employees e,employees c
where e.department_id=c.department_id and e.employee_id<> c.employee_id;

– 显示job_graders表结构。创建一个查询显示所有雇员的
– name、job_id、department_name、salary、grade等
select * from job_grades;
select e.last_name,e.job_id,d.department_name,e.salary,job.gra
from employees e,departments d,job_grades job
where e.department_id=d.department_id and e.salary between job.lowest_sal and job.highest_sal;

– 创建一个查询显示那些在雇员Davies之后入本公司工作的雇员的name和hire_date
select emp.last_name,emp.hire_date from employees emp, employees d
where (emp.hire_date-d.hire_date)>0 and d.last_name=‘Davies’

– 显示所有雇员的name和hire_date，他们在他们的经理前进入本公司。
– 连同他们的经理的名字和受雇日期一起显示。
– 列标签分别为Employee、Emp Hired、Manager和Mgr Hired
select emp.last_name “Employee”,emp.hire_date “Emp Hired”,mgr.employee_id “Manager”,mgr.hire_date “Mgr Hired”
from employees emp,employees mgr
where emp.manager_id = mgr.employee_id and emp.hire_date<mgr.hire_date

– 组函数
– 求雇员表中的平均薪水和薪水总和
select avg(salary),sum(salary) from employees;

select min(salary),max(salary) from employees;
select min(last_name),max(last_name) from employees;
select min(hire_date),max(hire_date) from employees;

select count(*) from employees;
select count(commission_pct) from employees;

select count(*) from employees where department_id=80;
select count(commission_pct) from employees where department_id=80;

select count(distinct department_id) from employees;

– 组函数和空值
– 所有组函数中忽略列中的空值，使用nvl函数来处理空值
– 计算有佣金的员工的佣金平均值
select avg(commission_pct) from employees;
– 计算所有员工的佣金的平均值
select avg(nvl(commission_pct,0)) from employees;

– 组函数小练习
–显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：Maximum、Minimum、Sum和Average。
– 四舍五入结果为最近的整数
select round(max(salary)) “Maximum”,round(min(salary)) “Minimum”,
round(sum(salary)) “Sum”,round(avg(salary)) “Average” from employees;

–修改上题显示每种工作类型的最低。最高。合计和平均薪水
select round(max(salary)) “Maximum”,round(min(salary)) “Minimum”,
round(sum(salary)) “Sum”,round(avg(salary)) “Average” from employees group by job_id;

– 写一个查询显示每一工作岗位的人数
select job_id,count(*) from employees group by job_id;

– 确定经理人数，不需要列出他们，列标签为Number of Managers
select count(distinct manager_id) “Number of Managers” from employees;

– 写一个查询显示最高和最低薪水之间的差。列标签是DIFFERENCE
select max(salary)-min(salary) as difference from employees;

– 显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。
– 排除最低薪水小于等于6000次组。按薪水降序排序输出
select manager_id,min(salary) from employees where manager_id is not null group by manager_id
having min(salary)>6000 order by min(salary) desc;

– 写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。
–四舍五入薪水到两位小数
select d.department_name,l.city,count(*),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 d.department_name,l.city;

– 创建一个查询显示雇员总数和在1995/1996/1997和1998受雇的雇员人数，创建适当的列标题
select count(*),sum(decode(to_char(hire_date,‘yyyy’),‘1995’,1,0)) “1995”,
sum(decode(to_char(hire_date,‘yyyy’),‘1996’,1,0)) “1996”,
sum(decode(to_char(hire_date,‘yyyy’),‘1997’,1,0)) “1997”,
sum(decode(to_char(hire_date,‘yyyy’),‘1998’,1,0)) “1998”
from employees;

– 创建一个混合查询显示工作岗位和工作岗位的薪水合计，并且
– 合计部门20,50,80,90的工作岗位的薪水，给每列一个恰当的列标题
select job_id,sum(salary),sum(decode(department_id,‘20’,salary)) “Dep 20”,
sum(decode(department_id,‘50’,salary)) “Dep 50”,
sum(decode(department_id,‘80’,salary)) “Dep 80”,
sum(decode(department_id,‘90’,salary)) “Dep 90”
from employees group by job_id;

– 子查询
– 查询谁的薪水比Abel高
– 自连接
select em.last_name,em.salary from employees em,employees e
where em.salary>e.salary and e.last_name=‘Abel’;

– 子查询
select em.last_name,em.salary from employees em
where em.salary > (select e.salary from employees e where e.last_name=‘Abel’);

– 显示那些job_id与雇员141相同的雇员的名字与job_id
select em.last_name,em.job_id from employees em
where em.job_id=(select e.job_id from employees e where employee_id=141);

– 显示job_id与雇员141相同，并且薪水高于雇员143 的那些雇员
select e.last_name,e.job_id,e.salary from employees e
where e.job_id = (select e1.job_id from employees e1 where e1.employee_id=141)
and e.salary > (select e2.salary from employees e2 where e2.employee_id=143);

– 显示所有其薪水等于最低薪水的雇员的last_name、job_id、salary
select e.last_name,e.job_id,e.salary from employees e
where e.salary = (select min(salary) from employees);

– 显示所有其最低薪水小于部门50的最低薪水的部门号和最低薪水
select e.department_id,min(e.salary) from employees e
group by e.department_id
having min(e.salary)>(select min(em.salary) from employees em where em.department_id=50);

– (1)查询本部门最高工资的员工
select em.last_name , em.salary,em.department_id from employees em,
(select department_id dd,max(salary) sal from employees group by department_id) temp
where em.salary = temp.sal and em.department_id=temp.dd
order by em.salary desc;

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

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

– 查询每个部门平均薪水的等级
select round(avg(salary)) from employees group by department_id;
select gra from job_grades;

select temp.sal,gra from job_grades j,
(select round(avg(salary)) sal from employees group by department_id)temp
where temp.sal between j.lowest_sal and j.highest_sal;

– 显示工作岗位不是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 salary from employees where job_id=‘IT_PROG’);

– 显示那些薪水低于岗位IT_PROG的最低薪水，并且工作岗位不是IT_PROG的所有雇员
select min(salary) from employees where job_id=‘IT_PROG’;
select last_name,salary from employees
where salary< all(select salary from employees where job_id=‘IT_PROG’)
and job_id<>‘IT_PROG’;

– 子查询小练习
select e.last_name,e.hire_date from employees e
where e.last_name<>‘Zlotkey’
and e.department_id=(select department_id from employees where last_name=‘Zlotkey’);

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

select e.department_id from employees e where e.last_name like ‘%u%’;

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