当前位置：
首页 >
Oracle作业2
发布时间：2024/9/30
28
豆豆
1.求 emp表中工资总和、工资平均值、最小工资、最大工资
SELECT sum(sal) FROM emp;
SELECT avg(sal) FROM emp;
SELECT min(sal) FROM emp;
SELECT max(sal) FROM emp;
2.求 emp表的总行数,再求出部门编号去重后的总行数
SELECT count(*) FROM emp;
SELECT sum(DISTINCT deptno) FROM emp;
3.求有奖金的员工的行数
SELECT count(comm) FROM emp;
4.求所有员工的平均奖金
SELECT avg(sal) FROM emp;
5.求每个部门的工资总和、平均工资、最小工资、最大工资
SELECT deptno,sum(sal) FROM emp GROUP BY deptno;
SELECT deptno,avg(sal) FROM emp GROUP BY deptno;
SELECT deptno,min(sal) FROM emp GROUP BY deptno;
SELECT deptno,max(sal) FROM emp GROUP BY deptno;
6.求每个部门中从事不同工作的员工的工资总和、平均工资、最小工资、最大工资
SELECT deptno,job,sum(sal) FROM emp GROUP BY deptno,job;
SELECT deptno,job,avg(sal) FROM emp GROUP BY deptno,job;
SELECT deptno,job,min(sal) FROM emp GROUP BY deptno,job;
SELECT deptno,job,max(sal) FROM emp GROUP BY deptno,job;
7.查出平均工资大于2000的部门
SELECT deptno FROM emp GROUP BY deptno HAVING avg(sal)> 2000;
8.查询每个部门的最高工资
SELECT deptno,max(sal) FROM emp GROUP BY deptno;
9.查询每个部门,每种职位的最高工资
SELECT deptno,job,max(sal) FROM emp GROUP BY deptno,job;
10.查询各部门工资最高的员工信息
SELECT*FROM emp e WHERE e.sal=(
SELECT max(sal) FROM emp WHERE deptno=e.deptno);
11、按工作种类进行分组，显示各个组内雇员工资的最高、最低、合计和平均值，并对各值去掉小数四舍五入
SELECT round(max(sal),0),round(min(sal),0),round(sum(sal),0),round(avg(sal),0) FROM emp GROUP BY job;
12、求 EMP表中 1980、1981、1982年加入公司的员工数量
SELECT count(*),to_char(hiredate,'yyyy') FROM emp GROUP BY to_char(hiredate,'yyyy') HAVING to_char(hiredate,'yyyy') IN ('1980','1981','1982');
总结
- 上一篇： Oracle作业1
- 下一篇： Mac 的World空格显示为.