数据库工程师试题

Question

  今天面试了一份数据库工程师题目,本人以前做编程的.有些不会做,现在贴出来,欢迎大家上机调试给出答案
1.有一关系数据库，三个基本表    
S(Sno,Sname)   
C(Cno,Cname,Cteacher)   
SC(Sno,Cno,Scgrade)   
1.找出没有选修过"李明"老师课程的所有学生姓名
2.列出二门以上(含两门)不及格课程的学生姓名与平均成绩
3.列出既学过"1"号,又学过"2"号课程的所有学生姓名
4.列出"1"号课程比"2"号课程成绩搞的所有学生学号
5.列出"1"号课的成绩比"2"号课成绩高的所有学生的学号及其"1"号课和"2"课的成绩问答题目:
1.oracle中如果经常出现死锁,应该考虑增加哪个配置参数
2.unio和union all的区别
3.SGA的组成
4.oracle中所占内存应该占物理内存的百分之几比较好?
5.建多列索引时,应该考虑什么?
6.在sybase中,如果日志满了,应该如何操作?如果不考虑备份,怎样清除?
7.sql查询中,一个大表和一个小表的关联查询,如何优化?sql调优
delete from test_table_1 a 
where not exists(select * from test_table_2 b where a.parent_id=b.id)
其中,test_table_1表4百万,有索引字段,test_table_2表4万
现在查询60s,请写出优化的SQL语句

Answer 1

1,
select sno,sname
from s
minus
select s.sno,s.sname
from s,c,sc
where s.sno=sc.sno
and c.cno=sc.cno
and c.Cteacher='李明'
group by s.sno,s.sname

Answer 2

2
select a.no,a.name,a.avg_sc
from(
select s.no,s.name,avg(scgrade) avg_sc,sum(decdoe(sign(sc.scrgde-60),-1,1,0)) no_pass
from s,sc
where s.sno=sc.sno
group by s.no,s.name
) a 
where a.no_pass>=2 

Answer 3

3
select a.sno,a.snmae
from
(select s.sno,s.sname,sc.cno,row_number() over(partition by s.sno,s.sname order by sc.cno) rn
from s,sc
where s.sno=sc.sno) a
where a.rn=2

Answer 4

3 有点错误
select a.sno,a.snmae 
from 
(select s.sno,s.sname,sc.cno,row_number() over(partition by s.sno,s.sname order by sc.cno) rn 
from s,sc 
where s.sno=sc.sno
and sc.cno in ('1','2')
) a 
where a.rn=2

Answer 5

4,5其实是一道题啊
select a.sno,a.snmae,a.c_1,a.c_2
from
(
select s.sno,s.sname,sum(decode(sc.cno,'1',sc.Scgrade,0)) c_1,sum(decode(sc.cno,'2',sc.Scgrade,0)) c_2  
from s,sc  
where s.sno=sc.sno 
and sc.cno in ('1','2') 
)  a where
a.c_1>a.c_2

Answer 6

问答题只知道2和7
2 union 滤去重复的数据，union all不滤去重复的
7 from 大表,小表sql 优化也不知道对不对
delete from test_table_1 a  
where not exists(select '1' from test_table_2 b where a.parent_id=b.id) 

Answer 7

oracle中如果经常出现死锁,是开发人员设计的问题。谁说在ORACLE中配个参数就能不再死锁吗？

Answer 8

1.死锁后，在跟踪文件中有这样一句话：The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. 3.主要部分：share pool,database buffer cache ,log buffer,java pool,large pool4.据说是1/3左右，这个要根据系统来具体分析。能这么教条地问个标准答案吗？5.a.考虑几个列会经常在WHERE子句中组合做条件。b.考虑DML操作效率问题。

Answer 9

SGA
java pool
shared pool
large pool
块缓存区
UGA有时也在这里
日志缓冲区
还有一些记不清了

Answer 10

4.oracle中所占内存应该占物理内存的百分之几比较好? 
不知道是不是oracle默认的就是最好的，今天刚装了oracle11g，默认40％

Answer 11

7.sql查询中,一个大表和一个小表的关联查询,如何优化? 
Oracle此时会自动进行先扫描小表后扫描大表,关键就看Where子句各条件数据量的大小及其顺序了.

Answer 12

这样子优化的空间估计不大
如果a.parent_id，b.id是not null的话，可以改成not in, 或许会快点我写一个：delete
(
select 1
  from test_table_2 a
  left join test_table_1 b on a.parent_id = b.id
 where b.id is null
)

Answer 13

1.
select s.sno,s.sname
  from s,sc,c
 where s.sno=sc.sno and c.cno=sc.cno
   and c.cteacher <>'李明'
   
2.
select s.sno,s.sname,avg(sc.scgrade)scgrade,count(*)
    from s,sc,c
    where s.sno=sc.sno and c.cno=sc.cno
      and sc.scgrade<60
group by  s.sno,s.sname having count(*)>=23.
select t.sname
  from (select s.sno, s.sname
          from s, sc, c
         where s.sno = sc.sno
           and c.cno = sc.cno
           and c.cno = 1) t,
       sc
 where t.sno = sc.sno
   and sc.cno = 24.
select sno
  from (select s.sno,
               max(case when sc.cno = 1 then sc.scgrade end )grade1,
               max(case when sc.cno = 2 then sc.scgrade end )grade2
          from s, sc, c
         where s.sno = sc.sno
           and c.cno = sc.cno group by s.sno)
 where grade1 > grade25.
select sno,grade1,grade2
  from (select s.sno,
               max(case when sc.cno = 1 then sc.scgrade end )grade1,
               max(case when sc.cno = 2 then sc.scgrade end )grade2
          from s, sc, c
         where s.sno = sc.sno
           and c.cno = sc.cno group by s.sno)
 where grade1 > grade2

Answer 14

SGA主要的就三个吧：共享池、高速数据缓存、重做日志缓存（shared pool、data buffer cache、redo logbuffer）

Answer 15

1. 
select s.sno,s.sname 
  from s,sc,c 
 where s.sno=sc.sno and c.cno=sc.cno 
   and c.cteacher  <>'李明' 这个错误，这样只是出来的人员名单中，还有会选择李明的人员，这样的结果只是显示所以选修剔掉李明之后的所有人的名单
假设a选了一门课，不是李明的
B选了两门课，一门是李明
你这样出来的结果是a和B，而实际题目要求的只会出现a

Answer 16

1.select * from s where sno not in (select sno from sc where
 cno  in
(select cno from c where cteacher ='李明'))

Answer 17

2. select s.sname,avg(sc.scgrade) from s join sc on s.sno=sc.sno where s.sno in
(select sno from (select sno,count(*) 'count' 
from (select sno from sc where scgrade<60) a group by a.sno) b where b.count>1) 
group by s.sname

Answer 18

3. select sname from s where sno in
(
select sno from sc where cno='1' and sno in(
select sno from sc where cno='2')
)

Answer 19

dreamjj001 
似乎可以把and c.cno=sc.cno 去掉
改成:
select s.sno,s.sname,avg(sc.scgrade)scgrade,count(*) 
    from s,sc,c 
    where s.sno=sc.sno and sc.scgrade <60 
group by  s.sno,s.sname having count(*)>=2 楼主以为如何?

Answer 20

刚看到该题目，谢谢分享，我的方法如下，方法有误之处请高手指正：
表1：e_s;
表2：e_c;
表3：e_sc;    -- 1、找出没有选修过"李明"老师课程的所有学生姓名
select * from e_s
where sno not in(
select c.sno
from e_c b,e_sc c ,e_s a
where  b.cno = c.cno and b.cteacher  ='李明' and a.sno = c.sno
);    --2、列出二门以上(含两门)不及格课程的学生姓名与平均成绩 
         --(1)二门以上(含两门)不及格课程的学生姓名
select * from (
select count(sno) as gs, sname, sno
  from (select a.sno, a.sname, b.cno, b.cname, b.cteacher, c.scgrade
          from e_c b, e_sc c, e_s a
         where b.cno = c.cno
           and a.sno = c.sno
           and c.scgrade <= '60')
 group by sname, sno)
 where gs >='2';         --(2)(含两门)不及格课程的学生姓名与平均成绩 
select bb.sno,bb.sname,avg(scgrade)
from e_sc aa,
(
select * from (
select count(sno) as gs, sname, sno
  from (select a.sno, a.sname, b.cno, b.cname, b.cteacher, c.scgrade
          from e_c b, e_sc c, e_s a
         where b.cno = c.cno
           and a.sno = c.sno
           and c.scgrade <= '60')
 group by sname, sno)
 where gs >='2'
) bb
where aa.sno = bb.sno
group by bb.sno,bb.sname;

       --3、列出既学过"1"号,又学过"2"号课程的所有学生姓名 
select t.sno,t.sname from e_s t,
  (
select a.sno, a.cno,a.scgrade,b.cno,b.scgrade from
  (select sno,cno,scgrade from e_sc where cno=1)a,
(select sno,cno,scgrade from e_sc where cno=2)b
where a.sno = b.sno
)d
where t.sno = d.sno;

      --4、列出"1"号课程比"2"号课程成绩高的所有学生学号 
select sno from 
  (
select a.sno,
        a.cno,
a.scgrade as scgrade_1, 
--b.sno,
b.cno,
b.scgrade as scgrade_2,
(a.scgrade - b.scgrade)as fsjg
from
  (select sno,cno,scgrade from e_sc where cno=1)a,
(select sno,cno,scgrade from e_sc where cno=2)b
where a.sno = b.sno
)where fsjg > 0

--5、列出"1"号课的成绩比"2"号课成绩高的所有学生的学号及其"1"号课和"2"课的成绩 
select sno,scgrade_1,scgrade_2 from 
  (
select a.sno,
        a.cno,
a.scgrade as scgrade_1, 
--b.sno,
b.cno,
b.scgrade as scgrade_2,
(a.scgrade - b.scgrade)as fsjg
from
  (select sno,cno,scgrade from e_sc where cno=1)a,
(select sno,cno,scgrade from e_sc where cno=2)b
where a.sno = b.sno
)where fsjg > 0