12.28笔记及作业

查询EMP、DEPT表，输出的列包含员工姓名、工资、部门编号、部门名称、部门地址.
select emp.empno,emp.ename,emp.sal,dept.deptno,dept.dname,dept.loc from emp,dept
where emp.deptno=dept.deptno order by emp.empno;
使用自连接查询EMP表，输出的列包含员工姓名、主管姓名.
select worker.ename ‘雇员姓名’,’works for ‘,manager.ename ‘经理姓名’ from emp worker,emp manager
where worker.mgr=manager.empno;
在第2题的基础上，思考下为什么输出结果没有KING的信息？ 如果要输出KING的信息，如何修改？
select worker.ename,’works for ‘,manager.ename from emp worker,emp manager
where worker.mgr=manager.empno(+);
使用左连接查询员工部门，包括没有员工的部门信息，输出列：部门编号、部门名称、位置。
—（左表为dept表，emp为右表）
select emp.ename,emp.sal,dept.deptno,dept.dname,dept.loc from emp,deptno
where emp.deptno(+)=dept.deptno order by emp.empno;
—综合查询
select job,sum(sal) payroll
from emp
where job not like ‘SALES%’ —职位是不是销售
group by job —根据职位进行分组
having sum(sal)>5000 —薪资总和大于5000
order by sum(sal) desc； —根据薪资总和排序
—group by 列可不在select 列表中()
select avg(sal),deptno from emp group by deptno;
select avg(sal),job from emp group by job
having sum(sal)>3000 order by job desc;
—子查询
elect from emp where ename=’JONES’
select from emp where sal>(elect sal from emp where empno=7566);
select from emp where sal<(elect sal from emp where empno=7566);
select from emp where sal<(select sal from emp where sal<2975) order by sal dessc;
—查询薪资比ALLEN高的有几人
select count() from emp where sal>(select sal from emp where ename=’ALLEN’);
—查询跟SMITH职位一样，薪资大于ALLEN有哪些人
select from emp where job =(select job from emp where ename=’SMITH’)’
and sal>(select sal from emp where ename=’ALLEN’);
—按部门分组，查询出比部门20最低薪资高的最低薪资
select min(sal) from emp where min(sal)>
(select min(sal) from emp where deptno=20) group by deptno ;
—any运算符
select empno,ename,job feom emp where sal >
any(select sal from emp where job=’CLERK’)
and job <>’CLERK’;
—all运算
select empno,ename,job,sal from emp where sal >
all(select avg(sal) from emp group by deptno);
作业
—1.请查SMITH领导的薪水
select b.sal from emp a,emp b
where a.mgr=b.empno and a.ename=’SMITH’;
—2.请查SMITH领导的薪水和所在的部门地址
select b.sal,c.loc from emp a,emp b,dept c
where a.mgr=b.empno and b.deptno=c.deptno and a.ename=’SMITH’
—3.请查SMITH领导的薪水和所在的部门地址以及领导的薪水等级
select b.ename,b.sal,c.loc,d.grade from emp a,emp b,dept c,salgrade d
where a.mgr=b.empno and b.deptno=c.deptno
and (b.sal between losal and hisal) and a.ename=’SMITH’;
—4.查出SMITH的薪水等级
select b.grade from emp a,salgrade b
where (a.sal between losal and hisal) and a.ename=’SMITH’;
—5.请查出SIMIH的薪水等级和他所在部门所在地
select b.grade,c.loc from emp a,salgrade b,dept c
where (a.sal between losal and hisal)
and c.deptno=a.deptno and a.ename=’SMITH’;
—6.按照职位分组，求出每个职位的最大薪水
select deptno,max(sal) from emp
group by deptno;
—7.
—I)求出每个部门中的每个职位的最大薪水
select deptno,job,max(sal) from emp
group by deptno,job
—II)在薪水大于1000，并且职位不是MANAGER的员工中，求职哪个职位的平均薪水大于2000
select job,avg(sal) from
(select * from emp
where sal>1000 and job!=’MANAGER’)
group by job having avg(sal)>2000
—8、列出SMITH的薪水和职位
select sal,job from emp
where ename=’SMITH’
—9、列出SMITH的部门地址和补贴和薪水等级
select b.loc,nvl(a.comm,0),c.grade from emp a,dept b,salgrade c
where a.deptno=b.deptno and (a.sal between losal and hisal) and a.ename=’SMITH’;
—10、列出薪金比”SMITH”多的所有员工
select ename from emp
where sal>(select sal from emp
where ename=’SMITH’);
—11、列出所有员工的姓名及其直接上级的姓名
—join
select a.ename “员工姓名”,b.ename “上级姓名” from emp a
left join emp b on a.mgr=b.empno
—+
select a.ename “员工姓名”,b.ename “上级姓名” from emp a,emp b
where a.mgr=b.empno(+)
—12、列出部门不是10，职位不是C开头的，薪资比公司平均薪资都高的员工名字
select ename from emp
where deptno!=’10’ and job not like ‘C%’
and sal>(select avg(sal) from emp);
—13、哪个部门下面没有员工
select deptno from dept
where deptno not in (select deptno from emp)
select deptno from (select a.ename,b.deptno from emp a,dept b
where a.deptno(+)=b.deptno)
where ename is null;
—14、谁的薪水比SMITH多，同时部门又是和SCOTT的部门相同
select ename from emp
where sal>(select sal from emp where ename=’SMITH’)
and deptno=(select deptno from emp where ename=’SCOTT’)
—15、列出薪资比每个部门每个职位的平均薪资还要高的员工
select ename from emp
where sal>all(select avg(sal) from emp group by deptno)
and sal>all(select avg(sal) from emp group by job)
—16、列出至少有一个员工的所有部门
select deptno from (select deptno,count(1) a from emp
group by deptno)
where a >1
—17、查找已经入职 8 个月多的员工
select ename from emp
where (sysdate-hiredate)/12>8;
—18、显示满 10 年服务年限的员工的姓名和受雇日期。
select ename,hiredate from emp
where (sysdate-hiredate)/365>10;
—19、对于每个员工，显示其加入公司的天数。
select ename,(sysdate-hiredate) from emp;
—20、找出各月倒数第 3 天受雇的所有员工。
select ename from emp
where hiredate=last_day(hiredate)-2;
—21、显示薪水的时候，把本地货币单位加在前面
select ‘￥’ || sal from emp;
—22、显示 1980 年入职的所有员工
select ename from emp
where to_char(hiredate,’yyyy’)=1980;
—23、显示所有 12 月份入职的员工
select ename from emp
where to_char(hiredate,’mm’)=12;