Oracle作业01

==============================================================
(12)查询各个管理者属下员工的最低工资，其中最低工资不能低于800
，没有管理者的员工不计算在内
select min(salary) from employee_lgm
group by job;
==============================================================
(11)查询员工中最高工资和最高工资的差距，列名为DIFFERENCE
select max(salary)-min(salary) DIFFERENCE from employee_lgm;
==============================================================
(4)查询各部门员工姓名和他们所在的位置，结果类似于下面的格式
Deptno Ename Loc
20 SMITH DALLAS
select e.deptno,e.ename,d.location from employee_lgm e
(21)哪些员工的工资，高于整个公司的平均，列出员工的名字和工资(降序)
select ename,salary from employee_lgm
where salary>(select avg(salary) from employee_lgm)
order by salary desc;
where deptno = e.deptno);
==============================================================
(23)列出各个部门中工资最高的员工的信息：名字，部门号，工资
select ename,deptno,salary from employee_lgm e
where ename='lucy');
==============================================================
(8)JONES领导是谁。
select ename from employee_lgm
where empno =(select mgr from employee_lgm
join department_lgm d
on e.deptno = d.deptno
;
==============================================================
(14)查询和scoot相同部门的员工姓名ename和雇佣日期
select ename ,hirdate from employee_lgm
select d.dname, count(*) emp_sum from employee_lgm e
join department_lgm d
on e.deptno = d.deptno
where e.salary>1500;
==============================================================
where ename='lucy';
==============================================================
(15)查询工资比公司平均工资高的所有员工的员工号empno，姓名ename和工资sal
select empno,ename,salary from employee_lgm
join department_lgm d
on e.deptno = d.deptno
where d.dname='market';
==============================================================
(20)显示各个部门中工资大于1500的员工数
dname 和岗位名称job
select e.ename,e.job,e.deptno,d.dname,d.location from employee_lgm e
join department_lgm d
on e.deptno=d.deptno
where d.location='wuhan';
where salary>(select avg(salary) from employee_lgm);
==============================================================
(16)查询和姓名中包含u的员工在相同的部门的员工和员工号empno和姓名enmae
==============================================================
==============================================================
(18)查询管理者是king的员工的姓名ename和工资
select ename ,salary from employee_lgm
where mgr=(select empno from employee_lgm
==============================================================
(7)查询JONES的领导是谁(JONES 向谁报告)
select ename from employee_lgm
where empno =(select mgr from employee_lgm
SIMTH 7369 FORD 7920
select t1.ename,t1.empno Emp#,t2.ename,t2.empno Mgr# from employee_lgm t1
join employee_lgm t2 on t1.mgr = t2.empno;
Oracle作业01：
(1)显示所有员工的姓名ename，部门编号deptno和部门名称dename
select e.ename,e.deptno,d.dname from employee_lgm e
join department_lgm d
on e.deptno=d.deptno;
==============================================================
where mgr=(select ename from employee_lgm);
==============================================================
(13)查询所有部门的部门名字dname，所在位置loc，员工数量和工资平均值
select d.dname,d.location,avg(salary) ,count(*) SUM from employee_lgm e
==============================================================
(3)选择所有员工的姓名ename,员工号deptno，以及他的管理者mgr的姓
名ename和员工号deptno，结果类似于下面的格式
employees Emp# manager Mgr#
where salary=(select max(salary) from employee_lg==========================================================
(24)那个部门的平均工资是最高的，列出部门号，平均工资
from employee_lgm;
==============================================================
(6)列出每个员工的名字，工资，涨薪后工资(涨幅为8%)，单位为元
select ename ,salary ,salary*(1.08) from employee_lgm;
==============================================================
(22)所在部门平均工资高于1500的员工的名字
select ename from employee_lgm e
where 7000<(select avg(salary) from employee_lgm
where ename='liucs');
==============================================================
(19)显示Operations部门有哪些职位
select distinct e.job from employee_lgm e
select empno ,ename from employee_lgm
where ename like '%n%';
==============================================================
(17)查询部门的loc为newYork的部门工作人员和姓名ename,部门名称