--1.使用等值连接,显示员工的编号,姓名,部门名,使用表别名简化(=号]
select e.employee_id 员工编号,e.first_name 姓名,d.department_name 部门名
from employees e,departments d
where e.department_id=d.department_id;
--2..使用非等值连接(就是不能使用=号),显示员工的编号,姓名,月薪,工资级别
select e.employee_id 员工编号,e.first_name 姓名,e.salary 月薪,s.s_grade
from employees e,salary_grade s
where e.salary between s.salary_min and s.salary_max;
--3使用外连接,按部门10,20,30,40号,统计各部门员工人数,显示部门号,
--部门名,人数
--select department_id 部门号,department_name 部门名,count(department_id)
--from departments;
--order by department_id ;
--在select子句中,只可以有组函数和分组字段
select e.department_id 部门号,d.department_name 部门名,count(e.employee_id) 人数
from employees e
right join departments d
on(d.department_id=e.department_id)
where e.department_id in(10,20,30,40)
group by e.department_id,d.department_name
order by e.department_id ;
--4使用自连接,显示"SMITH的老板是FORD"这种格式,表示层次关联的数据,用自连接
select e1.first_name||'的老板是'||e2.first_name 隶属关系
from employees e1,employees e2
where e1.manager_id=e2.employee_id;
--5查询部门名为'SALES'的员工信息
select *
from employees e
left join departments d
on(e.department_id=d.department_id)
where d.department_name='Sales';
--6查询每个部门的最低工资
select department_id 部门号,min(salary)最低工资
from employees
group by department_id;
--7查询工资比20号部门[任意any] 一个员工[低<] 的员工信息(多行子查询,any关键字)
--1查询20号部门的工资
select salary
from employees
where department_id=20;
--查询工资比20号部门[任意any] 一个员工[低<] 的员工信息
select *
from employees
where salary<any(select salary
from employees
where department_id=20
);
--8查询出平均工资小于5000的部门信息
select d.department_id,avg(salary)
from employees e
right join(departments d)
on(e.department_id=d.department_id)
group by d.department_id having avg(salary)>5000;
--having 分组后进行约束
select e.employee_id 员工编号,e.first_name 姓名,d.department_name 部门名
from employees e,departments d
where e.department_id=d.department_id;
--2..使用非等值连接(就是不能使用=号),显示员工的编号,姓名,月薪,工资级别
select e.employee_id 员工编号,e.first_name 姓名,e.salary 月薪,s.s_grade
from employees e,salary_grade s
where e.salary between s.salary_min and s.salary_max;
--3使用外连接,按部门10,20,30,40号,统计各部门员工人数,显示部门号,
--部门名,人数
--select department_id 部门号,department_name 部门名,count(department_id)
--from departments;
--order by department_id ;
--在select子句中,只可以有组函数和分组字段
select e.department_id 部门号,d.department_name 部门名,count(e.employee_id) 人数
from employees e
right join departments d
on(d.department_id=e.department_id)
where e.department_id in(10,20,30,40)
group by e.department_id,d.department_name
order by e.department_id ;
--4使用自连接,显示"SMITH的老板是FORD"这种格式,表示层次关联的数据,用自连接
select e1.first_name||'的老板是'||e2.first_name 隶属关系
from employees e1,employees e2
where e1.manager_id=e2.employee_id;
--5查询部门名为'SALES'的员工信息
select *
from employees e
left join departments d
on(e.department_id=d.department_id)
where d.department_name='Sales';
--6查询每个部门的最低工资
select department_id 部门号,min(salary)最低工资
from employees
group by department_id;
--7查询工资比20号部门[任意any] 一个员工[低<] 的员工信息(多行子查询,any关键字)
--1查询20号部门的工资
select salary
from employees
where department_id=20;
--查询工资比20号部门[任意any] 一个员工[低<] 的员工信息
select *
from employees
where salary<any(select salary
from employees
where department_id=20
);
--8查询出平均工资小于5000的部门信息
select d.department_id,avg(salary)
from employees e
right join(departments d)
on(e.department_id=d.department_id)
group by d.department_id having avg(salary)>5000;
--having 分组后进行约束