Oracle面试题集锦

1992 2.1 2.2 2.3 2.4

解一：

select [year],

(select amount from tab t where [month] = 1 and t.year = tab.year) as 'm1', (select amount from tab t where [month] = 2 and t.year = tab.year) as 'm2', (select amount from tab t where [month] = 3 and t.year = tab.year) as 'm3', (select amount from tab t where [month] = 4 and t.year = tab.year) as 'm4' from tab group by [year]

解二：

select t1.year,

t1.amount as 'm1', t2.amount as 'm2', t3.amount as 'm3', t4.amount as 'm4' from tab t1, tab t2,

tab t3, tab t4,

where t1.month < t2.month and t2.month < t3.month and t3.month < t4.month and t1.year = t2.year

and t2.year = t3.year and t3.year = t4.year

解三：

这个是ORACLE 中做的：

select * from (select name, year b1, lead(year) over (partition by name order by year) b2, lead(m,2) over(partition by name order by year) b3,rank()over( partition by name order by year) rk from t) where rk=1;

2. 用一条SQL语句查询出每门课都大于80分的学生姓名 name kecheng fenshu 张三语文 81 张三数学 75 李四语文 76 李四数学 90 王五语文 81 王五数学 100 王五英语 90

解：

select distinct [name] from student where [name] not in ( select distinct [name] from student

where fenshu <= 80) 3. 看脚本，回答下面问题 create table dept(

deptno varchar(10) primary key, dname varchar(10));

create table emp(

empno varchar(10) primary key, ename varchar(10), job varchar(10), mgr varchar(10),

sal varchar(10),

deptno varchar(10) references dept(deptno)); drop table dept; drop table emp;

insert into dept values ('1','事业部'); insert into dept values ('2','销售部'); insert into dept values ('3','技术部');

insert into emp values ('01','jacky','clerk','tom','1000','1'); insert into emp values ('02','tom','clerk','','2000','1'); insert into emp values ('07','biddy','clerk','','2000','1'); insert into emp values ('03','jenny','sales','pretty','600','2'); insert into emp values ('04','pretty','sales','','800','2'); insert into emp values ('05','buddy','jishu','canndy','1000','3'); insert into emp values ('06','canndy','jishu','','1500','3'); select * from dept; select * from emp;

1. 列出emp表中各部门的部门号，最高工资，最低工资

select deptno as 部门号,max(sal) as 最高工资,min(sal) as 最低工资 fromemp group by deptno;

2. 列出emp表中各部门job为'CLERK'的员工的最低工资，最高工资

select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp wherejob='clerk' group by deptno;

3. 对于emp中最低工资小于2000的部门，列出job为'CLERK'的员工的部门号，最低工资，

最高工资

select b.deptno as 部门号,max(sal) as 最高工资,min(sal) as 最低工资 from

emp as bwhere job='clerk' and (select min(sal)from emp as a where a.deptno=b.deptno)<2000 group byb.deptno;

4. 根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资

select ename as 姓名,deptno as 部门号,sal as 工资 from emp order by deptno desc,sal asc;

5. 列出'buddy'所在部门中每个员工的姓名与部门号

select b.ename as 姓名,b.deptno as 部门号 from emp as b where b.deptno=(select a.deptno from emp as a where a.ename='buddy'); 6. 列出每个员工的姓名，工作，部门号，部门名

select ename as 姓名,job as 工作,dept.deptno as 部门号,dept.dname as 部门名 from emp,deptwhere emp.deptno=dept.deptno;

7. 列出emp中工作为'CLERK'的员工的姓名，工作，部门号，部门名

select ename as 姓名,job as 工作,dept.deptno as 部门号,dept.dname as 部门名 from emp,deptwhere emp.deptno=dept.deptno and job='clerk'; 8. 对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为mgr）

select a.deptno as 部门号,a.ename as 员工,b.ename as 管理者 from emp as a,emp as b where a.mgr is not null and a.mgr=b.ename;

9. 对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与

工作

select a.deptno as 部门号,a.dname as 部门名,b.ename as 员工名,b.job as 工作 from dept as a,emp as b where a.deptno=b.deptno and b.job='clerk'; 10. 对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序

select b.deptno as 部门号,b.ename as 姓名,b.sal as 工资 from emp as bwhere b.sal>(select avg(a.sal) from emp as a where a.deptno=b.deptno) order by b.deptno;

11. 对于emp，列出各个部门中工资高于本部门平均工资的员工数和部门号，按部门号排

序

select a.deptno as 部门号,count(a.sal) as 员工数 from emp as awhere a.sal>(select avg(b.sal) from emp as b where a.deptno=b.deptno) group by a.deptno orderby a.deptno;

12. 对于emp中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，平均工

资，按部门号排序

select count(a.empno) as 员工数,a.deptno as 部门号,avg(sal) as 平均工资from emp as a where (select count(c.empno) from emp as c where c.deptno=a.deptno andc.sal>(select avg(sal) from emp as b where c.deptno=b.deptno))>1group by a.deptno order by a.deptno;

13. 对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少

于自己的人数

select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资,(select

count(b.ename) from emp as bwhere b.sal=5 4. 看脚本，回答下面问题

TableX有三个字段Code、 Name、 Age、其中Code为主键；

TableY有三个字段Code、 Class、Score, 其中Code + Class 为主键。两表记录如下：

Code Name Age Code Class Score 97001 张三 22 97001 数学 80 97002 赵四 21 97002 计算机 59 97003 张飞 20 97003 计算机 60 97004 李五 22 97004 数学 55

1. 请写出SQL，找出所有姓张的学生，并按年龄从小到大排列；

select * from TableX where name like '张%' order by age 2. 请写出SQL，取出计算机科考成绩不及格的学生；

select * from tableX where code in (select code from tableY WEHRE class='计算机' and score <60)

3. 通过等值联接，取出Name、Class、Score，请写出SQL即输出结果

select a.name,b.class,b.score from tableX a,tableY b where a.code=b.code 4. 通过外联接，取出每个学生的Name、Class、Score、请写SQL输出结果

select a.name,b.class,b.score from tableX full join tableY on a.code=b.code 5. 请写SQL，在TableX 表中增加一条学生记录(学号：97005 姓名：赵六年龄：20)；

insert into tablex values('97005','赵六',20)

6. 李五的年龄记录错了，应该是21，请写SQL，根据主键进行更新；

update tablex set age=21 where code='97004'

7. 请写SQL，删除TableX中没有考试成绩的学生记录，请使用not in条件；

delete tablex where code not in (select code from tabley) 5. 用一条SQL语句查询出每门课都大于80分的学生姓名