Oracle常见的查询测试题

第一套

数据库表包含两个,分别如下:

dept 部门表
-- depyno 部门编号
-- dname 部门名称
-- loc 部门地址
teacher 老师表
-- tno 老师编号
-- tname 老师姓名
-- tid 老师身份证号
-- gender 性别
-- birthdate 生日
-- hiredate 入职日期
-- sal 薪资
-- deptno 部门id
-- job 工作
-- comm
-- mgrno 经理

下面可以开始工作了:
1.列出至少有一个员工的所有部门。
(两个表联合查询,及group by...having的用法)

select dname from dept 
where deptno 
in 
(select deptno from emp 
    group by deptno 
    having count(*)>1);

2.列出薪金比“SMITH”多的所有员工。(经典的自连接查询)

select ename from emp 
    where sal > (select sal from emp where ename like 'SMITH');

3.列出所有员工的姓名及其直接上级的姓名。
(多次对自己查询,为表的取个别名,内部查询可以像对象一样引用外部的对象的字段,
这里引用与编程中的作用域相似,即与{}类比)

select ename,
    (select ename from emp 
        where empno in (a.mgr) ) 
from emp a ;

4.列出受雇日期早于其直接上级的所有员工。(同上,日期可直接拿来比较)

select ename from emp a 
where HIREDATE < (select HIREDATE from emp 
            where empno in(a.mgr));

5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门(以emp表为主,左连接查询)

select dname,emp.* from dept 
left join emp on dept.deptno=emp.deptno;

6.列出所有“CLERK”(办事员)的姓名及其部门名称。(域,注意())

select ename,(select dname from dept where deptno in(a.deptno))
as dname from emp a 
where JOB like 'CLERK';

7.列出最低薪金大于1500的各种工作。

select job from emp where sal>1500;

8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。(经典的两个表连接)

select ename from emp 
where deptno=(select deptno from dept where dname like'SALES');

9.列出薪金高于公司平均薪金的所有员工。(反复查自己)

select ename from emp 
where sal>( select avg( sal) from emp);

10.列出与“SCOTT”从事相同工作的所有员工。(排除自己)

select ename from emp 
where job in(select job from emp where ename like'SCOTT') 
and ename!='SCOTT' ;

11.列出薪金等于部门编号为30中员工的薪金的所有员工的姓名和薪金。
(any的用法,且排挤)

select ename,sal from emp
where sal = any(select sal from emp where deptno=30) 
and deptno!=30;

12.列出薪金高于在部门30工作的所有员工的姓名和薪金。(max的用法)

select sal,ename from emp where sal>(select max(sal) from emp where deptno=30);

13.列出在每个(每个是关键字,对此group by)部门工作的员工数量、平均工资和平均服务期限(单位:天)。(经典的group by用法)

select deptno,count(*),avg(a.sal),avg(sysdate-HIREDATE) from emp a group by deptno;

14.列出所有员工的姓名、部门名称和工资.(经典的两个表的连接查询,用具体的名称替换一个表中的主键的id (解决很多人在实际运用中会遇到的不能绑定多列的问题),也可用where来查询 ,与题5比较)

select ename,sal,(select dname from dept a where a.deptno=b.deptno)as dname from emp b;

15.列出所有部门的详细信息和部门人数。(因为是*,将显示dept和后面临时表b的全部字段(注意:不只是dept的字段,注意*号))

select * from dept a left join (select deptno,count(*) from emp group by deptno) b on a.deptno=b.deptno ;

16.列出各种(与每个同义(参看题13))工作的最低工资。

select job,min(sal) from emp group by job ;

17.列出各个部门的MANAGER(经理,经理唯一,不用group by)的最低薪金。

select min(sal) from emp where job like'MANAGER';(因为MANAGER是值不是字段,所以不能用小写)

18.列出所有员工的年工资,按年薪从低到高排序。(nvl:空转化函数)

select ename,(sal+nvl(comm,0))*12 as sal from emp order by sal ;

如果有错误,欢迎留言!!

Last modification:December 14th, 2017 at 11:55 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment