基于scott.emp表进行以下查询:
1.查询公司员工工资的最大值,最小值,平均值,总和
select max(SAL),min(SAL),avg(SAL),sum(SAL)
from scott.emp;
2.查询各种job的员工工资的最大值,最小值,平均值,总和
select JOB,max(SAL),min(SAL),avg(SAL),sum(SAL)
from scott.emp
group by JOB;
3.查询各种job的员工人数(提示:对job进行分组)
select JOB,count(EMPNO)
from scott.emp
group by JOB;
4.查询员工最高工资和最低工资的差距(DIFFERENCE)
select max(SAL)-min(SAL) DIFFERENCE
from scott.emp;
5.查询各个管理者手下员工的最低工资,其中最低工资不能低于800,没有管理者的员工不计算在内
select MGR,min(SAL)
from scott.emp
where MGR is not null
group by MGR having min(SAL) >= 800;
6.查询所有部门的名字dname,所在位置loc,员工数量和工资平均值
select DNAME,LOC,count(EMPNO),avg(SAL)
from scott.dept d,scott.emp e
where d.DEPTNO = e.DEPTNO;
7.查询公司的人数,以及在1980-1987年之间,每年雇用的人数,结果类似下面的格式
total | 1980 | 1981 | 1982 | 1987 |
30 | 3 | 4 | 6 | 7 |
select distinct
(select count(ename) from emp) "total",
(select count(ename) from emp where hiredate>=to_date('19800101','yyyymmdd') and hiredate<to_date('19810101','yyyymmdd')) "1980",<="" p="">
(select count(ename) from emp where hiredate>=to_date('19810101','yyyymmdd') and hiredate<to_date('19820101','yyyymmdd')) "1981",<="" p="">
(select count(ename) from emp where hiredate>=to_date('19820101','yyyymmdd') and hiredate<to_date('19830101','yyyymmdd')) "1982",<="" p="">
(select count(ename) from emp where hiredate>=to_date('19870101','yyyymmdd') and hiredate<to_date('19880101','yyyymmdd')) "1987"<="" p="">
from emp;
8.查询和scott相同部门的员工姓名ename和雇用日期hiredate
select HIREDATE,ENAME
from scott.emp
where deptno = (
select deptno
from scott.emp
where ENAME = 'SCOTT');
9.查询工资比公司平均工资高的所有员工的员工号empno,姓名ename和工资sal。
select ENAME,EMPNO,SAL
from scott.emp
where SAL > (
select avg(SAL)
from scott.emp);
10.查询姓名中包含字母u的员工在相同部门的员工的员工号empno和姓名ename
select empno,ename
from scott.emp
where empno in (
select empno
from scott.emp
where ename like '%U%');
11.查询在部门的loc为newYork的部门工作的员工的员工姓名ename,部门名称dname和岗位名称job
select ename,dname,job
from scott.dept d,scott.emp e
where d.DEPTNO = e.DEPTNO and e.DEPTNO = (
select deptno
From scott.dept
Where loc = 'NEW YORK');
12. 查询管理者是king的员工姓名ename和工资sal
select ENAME,SAL
from scott.emp
where MGR = (
select EMPNO
from scott.emp
where ENAME = 'KING');