【Oracle数据库】查询练习

基本查询

• 查询所有的部门编号：

Select deptno from emp;

• 查询所有有人的部门编号：

select ename,deptno from emp;

• 查询所有岗位名称：

Select job from emp;

• 列出部门表中的部门名称和所在城市

select dname,loc from dept;

• 连接员工表中的员工姓名、职位、薪水，列之间用逗号连接，列头显示成ename_job_sal

select ename||','||job||','||sal as ename_job_sal from emp 

• 查询员工表中员工号，姓名，工资，以及工资提高百分之20之后的结果

select empno,ename,sal,sal*1.2 from emp

条件查询

• 查询所有薪水超过两千的员工信息

Select * from where sal>2000;

• 查询所有20部门的员工姓名，编号及薪水

Select ename,empno,sal from where deptno=20;

• 查询所有没有奖金的员工信息

Select * from emp where sal is  null;

• 查询所有有奖金的员工信息

Select * from emp where sal is not null;

• 查询没有领导的员工信息

Select * from emp where mgr is null;

• 查询所有81年之后入职的员工信息

Select * from emp 
where hiredate>to_date('1981/01/01','yyyy/mm/dd')

• 查询所有薪水在2000-4000范围内的员工信息

Select * from emp where sal between 2000 and 4000;

• 查询所有部门编号是10或30的员工信息

Select * from emp where deptno = 20 or deptno =30;

• 查询所有20部门并且薪水超过2000的员工信息：

Select * from emp where deptno=20 and sal>2000;

• 查询所有薪水不在2000-4000范围内的员工信息

select * from emp where sal not between 2000 and 4000;

• 查询所有部门编号不是10，30的员工信息

select * from emp where deptno!=10 and deptno!=30;

• 查询用户名为scott的员工信息：注意区分大小写

select * from emp where ename='SCOTT'

• 查询员工表中工资大于1600的员工的姓名和工资

select ename,sal from emp;

• 查询员工表中员工号是17的员工的姓名和部门编号

select ename,deptno from emp where empno=17;

• 选择员工表中工资不在4000到5000内的员工的姓名和工资

select ename,sal from emp where sal not between 4000 and 5000

• 选择员工表中在20和30部门工作的员工的姓名和部门号

select ename,deptno from emp where deptno=20 or deptno=30

• 列出除了ACCOUNTING部门之外还有什么部门

select dname from dept where dname!='ACCOUNTING'

模糊查询

• 查询姓名里面包含ALL的员工姓名

select * from emp where ename like '%ALL%'

• 查询所有以”S”开头的同学

select * from emp where ename like 'S%'

• 查询第二个字母为A的员工姓名

select * from emp where ename like '_A%'

• 查询所有员工姓名中包含‘A’的最高薪水

select MAX(sal) from emp where ename like '%A%'

• 选择员工表中员工姓名的第三个字母是A的员工姓名

select * from emp where ename like '__A%'

查询排序

• 查询所有员工的编号、姓名、部门编号、职位、薪水，按照薪水降序排列

select empno,ename,deptno,job,sal from emp order by sal desc

• 查询所有员工信息，按照部门降序排列，部门内按照薪水升序排列

select * from emp order by deptno desc ,sal asc

• 查询姓名中包含‘A’员工的姓名，编号，薪水，按照薪水降序排列

select empno,sal from emp order by sal desc

• 查询年收入超过10000的员工的姓名，编号，薪水，年收入，按照年收入降序排列

select ename,empno,sal,(sal+"NVL"(comm,0))*12 as yearsal
from emp where (sal+"NVL"(comm,0))*12>10000 order by yearsal desc;

• 查询年薪超过10000的员工的姓名，编号，薪水，年收入，按照年薪降序排列

select ename,empno,sal,(sal+"NVL"(comm,0))*12 as yearsal
from emp where sal*12>10000 order by yearsal desc;

• 选择员工表中没有管理者的员工姓名及职位，按职位排序

select ename,job from emp where mgr is null order by job asc;

• 选择员工表中有奖金的员工姓名，工资和奖金，按工资倒序排列

select ename,sal,comm from emp where comm is not null order by sal desc

• 查询员工的姓名和工资数，条件限定为工资数必须大于1200，并且查询结果按入职时间进行排序。早入职的员工排在前面

select ename,sal from emp where sal>1200 order by hiredate;

聚合函数

• 查询雇员表中，姓名为SMITH的雇员，截止到今天共工作了多少周，则可以使用如下的SQL语句

select round((sysdate-hiredate)/7) as "Weeks" from emp 
where ename = 'SMITH';

• 查询各部门的最高薪水、最低薪水、平均薪水….

select MAX(sal),MIN(sal),AVG(sal) from emp 
group by deptno;

• 查询公司所有员工的个数

select COUNT(ename) from emp;

• 查询公司中最高薪水是多少

select MAX(sal) from emp;

• 查询公司中最晚入职的时间

select MAX(hiredate) from emp;

• 查询公司中有奖金的人数

select COUNT(ename) from emp where comm is not null;

• 查询20部门的最高薪水是多少

select MAX(sal) from emp where deptno=20;

子查询

• 查询‘SMITH’的领导姓名

select ename from emp 
where empno=(select mgr from emp where ename='SMITH');

• 查询部门名称是‘SALES’的员工信息

select * from emp where deptno =(
select deptno from dept where dname='SALES');

• 查询公司中薪水最高的员工信息

select * from emp where sal=(select MAX(SAL) from emp);

• 查询薪水等级为4的员工信息

select * from emp where sal between
(select losal from salgrade where grade=4)and
(select hisal from salgrade where grade=4)

• 查询领导者是‘BLAKE’的员工信息

select * from emp where mgr=(select empno from emp 
where ename='BLAKE')

• 查询最高领导者的薪水等级

select grade from salgrade where(select sal from emp 
where mgr is null)between losal and hisal;

• 查询薪水最低的员工信息

select * from emp where sal=(select MIN(sal) from emp);

• 查询和SMITH工作相同的员工信息

select * from emp 
where job=(select job from emp where ename='SMITH')

• 查询不是领导的员工信息

select * from emp 
where empno not in(select NVL(mgr,0)from emp);
select * from emp e1 
where not exists(select * from emp e2 where e2.mgr=e1.empno)

• 查询平均工资比10部门低的部门编号

select deptno from emp group by deptno having avg(sal)<
(select avg(sal)from emp where deptno=10);
select count(deptno) from emp group by deptno

• 查询在纽约工作的所有员工

select * from emp 
where deptno=(select deptno from dept where loc='NEW YORK');

• 查询‘SALES’部门平均薪水的等级

select grade from salgrade where
(select avg(sal) from emp where deptno=
(select deptno from dept where dname='sales'))between losal and hisal;

• 查询10号部门的员工在整个公司中所占的比例：

select(select COUNT(deptno)from emp 
where deptno=10)/(select COUNT(*)from emp) from dual;

• 查询各部门工资大于该部门平均工资的员工信息：

select * from emp e1 where sal>(select avg(sal) from emp e2 
where e1.deptno=deptno);

• 查询各岗位工资小于该岗位平均工资的员工信息；

select * from emp e1 where sal<(select avg(sal) from emp e2 
where e1.deptno=deptno);

• 求薪水最高的员工姓名

select ename from emp where sal=(select max(sal) from emp);

分组查询

• 查询各部门的平均薪水及部门编号，部门名称。

select AVG(sal),dname,emp.deptno 
from emp,dept 
where emp.deptno=dept.deptno 
group by dname,emp.deptno;

• 查询各部门中最高薪水的员工编号，姓名…

select empno,ename from emp 
where sal in(select MAX(sal) from emp group by deptno);

或者

select emp.deptno as 部门,'名字:'||emp.ename||'编号：'||emp.empno||'薪水：'||emp.sal as 最高薪员工
from emp,dept
where emp.sal in(select max(nvl(sal,0)) from emp 
group by emp.deptno)
group by emp.deptno,emp.ename,emp.empno,emp.sal
order by emp.empno asc;

• 查询各岗位的最高薪水，最低薪水。要求只统计薪水>1000的

select MAX(sal),MIN(sal) from emp 
where sal>1000 group by job;

• 查询各部门的平均薪水及部门编号，要求只列出平均薪水>2000

select "AVG"(sal),deptno from emp 
group by deptno having AVG(sal)>2000;

• 查询各部门的平均薪水及部门编号，要求只有员工姓名中包含
  ‘A’才参与统计，只列出平均薪水>1500的，按照平均薪水降序排列

select "AVG"(sal),deptno from emp 
where ename like '%A%' group by deptno having AVG(sal)>1500;

• 把雇员按部门分组，求最高薪水，部门号 要求过滤掉名字中第二个字母是’A’的员工， 并且部门的平均薪水 > 3000,按照部门编号倒序排列

select max(sal),deptno from emp 
where ename not like '_A%' 
group by deptno having avg(sal)>3000

• 按照部门分组统计，求最高薪水，平均薪水，最低薪水，只有薪水是1200以上的员工才参与统计，并且分组结果中只包含平均薪水在1500以上的部门，并且按照平均薪水倒序排列

select max(sal),min(sal),avg(sal) from emp where sal>1200
group by deptno having avg(sal)>100 order by avg(sal) desc;

分析查询

• 查询所有领导的信息：要求使用exists关键字

• 显示员工表中的不重复的岗位job

select distinct job from emp;

分页查询

• 显示第一页内容：

select rownum rn,emp.*from emp where rownum<=5;

• 显示第二页的内容：

select * from(select rownum rn,emp.*from emp 
where rownum <=10) where rn>5;

• 按照薪水降序排列，每页显示5条,显示第二页的内容:

关联

内连接

• 查询所有员工的姓名，薪水，部门名称

select ename,dname from emp,dept where emp.deptno=dept.deptno;

• 查询所有员工的姓名，薪水，部门名称,薪水等级

select ename,sal,dname,grade from emp,dept,SALGRADE 
where emp.deptno=dept.deptno and sal between losal and hisal;

• 查询所有员工的姓名，部门名称

select ename,dname from emp,dept where emp.deptno = dept.deptno;

左连接

• 查询员工姓名及领导者姓名

select a.ename as 员工姓名,b.ename as 领导姓名 from emp a 
LEFT JOIN emp b on a.mgr=b.empno

自然连接

• 求工作职位是’manager’的员工姓名，部门名称和薪水等级

select ename,dname,grade from emp 
natural join dept left join salgrade s1
on sal between s1.losal and s1.hisal where job = 'MANAGER'