Oracle —— 查询函数练习

创建表和插入数据：

create table dept(
  deptno int,
  dname varchar(20),
  loc varchar(20)
);
insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');

create table emp(
  empno int,
  ename varchar(20),
  job varchar(20),
  mgr int,
  hiredate date,
  sal int,
  comm int,
  deptno int
);
insert into emp values ( 7369,'SMITH','CLERK',7902,'17-12月-1980',800,null,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,'20-2月-1981',1600,300,30 );
insert into emp values ( 7521,'WARD','SALESMAN',7698,'22-2月-1981',1250,500,30);
insert into emp values ( 7566,'JONES','MANAGER',7839,'2-4月-1981',2975,null,20);
insert into emp values ( 7654,'MARTIN','SALESMAN',7698,'28-9月-1981',1250,1400,30);
insert into emp values ( 7698,'BLAKE','MANAGER',7839,'1-5月-1981',2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,'9-6月-1981',2450,null,10 );
insert into emp values ( 7788,'SCOTT','ANALYST',7566,'19-4月-1987',3000,null,20);
insert into emp values ( 7839,'KING','PRESIDENT',null,'17-11月-1981',5000,null,10);
insert into emp values ( 7844,'TURNER','SALESMAN',7698,'8-9月-1981',1500,0,30);
insert into emp values ( 7876,'ADAMS','CLERK',7788,'23-5月-1987',1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,'3-12月-1981',950,null,30 );
insert into emp values ( 7902,'FORD','ANALYST',7566,'2-12月-1981',3000,null,20);
insert into emp values ( 7934,'MILLER','CLERK',7782,'23-1月-1982',1300,null,10);

查询语句

• 查询各公司员工工资的最大值，最小值，平均值，总和

select max(sal),min(sal),avg(sal),sum(sal) from emp;

• 查询各职位的员工工资的最大值，最小值，平均值，总和

select  job,min(sal) min, max(sal) max, avg(sal) avg, sum(sal) sum 
from  emp 
group by  job ;

• 选择具有各个 job 的员工人数(提示:对job 进行分组)

select  job,count(distinct empno) count 
from  emp 
group by  job ;

• 查询员工最高工资和最低工资的差距,列名为 DIFFERENCE ；

select max(sal)-min(sal) as  DIFFERENCE 
from emp ;

• 查询各个管理者属下员工的最低工资，其中最低工资不能低于 800 ，没有管理者的员工不计算在内

select  a.empno, a.ename, b.min 
from 
emp a 
inner join  (
      select   mgr, min(sal) min 
      from emp 
      where mgr is not null 
      group by mgr 
      having min(sal) >= 800 
) b 
on a.empno = b.mgr ;

• 查询所有部门的部门名字 dname ，所在位置 loc ，员工数量和工资平均值；

select min(a.dname) dname, min(a.loc) loc, count(b.empno) count, avg(b.sal) avg 
from 
dept a left join emp b
on a.deptno = b.deptno 
group by a.deptno  ;

• 查询公司的人数，以及在1980、1981、1982、1987年，每年雇用的人数，结果类似下面的格式

select count(*) total,
sum(decode(to_char(hiredate,'yyyy'),1980,1,0))"1980",
sum(decode(to_char(hiredate,'yyyy'),1981,1,0))"1981",
sum(decode(to_char(hiredate,'yyyy'),1982,1,0))"1982",
sum(decode(to_char(hiredate,'yyyy'),1987,1,0))"1987"
from emp;

• 查询和 SCOTT 相同部门的员工姓名 ename 和雇用日期 hiredate

select  a.ename,a.hiredate from emp 
a  join (
      select   deptno 
      from  emp  
      where emp.ename = 'SCOTT'  
) 
b on a.deptno = b.deptno and a.ename != 'SCOTT';  

• 查询工资比公司平均工资高的所有员工的员工号 empno，姓名ename 和工资sal

select a.empno,a.ename,a.sal,b.sal from emp 
a join ( select  avg(sal) sal from emp )  
b on  a.sal > b.sal ;

• 查询和姓名中包含字母 U 的员工在相同部门的员工的员工号 empno 和姓名ename

select a.empno,a.ename,a.deptno from emp a 
inner join (
      select  ename,deptno
      from emp 
      where ename like '%U%' 
) b on a.deptno = b.deptno and a.ename != b.ename ;

• 查询在部门的loc 为 NEW YORK 的部门工作的员工的员工姓名ename，部门名称 dname 和岗位名称job

select a.ename, b.dname, a.job from emp a join (
      select deptno, dname
      from dept 
      where loc = 'NEW YORK' 
) b on a.deptno = b.deptno ;

• 查询管理者是 KING 的员工姓名 ename 和工资sal

select a.ename, a.sal from emp a join (
      select empno from emp where ename = 'KING' 
) b on a.mgr = b.empno;