今天学习了oracle表的设计,及其多表的联合查询。。。。 --找出it部门所有员工当中薪水大于5k的姓名和薪水 select first_name , salary from employees e inner join departments d on e.department_id=d.department_id where d.department_name='IT' and e.salary>5000; --显示所有员工及其所属的部门名称 select e.first_name,d.department_name from employees e , departments d where e.department_id=d.department_id(+); --查询出员工中工资大于平均工资的哪些人。 select round(avg(salary),2) from employees; select first_name ,salary from employees where salary>(select round(avg(salary),2)from employees); --1.查询出各个部门的平均工资和部门号 select department_id,round(avg(salary),2) from employees group by department_id; --2.把上面的查询结果看作是一张子表,查询出员工工资大于本部门的平均工资的那些人的名字,工资和他所属部门的平均工资。 select e.first_name ,e.salary ,f.ss from employees e inner join (select department_id,round(avg(salary),2) ss from employees group by department_id ) f on e.department_id=f.department_id and e.salary>f.ss; --获取在部门编号为60,90工作的员工姓名。 select first_name from employees where department_id in(60,90); --获取在伦敦London工作的员工姓名。 select d.department_id from departments d inner join locations l on d.location_id=l.location_id where l.city='London'; select e.first_name from employees e where e.department_id in(select d.department_id from departments d inner join locations l on d.location_id=l.location_id where l.city='London'); --获取在地区编号为2400工作的所有员工。 select department_id from departments d where d.location_id=2400; select *from employees e where department_id in(select department_id from departments d where d.location_id=2400);