sql plsql test

8/18/2019 SQL Plsql Test

1/7

Oracle SQL and PL/SQL TEST

(Time: 60 minutes)

I – Oracle Questions

1. What is the DUAL table? How can we determine the date of the system using the DUALtable?

The Dual table is special one row and one column table. The table has a singlearchar!"1# column

called Dummy.

$%L%&T sysdate '()* DUAL +

!. What is a ,)- and how many /oin types e0ist?

A /oin is a uery that combines rows from two or more tables2 iews2 or materiali3ed iews.There are 4 different types of )racle /oins5

6 )racle - %( ,)- " simple /oin#6 )racle L%'T )UT%( ,)- "L%'T ,)- #6 )racle (-7HT )UT%( ,)- "(-7HT ,)- #6 )racle 'ULL )UT%( ,)- " 'ULL ,)- #

8. %0plain difference between U -) and U -) ALL? When is it used? Write an e0ampleusing either one.

The union operator returns results from all the participating ueries after eliminating duplications

The union all operator returns results from all the participating ueries including the duplicate rows.

ote5 number of columns and column data type returned by the uery must match howe er the column

name can be different.

'or e.g $elect first9name2last9name from emp union select first9name2 to9char"age# from emp1+

1


2/7

4. What is an U:DAT%? Write an e0ample.

U:DAT% statement is used to update e0isting records in a table.

U:DAT% customers $%T last9name ; ‘ A D< ’ where customer9id; =>+

=. What are $ynonyms in )racle?

A synonym is an alternati e name for ob/ects such as tables2 iews2se uences2stored procedures and otherdatabase ob/ects.$ynonym is used to hide the owner of the ob/ect.

-t wor li e a mirror of the ob/ect. -t is depend on ob/ect. We can possible to create synonym of synonym. $ynonym are two types

a#pri ate

b#public

@. What does &)**-T stand for? -n which cases this has to be applied? What is ()LL A&B?

&)**-T5 # from sales+Using L function we can substitute a alue at the place of ull alues.The substituted alue thentemporarily replaces the ULL alues in your calculations or e0pression.

That way2we do comparison easily.ote5 The substituted alues only replaces the ULL alue temporarily for session and does not affect thealue stored in the table.

!


3/7

-- – :LF$GL

. What are the two parts of a pac age in :LF$GL? $pecify the importance of creatingpac ages in :LF$GL.

:LF$GL pac ages are schema ob/ects that groups logically related :LF$GL types2 ariables and subprograms.A pac age will ha e two mandatory parts5

a# :ac age specification b#:ac age body or definition-mportance of :ac ages1#:ac ages impro e the performance of functions and procedures!#:ac ages allow declare global ariables8#All the procedures and functions relating to a specific subIsystem are in one program unit. This is gooddesign practice and easy to manage.4#$ecurity 5Defining pri ate procedures in the pac age body which can only be used by the pac age.

=#Hidden -mplementation5 :ac ages let you share your interface information in the pac age specification andhide the implementation details in the pac age body.@#*odularity5 :ac ages encapsulate logically related ariables2 constants2subprograms2cursors and e0ceptionsin named :LFs l *odules.

J. What is the difference between a :()&%DU(% and a 'U &T-) ?

:rocedure51#-t may or may not return alue.!#-t has parameters - 2)ut and - )UT.8# :rocedure are mainly implemented for business logic.

4#-t cannot be used in e0pression directly.=#-t may or may not used (%TU( eyword.

'unction51#-t should return a alue.!# -t has only - parameter.8# 'unctions are for calculations.4#-t can be used in e0pression directly=#*ust used (eturn Beyword.

1>. What is a cursor? Why &ursor is re uired?

"


4/7

A cursor is a pointer to a pri ate $GL area that stores information about the processing of a $%L%&T or

data manipulation language "D*L# statement "- $%(T2 U:DAT%2 D%L%T%2 or *%(7%#.

*ust used (eturn Beyword.-t re uired for row wise alidation or in other way you can perform operation on each row.

11. When is $%L%&T - T) statement used? Write an e0ample.

The $%L%&T - T) statement retrie es data from one or more database tables2 and assigns the selectedalues to ariables

Declare

9sal number"1>#+beginselect salary into 9sal from emp where emp9id;1>+

1!. What is an %0ception? What are the types of %0ception?

%0ception is the error handling part of :LF$GL bloc .There are two types of %0ceptions.

K $ystemIdefined e0ceptions

K UserIdefined e0ceptions

18. %0plain what is meant by D< A*-& $GL in :LF$GL and why it is sometimes re uired touse?

--- I $GL

We ha e the ne0t tables5

#


5/7

%*:L)


6/7

1 . Allow all users on the system to uery data from Alice %s D%:A(T*% T$ table.

1J. &hange the last name of employee 8 to &De0ter %.

update %*::L).

!1. $how how many employees ha e a name that ends with an %n %.

$elect from %*:L)


7/7

sql plsql test

Documents