ORACLE数据库面试题

1

update t

set logdate=to_date('2003-01-01','yyyy-mm-dd')

where logdate=to_date('2001-02-11','yyyy-mm-dd');

2

select

from t

where name in (select name from t group by name having coung()>1)

order by name;--没说清楚，到底是升序还是降序

3

select ID，NAME，ADDRESS，PHONE，LOGDATE

from

(

select t,row_number() over(partition by name order by name) rn

from t

)

where rn = 1;

4

update t

set (address,phone)=

(select address,phone from e where ename=tname);

5

select

from t

where rownum <=5

minus

select

from t

where rownum <=2;

也没什么特别的地方，有些题目用oracle特有的函数去做会比较简单，像在第三题中用到的oracle的分析函数，以及在第一题中用到的oracle的to_char()函数。

这几个题目主要是看你能不能使用oracle的函数去处理

惠普招聘常考的笔试题目主要考数据库相关知识：

1Hashtable和HashMap有什么区别

2你怎么理解MVC模式

3SQLServer中左联接查询用left join，Oracle中用什么

4SQLServer中的数据库，在Oracle中对应的是什么

5如果SQLServer中有两个数据库，那么让你把这两个数据库对应到Oracle中，你应该怎么做

6有两个页面ajsp和bjsp，要从ajsp传值到bjsp有几种方法分别是什么

7有三个页面，ajsp，bjsp和cjsp，流程是：ajsp->bjsp->cjsp，其中ajsp中提交的数据要在cjsp中访问，用最简单的方法怎么做注意不能放在session里

7jsp和servlet有什么区别

8映射是什么你怎么理解映射

9Hibernate中：不看数据库，不看XML文件，不看查询语句，怎么样能知道表结构

10SQLServer支持集群吗

11为什么要用MVC我从JSP页面直接访问数据库不是更简单吗，为什么非要先提交到控制器再做处理

12在struts中，假设有一个对数据库中一张表的增删改查的 *** 作，你是写一个action还是写多个action为什么

13struts中的actionform有什么好处

14用过Hibernate吗，用它有什么好处

15通常所说的web应用程序分3层，即MVC，如果我想分4层，应该怎么分

什么数据库？

nal

是什么函数？

1，不等于基本不会用索引

但是不确定当0基数比较大

，而结果集基数比较小的情况下

有没有可能走索引

2，等于的话如果有索引

，而且结果集比较小的话应该会用

，结果集大道一定程度的话

不会用

3like的话不知道NAL是个什么东西

，

如果是NVL的话

like后面如果被替换成了%

，就不会用索引

如果name有值

有可能用索引

同样和结果集大小会有关系

4如果语法通的话

应该是可以

，

没这么写过，

同样考虑结果集

5

同样不理解NAL

6

看结果集

结果集小有可能用

以上答案假设数据库是oracle

，但是也不全面

仅供参考

--插入学生表测试数据

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

--课程表测试数据

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

--教师表测试数据

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

--成绩表测试数据

insert into Score values('01' , '01' , 80);

insert into Score values('01' , '02' , 90);

insert into Score values('01' , '03' , 99);

insert into Score values('02' , '01' , 70);

insert into Score values('02' , '02' , 60);

insert into Score values('02' , '03' , 80);

insert into Score values('03' , '01' , 80);

insert into Score values('03' , '02' , 80);

insert into Score values('03' , '03' , 80);

insert into Score values('04' , '01' , 50);

insert into Score values('04' , '02' , 30);

insert into Score values('04' , '03' , 20);

insert into Score values('05' , '01' , 76);

insert into Score values('05' , '02' , 87);

insert into Score values('06' , '01' , 31);

insert into Score values('06' , '03' , 34);

insert into Score values('07' , '02' , 89);

insert into Score values('07' , '03' , 98);

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select c,as_score as 01课程score,bs_score as 02课程score from

score a,score b

left join student c

on bs_id = cs_id

where as_id = bs_id and ac_id = '01' and bc_id = '02' and as_score > bs_score;

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select a ,bs_score as 01课程,cs_score as 02课程 from student a

join score b

on as_id=bs_id and bc_id = '01'

left join score c

on bs_id = cs_id and cc_id = '02'

where bs_score < cs_score ;

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select as_id,as_name,round(avg(bs_score),2) as 平均成绩 from student a

join score b

on as_id = bs_id

group by bs_id having 平均成绩 >= 60;

备注：round[avg(成绩),1]里,round是四舍五入函数，1代表保留1位小数

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

-- (包括有成绩的和无成绩的)

select b ,round(avg(as_score),2) as 平均成绩 from

student b

left join score a on bs_id = as_id group by as_id having 平均成绩 < 60

union

select b ,0 as 平衡成绩 from student b where bs_id not in (select s_id from score);

-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select as_id,as_name,count(bc_id) as 选课总数 ,sum(bs_score) as 总分 from student a

left join score b

on as_id = bs_id group by s_id ;

-- 6、查询"李"姓老师的数量

select count() as 李姓老师数量 from teacher where t_name like '李%';

-- 7、查询学过"张三"老师授课的同学的信息

select a from student a join score b

on as_id = bs_id

where bc_id in (select cc_id from course c

join teacher d on ct_id = dt_id where dt_name = '张三');

-- 8、查询没学过"张三"老师授课的同学的信息

select a from student a left join score b on as_id = bs_id where as_id not in

(select s_id from score where c_id =

(select c_id from course where t_id =

(select t_id from teacher where t_name = '张

三'))) group by as_id;

-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select from student where s_id in

(select as_id from score a join score b on as_id = bs_id

where ac_id = '01' and bc_id = '02');

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select from student where s_id in

(select s_id from score where c_id = '01' )

and s_id not in (select s_id from score where c_id = '02' );

-- 11、查询没有学全所有课程的同学的信息

select from student where s_id not in

(select s_id from score group by s_id having count(c_id) = 3);

-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select distinct a from student a left join score b

on as_id = bs_id where bc_id in

(select c_id from score where s_id = '01') and as_id != '01' ;

注意：distinct是去重的

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select from student where s_id in

(select s_id from score group by s_id having count(c_id) =

(select count(c_id) from score where s_id = '01') and s_id not in

(select s_id from score where c_id not in

(select c_id from score where s_id = '01')) and s_id != '01');

-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select s_name from student where s_id not in

(select s_id from score where c_id in

(select c_id from course where t_id in

(select t_id from teacher where t_name ='张三')));

-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select as_id ,bs_name,round(avg(as_score),2) as 平均成绩 from score a

left join student b on as_id = bs_id

where s_score < 60 group by s_id having count(1) >=2;

或者试试

select as_id ,bs_name,round(avg(as_score),2) as 平均成绩 from score a

left join student b on as_id = bs_id

where as_score < 60 group by as_id having count() >=2;

-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息

select a ,bc_id ,bs_score from student a

left join score b on as_id = bs_id

where bc_id = '01' and bs_score < 60

order by bs_score desc;

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select as_name ,

sum(case when bc_id = '01' then s_score else null end ) as 语文,

sum(case when bc_id = '02' then s_score else null end ) as 数学,

sum(case when bc_id = '03' then s_score else null end ) as 英语,

round(avg(s_score),2) as 平均成绩

from student a left join score b on as_id = bs_id group by as_name

order by 平均成绩 desc;

-- 18查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select bc_id,bc_name,

max(as_score) as 最高分,

min(as_score) as 最低分,

round(avg(as_score),2) as 平均分,

round(sum(case when as_score>= 60 then 1 else 0 end)/count(s_id),2) as 及格率 ,

round(sum(case when as_score>= 70 and as_score <80 then 1 else 0 end)/count(s_id),2) as 中等率,

round(sum(case when as_score>= 80 and as_score <90 then 1 else 0 end)/count(s_id),2) as 优良率,

round(sum(case when as_score>= 90 then 1 else 0 end)/count(s_id),2) as 优秀率

from score a

left join course b

on ac_id = bc_id group by bc_id;

-- 19、按各科成绩进行排序，并显示排名

第一种：

set @pre_c_id:= '01';

set @rank:=0;

select tb2s_id ,tb2c_id,tb2s_score,tb2排名 from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2;

如果看不懂用第二种方法：

SELECT ac_id,as_id,as_score,COUNT(bs_score)+1 AS 排名

FROM score a LEFT JOIN score b ON as_score<bs_score AND ac_id = bc_id

GROUP BY ac_id,as_id,as_score ORDER BY ac_id,排名,as_id ASC

-- 20、查询学生的总成绩并进行排名

set @rank:=0;

select ,(@rank:=@rank+1) as rank from

(select s_id ,sum(s_score) as 总成绩 from score

group by s_id order by 总成绩 desc) tb1;

-- 21、查询不同老师所教不同课程平均分从高到低显示

select ac_id, dt_name,round(avg(as_score)) as 平均分 from score a

left join student b on as_id = bs_id

left join course c on ac_id = cc_id

left join teacher d on ct_id = dt_id group by ac_id

order by 平均分 desc;

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

set @pre_c_id:= '01';

set @rank:=0;

select bs_name,tb2s_id ,tb2c_id,tb2s_score,tb2排名 from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2s_id = bs_id where 排名 = 2 or 排名 =3;

-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],(85-70],(70-60],(0-60]及所占百分比

select bc_id,bc_name ,

sum(case when as_score >=85 then 1 else 0 end) as 100-85 ,

concat(round(100 sum(case when as_score >=85 then 1 else 0 end)/count( ),2), '%') as 百分比,

sum(case when as_score <85 and as_score >=70 then 1 else 0 end) as 85-70 ,

concat(round(100 sum(case when as_score <85 and as_score >=70 then 1 else 0 end)/count( ),2),'%') as 百分比,

sum(case when as_score <70 and as_score >=60 then 1 else 0 end) as 70-60 ,

concat(round(100 sum(case when as_score <70 and as_score >=60 then 1 else 0 end)/count( ),2) ,'%')as 百分比,

sum(case when as_score <60 and as_score >=0 then 1 else 0 end) as 60-0 ,

concat(round(100 sum(case when as_score <60 and as_score >=0 then 1

else 0 end)/count( ),2),'%') as 百分比

from score a left join course b on ac_id = bc_id group by bc_id;

-- 24、查询学生平均成绩及其名次

select tb1,(@rank:=@rank +1 ) as rank from

(select s_id ,round(avg(s_score),2) as 平均成绩 from score

group by s_id order by 平均成绩 desc) tb1,(select @rank:=0) b;

-- 25、查询各科成绩前三名的记录

set @pre_c_id:= '01';

set @rank:=0;

select bs_name,tb2s_id ,tb2c_id,tb2s_score,tb2排名 from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2s_id = bs_id where 排名 <4;

-- 26、查询每门课程被选修的学生数

select c_id ,count(s_id) as 选修人数 from score group by c_id;

-- 27、查询出只有两门课程的全部学生的学号和姓名

select as_id ,bs_name from score a left join student b on as_id = bs_id group by s_id having count() = 2;

-- 28、查询男生、女生人数

select sum(case s_sex when '男' then 1 else 0 end) as 男生人数,

sum(case s_sex when '女' then 1 else 0 end) as 女生人数 from student;

-- 29、查询名字中含有"风"字的学生信息

select from student where s_name like '%风%';

-- 30、查询同名同性学生名单，并统计同名人数

--略，不想写

-- 31、查询1990年出生的学生名单

select from student where s_birth like '1990%';

-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select c_id ,round(avg(s_score),2) as 平均成绩 from score group by c_id order by 平均成绩 desc, c_id asc;

-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select as_id,bs_name ,round(avg(s_score),2) as 平均成绩 from score a

left join student b on as_id = bs_id group by as_id having 平均成绩>=85;

-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

select bs_name ,as_score from score a

left join student b on as_id = bs_id

where ac_id=(select c_id from course where c_name = '数学')and as_score < 60;

-- 35、查询所有学生的课程及分数情况；

select bs_name,

sum(case when ac_id = '01' then as_score else null end) as 语文,

sum(case when ac_id = '02' then as_score else null end) as 数学,

sum(case when ac_id = '03' then as_score else null end) as 英语

from score a right join student b on as_id = bs_id group by bs_name

-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

select bs_name,

sum(case when ac_id = '01' then as_score else null end) as 语文,

sum(case when ac_id = '02' then as_score else null end) as 数学,

sum(case when ac_id = '03' then as_score else null end) as 英语

from score a right join student b on as_id = bs_id group by bs_name having 语文>= 70 or 数学>= 70 or 英语>= 70 ;

-- 37、查询不及格的课程

select as_id,ac_id,bc_name,as_score from score a

left join course b on ac_id = bc_id where as_score<60;

--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

select as_id,bs_name from score a left join student b on as_id = bs_id where ac_id = '01' and as_score>=80;

-- 39、求每门课程的学生人数

select c_id,count() as 学生人数 from score group by c_id ;

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select a,bc_id,max(bs_score) as 最高成绩 from student a

right join score b on as_id = bs_id

group by bc_id

having bc_id = (select c_id from course

where t_id = (select t_id from teacher where t_name = '张三'));

-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

--（这题我搞不清题目是什么意思,是指查找学生个体参加了的所有课程的成绩各不相同的那个学生信息呢？还是所有课程之间做对比呢，我更倾向于理解为前者）

--理解为前者的写法

select from

(select from score group by s_id,s_score) tb1

group by s_id having count() = 1;

--理解为后者的写法

select distinct as_id,ac_id,bs_score from score a,score b where ac_id != bc_id and as_score = bs_score;

-- 42、查询每门课程成绩最好的前两名

set @pre_c_id:= '01';

set @rank:=0;

select tb2s_id ,tb2c_id,tb2s_score from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2

join student b on tb2s_id = bs_id where 排名 <3;

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人相同，按课程号升序排列

select c_id ,count() as 选修人数 from score group by c_id having 选修人数>5 order by 选修人数 desc , c_id asc;

-- 44、检索至少选修两门课程的学生学号

select s_id from score group by s_id having count() >= 2;

-- 45、查询选修了全部课程的学生信息

select from student where s_id in

(select s_id from score group by s_id having count() = 3)

--46、查询各学生的年龄

select s_name ,(date_format(now(),'%Y')-date_format(s_birth,'%Y') + (CASE when date_format(now(),'%m%d')>=date_format(s_birth,'%m%d') then 0 else 1 end)) as age

from student

-- 47、查询本周过生日的学生

---(实现得并不完全，因为例如出生月日为‘01-01’在每一年可能会输入不同周）

select from student where week(date_format(s_birth,'%m%d'))=week(date_format(now(),'%m%d')) ;

-- 48、查询下周过生日的学生

select from student

where week(date_format(s_birth,'%m%d'))=week(date_format(date_add(now(),interval 7-dayofweek(now())+1 day),'%m%d'));

-- 49、查询本月过生日的学生

select from student where date_format(s_birth,'%m') = date_format(now(),'%m')

-- 50、查询下月过生日的学生

select from student where date_format(s_birth,'%m') = date_format(date_add(now(),interval 1 month),'%m')

原文链接： >

1 数据表加1个字段，userid用来记录正在处理的人，进入php页面时，写入该字段。处理完成时，或未处理直接退出时，清空userid记录。其他人进入php页面时判断userid是否为空，不为空则禁止进入。

2 lock 和 unlock

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;

mysql> INSERT INTO real_table SELECT FROM insert_table;

mysql> TRUNCATE TABLE insert_table;

mysql> UNLOCK TABLES;

3 mysql_insert_id() 得到插入的id

4 select last_insert_id()

有逻辑备份和物理备份

1、逻辑备份包含：全部备份（full backup），增量备份（incremental backup）

增量备份又可分为：差分增量备份（differential incremental backup ），累积增量备份（cumulative incremental backup ）

2、物理备份：冷备份（offline backup），热备份（online backup）

以上就是关于ORACLE数据库面试题全部的内容，包括:ORACLE数据库面试题、惠普数据库类笔试题和面试题答案、一道关于数据库索引的面试题等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！