PL/SQL development skill test

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/，如需轉載，請註明出處，否則將追究法律責任。