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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7899089/viewspace-750455/