ORACLE查询练习题

旧城等待， 2022-05-27 03:49 236阅读 0赞

--01.查询员工表所有数据，并说明使用*的缺点
    --会将所有数据显示，数据过于庞大，不直观
    --01．查询职（job）为'PRESIDENT'的员工的工资 
     Select ename,job,sal from emp
    				Where job=’PRESIDENT’;
            
    --02．查询佣金为0或为null的员工的信息 
    Select * from emp where comm=0 or comm is null;
    
    --03．查询入职日期在1981-5-1到1981-12-31之间的所有员工的信息 
    Select * from emp 
    Where
    hiredate >to_date(‘1981-5-1’,’yyyy-mm-dd’)
    and
    hiredate<=to_date(‘1981-12-31’,’yyyy-mm-dd’);
    
    --04．.查询所有名字长度为4的员工的员工的编号，姓名 
    Select ename,empno from emp 
    Where enaem like ‘____’;
    
    --05．显示10号部门饿所有经理和20号部门的所有员工 
    Select ename,job,deptno from emp
    Where (deptno=10 and job=’MANAGER’ OR DEPTNO=20);
    
    --06．显示姓名没有'L'字的员工的详细信息或含有'SM'字的员工信息 
    Select ename from emp 
    Where
     (ename not like ‘%L%’)
     Or
     (ename like %SM%);
     
    --07．显示各个部门经理的工资 
    Select * from emp where job=’MANAGER’;
    
    --08．显示佣金收入比工资高的员工的详细信息 
    Select * from emp where comm>sal;
    
    --10．把hiredate列看做是员工的生日，求本月过生日的员工： 
    Select ename,hiredate from emp 
    Where extract(month from hriedate)=extract(month from sysdate)-1;
    
    --11．把hiredate列看做是员工的生日，求下月过生日的员工 
    Where extract(month from hriedate)=extract(month from sysdate)+1;
    
    --12.求1982年入职的员工 
    Select * from emp where extract(year from hiredate)=’1982’;
    
    --13．求1981年下半年入职的员工 
    Select * from emp where 
    hiredate > to_date(‘1981-6-1’,’yyyy-mm-dd’)
    and
    hiredate<to_date(‘1981-12-31’,’yyyy-mm-dd’);
    
    --14．求1981年各个月入职的员工个数 
    select count(*),to_char(hiredate,’mm’) from emp
    where to_char(hiredate,'yyyy')=1981 group by to_char(hiredate,'mm')
    Select * from emp 
    
    
    --PartII 
    --01 .查询各个部门的平均工资 
    SELECT deptno,ROUND(avg(sal),2) avg
    FROM emp
    GROUP BY deptno
    
    --02.显示各种职位的最低工资 
    SELECT job,min(sal)
    FROM emp
    GROUP BY job
    
    --03．按照入职日期由新到旧排列员工信息 
    SELECT *
    FROM emp
    ORDER BY hiredate desc
    
    --04．查询员工的基本信息，附加其上级的姓名（自关联）
    SELECT e.*,d.ename
    FROM emp e,emp d
    WHERE e.mgr=d.empno(+)
    
    --05．显示工资比’ALLEN’高的所有员工的姓名和工作 
    SELECT ename,job,sal
    FROM emp
    WHERE sal>
    (SELECT sal
    FROM emp 
    WHERE ename='ALLEN')
    
    --06．显示与scott从事相同工作的员工的信息（子查询） 
    SELECT * 
    FROM emp
    WHERE job=
      (SELECT job
      FROM emp
      WHERE ename='SCOTT')
      AND ename<>'SCOTT'
    
    --07．显示销售部(‘SALES’)员工的姓名 
    SELECT ename
    FROM emp
    WHERE deptno=(
    SELECT deptno
    FROM dept
    WHERE dname='SALES')
    
    --08．显示与30号门’MARTIN’员工工资相同的员工的姓名和工资 
    SELECT ename,sal
    FROM emp
    WHERE sal=(
    SELECT sal
    FROM emp
    WHERE deptno=30 
    AND ename='MARTIN')
    
    --09．查询所有工资高于平均工资（包括所有员工）的销售人员 
    SELECT ename,sal
    FROM emp
    WHERE job='ANALYST' AND sal>
    (SELECT MAX(avg(sal))
    FROM emp
    GROUP BY deptno)
    
    --10．显示所有职员的姓名及其所在部门的名称和工资(表连接) 
    SELECT e.ename,e.sal,d.dname
    FROM emp e,dept d
    WHERE e.deptno=d.deptno
    
    --11．查询在研发部(RESEARCH)工作人员的编号，姓名，工作部门，工作所在地 
    SELECT d.deptno,e.ename,d.dname,d.loc
    FROM dept d,emp e
    WHERE d.dname='RESEARCH'
    AND e.deptno=d.deptno
    select * from dept
    --12．查询各个部门的名称和员工人数 
    SELECT d.dname,temp.count
    FROM dept d,(
    SELECT deptno dno,COUNT(empno) count
    FROM emp GROUP BY deptno ) temp
    WHERE d.deptno=temp.dno
    
    --13．查询各个部门员工工资大于平均工资（平均工资包括所有员工）的人数和员工职位(子查询)
     --有问题，读不懂
     SELECT d.dname,e.job,tmp.con
     FROM emp e,dept d,(SELECT job j,COUNT(empno) con FROM emp GROUP BY job) tmp
     WHERE sal>
     (SELECT ROUND(AVG(sal),2)
     FROM emp)
     AND tmp.j=e.job
     AND d.deptno=e.deptno;
     --select count(*),job from emp where sal>(select avg(sal) from emp) group by job; 
    
    --14．查询工资相同的员工的工资和姓名（子查询） 
    SELECT e.ename,e.sal
    FROM emp e
    WHERE (SELECT COUNT(*)
    FROM emp
    WHERE sal=e.sal GROUP BY sal )>1
    
    --15．查询工资最高的3名员工信息(排序) 
    SELECT *
    FROM 
    (SELECT e.* ,
    ROW_NUMBER() OVER(ORDER BY sal desc) id 
    FROM emp e)
    WHERE id<=3
    
    --16．按工资进行排名：排名从1开始，工资相同排名相同
    --（如果两个并列第1则没有地2名，从第三名继续排） 
    SELECT e.*,RANK()OVER(ORDER BY sal DESC) 排名 
    FROM emp e
    
    --17．求入职日期相同的（年月日相同）的员工 
    SELECT e.*
    FROM emp e
    WHERE (
    SELECT COUNT(*) 
    FROM emp 
    WHERE e.hiredate=hiredate 
    GROUP BY hiredate )>1
    
    --18．查询每个部门的最高工资 
    SELECT e.deptno,MAX(sal)
    FROM emp e
    GROUP BY deptno;
    
    --19．查询每个部门，每个职位的最高工资 
    SELECT deptno,job,MAX(SAL)
    FROM emp
    GROUP BY deptno,job 
    ORDER BY deptno;
    
    --20．查询每个员工的信息及工资级别，用到表(Salgrade) 
    SELECT e.*,s.grade
    FROM emp e,salgrade s
    WHERE e.sal 
    BETWEEN s.losal AND s.hisal
    SELECT * FROM salgrade;
    
    --21.查询工资最高的第6-10名员工 
    结果： 
    R SAL 
    ---------- ----- 
    6 2450 
    7 1600 
    8 1500 
    9 1300 
    10 1250 
    Order by不能和rownum合用，
    SELECT * 
    FROM (SELECT ROWNUM r,sal
    FROM( SELECT sal 
    FROM emp ORDER BY sal DESC))
    WHERE r BETWEEN 6 AND 10;
     
    --22．查询各个部门工资最高的员工信息 
    SELECT *
    FROM emp 
    WHERE sal in (SELECT MAX(sal)
    FROM emp
    GROUP BY deptno)
    
    --23．查询每个部门工资最高的前2名员工 
    SELECT *
    FROM 
    (SELECT e.*,ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal) DES
    FROM emp e) 
    WHERE DES<3
    
    --24．查询出有3个以上下属的员工信息（自关联） 
    SELECT * 
    FROM emp e
    WHERE(
    SELECT COUNT(*)
    FROM emp WHERE e.empno=mgr)>=3
    
    --25．查询所有大于本部平均工资的员工信息 
    SELECT  * 
    FROM emp e
    WHERE e.sal >
    (SELECT ROUND(AVG(sal),2) AVG
    FROM emp 
    WHERE e.deptno=deptno)
    
    --26．查询平均工资最高的部门信息 
    SELECT d.*,avgsal
    FROM dept d,
    (SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) e
    WHERE avgsal=
    (SELECT MAX(AVG(sal)) maxsal FROM emp GROUP BY deptno)
    AND d.deptno=e.deptno
    
    --27．查询大于各个部门总工资的平均值的部门信息 
    SELECT d.deptno,d.dname,e.avg
    FROM dept d,
    (SELECT e.deptno dno, AVG(sal) avg FROM emp e GROUP BY deptno) e
    WHERE e.avg=
    (SELECT MAX(AVG(sal)) maxavg FROM emp GROUP BY deptno)
    AND e.dno=d.deptno
    
    --28．查询大于各个部门总工资平均值的部门下的员工信息 
    SELECT e.*,d.*
    FROM emp e,dept d
    WHERE e.sal>
    (SELECT MAX(AVG(sal))
    FROM emp e
    GROUP BY deptno)
    AND d.deptno=e.deptno
    
    --29．查询么有员工的部门信息 
    SELECT d.*,e.count
    FROM dept d,
    (SELECT e.deptno dno,COUNT(empno) count
    FROM emp e 
    GROUP BY deptno)e
    WHERE e.dno(+)=d.deptno
    AND e.count is null
    
    --partIII
    --1、列出至少有一个雇员的所有部门信息（嵌套子查询）
    SELECT d.*,e.* FROM dept d,
    (SELECT e.deptno dno,e.* FROM emp e)e
    WHERE d.deptno=e.dno
    
    --2、列出薪金(工资)比'SMITH'多的所有雇员信息（嵌套子查询）
    SELECT e.*
    FROM emp e
    WHERE e.sal>
    (SELECT sal 
    FROM emp 
    WHERE ename='SMITH' )
     
    --3、列出所有雇员的姓名及其上级的姓名（自表外连接）
    SELECT e.*,d.empno
    FROM emp e,emp d
    WHERE e.mgr=d.empno(+)
    
    --4、列出入职日期(雇佣日期)早于其直接上级的所有雇员（自表连接）
    SELECT e.*
    FROM emp e,emp d
    WHERE e.mgr=d.empno
    AND e.hiredate<d.hiredate
    
    --5、列出部门名称和这些部门的雇员，同时列出那些没有雇员的部门（外连接）
    SELECT e.*,d.*
    FROM emp e,dept d
    WHERE e.deptno(+)=d.deptno
    
    --6、列出所有'CLERK'(办事员)的姓名及其部门名称（内连接）
    SELECT e.ename,e.job,d.dname
    FROM emp e,dept d
    WHERE e.job='CLERK'
    AND d.deptno=e.deptno
    
    --7、列出各种工作的最低薪金，并使最低薪金大于1500（分组和过滤）
    SELECT job,MIN(sal)
    FROM emp
    GROUP BY job having MIN(sal)>1500
    
    --8、列出从事sales(销售)工作的雇员的姓名，假定不知道销售部的部门编号
    --（使用嵌套子查询进行连接）
    SELECT e.deptno,e.*
    FROM dept d,emp e
    WHERE dname='SALES'
    AND e.deptno=d.deptno;
    
    --9、列出薪金高于公司平均水平的所有雇员信息（聚合函数）
    SELECT e.*
    FROM emp e
    WHERE e.sal>
      (SELECT MAX(AVG(sal))
      FROM emp
      GROUP BY deptno)
    
    --10、列出与"SCOTT"从事相同工作的所有雇员（嵌套自查询）
    SELECT e.*
    FROM emp e
    WHERE e.job=
      (SELECT job
      FROM emp
      WHERE ename='SCOTT')
      AND e.ename<>'SCOTT'
    
    --11、列出薪金等于在部门30工作的雇员的所有姓名和薪金（有问题）
    
    --12、列出薪金高于在部门30工作的所有雇员的姓名和薪金（嵌套子查询）
    SELECT e.ename,e.sal
    FROM emp e
    WHERE e.sal>(SELECT MAX(sal)
    FROM emp
    WHERE deptno=30)
    
    --13、列出每个部门的信息（外连接）
    --以及该部门中（分组）雇员的数量（外连接并分组）
    SELECT d.*,e.count
    FROM dept d,
    (SELECT e.deptno dno,COUNT(deptno) count
    FROM emp e
    GROUP BY e.deptno)e
    WHERE e.dno(+)=d.deptno
    
    --14、列出所有雇员的姓名、部门名称和薪金(外连接)
    SELECT e.ename,d.dname,e.sal
    FROM emp e,dept d
    WHERE e.deptno(+)=d.deptno
    
    --15、列出从事同一种工作但属于不同的部门的雇员的不同组合(自表笛卡尔积)
    SELECT e.deptno,d.deptno,e.job,d.job,e.ename,d.ename
    FROM emp e,emp d
    WHERE e.job=d.job AND e.deptno<>d.deptno AND e.ename<d.ename
    
    --16、列出分配有雇员数量（聚合函数）的所有部门（按部门分组并进行外连接）的详细信息，
    --即使分配有零个雇员--这求出的是至少有一名雇员的部门信息.
    SELECT d.*,e.count
    FROM dept d,
      (SELECT e.deptno,COUNT(e.empno) count
      FROM emp e
      GROUP BY deptno)e
      WHERE e.deptno(+)=d.deptno
    
    --17、列出各种类别工作（分组）的最低工资
    SELECT e.job,MIN(sal)
    FROM emp e
    GROUP BY job
    
    --19、列出按计算字段的排序的所有雇员的年薪(计算字段指的是年薪)
    SELECT e.*,sal*12 year
    FROM emp e
    ORDER BY year DESC
    
    --20、列出薪金水平处于第四位的雇员信息（多重嵌套查询）
    SELECT *
    FROM
    (SELECT d.*,ROW_NUMBER()OVER(ORDER BY d.sal DESC) grade
    FROM emp d)
    WHERE grade=4
    
    
    --21、查询各部门薪水最高的员工
    SELECT e.*
    FROM emp e
    WHERE e.sal in
      (SELECT MAX(sal)
      FROM emp
      GROUP BY deptno)