首页 > Linux操作系统 > Linux操作系统 > PL/SQL development skill test

PL/SQL development skill test

原创 Linux操作系统 作者:keeking 时间:2012-12-03 15:33:51 0 删除 编辑

PL/SQL development skill test


 比如如何进行异常处理? 比如为什么用FORALL ,比如如何用RECORD ,COLLECTION ,为什么? 比如自治transaction,如何使用等等



1.                  In Oracle there are 4 types of logically ‘join’ relationship between tables they are :  ___________________,_________________,  ___________________,_____________________


2.   When run a SQL the data scan method between two tables  may be:



3.   The index type in Oracle include: b*tree index, __________________,__________________.


4.   SQL in Oracle can be classified as ‘DDL’ and ‘DML’ please fill the type for below SQL:   


 create table __________________  Update table ____________


      Truncate table _________________ delete data from table  ____________


According below SQL answer the question 5-7


       Select c.class_name, p.age, max(c.score)    (1)

       From class c                                (2)

       left join person p  On =      (3)

       Where p.gender = man                    (4)

      Group by c.class_name, p.age                 (5)

     Order by -1                                   (6)




5.  what is the order when this SQL is executed  ________________________________  

The result of this SQL will ordered by column: _____________,

Ascending or Descending: _____________.


6.  If we move (4) to the end of (3 ) change the (3) to

On And p.gender = ‘man’  

And then delete the ‘where’ the result of this new SQL will ____________(Same/different) as the original one


7. To improve the performance of this SQL to add ________________index on column _____________ may works and after this change the execute plan of the table join relationship   may changed

from___________________________to _____________________________



8. Write a simple SQL to get the Monday date of the week ’2015-10-22’






9.  Write a simple SQL to move data from Table_1 to Table_2, in case when the column Table_1.A ‘s value equal to Table_2.A then don’t insert this row data to Table_2 but update Table_2.B using Table_1.B’s value for this row in other situation just copy the whole row from Table_1 to Table_2.














10. Write a Update SQL to update using these two table both have a column

Named ‘code’ and these two tables can be joined by this column ‘code’:



















According to below table to answer the question from 11-12


  Table name: t_student




















11. According to the table t_student which contains the info about students’ score for each subject please write a simple SQL to find the student whose score is the third of each subject














12.  According to the table t_student to write a SQL to calculate the student num by gender:

The layout of the SQL should like this:

  For example:




total num



















13 :Descript. the function of below three hint
A: /*+ORDERED*/  
 B: /*+APPEND*/  
C: /*+bypass_ujvc*/


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

上一篇: 数据库性能优化
下一篇: Oracle分区表
请登录后发表评论 登录


  • 博文量
  • 访问量