ORACLE关于scott用户下相关表的sql练习

最新推荐文章于 2025-04-26 08:57:04 发布

sunlibincn

最新推荐文章于 2025-04-26 08:57:04 发布

阅读量371

点赞数

文章标签： oracle

本文链接：https://blog.youkuaiyun.com/EJEEMT/article/details/118230419

版权

本文提供Oracle SQL练习案例，包括创建EMP、DEPT等表并填充数据，通过多种查询展示如何运用SQL函数处理日期、字符串等，并进行多表联查。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

EMP员工表
DEPT 部门表
BONUS 奖金表
SALGRADE 薪资等级表
下面是一些sql练习

oracle 默认有一个可以用来练习的用户：scott ，在这个用户下有4张表：EMP、DEPT、BONUS、SALGRADE，这4张表中 SALGRADE、EMP和DEPT会有一些数据可以拿来练习基础sql和函数，网上一搜也有好多，但是相关联的就有解锁scott用户、切换各种麻烦事情，下文将给出建表和数据，是不是在scott下无所谓了，不BB，看代码吧

EMP员工表

EMPNO(员工号) ENAME(员工姓名) JOB(工作) MGR(上级编号) HIREDATE(受雇日期) SAL(薪金) COMM(佣金) DEPTNO(部门编号)

-- auto-generated definition
create table EMP
(
    EMPNO    NUMBER(4) not null
        constraint PK_EMP
            primary key,
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(4),
    HIREDATE DATE,
    SAL      NUMBER(7, 2),
    COMM     NUMBER(7, 2),
    DEPTNO   NUMBER(2)
        constraint FK_DEPTNO
            references DEPT
);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('1980-12-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 800.00, null, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1600.00, 300.00, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('1981-02-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1250.00, 500.00, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1981-04-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2975.00, null, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('1981-09-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1250.00, 1400.00, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1981-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2850.00, null, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1981-06-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2450.00, null, 10);

DEPT 部门表

DEPTNO(部门号) ENAME(部门名称) LOC(地方)

-- auto-generated definition
create table DEPT
(
    DEPTNO NUMBER(2) not null
        constraint PK_DEPT
            primary key,
    DNAME  VARCHAR2(14),
    LOC    VARCHAR2(13)
);

INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIONS', 'BOSTON');

BONUS 奖金表

ENAME(员工姓名) JOB(工作名称) SAL(薪金) COMM(佣金)

create table BONUS
(
    ENAME VARCHAR2(10),
    JOB   VARCHAR2(9),
    SAL   NUMBER,
    COMM  NUMBER
);

SALGRADE 薪资等级表

GRADE(等级) LOSAL(最低工资) HISAL(最低高)

-- auto-generated definition
create table SALGRADE
(
    GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER
);

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (1, 700, 1200);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (2, 1201, 1400);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (3, 1401, 2000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (4, 2001, 3000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (5, 3001, 9999);

下面是一些sql练习

基础sql练习


--25.对于每个员工,显示其加入公司多少年了？？多少个月了？？？
SELECT ENAME, HIREDATE,
    '多个月:' || TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS EMONTH,
    '多少年:' || TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE) / 12) AS EYEAR FROM EMP;
--24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%';
--23.对于每个员工,显示其加入公司的天数(trunc取整,不进行四舍五入,全舍).
SELECT ENAME, 'DAYS:' || TRUNC(TO_NUMBER(SYSDATE - HIREDATE)) AS DAYS FROM EMP;
SELECT ENAME, TRUNC(TO_NUMBER(SYSDATE - HIREDATE)) AS DAYS FROM EMP;
--22.找出在(任何年份的)2月受聘的所有员工。
SELECT ENAME, HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE, 'MM') = 02;
--21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.
SELECT ENAME, SAL, COMM, TRUNC((NVL(SAL, 0) + NVL(COMM, 0)) / 30) AS SUM FROM EMP;
--20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
SELECT ENAME, TO_CHAR(HIREDATE, 'yyyy') AS EYEAR, TO_CHAR(HIREDATE, 'MM') AS EMONTH FROM EMP ORDER BY EMONTH, EYEAR;
--19.显示所有员工的姓名、工资和佣金,按工作的降序排序,若工资相同则按佣金排序.
SELECT ENAME, SAL, COMM FROM EMP ORDER BY SAL DESC, COMM DESC;
--18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
SELECT ENAME, HIREDATE FROM EMP ORDER BY HIREDATE;
--17.显示员工的详细资料,按姓名排序【ASC默认,升序|DESC降序】
SELECT * FROM EMP ORDER BY ENAME;
--16.显示满10年服务年限的员工的姓名和受雇日期(ADD_MONTHS系统函数).
SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE < ADD_MONTHS(SYSDATE, -12 * 10);
--15.显示所有员工的姓名,用A替换所有"a"
SELECT REPLACE(ENAME, 'a', 'A') FROM EMP;
--14.显示所有员工姓名的前三个字符.
SELECT SUBSTR(ENAME, 1, 3) FROM EMP;
--13.显示不带有"R"的员工的姓名.
SELECT ENAME FROM EMP WHERE ENAME NOT IN (SELECT ENAME FROM EMP WHERE ENAME LIKE '%R%');
SELECT ENAME FROM EMP WHERE ENAME NOT LIKE '%R%';
--12.显示正好为5个字符的员工的姓名.
SELECT ENAME FROM EMP WHERE LENGTH(ENAME) = 5;
--11.以首字母大写的方式显示所有员工的姓名.
SELECT INITCAP(ENAME) FROM EMP;
--10.找出早于12年前受雇的员工.
SELECT * FROM EMP WHERE HIREDATE < ADD_MONTHS(SYSDATE, -12 * 39);
--9.找出各月倒数第3天受雇的所有员工.
SELECT * FROM EMP WHERE HIREDATE = LAST_DAY(HIREDATE) - 2;
--8.找出不收取佣金或收取的佣金低于100的员工.
SELECT * FROM EMP WHERE COMM IS NULL OR COMM < 100;
--7.找出收取佣金的员工的不同工作.
SELECT * FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE COMM IS NOT NULL GROUP BY DEPTNO);
--6.找出既不是经理(MANAGER)又不是办事员(CLERK)但其薪金大于或等于2000的所有员工的详细资料.
SELECT * FROM EMP WHERE SAL >= 2000 AND JOB NOT IN ('MANAGER', 'CLERK');
--5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
SELECT * FROM EMP WHERE (DEPTNO = '10' AND JOB = 'MANAGER') OR (DEPTNO = '20' AND JOB = 'CLERK');
--4.找出佣金高于薪金的60%的员工.
SELECT * FROM EMP WHERE COMM > (SAL * 0.6);
--3.找出佣金高于薪金的员工.
SELECT * FROM EMP WHERE COMM > SAL;
--2.列出所有办事员(CLERK)的姓名,编号和部门编号.
SELECT ENAME, EMPNO, DEPTNO FROM EMP WHERE JOB = 'CLERK';
--1.选择部门30中的所有员工.
SELECT * FROM EMP WHERE DEPTNO = '30';

以上函数练习

--17统计comm字段不为空的记录数
SELECT COUNT(COMM) FROM EMP;
--16 decode()函数:显示员工编号,姓名,月薪及月薪等级:
SELECT T1.EMPNO, T1.ENAME, T1.SAL, T2.GRADE FROM EMP T1 LEFT JOIN SALGRADE T2 ON T2.LOSAL < T1.SAL AND T1.SAL < T2.HISAL;
SELECT T1.EMPNO, T1.ENAME, T1.SAL, T2.GRADE FROM EMP T1 LEFT JOIN SALGRADE T2 ON T1.SAL BETWEEN T2.LOSAL AND T2.HISAL;
SELECT T1.EMPNO, T1.ENAME, T1.SAL, T2.GRADE,
       decode(T1.SAL, 800,'初级',1600,'中级',3000,'高级','其它')
    FROM EMP T1 LEFT JOIN SALGRADE T2 ON T1.SAL BETWEEN T2.LOSAL AND T2.HISAL;

--15.对于每个员工,显示其加入公司的天数.
SELECT T1.ENAME, TRUNC(TO_NUMBER(SYSDATE - T1.HIREDATE)) AS DAYS
    FROM EMP T1 WHERE T1.ENAME IN (SELECT ENAME AS DAYS FROM EMP GROUP BY ENAME);
--14.显示所有员工的姓名、加入公司的年份和月份
SELECT ENAME,
       '加入公司的年份：' || TO_CHAR(HIREDATE, 'YYYY') AS EYEAR,
       '加入公司的月份：' || TO_CHAR(HIREDATE, 'MM') AS EMONTH FROM EMP;
--13.显示满10年服务年限的员工的姓名和受雇日期.
SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE < ADD_MONTHS(SYSDATE, -12 * 10);
--12.显示所有员工的姓名,用a替换所有"A"
SELECT ENAME, REPLACE(ENAME, 'A', 'a') AS REPNAME FROM EMP;
--11.显示不带有"T"的员工的姓名.
SELECT ENAME FROM EMP WHERE ENAME NOT LIKE '%T%';
--10.显示正好为5个字符的员工的姓名.
SELECT ENAME FROM EMP WHERE LENGTH(ENAME) = 5;
--9.以首字母大写的方式显示所有员工的姓名.
SELECT INITCAP(ENAME) FROM EMP;
--8.找出早于27年前受雇的员工.
SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE < ADD_MONTHS(SYSDATE, -12 * 27);
--5,nvl函数(一般用于当字段为null时候返回默认值)
SELECT NVL(COMM,1111),comm FROM EMP;
--4.查找入职时间在1981-4-01后的员工
SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE > TO_DATE('1981-4-01', 'YYYY-MM-DD');
--3.以货币形式显示sal列
SELECT TO_CHAR(SAL, 'FM999,999,999,999,999.00') FROM EMP;
SELECT TO_CHAR(SAL,'$99,999,999'),TO_CHAR(SAL,'L99,999,999'),TO_CHAR(SAL,'L00,000,000') FROM EMP;
--2,round函数的使用【四舍五入】
SELECT * FROM DUAL;
--1,例:提取 7654 号雇员姓名的 2－4位
SELECT EMPNO, ENAME, SUBSTR(ENAME, 2,3) AS STR FROM EMP WHERE EMPNO = '7654';

多表联查

--9,统计每个部门工资在(500-1000)、(1000-3500)、(3500-7000) 的人数
SELECT A.*,
(SELECT COUNT(*) FROM EMP B WHERE B.DEPTNO=A.DEPTNO AND SAL>500 AND SAL<=1000) AS "500-1000人数",
(SELECT COUNT(*) FROM EMP B WHERE B.DEPTNO=A.DEPTNO AND SAL>1000 AND SAL<=3500) AS "1000-3500人数",
(SELECT COUNT(*) FROM EMP B WHERE B.DEPTNO=A.DEPTNO AND SAL>3500 AND SAL<=7000) AS "3500-7000人数"
FROM DEPT A;
--8,统计每个部门的信息和人数
SELECT T1.*, (SELECT COUNT(1) FROM EMP WHERE DEPTNO = T1.DEPTNO) AS PCOUNT FROM DEPT T1;
--7,查询本部门最高工资的员工(2种方法)
SELECT * FROM EMP T1, (SELECT MAX(SAL) MAXSAL, DEPTNO FROM EMP GROUP BY DEPTNO) T2 WHERE T1.SAL = T2.MAXSAL AND T1.DEPTNO = T2.DEPTNO;
SELECT * FROM EMP T1 WHERE SAL = (SELECT MAX(T2.SAL) FROM EMP T2 WHERE T2.DEPTNO = T1.DEPTNO);
--6,查询最高工资的员工
SELECT * FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP);
--5,查询工资高于本部门平均工资的所有员工(2种 )
SELECT * FROM EMP T1
    LEFT JOIN (SELECT AVG(SAL) AS AVGSAL, DEPTNO FROM EMP GROUP BY DEPTNO) T2
        ON T1.DEPTNO = T2.DEPTNO WHERE T1.SAL > T2.AVGSAL;
SELECT * FROM EMP A WHERE A.SAL > (SELECT AVG(SAL) FROM EMP B WHERE B.DEPTNO=A.DEPTNO);
--4,查询工资高于公司平均工资的所有员工
SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);
--3,查询工资高于WARD工资的所有员工
SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'WARD');
--2,查询员工姓名和所在部门的名称,要求部门编号为30(2种 )
SELECT T1.ENAME, T2.DNAME FROM EMP T1 LEFT JOIN DEPT T2 ON T1.DEPTNO = T2.DEPTNO WHERE T1.DEPTNO = '30';
SELECT ENAME, DNAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO = '30';
--1,查询员工姓名和所在部门的名称(2种写法)
SELECT T1.ENAME, T2.DNAME FROM EMP T1 LEFT JOIN DEPT T2 ON T1.DEPTNO = T2.DEPTNO;
SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;