美文网首页
Oracle针对SCOTT下EMP表的练习题

Oracle针对SCOTT下EMP表的练习题

作者: printf200 | 来源:发表于2019-04-23 08:33 被阅读0次

    1.查询所有的部门编号:
    select DEPTNO from DEPT ;

    2.查询所有有人的部门编号:
    select DEPTNO from EMP ;

    3.查询所有岗位名称:
    select DISTINCT “JOB” from EMP ;

    4.查询所有薪水超过两千的员工信息
    select * from EMP where SAL>2000;

    5.查询所有20部门的员工姓名,编号及薪水
    select ENAME,EMPNO,SAL from EMP where DEPTNO=20;

    6.查询所有没有奖金的员工信息
    select * from EMP where (COMM is NULL OR COMM = 0);

    7.查询所有有奖金的员工信息
    select * from EMP where (COMM is NOT NULL );

    8.查询最高领导的员工信息
    select * from EMP where MGR is NULL;

    9.查询所有81年之后入职的员工信息
    select * from EMP where HIREDATE >= “TO_DATE”(‘1981-01-01’,’yyyy-mm-dd’);
    select * from EMP where HIREDATE >= ‘1-1月-81’;

    10.查询所有薪水在2000-4000范围内的员工信息
    select * from EMP where SAL BETWEEN 2000 AND 4000;

    11.查询所有部门编号是10或30的员工信息
    select * from EMP where DEPTNO IN (10,30);

    12.查询所有20部门并且薪水超过2000的员工信息:
    select * from EMP where DEPTNO = 20 AND SAL > 2000;

    13.查询所有薪水不在2000-4000范围内的员工信息
    select * from EMP where SAL NOT BETWEEN 2000 AND 4000;

    14.查询所有部门编号不是10,30的员工信息
    select * from EMP where DEPTNO NOT IN (10,30);

    15.查询用户名为scott的员工信息:注意区分大小写
    select * from EMP where ENAME = ‘SCOTT’;

    16.查询姓名里面包含ALL的员工姓名
    select ENAME from EMP WHERE ENAME LIKE ‘%ALL%’;

    17.查询所有以”S”开头的同学
    select * from EMP WHERE ENAME LIKE ‘S%’;

    18.查询第二个字母为A的员工姓名
    select ENAME from EMP WHERE ENAME LIKE ‘_A%’;

    19.查询所有员工的编号、姓名、部门编号、职位、薪水,按照薪水降序排列
    select EMPNO,ENAME,DEPTNO,”JOB”,SAL from EMP ORDER BY SAL DESC;

    20.查询所有员工信息,按照部门降序排列,部门内按照薪水升序排列
    select * from EMP ORDER BY DEPTNO DESC,SAL ASC;

    21.查询姓名中包含‘A’员工的姓名,编号,薪水,按照薪水降序排列
    select ENAME,EMPNO,SAL from EMP WHERE ENAME LIKE ‘%A%’ ORDER BY SAL DESC;

    22.查询年收入超过10000的员工的姓名,编号,薪水,年收入,按照年收入降序排列
    SELECT ENAME,EMPNO,SAL,(SAL+”NVL”(COMM, 0))12 AS YEARSAL
    FROM EMP WHERE (SAL+”NVL”(COMM, 0))
    12 > 10000 ORDER BY YEARSAL DESC;

    23.查询年薪超过10000的员工的姓名,编号,薪水,年收入,按照年薪降序排列
    SELECT ENAME,EMPNO,SAL,SAL12 AS YEARSAL
    FROM EMP WHERE SAL
    12 > 10000 ORDER BY YEARSAL DESC;

    后续练习题:

    24.查询雇员表中,姓名为SMITH的雇员,截止到今天共工作了多少周,则可以使用如下的SQL语句
    SELECT ROUND((SYSDATE-HIREDATE)/7) AS “Weeks” FROM EMP WHERE ENAME = ‘SMITH’;

    25.查询各部门的最高薪水、最低薪水、平均薪水….
    SELECT DEPTNO,”MAX”(SAL),”MIN”(SAL),”AVG”(SAL) FROM EMP GROUP BY DEPTNO;

    26.查询‘SMITH’的领导姓名
    SELECT ENAME FROM EMP WHERE EMPNO = (SELECT MGR FROM EMP WHERE ENAME = ‘SMITH’);

    27.查询部门名称是‘SALES’的员工信息
    SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = ‘SALES’);

    28.查询公司中薪水最高的员工信息
    SELECT * FROM EMP WHERE SAL = (SELECT “MAX”(SAL) FROM EMP);

    29.查询公司所有员工的个数
    SELECT “COUNT”(ENAME) FROM EMP ;

    30.查询公司中最高薪水是多少
    SELECT “MAX”(SAL) FROM EMP ;

    31.查询公司中平均奖金是多少
    SELECT “AVG”(“NVL”(COMM, 0)) FROM EMP ;

    32.查询公司中最晚入职的时间
    SELECT “MAX”(HIREDATE) FROM EMP ;

    33.查询公司中有奖金的人数
    SELECT “COUNT”(ENAME) FROM EMP WHERE (COMM IS NOT NULL) ;

    34.查询20部门的最高薪水是多少
    SELECT “MAX”(SAL) FROM EMP WHERE DEPTNO = 20 ;

    35.查询各部门的平均薪水及部门编号,部门名称。
    SELECT “AVG”(SAL),DNAME,EMP.DEPTNO FROM EMP,DEPT where EMP.DEPTNO = DEPT.DEPTNO GROUP BY DNAME,EMP.DEPTNO;

    36.查询各部门中最高薪水的员工编号,姓名…
    select empno , ename,DEPTNO from emp WHERE SAL in (select “MAX”(SAL) from EMP GROUP BY DEPTNO);

    37.查询所有员工姓名中包含‘A’的最高薪水
    SELECT “MAX”(SAL) FROM EMP WHERE ENAME LIKE ‘%A%’ ;

    38.查询各岗位的最高薪水,最低薪水。要求只统计薪水>1000的
    SELECT “MAX”(SAL),”MIN”(SAL) FROM EMP WHERE SAL > 1000 GROUP BY “JOB”;

    39.查询各部门的平均薪水及部门编号,要求只列出平均薪水>2000
    SELECT AVG(SAL),DEPTNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 2000;

    40.查询各部门的平均薪水及部门编号,要求只有员工姓名中包含
    ‘A’才参与统计,只列出平均薪水>1500的,按照平均薪水降序排列
    SELECT AVG(SAL),DEPTNO FROM EMP where ENAME LIKE ‘%A%’ GROUP BY DEPTNO HAVING AVG(SAL) > 1500 ORDER BY AVG(SAL) DESC;

    41.查询各部门最高薪水的员工信息(有坑)(薪水和部门编号都需要匹配)
    select * from EMP where (deptno,SAL) in (select deptno,MAX(sal) from emp GROUP BY DEPTNO);

    42.查询最高薪水的员工信息
    SELECT * FROM EMP WHERE SAL = (SELECT “MAX”(SAL) FROM EMP);

    43.查询薪水大于该部门平均薪水的员工信息
    select * from emp e1 where sal > (
    select avg(sal) from emp e2 where e1.deptno = deptno
    );

    44.查询最高薪水的员工信息
    SELECT * FROM EMP WHERE SAL = (SELECT “MAX”(SAL) FROM EMP);
    45.查询各部门最高薪水的员工信息
    select * from EMP where (deptno,SAL) in (select deptno,MAX(sal) from emp GROUP BY DEPTNO);

    46.查询‘SMITH’的领导姓名
    select ENAME from EMP where EMPNO = (select MGR from EMP where ENAME = ‘SMITH’) ;

    47.查询部门名称是‘SALES’的员工信息
    select * from EMP where DEPTNO = (SELECT DEPTNO FROM DEPT WHERE dname = ‘SALES’) ;

    48.查询公司中薪水最高的员工信息
    select * from EMP where SAL = (SELECT MAX(SAL) FROM EMP) ;

    49.查询薪水等级为4的员工信息
    select * from emp where sal between
    (select LOSAL from salgrade where grade = 4) AND
    (select HISAL from salgrade where grade = 4) ;

    50.查询领导者是‘BLAKE’的员工信息
    select * from emp where MGR = (select EMPNO from emp where ENAME = ‘BLAKE’);

    51.查询最高领导者的薪水等级
    select grade FROM SALGRADE where (SELECT SAL from emp where mgr is NULL) BETWEEN LOSAL AND HISAL;

    52.查询薪水最低的员工信息
    select * from emp where SAL = (select MIN(sal) from emp);

    53.查询和SMITH工作相同的员工信息
    select * from emp where JOB = (select JOB from emp where ENAME = ‘SMITH’) ;

    54.查询不是领导的员工信息
    select * from emp where EMPNO not in (SELECT “NVL”(MGR, 0) FROM EMP) ;

    select * from emp e1 where not EXISTS
    (select * from emp e2 where e2.mgr = e1.empno);

    55.查询平均工资比10部门低的部门编号
    select deptno from emp group by deptno having avg(sal) <
    (select avg(sal) from emp where deptno = 10);

    56.查询在纽约工作的所有员工
    select * from EMP where DEPTNO = (select DEPTNO from DEPT where LOC = ‘NEW YORK’);

    57.查询‘SALES’部门平均薪水的等级
    select grade from SALGRADE where
    (select AVG(SAL) from emp where DEPTNO =
    (select DEPTNO FROM DEPT WHERE dname = ‘SALES’) ) BETWEEN LOSAL AND HISAL;

    58.查询10号部门的员工在整个公司中所占的比例:
    select (select COUNT(DEPTNO) from EMP where DEPTNO = 10) / (select COUNT(*) from EMP) FROM dual;

    59.每页显示5条。
    显示第一页内容:
    select rownum rn,EMP.* from emp where rownum <= 5;
    显示第二页的内容:
    select * from
    (select rownum rn,EMP.* from emp where rownum <= 10)
    where rn > 5;
    按照薪水降序排列,每页显示5条,显示第二页的内容:
    select emp.* from
    (select rownum rn,t1.* from
    (select * from emp order by sal desc) t1
    where rownum <= 10) emp
    where rn > 5;

    60.查询各部门工资大于该部门平均工资的员工信息:
    select * from emp e1 where sal > (
    select avg(sal) from emp e2 where e1.deptno = deptno
    );

    61.查询各岗位工资小于该岗位平均工资的员工信息;
    select * from emp e1 where sal < (select avg(sal) from emp e2 where e1.job= job group by job);

    62.查询所有领导的信息:要求使用exists关键字
    select * from emp e1 where EXISTS
    (select * from emp e2 where e2.mgr = e1.empno);

    63.查询所有员工的姓名,薪水,部门名称
    select ename,sal,dname from emp,dept where EMP.DEPTNO = DEPT.DEPTNO;

    64.查询所有员工的姓名,薪水,部门名称,薪水等级
    select ename,sal,dname,grade from emp,dept,SALGRADE
    where EMP.DEPTNO = DEPT.DEPTNO and SAL BETWEEN LOSAL AND HISAL;

    65.查询员工姓名及领导者姓名
    select a.ename AS 员工姓名 ,b.ename AS 领导姓名
    from emp a LEFT JOIN emp b on a.mgr = b.empno;

    66.查询所有员工的姓名,部门名称
    select ename, dname from emp ,dept where EMP.deptno = DEPT.deptno;

    练习题:

    1.查询员工表中工资大于1600的员工的姓名和工资
    select ename,sal from emp where sal > 1600;

    2.查询员工表中员工号是17的员工的姓名和部门编号
    select ename,deptno from emp where empno = 17;

    3.选择员工表中工资不在4000到5000内的员工的姓名和工资
    select ename,sal from emp where sal not BETWEEN 4000 and 5000;

    4.选择员工表中在20和30部门工作的员工的姓名和部门号
    select ename,deptno from emp where deptno in (20,30);

    5.选择员工表中没有管理者的员工姓名及职位,按职位排序
    select ename,job from emp where mgr is null ORDER BY job asc;

    6.选择员工表中有奖金的员工姓名,工资和奖金,按工资倒序排列
    select ename,sal,comm from emp where comm is not null ORDER BY sal asc;

    7.选择员工表中员工姓名的第三个字母是A的员工姓名
    select ename from emp where ename like ‘__A%’;

    8.列出部门表中的部门名称和所在城市
    select dname,loc from dept ;

    9.显示员工表中的不重复的岗位job
    select DISTINCT job from emp ;

    10.连接员工表中的员工姓名、职位、薪水,列之间用逗号连接,列头显示成out_put
    select ename ||’,’|| job ||’,’|| sal AS ename_job_sal from emp ;

    11.查询员工表中员工号,姓名,工资,以及工资提高百分之20之后的结果
    select empno ,ename,sal ,sal*1.2 from emp ;

    12.查询员工的姓名和工资数,条件限定为工资数必须大于1200,并且查询结果按入职时间进行排序。早入职的员工排在前面
    select empno ,sal,HIREDATE from emp where sal > 1200 ORDER BY HIREDATE ;

    13.列出除了ACCOUNTING部门之外还有什么部门
    select dname from dept where dname != ‘ACCOUNTING’;

    14.把雇员按部门分组,求最高薪水,部门号 要求过滤掉名字中第二个字母是’A’的员工, 并且部门的平均薪水 > 3000,按照部门编号倒序排列
    select MAX(sal),deptno from emp where ename not like ‘_A%’ GROUP BY deptno having avg(sal) > 3000 ;

    15.求工作职位是’manager’的员工姓名,部门名称和薪水等级
    select ename ,dname,grade from emp,dept,salgrade
    where job = ‘MANAGER’ and (sal BETWEEN LOSAL and HISAL)
    and EMP.DEPTNO = DEPT.DEPTNO;

    按照部门分组统计,求最高薪水,平均薪水,最低薪水,只有薪水是1200以上的员工才参与统计,并且分组结果中只包含平均薪水在1500以上的部门,并且按照平均薪水倒序排列
    select max(sal),min(sal),avg(sal) from emp where sal > 1200
    group by deptno having avg(sal) > 1500 order by avg(sal) DESC;
    17.求薪水最高的员工姓名
    select ename from emp where sal = (select max(sal) from emp);

    18.查询各部门平均薪水等级,并且按平均薪水等级的降序排列
    select grade from salgrade s join
    (select avg(sal) avg_sal from emp e group by deptno) temp
    on TEMP.avg_sal between s.LOSAL and s.HISAL;

    19.查询所有员工姓名以S或s开头的所有员工信息
    select * from emp where ename like ‘S%’ or ename like ‘s%’;

    20.查询所有工作时间超过一年的员工编号,姓名及入职时间,要求雇用时间的格式为’yyyy年mm月dd日’
    select empno,ename,TO_CHAR(HIREDATE,’yyyy”年”MM”月”dd”日”’) from emp where
    TO_CHAR(SYSDATE,’YYYY’) - TO_CHAR(hiredate,’YYYY’) > 1;

    21.查询20部门的所有员工的员工姓名,实际收入
    select ename,sal+NVL(comm, 0) from emp where deptno = 20 ;

    22.查询10部门工资大于3000的员工信息,要求按员工的入职时间由前到后排序
    select * from emp where deptno = 10 and sal > 3000 ORDER BY HIREDATE ;

    23.查询10部门或20部门的所有员工的姓名,并截取前三位,按员工姓名升序排列
    select SUBSTR(ename, 1, 3) from emp where deptno in (20,10) ORDER BY ename ;

    24、查询所有员工的姓名,要求所有员工的姓名显示成小写,雇用日期显示为”yyyy-mm-dd”这种格式,薪水转换成’99,999.999′这种格式selectlower(ename),TOCHAR(HIREDATE,′YYYY−MM−DD′),TOCHAR(sal,′99,999.999′这种格式selectlower(ename),TOCHAR(HIREDATE,′YYYY−MM−DD′),TOCHAR(sal,′99,999.999’) from emp ;

    25、查询所有员工的姓名,所在部门名称,薪水,薪水等级、直接领导的姓名 (有问题,不显示最高领导)
    select e1.ename,DEPT.dname,e1.sal,grade,e2.ename AS leader from emp e1,emp e2,dept,salgrade
    where nvl(e1.MGR,0) = e2.empno and (e1.sal between SALGRADE.LOSAL and SALGRADE.HISAL)
    and e1.deptno = dept.deptno;

    26、查询部门名称是’ACCOUNTING’的员工姓名及薪水等级
    select ename,grade from emp ,salgrade ,dept where
    dname=’ACCOUNTING’ and sal between LOSAL and HISAL
    and EMP.deptno = DEPT.deptno ;

    27、不能使用组函数,查询薪水的最高值
    select sal from emp where sal >= all (select sal from emp);

    28、统计平均薪水最高的部门名称

    29、查询平均薪水等级最低的部门名称

    选做

    1、查询平均薪水最低的部门名称,要求:只有领导才参加统计
    2、查询比普通员工的最高薪水还要高的领导者姓名
    3、找出薪水最高的五个人
    4、查询第2到第7名的员工,按薪水降序排列
    5、查询最后入职的5名员工

    相关文章

      网友评论

          本文标题:Oracle针对SCOTT下EMP表的练习题

          本文链接:https://www.haomeiwen.com/subject/yippvqtx.html