Oracle查询相关

古城微笑少年丶 2022-04-25 04:18 341阅读 0赞

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

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

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

![asd][]

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

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

![asd][asd 1]

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

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

![asd][asd 2]

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

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

![asd][asd 3]

###### 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');

![sa][]

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

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

![as][]

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

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

![sa][sa 1]

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

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

![asd][asd 4]

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

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

![asd][asd 5]

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

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

![asd][asd 6]

###### 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;

![asd][asd 7]

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

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

![asd][asd 8]

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

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

![asd][asd 9]

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

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

![asd][asd 10]

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

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

![asd][asd 11]

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

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

![asd][asd 12]

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

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

![asd][asd 13]

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

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

![asd][asd 14]

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

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

![asd][asd 15]

###### 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);

![asd][asd 16]

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

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

![asd][asd 17]

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

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

![asd][asd 18]

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

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

![asd][asd 19]

###### 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;

![asd][asd 20]

[asd]: /images/20220210/b569dbd5071d48f284151835ade24097.png
[asd 1]: /images/20220210/e1ecbcb5df874b15bee81d77fe661e24.png
[asd 2]: /images/20220210/b7886f862fd1441e82cd3b65a9116a5e.png
[asd 3]: /images/20220210/2f371dbda7074b31b1200e90b3fe46b8.png
[sa]: /images/20220210/48fc6a23560c4f9b97b8f546341ca64c.png
[as]: /images/20220210/e1b024c06e83407ba1313813719d211d.png
[sa 1]: /images/20220210/62f1958ee3e64a3e9b72976400e1cdba.png
[asd 4]: /images/20220210/f323f54f98a34ec9bfcb8a7d04f2b884.png
[asd 5]: /images/20220210/27796daa1a894ffcb0da707a5cf2c324.png
[asd 6]: /images/20220210/8d32b8dbc53b451bad3823f7d754d04e.png
[asd 7]: /images/20220210/4d2c8bbc5d8140daab06925c37353419.png
[asd 8]: /images/20220210/10d10aa7c10b4055b7522b35bae1576a.png
[asd 9]: /images/20220210/2bef843070f542fd8991aaf4136012b6.png
[asd 10]: /images/20220210/be121596ec244abab583406c6eb4ac18.png
[asd 11]: /images/20220210/540f488b3600422d8c6f9d0977e2f963.png
[asd 12]: /images/20220210/90faae0d7e53424d9220d4701066901b.png
[asd 13]: /images/20220210/4b8916542d9c4d608222ba344ca7cd8c.png
[asd 14]: /images/20220210/b0ee088d95ca48e7b620a1cedb27398a.png
[asd 15]: /images/20220210/ce196af8389b4fd385fe724fcb23e9c8.png
[asd 16]: /images/20220210/70cb692518f84cd5a297b2cf695db9bb.png
[asd 17]: /images/20220210/a6f8be50bfb54065b4cadc36f8d1961c.png
[asd 18]: /images/20220210/c82dbce28de247eaac66e1ef6cbd248b.png
[asd 19]: /images/20220210/d2e5fae3e01d4a368e338ebd44459957.png
[asd 20]: /images/20220210/b4f9697ec8fa4cfbbf4d6e76167cabc3.png