SQL题目1 - oracle

文章目录

• • • 1. 单行子查询
    • 2. 多行子查询
    • 3. 多列子查询
    • 4. form中使用子查询
    • • 伪列Rownum
      • 分页-Rownum
    • 5. 总练习

1. 单行子查询

1. 查询入职日期最早的员工姓名，入职日期

select ename, hiredate from empwhere hiredate = (select min(hiredate) from emp );

2. 查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称

select ename, sal, dname from emp, deptwhere emp.deptno = dept.deptno and  sal > (select sal from emp where ename = 'SMITH') and  loc = 'CHICAGO';

3. 查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期

select ename, hiredatefrom empwhere hiredate < ( select min( hiredate ) from emp where deptpno = 20 );

4. 查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数

select deptno, dname, count(*)from dept, empwhere emp.deptno = dept.deptnogroup by emp.deptno, dnamehaving count(*) > ( select count( empno ) / count( distinct deptno ) from emp where deptno is not null ) ;                   

2. 多行子查询

1. 查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括 10部门员工

select ename, hiredatefrom empwhere deptno != 10and hiredate > any (select hiredate from emp where deptno = 10);

2. 查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工

select ename, hiredatefrom empwhere deptno != 10and hiredate > all ( select hiredate from emp where deptno = 10 );    

3. 查询职位和10部门任意一个员工职位相同的员工姓名，职位职位，不包括10部门员工

select ename, jobfrom empwhere deptno != 10and job in ( select job from emp where deptno = 10 );    

3. 多列子查询

1. 查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位职位，不包括10部门员工

select ename, jobfrom empwhere deptno != 10and ( job, mgr ) in ( select job, mgrfrom empwhere deptno = 10);

2. 查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，不包括10部门员工

select ename, jobfrom empwhere deptno != 10and ( job in (select job from emp where deptno = 10)ormgr in (select mgr from emp where deptno = 10));

4. form中使用子查询

1. 查询比自己部门职位平均工资高的员工姓名、职位，部门名称，职位平均工资

select ename, job, dname, sal, avg_salfrom emp, ( select emp.deptno, dname, avg( sal ) as avg_salfrom dept, empwhere dept.deptno = emp.deptnogroup by emp.deptno, dname) awhere emp.deptno = a.deptnoand sal > avg_sal;

2. 查询比自己职位平均工资高的员工姓名、职位，部门名称，职位平均工资

select ename, job, dname, sal, avg_salfrom emp,dept,(select job, avg(sal) as avg_sal from emp group by job ) awhere emp.deptno = dept.deptnoand emp.job = a.joband sal > avg_sal ;

3. 查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位，不包括SCOOT和BLAKE本人

select ename, emp.jobfrom emp,( select job, mgr from empwhere ename in ('SCOTT', 'BLAKE') ) awhere ename not in ('SCOTT', 'BLAKE')and emp.job = a.joband emp.mgr = a.mgr;

4. 查询不是经理的员工姓名

select enamefrom emp, (select distinct mgr from emp where mgr is not null ) emp2where emp.empno = emp2.mgr( + ) and emp2.mgr is null;

伪列Rownum

1. 查询入职日期最早的前5名员工姓名，入职日期

select ename, hiredatefrom (select * from emp order by hiredate desc)where rownum < 6;

2. 查询工作在CHICAGO并且入职日期最早的前2名员工姓名，入职日期

select ename, hiredate from  ( select *from emp where deptno = (select deptno from dept where loc = 'CHICAGO')order by hiredate asc )where rownum < 3;

分页-Rownum

1. 按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称。

select ename, hiredate, ( select dname from dept where b.deptno = dept.deptno )from  (select rownum rn, a.*from ( select * from emp order by hiredate  desc ) a )  bwhere rn between 1 and 5;       -- 第一页-- where rn between 6 and 10;   第二页
-- where rn between 11 and 15;   第三页

2. 按照每页显示5条记录，分别查询工资最高的第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称、工资。

--- 第一页的最高工资
select ename, hiredate, sal,( select dname from dept where b.deptno = dept.deptno )from  (select rownum rn, a.*from ( select * from emp order by hiredate desc ) a )  bwhere rn between 1 and 5and sal = ( select max(sal)from  (select rownum rn, a.*from ( select * from emp order by hiredate desc ) a ) where rn between 1 and 5 ); ---  第二页、三页的最高工资只要更改上述代码的的 rn between and 就可以了

5. 总练习

1. 查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资

select empno, ename, salfrom empwhere sal > ( select sal from emp where empno = 7782)and job = ( select  job from emp where empno = 7369 );

2. 查询工资最高的员工姓名和工资

select ename, salfrom empwhere sal = ( select max(sal) from emp );

3. 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

select emp.deptno, dname, min(sal)from emp,deptwhere emp.deptno = dept. deptnogroup emp.deptno, dnamehaving min(sal) > ( select min(sal) from emp where deptno = 10 )

4. 查询员工工资为其部门最低工资的员工的编号和姓名及工资

--- 普通子查询
select empno, ename, salfrom emp,( select deptno, min(sal) min_sal from emp group by deptno ) dept_minwhere emp.deptno = dept_min.deptno and sal = dept_min.min_sal;--- 相关子查询
select empno, ename, salfrom emp ewhere sal = ( select min(sal) from emp where deptno = e.deptno )

5. 显示经理是KING的员工姓名，工资

select ename, salfrom empwhere mgr = ( select empno from empwhere ename = 'KING');

6. 显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间

select ename, sal, hiredatefrom empwhere hiredate > (select hiredate from emp where ename = 'SMITH');

7. 使用子查询的方式查询哪些职员在NEW YORK工作

select * from emp where deptno = ( select deptno from deptwhere loc = 'NEW YORK' );

8. 写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH

select ename, hiredatefrom empwhere deptno in ( select deptno from empwhere ename = 'SMITH')and  ename != 'SMITH';

9. 写一个查询显示其工资比全体职员平均工资高的员工编号、姓名

select empno, enamefrom empwhere sal > ( select avg(sal) from emp  );

10. 写一个查询显示其上级领导是KING的员工姓名、工资

select ename, salfrom empwhere mgr = ( select empno from emp where ename = 'KING' );

11. 显示所有工作在RESEARCH部门的员工姓名，职位

select ename, job from empwhere deptno = ( select deptno from dept where dname = 'RESEARCH' );

12. 查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资

select deptno, avg(sal)from empwhere deptno is not nullgroup by deptnohaving avg(sal) > ( select avg(sal) from empwhere deptno = 20);

13. 查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度

select ename, sal, avg_sal, sal - avg_salfrom emp,(select deptno, avg(sal) as avg_sal from empgroup by deptno) awhere emp.deptno = a.deptnoand sal > avg_sal;

14. 列出至少有一个雇员的所有部门

select dept.deptnofrom emp, deptwhere emp.deptno = dept.deptnogroup by dept.deptnohaving count(*) > 0;

15. 列出薪金比"SMITH"多的所有雇员

select emp.*from empwhere sal > ( select sal from empwhere ename = 'SMITH' );

16. 列出入职日期早于其直接上级的所有雇员

select emp1.*from emp emp1, emp emp2where emp1.mgr = emp2.empnoand emp1.hiredate < emp2.hiredate

17. 找员工姓名和直接上级的名字

select emp1.ename, emp2.enamefrom emp emp1, emp emp2where emp1.mgr = emp2.empno

18. 显示部门名称和人数

select dname, count(*)from emp, deptwhere emp.deptno = dept.deptnogroup by dname;

19. 显示每个部门的最高工资的员工

select emp.*from emp, ( select deptno, max(sal) max_salfrom empgroup by deptno) awhere emp.deptno = a.deptnoand sal = max_sal;

20. 显示出和员工号7369部门相同的员工姓名，工资

select ename, salfrom empwhere deptno = ( select deptnofrom empwhere  empno = 7369 );

21. 显示出和姓名中包含"W"的员工相同部门的员工姓名

select enamefrom empwhere deptno in ( select deptno from empwhere ename like '%W%' );

22. 显示出工资大于平均工资的员工姓名，工资

select ename, salfrom empwhere sal > ( select avg(sal) from emp );

23. 显示出工资大于本部门平均工资的员工姓名，工资

select ename, salfrom emp, ( select deptno, avg(sal) avg_salfrom empgroup by deptno ) awhere emp.deptno = a.deptnoand sal > avg_sal;            

24. 显示每位经理管理员工的最低工资，及最低工资者的姓名

select mgr_id, ename, min_salfrom emp, ( select emp2.empno mgr_id, min( emp1.sal ) min_salfrom emp emp1, emp emp2where emp1.mgr = emp2.empnogroup by emp1.mgr, emp2.empno ) where emp.mgr = mgr_idand sal = min_salorder by mgr_id;

25. 显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间

select ename, hiredate from empwhere hiredate > all ( select hiredatefrom empwhere sal = ( select max(sal) from emp  )and hiredate is not null );

26. 显示出平均工资最高的的部门平均工资及部门名称

select dnamefrom emp, deptwhere emp.deptno = dept.deptnogroup by deptnohaving avg(sal) = ( select max( avg(sal) ) from empwhere deptno is not null group by deptno );

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！