oracle:oracle学习案例(五)：基础练习

基本oracle语句应用

---

1. 字符和日期型数据应包含在单引号中

select job,sum(sal) from emp
where job !='SALESMAN'
group by job
having sum(sal)>5000
order by sum(sal) ;

select * from emp
where sal>(select sal from emp where ename='SMITH');

select * from emp
where sal>(select avg(sal) from emp );

2. 子查询

select * from emp
where job=(select job from emp where ename='ALLEN')
and sal=(select sal from emp where ename='ALLEN');

3. 找出和经理一样工资的员工信息（经理不止一个,且工资不同)

select * from emp
where sal in (select sal from emp where job='MANAGER');

4. 上述问题的等价形式

select * from emp
where sal =any(select sal from emp where job='MANAGER');

select sal from emp e right join dept d on e.deptno=e.deptno

5. 返回部门最高的平均工资

select max(avg(sal) )from emp
group by deptno;

select dname,count(*),avg(e.sal)
from dept d left join emp e on d.deptno=e.deptno
group by dname;

6.查询出每个部门的编号、名称、位置、部门人数、平均工资

/*
1.问题：什么时候用多字段分组？(查询的(多个)字段位于非主表时(分组表)时)
2.多字段分组时，groupby后面的字段存在优先级，一般统计函数所在的字段优先；
*/
select d.deptno,dname,count(*),d.loc,avg(e.sal)
from dept d left join emp e on d.deptno=e.deptno
group by d.deptno,dname,loc;

-- 上述问题使用子查询                               

select deptno,dname,b.avgSal,loc 
from dept d,(select deptno dn,avg(sal) as avgSal from emp group by deptno) b
where deptno=b.dn;
  

7. 查询行号

/*
3.问题：为什么用e.*而不用*？
-- * 已经代表全部，再加rownum时会出现矛盾；所以这里需要特别指定；
*/
select rownum,e.* from emp e;
-- 查询前 5 条记录
select rownum,e.* from emp e
where  rownum<6;
-- 查询 6-10 条记录
--select rownum,e.* from emp e
--where rownum between 6 and 10;
-- 上述语句会报错，原因是rownum不能同时使用>和<
-- 改进
select * from 
(select rownum rm ,e.* from emp e where rownum<11) b
where b.rm>5;

8. 分页

select * from 
(select rownum as rm,e.* from emp e where rownum<to_number('&endNum')) b 
where b.rm > to_number('&startNum');

9.要求查询出每一位雇员的姓名、职位、雇员的直接上级领导的姓名

select e1.ename,e1.job,e2.ename 
from emp e1 left join emp e2 on e1.mgr=e2.empno ;

select e1.ename,e1.job,e2.ename,d.dname 
from emp e1 left join emp e2 on e1.mgr=e2.empno 
left join dept d on e1.deptno=d.deptno;

select e1.empno,e1.sal,e1.ename,e1.job,e2.ename,d.dname,d.loc 
from emp e1 left join emp e2 on e1.mgr=e2.empno 
left join dept d on e1.deptno=d.deptno;

10. 创建子表

create table B as
select * from emp
where sal>=3000;

11.行号练习

-- 先取行号，再排序
select rownum,e.* from emp e order by sal desc;

12.查询每个部门平均薪水的等级 部门编号，部门平均薪水，等级

select * 
from salgrade s,(select deptno,avg(sal) as 平均薪水 from emp group by deptno) t 
where t.平均薪水>=s.losal and t.平均薪水<=s.hisal

---

********************** Oracle在线练习平台****************

牛客网数据库sql练习
leetcode

---