ITPub博客

首页 > 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

Name

 比如如何进行异常处理? 比如为什么用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 c.name = p.name      (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 c.name =p.name 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 Table_b.name using Table_a.name 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

          

  Subject

 Name

 gender

 Score

  English

 Jack

  M

 79

  Mathematics

Jack

 M

58

  Sports

 Grece

  F

 66

…………………………

   

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:

 

Male_num

Fmale_num

total num

20

19

38

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7899089/viewspace-750455/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2009-07-23

  • 博文量
    194
  • 访问量
    242153