Oracle查询相关

所有查询基于SCOTT用户下的EMP和DEPT两张表。

1.查询部门编号是20的员工信息。

select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
from EMP 
where  DEPTNO = 20;

2.查询工作为CLERK的员工的员工号，员工名和部门号

select EMPNO, ENAME, DEPTNO
from EMP
where JOB = 'CLERK';

3.查询奖金COMM高于工资SAL的员工信息。

select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
from emp
where COMM > SAL;

4.查询奖金COMM高于工资SAL20%的员工信息

select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
from EMP
where COMM > SAL * 0.2;

5.查询部门编号是10并且工作为MANAGER的员工和部门编号是20并且工作为CLERK的员工信息

select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
from EMP
where (DEPTNO = 10 AND JOB = 'MANAGER') 
OR (DEPTNO = 20 AND JOB = 'CLERK');

6.查询工作不是MANAGER和CLERK，并且工资大于或等于2000的员工信息

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE JOB NOT IN ('MANAGER', 'CLERK')
AND SAL >= 2000;

7. 查询有奖金的员工信息

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE COMM IS NOT NULL AND COMM != 0;

8.查询所有员工的人数和他们的平均工资

SELECT COUNT(*), AVG(SAL)
FROM EMP;

9.查询没有奖金或奖金低于100的员工信息

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE COMM IS NULL OR COMM < 100;

10.查询最近两年入职的员工信息

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM emp
WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE)/12 <= 2;

11.查询工龄大于或等于10年的员工信息

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE)/12 >= 10;
-- select MONTHS_BETWEEN(SYSDATE, HIREDATE)/12 FROM EMP;

12.查询员工信息，要求以首字母大写的方式显示所有员工的姓名

SELECT EMPNO,INITCAP(ENAME), JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP;

13.查询员工名正好为6个字母的员工信息

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE ENAME LIKE '______';

14.查询员工名字中不包含字母S的员工

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE ENAME NOT LIKE '%S%';

15.查询员工姓名的第二个字母为M的员工信息。

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE ENAME LIKE '_M%';

16.查询所有员工姓名的前三个字符

SELECT SUBSTR(ENAME, 1, 3)
FROM EMP;

17.查询所有员工的姓名，如果包含字母s，则用S替换

SELECT REPLACE(ENAME, '%s%', '%S%') AS 大写替换小写
FROM EMP;

18.查询所有员工的姓名和入职日期，并按入职日期从先到后进行排序

SELECT ENAME AS 姓名,HIREDATE AS 入职日期
FROM EMP
ORDER BY HIREDATE ASC;

19.查询所有员工的姓名，工作，工资，按工作降序排序，若工作相同，则按工资升序排序

SELECT ENAME, JOB, SAL
FROM EMP
ORDER BY JOB DESC, SAL;

20.显示所有员工的姓名，入职的年份和月份按入职日期所在的月份排序。若月份相同，则按入职的年份排序

SELECT ENAME,EXTRACT(year FROM HIREDATE) AS hireyear,EXTRACT(month FROM HIREDATE) as hiremonth
FROM EMP
ORDER BY EXTRACT(month FROM HIREDATE), EXTRACT(year FROM HIREDATE);

21.查询每个部门中的员工数量，平均工资和平均工作年限

SELECT DEPTNO, COUNT(DEPTNO), AVG(SAL), AVG(MONTHS_BETWEEN(SYSDATE, HIREDATE))
FROM EMP
GROUP BY DEPTNO;

22.查询各个部门的人数及平均工资

SELECT DEPTNO, COUNT(DEPTNO), AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

23.查询各种工作的最低工资，并输出最低工资低于3000的工作名称

SELECT MIN(SAL) AS 最低工资, JOB
FROM EMP
GROUP BY JOB
HAVING MIN(SAL) < 3000;

24.查询各个部门中不同工种的最高工资

select d.dname 部门, e.job 工作,max(e.sal) 
from DEPT d join emp e on (d.DEPTNO = e.DEPTNO)
group by d.dname, e.job;