1.查询员工编号、姓名、薪资,按薪资升序排列
SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL;
2.查询员工编号、姓名、薪资、部门编号,按部门升序及薪资降序排列
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM EMP
ORDER BY DEPTNO,SAL DESC;
3.查询除20号部门外,员工编号、姓名、薪资、部门编号,按部门升序、薪资升序、工号降序排列
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM EMP
WHERE DEPTNO <> 20
ORDER BY DEPTNO,SAL,EMPNO DESC;
4.查询员工姓名、薪资、佣金、薪资佣金合计,按薪资佣金合计值升序排列,
薪资佣金以“总计” 二字显示
SELECT ENAME,SAL,COMM,NVL(COMM,0)+SAL 总计
FROM EMP
ORDER BY 总计 ;
5.查询员工姓名、岗位、薪资,按岗位升序排列,其中岗位PRESIDENT排在最前
SELECT ENAME,JOB,SAL,CASE WHEN JOB = 'PRESIDENT' THEN 1 ELSE 2 END
FROM EMP
ORDER BY CASE WHEN JOB = 'PRESIDENT' THEN 1 ELSE 2 END ,JOB;
6.查询员工姓名、薪资、12个月工资(以“年薪”二字展示),按年薪降序排列
SELECT ENAME,SAL,SAL*12 年薪
FROM EMP
ORDER BY 年薪 DESC;
7.统计员工姓名及员工资历,并按资历排序,将老员工排在前,新员工排在后
(老员工:80年及以前
新员工:81年及以后)
SELECT ENAME,
CASE WHEN TO_CHAR(HIREDATE,'YYYY') >= '1981' THEN '新员工'
WHEN TO_CHAR(HIREDATE,'YYYY') <= '1980' THEN '老员工'
END 员工资历
FROM EMP
ORDER BY HIREDATE;
8.查询员工姓名、岗位、薪资及岗位类型,并对岗位类型按(管-行-技-销)顺序排序,
岗位类型相同的按薪资降序排列
(行政岗:办事员
管理岗:经理/总经理
技术岗:分析师
销售岗:销售)
SELECT ENAME,JOB,SAL,
CASE WHEN JOB = 'MANAGER' OR JOB = 'PRESIDENT' THEN '管理岗'
WHEN JOB = 'CLERK' THEN '行政岗'
WHEN JOB = 'ANALYST' THEN '技术岗'
WHEN JOB = 'SALESMAN' THEN '销售岗'
END 岗位类型
FROM EMP
ORDER BY CASE WHEN JOB = 'MANAGER' OR JOB = 'PRESIDENT' THEN 1
WHEN JOB = 'CLERK' THEN 2
WHEN JOB = 'ANALYST' THEN 3
WHEN JOB = 'SALESMAN' THEN 4
END ,SAL DESC;
9.查询20号部门员工的姓名、岗位、薪资、奖金(奖金为空的处理成0),
并以薪资奖金的合计值进行降序排列,合计值相同的按员工编号升序排列
数据源: EMP
筛选条件: DEPTNO = 20
查询的列: ENAME,JOB,SAL,NVL(COMM,0)
排序规则: NVL(COMM,0)+SAL DESC, EMPNO ASC
SELECT ENAME,JOB,SAL,NVL(COMM,0)
FROM EMP
WHERE DEPTNO = 20
ORDER BY NVL(COMM,0)+SAL DESC, EMPNO ASC ;
10.查询每个员工的姓名、岗位,以及以下列
CLERK_SAL,MANAGER_SAL,PRESIDENT_SAL,ANALYST_SAL,SALESMAN_SAL
每个人在自己所属岗位下有对应的薪资信息,其他字段显示为0
EG:SCOTT ANALYST 0 0 0 3000 0
SELECT ENAME,
JOB,
CASE WHEN JOB = 'CLERK' THEN SAL ELSE 0 END CLERK_SAL ,
CASE WHEN JOB = 'MANAGER' THEN SAL ELSE 0 END MANAGER_SAL ,
CASE WHEN JOB = 'PRESIDENT' THEN SAL ELSE 0 END PRESIDENT_SAL,
CASE WHEN JOB = 'ANALYST' THEN SAL ELSE 0 END ANALYST_SAL ,
CASE WHEN JOB = 'SALESMAN' THEN SAL ELSE 0 END SALESMAN_SAL
FROM EMP;