oracle sql 试题解答笔记

万达换票券

待验证　　

　　
1.创建一张学生信息表student_info，包含以下信息：学号、姓名、性别、生日、家庭住址、联系电话；创建一张学生成绩表student_score 包含学号、学科、成绩。
　　
CREATE TABLE student_info(
　　
ID INT,
　　
NAME VARCHAR2(10),
　　
sex VARCHAR2(4),
　　
brithday DATE,
　　
address VARCHAR2(50),
　　
phone INT);
　　

　　
CREATE TABLE student_score(
　　
ID INT,
　　
subject VARCHAR2(20),
　　
score NUMBER(4,1));
　　

　　
2、根据题目中的两张表，创建一张新表test，包含以下信息：学号，姓名，学科，成绩
　　
CREATE TABLE TEST AS SELECT a.id,a.name,b.subject,b.score FROM student_info a,student_score b;
　　

　　
3、将根据字符查询转换后结果，规则为：'A'转换为'男'，'B'转换为'女'，其他字符转换为‘未知’，试用一个SQL语句写出。
　　
INSERT INTO student_info VALUES (1100,'aaa','A',to_date('1990/3/3 12:12:12','yyyy/mm/dd hh24:mi:ss'),'asdg',333);
　　

　　
SELECT DECODE(sex,'A','男'，'B','女','其他') FROM student_info;
　　
4、提取题目1表中，学生张三的各科成绩，需要以下信息：学号、姓名、学科、成绩，且按成绩的降序排序。
　　
SELECT a.id,a.name,b.subject,b.score FROM student_info a,student_score b WHERE a.id=b.id AND a.name='张三' ORDER BY b.score DESC;
　　

　　
5、题目1表中，按学生总成绩由高到低排名，如果成绩相同则排名一样，输出字段为：排名、学号、姓名、总成绩。
　　
SELECT dense_rank() OVER(ORDER BY sum(b.score) DESC) dense_rank,a.id,a.name,sum(b.score) total FROM student_info a,student_score b WHERE a.id=b.id GROUP BY a.name,a.id
　　

　　
①ROW_NUMBER：
　　

　　
Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
　　

　　
②DENSE_RANK：
　　
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。
　　

　　
③RANK：
　　
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
　　
http://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_rank.html
　　

　　
6.题目1中，假设学科包含'语文'，'英语'，'数学'，'地理'，请按以下格式输出学生的成绩
　　
学号 姓名 语文 英语 数学 地理 总成绩
　　
xxx xxx 70 70 70 70 280
　　

　　
SELECT A.ID,
　　
       A.NAME,
　　
       SUM(DECODE(B.SUBJECT, '语文', SCORE, NULL)) AS 语文, --这里没有单引号
　　
       SUM(DECODE(B.SUBJECT, '数学', SCORE, NULL)) AS 数学,
　　
       SUM(DECODE(B.SUBJECT, '英语', SCORE, NULL)) AS 英语,
　　
       SUM(DECODE(B.SUBJECT, '地理', SCORE, NULL)) AS 地理,
　　
       SUM(B.SCORE) TOTAL
　　
  FROM STUDENT_INFO A, STUDENT_SCORE B
　　
WHERE A.ID = B.ID
　　
GROUP BY A.NAME, A.ID;
　　

　　
http://blog.163.com/magicc_love/blog/static/185853662201371481247696/
　　

　　
7、题目1表中，求出各个学科的平均成绩，要求降序排序
　　
SELECT b.subject,AVG(b.score) average FROM student_info a,student_score b WHERE a.id=b.id GROUP BY b.subject ORDER BY average DESC
　　

　　
8、下面两端代码有什么区别
　　
select
　　
CUST.*
　　
,POS.bus_desc
　　
from dw_ods.s00_sema_scmcustp CUST
　　
left join dw_pdm.t99_pos_code POS
　　
on CUST.cust_pos_code=POS.pos_code
　　
and CUST.dw_etl_date='2013-07-31'::date;
　　

　　
符合这两个条件的数据会被汇总到起来
　　

　　
select
　　
CUST.*
　　
,POS.bus_desc
　　
from dw_ods.s00_sema_scmcustp CUST
　　
left join dw_pdm.t99_pos_code POS
　　
on CUST.cust_pos_code=POS.pos_code
　　
where CUST.dw_etl_date='2013-07-31'::date;
　　

　　
符合上面条件的数据被汇总起来，从其中取出符合下面数据的
　　

　　
9、有一张表TEST，只有一个字段COL，一共有4条记录，分别是a，b，c，d，对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合。
　　
select a.col,b.col from TEST a ,TEST b where a.col= 2
　　
GROUP BY A.SNAME
　　
3. SELECT A.SNAME
　　
  FROM S A, C B, SC C
　　
WHERE A.SNO = C.SNO
　　
   AND B.CNO = C.CNO
　　
   AND (SELECT B.CNO
　　
          FROM S A, C B, SC C
　　
         WHERE A.SNO = C.SNO
　　
           AND B.CNO = C.CNO) = 1
　　
   AND (SELECT B.CNO
　　
          FROM S A, C B, SC C
　　
         WHERE A.SNO = C.SNO
　　
           AND B.CNO = C.CNO) = 2;
　　
4. SELECT A.SNO
　　
  FROM S A, C B, SC C
　　
WHERE A.SNO = C.SNO
　　
   AND B.CNO = C.CNO
　　
   AND (SELECT C.SCGRADE FROM SC) >
　　
       (SELECT C.SCGRADE
　　
          FROM S A, C B, SC C
　　
         WHERE A.SNO = C.SNO
　　
           AND B.CNO = C.CNO
　　
           AND A.SNO = 2
　　
           AND B.CNO = 1)
　　
5.  SELECT A.SNO, C.SCGRADE
　　
          FROM S A, C B, SC C
　　
         WHERE A.SNO = C.SNO
　　
           AND B.CNO = C.CNO
　　
           AND A.SNO =
　　
               (SELECT A.SNO
　　
                  FROM S A, C B, SC C
　　
                 WHERE A.SNO = C.SNO
　　
                   AND B.CNO = C.CNO
　　
                   AND (SELECT C.SCGRADE FROM SC WHERE CNO = 1) >
　　
                       (SELECT C.SCGRADE FROM SC WHERE CNO = 2))
　　
           AND C.CNO IN (1, 2);
　　

　　

　　
常问的面试题汇总：
　　
1、怎么在存储过程中使用临时表？
　　
2、口述一下存储过程的写法
　　
3、你了解oracle表分区吗？它有什么优缺点
　　
4、oracle表分区的分区类型有哪几种？它们的操作方法你知道吗？
　　
5、谈谈你对执行计划的理解，你主要看执行计划的哪部分？
　　
6、你对sql语句优化有何看法，能说出几种优化方法吗？
　　
7、oracle优化器内部处理的表连接方式知道吗？有哪几种？
　　
8、会使用开窗函数吗？说一下你对开窗函数、聚合函数、分析函数的理解
　　
9、游标的属性有哪几种？显式游标和隐式游标的使用方式分别是什么？
　　
10、plsql块怎么捕捉到异常？你能说出几个常见的预定义异常吗?
　　
11、说一下自定义异常如何在plsql块中实现？
　　
12、你了解视图吗？视图的优缺点是什么？
　　
13、假如让你开发一张报表，数据量比较小，你会怎样去实现它？让报表的数据展现出来？
　　
14、hash join听说过吗？（回答 了解过 后，继续追问 还有几种连接方式你知道吗？说一下看看） （这个其实跟7是差不多的，哈希连接这个东西我面试4次被问了3次，印象深刻）
　　
15、你在学校里有没有接触过数据库？了解数据库的体系结构吗？
　　
16、你知道SGA和PGA分别是什么吗？
　　
17、有没有试着写过触发器？触发器的写法能大致描述一下吗？
　　
18、函数和存储过程有什么区别？
　　
19、对函数了解吗？说出几个常用的函数
　　
20、（说出SUM、COUNT、AVG等函数后）知道instr和substr吗？他们的作用是什么？
　　
22、（面试官拿出个本子）写一下decode函数的基本结构（decode（valus，if1，then1，if2，then2……））
　　
23、OLAP和OLTP分别指什么？
　　
24、谈谈你对索引的理解
　　
25、delete和truncate有什么区别
　　
26、授权和撤销权限用的指令分别是？
　　
27、rowid和rownum有什么不同？
　　
28、分页存储过程里的分页sql语句部分是怎么写的？能不能直接先select * from table_name where rownum>=6 然后再rownum