说明:表数据来自oracle 初始用户之一scott里面的三个初始表:emp,dept,salgrade
--1、查询员工表中工资最高的雇员的员工号、员工姓名、工资和部门号。
select empno,ename,sal,deptno from emp where sal=(select max(sal) from emp);
--2、薪水大于1200的雇员,按照部门编号进行分组,分组后平均薪水必须大于1500,查询各分组的平均水平,按照工资的倒序进行排列。
select deptno,avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc;
--3、查询每个雇员和其所在部门名、按照员工名字排序,要求值显示第三条到第六条记录
-- rownum是伪列,rownum>2,没有查询到任何记录。
-- 因为rownum总是从1开始的,第一条不满足去掉的话,第二条的rownum 又成了1。依此类推,所以永远没有满足条件的记录。
-- 可以这样理解:rownum是一个序列,是Oracle数据库从数据文件或缓冲区中读取数据的顺序
select * from
(select emp.ename,dname,rownum rn from emp,dept where emp.deptno=dept.deptno and rownum<=6 order by emp.ename )--必须是内小于外大于
where rn>=3;
-- 内连接 只返回两个表中连接字段相等的行。
select ename,dname from emp inner join dept on emp.deptno = dept.deptno;
-- 第二种方式
select * from
(select emp.ename,dname,rownum rn from emp,dept where emp.deptno=dept.deptno order by emp.ename )
where rn between 3 and 6;
-- 第三种
select * from(
select t.*, rownum rn from(
select ename,dname from emp inner join dept on emp.deptno = dept.deptno order by emp.ename
) t where rownum<=6
) where rn>=3;
-- 4、查询每个雇员姓名以及工资所在的等级。
--第一种
select ename,sal,grade from emp,salgrade where sal between losal and hisal;
--第二种 内连接
select ename,sal,grade from emp inner join salgrade on sal between losal and hisal;
--5、查询雇员名第二个字母不是a的雇员的姓名、所在的组名、工资所在的等级
--第一种
--用substr分割截取字符串
select ename,dname,grade from emp,dept,salgrade
where sal between losal and hisal and emp.deptno=dept.deptno and substr(ename,2,1) !='a' and substr(ename,2,1) !='A';
--第二种
--内连接 模糊查询,第一个下划线代表第一个字符
select ename,dname,grade
from emp inner join dept on dept.deptno=emp.deptno
inner join salgrade on sal between losal and hisal
where ename not like '_A%';
--6、查询每个雇员和其经理的姓名
--第一种 使用自连接
select e1.ename empname,e2.ename mgrname from emp e1,emp e2
where e1.mgr=e2.empno;
--第二种 自连接与内连接
select e1.ename empname,e2.ename mgrname
from emp e1 inner join emp e2
on e1.mgr=e2.empno;
--7、查询每个雇员和其经理的姓名(包括公司老板本身(他上面没有经理));
--左连接,左表中所有记录()以及右表中连接字段相等的记录
select e1.ename empname,e2.ename mgrname from emp e1 left join emp e2 on e1.mgr=e2.empno;
--8、查询每个雇员的姓名及其所在部门的部门名(包括没有雇员的部门)
--右连接,油表中所有记录()以及右表中连接字段相等的记录
select ename,dname from emp right join dept on emp.deptno=dept.deptno;
--9、查询每个部门中工资最高的人的姓名、薪水和部门编号。
--把查询变成一个子表
--group by要包含所有选择的字段(除了函数的),所以先查询出每个部门最高工资,然后和原始表关联,就可以查出部门最高工资者的所有信息
select ename,sal,emp.deptno from emp
inner join
(select deptno,max(sal) msal from emp group by deptno) maxsalresult
on maxsalresult.deptno=emp.deptno and sal=maxsalresult.msal;
--10、查询每个部门平均工资所在的等级
select deptno,grade,avgsalresult.avgsal avragesal
from salgrade inner join
(select avg(sal) avgsal,deptno from emp group by deptno) avgsalresult
on avgsal between losal and hisal;