May 18
Oracle PL/SQL Interview Question and Answers INTERVIEW QUESTIONS(SQL QUESTIONS (SQL AN AN PL/SQL! PL /SQL! AN ANSWERS 1. Select emp name, emp no, sal from emp who is getting the salary greater than the supervisor salary. Select empno, ename, sal from emp where sal>all(select sal from emp where job=M!"!#$%&' . )an you change null value into not null value* )an you change not null value into null value* "o, +e can not change (moify column& null value into not null. -ut we can change not null value into null value. . /elete the uplicate row from the emp table* /elete from emp a where rowi not in(select min(rowi& from emp where eptno=a.eptno&' (OR! /elete from emp where rowi not in(select min(rowi& from emp group by eptno&' 0. Select entire employees names an their supervisor name* Select a.empno, a.ename, b.ename from emp a, emp b where a.mgr=b.empno' 2. Select job from emp table whose total salary are more than 23,333* Select job from emp where sal>23333' Ans" "o rows selecte 4. +hat the SI#N function will return* 5t will return sign of value without its magnitue. 6. +hat is the wrong in the following program* 7or i in 1...2 loop upate emp set comm=233 where sal=1333' en loop' Ans" S9360: un;nown comman beginning <7or i in 1...< 9 rest of line ignore. 8. Suppose 13 users are using one proceure how many copy of the proceure will be there at that time.
!ns: 13 . +rite a uery to isplay a proceure or function. Select te?t from user@source where name=S!MA$' Bere $SA%PLE$ is a proceure. 13. +hat is use of plCsl* ACSDA provies a proceural e?tension to SDA. 5t combines the ata manipulation power of SDA with the ata processing power of proceural language. 11. +hat is pac;age* ac;age is a container which contains relate objects. 1. +hat is e9normaliEation* Fhe intentional introuction of reunancy in the table is calle e9normaliEation. 1. +hat is join* $?tracting recors one or more table with proper where or join conition. Fhey are classifie into two types. Fhey are inner join an outer join. $ui9join,"on9eui9join,Self join 999> 5nner join an Aeft outer join, %ight Guter join999 àGuter Hoin.. 10. +hat is self join* Same table with ifferent ifferent alias. 12. +rite +rite the uery ientifying uplicate recors from the table* Select I from emp a where rowi not in(select min(rowi& from emp where eptno=a.eptno&' 14. Bow o you copy one table structure without its ata* )reate table emp@temp as select I from emp where 1=' (i.e. logic is false. 1=& 16. +hat is ifference between proceure an function* PRO&EURE 1. roc roce eur uree can can not not ret retur urn n a valu valuee usi using ng retu return rn stat statem emen ent. t. . )an not be calle from SDA statements. . )an be calle from -rowser. 0. 5f we want perform a tas; write proceure. 18. +hat are types of e?ceptions* 1. Frapp rappab able le . "on9 "on9Fr Frapp appab able le..
'UN&TION 1. Must Must retu return rn a val value ue.. . )an be calle from SDA comman . )an not be calle from -rowser. 0. 5f we want compute a value write
System error
re9efine error
Jser9efine error.
"on9pre9efine error.
1. +hat are types of error* 1. re9efine. . "on9pre9efine. . Jser9efine. 3. re9efine:9 Gnly error hanle by user. $rror $rror create, error raise by oracle. reefine means alreay efine. "on9reefine:9 $rror create an hanle by user. Gnly error raise by oracle. "on preefine means not alreay efine. Jser efine:9 $rror create, raise raise an hanle by user. Bere three events are one by user. Fhat is everything one by user. 1. +hat are pre9efiene e?ceptions* $?ception is error hanling part of plCsl. $?ceptions are classifie into three types.1. re9efine e?ceptions . "on9reefine e?ceptions. . Jser9efine e?ceptions. Prede)ined e*ce+tions" 1. )ursor@alreay@open . /up@val@on@ine? . 5nvali@cursor 0. 5nvali@number 2. Aogin enie 4. "ot@logge@on 6. Storage@error 8. rogram@error . Kalue@err Kalue@error or 13. Foo@many@rows Foo@many@rows 11. Lero@ivie 1. "o@ata@foun 1. Fimeout@on@resource 10. Fransaction@bac;e@out
12. !ccess@into@null 14. )ollection@is@null 16. %owtype@mismatch 18. Subscript@beyon@count 1. Subscript@outsie@limit . +hat is ifference between implicit cursor an e?plicit cursor* Fhe events are synta?,semantic,parsing,e?istence,create a cursor,open,fetch an close by one by system which is calle implicit cursor.(Fhese 8 actions are one implicitly through system itself& Fhe events create a cursor,open,fetch an close by a user which is calle e?plicit cursor. 5mplicit cursor performance is slow. -ut e?plicit cursor performance is fast. So we use e?plicit cursor.
. )an you create synonyms for objects eclare within pac;age or subprograms* "o, +e can not create synonyms for objects eclare within pac;age or subprograms. 0. +hat are the ifferent types of constraints an e?plain each. )onstraints are conitions. /omain integrity constraint9chec; constraint, not null constraint $ntity integrity constraint9primary ;ey constraint, uniue ;ey constraint %eferential integrity constraint97oreign ;ey constraint, on elete cascae
cade:
5t chec; the bounaries of value. &,ec- constraint : 5t oes not allow null value in a column of atabase table. Not null constraint : Uni.ue constraint : 5t oes not allow the uplicate value in a column or set of column of table. 5t oes not allow the uplicate values an null values in a Priar0 -e0 constraint : column or set of columns of table. 'orei1n -e0 constraint : 5t is a local representation of remote primary ;ey(%eferential 5ntegrity& +hen on9elete9cascae is specifie oracle maintains referential integrity by automatically removing epenent foreign ;ey values if a reference primary ;ey value or uniue ;ey value is remove. 2. +hat is output of program* /eclare 5 char(2&=
I $%%G% at line : G%!934223: line , column 1: AS93313: $ncountere the symbol <=< when e?pecting one of the following: := ' not null efault character Fhe symbol <:= was inserte before <=< to continue. G%!934223: line , column 10: AS93313: $ncountere the symbol <=< when e?pecting one of the following: := ' not null efault character
4. +hat is ifference between /elete, Fruncate an /rop* ELETE 1./elete one or more recors
. +here conition. .)an be rolle bac;. 0. 5t maintains log. 2. )reate trigger
TRUN&ATE /rop all recors leaving out of structure also. +e can not give where conition. 5t can not be rolle bac;. "o log 5t will not create trigger.
6. +hat is ata base lin; an its use* 1. /ata base lin; is a path between two atabases. . /atabase lin; is use to access remote atabase. 8. +hich one attribute is use to chec; whether the cursor is opene or not* !ns: isopen . Ae?ical analysis is use in which part* !ns: ACSDA part. 3. Bow o you initialiEe ate fiel in oracle atabase* NN/!F$NN, N/!F$N, N/)/!F$N Ans"2ATE2 1. +hat will be the output of the following program* )reate seuence Bi start with 3 5ncrement by 1' $%%G% at line 1: OUTPUT" G%!930334: SF!%F +5FB cannot be less than M5"K!AJ$ . +hich one is /MA statement* create,alter,select,rop Ans" Select
ROP $rase the table.
!ll pening transacti $rases from //A
. Bow many rows the following uery will select* A 3 03 23 3 null null null 3 3 Select col1, col from !,-* (a&0 (b&3 (c&one (&14 Ans"14 ($?ample:9 select a4sal,b4comm from emp a,emp b'& 0. /eclare %aise@paise e?ception' )har 5=13' )har H=2' -egin )har 5=13' )har H=2' 5f 5=H then %aise@aise' $n if' +hen raise@paise then Message(
AS93313: $ncountere the symbol <'< when e?pecting one of the following: precision Fhe symbol
33 group by empno, ename orer by avg(sal& OUTPUT" from emp where avg(sal&>33 I $%%G% at line 2: G%!9330: group function is not allowe here 6. Select /istinct (ty& from the following table* E+6nae Qt0 ? 233 y 233 ? 3 j 33 m 033
(a&0 (b&2 (c&one (&null !ns:0
8. +hich one is not correct in the following* (a&Seuence can not be use in view (b&Seunece can not be use in the S$A$)F statement which is having #%GJ -y clause. (c&Seuence can not be use SJ-DJ$%P where the subuery contain #%GJ -P clause. !ns: (b&
. Bow o you select ne?t value from a seuence table* (a& Select seuence@name4ne?tval (b& Select seuence@name4ne?tvalue Ans "(a& Select seuence@name4ne?tval 03. 5f you want to create foreign ;ey which one is necessary* (a&Fhe main table contain primary ;ey (b&Fhe main table contain uniue ;ey (c&Fhe main table contains primary ;ey an uniue ;ey (&"one of these. Ans"(a&Fhe main table contain primary ;ey
01. Bow many types of sl statements are there an what they are* 1. /ata /efinition Aanguage statements . /ata Manipulation Aanguage statements . Fransaction )ontrol statements 0. /ata )ontrol Aanguage statements 2. Session )ontrol statements 4. System )ontrol Statements. 74 ata e)inition Lan1ua1e stateents )reate,alter,rop,truncate,no auit an comment. 84 ata %ani+ulation Lan1ua1e stateents insert,upate,elete,select,loc; table an e?plain plan. 94 Transaction &ontrol lan1ua1e stateents commit,rollbac; an savepoint. :4 ata control lan1ua1e stateents4 #rant an %evo;e. ;4 Session control stateents !lter session an set role <4 S0ste &ontrol stateents !lter system 0. +hat is trigger* Frigger is a plsl bloc; that gets e?ecute automatically as well as ev ent occurs. Fhat is implicitly calle. Fotally 1 triggers an view triggers. 1. -efore insert row 11. !fter upate statement . -efore upate row 1. !fter elete statement . -efore elete row 0. !fter insert row 2. !fter upate row 4. !fter elete row 6. -efore insert statement 8. -efore upate statement . -efore elete statement 13. !fter insert statement 0. +hat is the use of store proceure* 1. Moularity . %eusability . Maintainability 0. $?tensibility 2. Gne time compilation 00. +hat are oracle ;ey wors* 5nsert, Jpate, /elete, Select, )reate, !lter, /rop.
02. +hat is the ifference between null an =null. "ull is vali. ="ull is not vali. 04. +hat is the use of reference variable* %eference variable is use to process ata to server. 06. Bow many triggers can be written in a single table* !ns: 1 triggers. 08. +hat is meant by le?ical analysis* ACSDA te?t can contain group of characters is ;nown as Ae?ical analysis. 5t has four units. Fhey are ientifiers, literals, comments an elimiters. 0. +hat is the ifference between primary ;ey an uniue ;ey* Priar0 -e0 constraint" 5t oes not allow null value. Uni.ue -e0 constraint" 5t allows null value. 23. Bow can u fin out, a table is cost base optimiEation or rule base optimiEation* !nalyEe table Qtable@name> compute statistics 21. +hat is S/A) process* S/A) process is Software /evelopment Aife )ycle. 5n oracle it is calle System /evelopment Aife )ycle.
2. )an u have null value in foreign ;ey constraint* Pes, +e can have null value in foreign ;ey constraint. 2. +rite a uery to isplay row number an all columns. Select rownum,emp.I from emp' 20. +hat are the ifferent types of parameters* In parameter, Out parameter an Inout parameter. 22. +hat is the ifference between K- an /;* 1. /; is Gracle )orporation prouct. . -ut K- is Microsoft rouct. 24. +hat is the use of "G)GP parameter in oracle proceure* "ocopy parameter is a compiler hint. "ocopy parameter pass parameter by reference. 26. Bow o you count total no of recors from two tables if no relation between both tables* Select a.bRc. from (select count(I&b from emp&a, (select count(I& from ept&c 28. +hat is AG-* AG- is a ata type.
2. +hat is synta? for proceure an functions* Procedure" create or replace proceureQproceure9name> parameter listT is (local eclaration& begin (e?ecutable statements& e?ceptionT (e?ception hanlers& en'
'unction" create or replace functionQfunction name> argumentT return atatype is (local eclaration& begin (e?ecutable statements& e?ceptionT (e?ception hanlers& en'
43. +hat are parameters* $?plain each. In +araeter " asses a constant value from the calling environment into the proceure. Out +araeter " asses a value from the proceure to the calling environment. In Out +araeter " asses a value from the calling environment into the proceure an a possibly ifferent value from the proceure bac; to the calling environment using the same parameter. 41. Bow the view will be store in the atabase* Fhe view will be store in the atabase as uery. 4. 5f any possibilities to return more than one value using sl function* Pes, 7unction will return more than one value. 4. +hat are the schema objects* Schema objects are tables, views, seuences, synonyms, ine?es, clusters, proceures, functions, atabase triggers an atabase lin;s. 40. +hat is the ifferent between oracle 8.3 an oracle 8i version* (a& )ube operator is available in oracle 8i. -ut cube operator is not available in oracle 8.3. (b& %ollup operator is available in oracle 8i.
-ut rollup operator is not available in oracle 8.3. (c& +e can rop a column in oracle 8i. +e can not rop a column in oracle 8.3. (& +e can use internet in oracle 8i. +e can not use internet in oracle 8.3 42. +hat is synonym* !n its type. Synonym is an alias. 5t has two types.1.rivate synonym .ublic synonyms. 44. +hat are clusters* )lusters are groups of one or more tables. 46. +hat are the set operators* Set operators combine the results of two ueries into one result. Fhey are union, union all, intersect an minus. 48. +hat is ifference between union an union all* Jnion returns all istinct rows selecte by either ueries. Jnion all returns all rows selecte by either uery incluing all uplicates. 4. 5s there any restrictions to write sub uery* Pes. 63. +hat is output of following uery* Select(sysateRnull& from ual !ns: "ull. 61. Select null from ual where null=null* !ns: "o rows selecte 6. +hat are the attributes of rowi* 18 characters long, uniue, alphanumeric. 6. +here you can eclare e?ception* 5s eclaration section or e?ception section* /eclaration section. 60. Bow o you trap error in oracle* Jsing error hanler. 62. Bow many types of cursor use in plCsl* +hat are they* (7! I+licit cursor SQL &URSOR (8! E*+licit cursor (a! Static &ursor (=! 0naic cursor (c! Re) cursor
64. +hat are types of %$7 cursor* (1& Strong cursor. (& +ea; cursor. 66. +hat are e?plicit cursors attributes* foun, not foun, row count, isopen 68. +hat is force view* 7orce view is a view which is create without base table. 6. )an you create pac;age boy without pac;age specification* "o, +e can not create pac;age boy without pac;age specification. 83. +hat are the features of oracle i* 1. Nulli)" 5t will return null if e?pression1 an e?pression are eual. 5t will return e?pression1 if e?pression1 an e?pression
are not eual. . &ase" 5t will return selecte result. . NVL8" 5t will return null if e?pression1 is null. 5t will return e?pression1 if e?pression1 is not null. 0. &oalesce" 5t will return first not null e?pression in the list. 2. %ultilevel collection" 5t is nothing but collection of collection. 81. +hat is materialiEe view* +hy are you using materialiEe view* MaterialiEe view a atabase object which stores results of local atabase table. Advanta1es" (a& SummariEing ata. (b& re9computing ata (c& %eplicating ata. (& /istributing ata (e& 7aster access for comple? joins (f& Fransparent to en9users isadvanta1es" (a& erformance costs of maintaining the views. (b& Storage costs of maintaining the views
8. )an you eclare efault value in 5", GJF an 5"GJF parameter* Pes, +e can eclare efault value for 5" an 5"GJF parameters. $?ample: proceure@name(p1 in varchar(3& efault UFesting, inout varchar(3& efault UFesting& 8. +hat are avantages of trigger* (a& %eplication(/isaster %ecovery& (b& #eneral purpose. (c& !uiting(Fo fin who creating a trigger& (& %eferential integrity.
80. +hat is plCsl table* ACSDA table is li;e array in c. 82. +hat is plCsl recor* ACSDA recor is li;e structure in c. 84. )an u create two proceures of same name in a pac;age* Pes, +e can create two proceures of same name in a pac;age(overloaing& 86. +hy o you create local an global ine?* +e create local an global ine? only for partitione tables. 88. +hy are you creating the ine?* Fo fetch recor very fastly. 8. +hat are types of ine?* 5ne? can be classifie into two types. Fhey are Jniue ine? an "on9Jniue ine?. 74 Uni.ue Inde*4 (a& rimary ;ey ine? an Jniue ine?. 84 NonUni.ue Inde*4 (a& 7unctional ine?. (b& Aob ine?. (c& -it map ine?. (& -9Free ine?. (e& 5GF(5ne? organiEe Fable& (f& %everse ine?. (g& Gbject ine? (h& artition 5ne?. 3. )an you upate base table using view* Pes, +e can upate base table using view. 1. Bow many level of triggers are there in oracle* 1.%ow level triggers .Statement level triggers . +hat is correlate subuery* 5s a subuery which is e?ecute once for every row processe by outer uery. . +hat is the use of instea of upate triggers* Jpate comple? view. 0. +hat is oracle instance* Gracle instance is combination of bac;groun process an system global area is ;nown as oracle instance.
2. 5f you use null with an y arithmetical operation what will be output* 3, null, 1, none of the above. Ans" null 4. +hich one is true* Fhe AG- can be a user9efine ata type 5s a table shoul have only one AGAns" Fhe AG- can be a user9efine ata type. 6. +hich one of the following is true in !//@MG"FB function in oracle* )an we have negative number in secon parameter* )an we have integer in the first parameter* Ans" Pes, +e can have negative number in secon parameter. 8. )onsier the function, suppose the secon value is greater than the first value what will be output* 5t will return negative value 5t will return positive value 5t will return the correct number of month left. Ans" 5t will return negative value. . +hat is the ma?imum length of )B!% in oracle* 333, 02, 0333 Ans" 333 bytes. 133. +hat is the ma?imum length of K!%)B!% in oracle* 333, 02, 0333, "one of these. Ans" 0333 bytes. 131. +hat is the use of A$"#FB function in oracle* A$"#FB function is use to fin the length of a string. 13. )an you use KS5L$ function in function or proceure* Pes, +e can use KS5L$ function sl functions. 13. +hich one is use to rename a table in oracle* %ename, !lter comman, both, either. Ans" %ename 130. +hat are stans for G7!* Gptimal 7le?ible !rchitecture 132. +hat are the uses of last ay an ne?t ay function* Last6da0 function returns ate corresponing to the last ay of the month. Ne*t6da0 function returns ate of specifie ay accoring to the sysate. $?ample: SDA> select last@ay(sysate& from ual'
SDA> select ne?t@ay(sysate,monay& from ual' 134. Bow o you mas; irrelevant ata from users* Synonyms, view, seuence, partitions, none of these. Ans" Synonyms 136. )an you put R operator both sie of outer9join* Ans" "o, +e can not put R operator both sie of outer9join. 138. +hich one is correct for A!/* -lan;space,< <,IIII,all' Ans" all 222. )an you elete base table using view* Pes, +e can elete base table using view. 113. Select first ma?imum salary, secon ma?imum an thir ma? imum of emp table 'irst a*iu " select ma?(sal& from emp' Second a*iu" select ma?(sal& from emp where salQ(select ma?(sal& from emp&' T,ird a*iu " select ma?(sal& from emp where salQ(select ma?(sal& from emp where salQ(select ma?(sal& from emp&& 111. #ive )os rules. 1. Fhe information rule. . Fhe rule of guarantee access. . Fhe systematic treatment of "ull values. 0. Fhe atabase escription rule. 2. )omprehensive ata sub language. 4. Fhe view upating rule. 6. Fhe insert an upate rule. 8. Fhe physical inepenence rule. . Fhe logical ata inepenence rule. 13. Fhe integrity inepenece rule. 11. Fhe istribution rule. 1. Fhe non9subervision rule. 11. +rite a uery to isplay all recors of emp table an rowi. Select rowi,emp.I from emp' 11. +rite a uery to isplay rownum, rowi from emp table. Select rowi, rownum from emp' 110. +rite a uery to select 2 highest pai employees from the emp table the month between jan to march. Select sal from(select sal from emp where hireate between 319jan983 an 19mar983 orer by sal esc& where rownumQ=2' Note: Bere (select sal from emp where hireate between 319jan983 an 19mar983 orer by sal esc& is a inline view.
112. 5f you use null with any arithmetical operation what will be the output* (a&3,(b&null,(c&1,(&none of these. Ans: null. 114. +rite a uery to isplay ma?imum salary for every epartment of a table. Select I from emp where sal in(select ma?(sal& from emp group by eptno&' 116. +rite a uery to isplay minimum salary for every ept within a table. Select I from emp where sal in(select min(sal& from emp group by eptno&' 118. +rite a uery to isplay average salary for each epartment. Select eptno,avg(sal& from emp group by eptno' 11. +rite a uery to isplay the employee who earns a salary that is higher than the salary of cler;s. Sort the results by salary from highest to lowest. Select ename, eptno,job from emp where sal>all (select sal from emp where job =)A$%V& Select ename, eptno,job from emp where sal>all (select sal from emp where job =)A$%V& orer by sal esc' 11. +rite a uery to ispaly the alternate rows.(G recors& Select ename from(select ename,mo(rownum,&r from emp& where r=1' 13. +rite a uery to isplay the alternate rows.(even recors& Select ename from(select ename,mo(rownum,&r from emp& where r=3' 11. +rite a uery to select etails of those persons whose sal is repeate* Select ename,sal from emp where sal in(select sal from emp group by sal having count(sal&>1&' 1. +rite a uery to select the name of managers who manag e more than one employee. Select mgr from emp group by mgr having count(I&>1' 1. +hich /MA comman oes not support ACSDA* $?plain plan. 10. +rite a uery to count the rows in two tables an isplay them in a single row. Select a, b, aRb from(select count (I&a from emp&,(select count(I& b from ept&' 12. +rite a uery to select the "th highest salary from emp table. (Jsing )onnect by prior& Select level, ma?(sal& from emp where level=1 connect by prior sal > sal group by level' 14. +rite a uery to isplay rows between nth row an mth row. Select I from emp where rownumQWa minus select I from emp where rownumQWb'
16. !t a time can u upate more than one column values of a table* +hat uery* Pes, it is possible to upate more than one columns values. upate tests set place=;arai;ui,job=acct where name=anbu' upate tests set place=unjanai,job=cler;,age=2 where name=anna' 18. )an u use cursor in trigger* Pes. 1. +rite a uery to use where,group by,orer by an having clauses are using at a time. Select job,sum(sal& from emp where job not li;e S!A$SM!" group by job having sum(sal&>2333 orer by sum(sal&'
13. +rite a uery use in clause. Select ename,sal,eptno from emp where sal in(select min(sal& from emp group by eptno&' 11. +rite a uery use any clause. Select empno,ename,job from emp where salQany(select sal from emp where job=)A$%V& an jobQ>)A$%V' 1. +rite a uery use all clause. Select empno,ename,job from emp where sal>all(select avg(sal& from emp group by eptno&' 1. +rite a uery to isplay the recors of emp table where co mm is null. Select I from emp where comm is null. 10. +rite a uery to rop a column of a table. !lter table Qtable name> set unuse column Qcolumn name> (OR! alter table Qtable9 name> rop column Qcolumn@name> 12. +rite a uery to isable the constraint. !lter table emp isable constraint emp@empno@p; cascae' 14. +rite a uery to isplay top9n salary. Select rownum as ran;,ename,sal from (select ename,sal from emp orer by sal esc& where rownumQ=' 16. /efine Fransaction* ! Fransaction is a logical unit wor; that comprises one or more SDA statements e?ecute by a single user. 18. +hat are the ifferent types of views are there* 1. 7orce view 9 Fo create a view without base table. . 5nline view 9 Fo fin top9n salary
. MaterialiEe view 9 Fo store result of local atabase table. 0. )omple? view 9 Fo create a view from multiple tables. 2. %ea only view 9 Fo view rea only purpose. 1. /elete an truncate. Bere which comman to ta;e less time to e?ecute* Fruncate comman ta;e less time to e?ecute. 103. Bow o you isplay 2th line of a proceure* Select te?t from user@source where name=S!MA$ an line=2' 101. +hat is GAF* Fhe Gnline Fransaction rocessing (GAF& 10. +hat files are use in the Gracle instance* 1. /ata files . %eo log files . )ontrol files. 0. arameter file. 10. +hat is ifference between instring an substring* INSTR(strin1>c,ar! 5"SF%5"# returns location of a string accoring to specifie character. E*a+le" Select ename, instr(ename,$& from emp' ENA%E INSTR(ENA%E>$E$! SM5FB 3 !AA$" 0 +!%/ 3 HG"$S 0
SU3STR(Strin1 n>! SJ-SF% returns a substring accoring to specifie position. E*a+le" Select ename,substr(ename,,0& from emp' ENA%E SU3S SM5FB M5FB !AA$" AA$" +!%/ !%/ HG"$S G"$S
100. +hat is SDAI"et* +hat is use of SDAI"$F* SDAI"et is Gracles communication protocol. SDAI"et uses various networ; communication protocols such as F) an 5 102. +hat is ifference between char an varchar* )B!% pas blan; spaces to the ma?imum length. K!%)B!% oes not pa blan; spaces. 104. +hat is SDAIAoaer* +hat are contents of slIloaer*
SDAIAoaer is Gracle utility that is use for loaing ata into an Gracle atabase. SQL?Loader acce+ts two in+ut )ile t0+es" Fhe input ata file an control file. 106. !n emp table contains 133 recors. 7irst 03 recors are colu mns one male an remaining 43 recors are columns are female. Jsing single statement can you change male to female an female to male* (1& Jpate empmale a set se? = (select case se? when female then male else female en from empmale b where a.empno = b.empno& (& Jpate empmale a set se? = (select ecoe(se?,male,female,female,male& from empmale b where a.empno = b.empno& 108. +hat operators are using in sub ueries operators* (a& !ny (b& !ll (c& 5n (& Some (e& $?ists 10. +rite about $?plicit cursor attributes. @'ound " Fo chec; whether cursor has fetche any row. Frue if rows are fetche. Fo chec; whether cursor has fetche any row. Frue if no rows are @Not 'ound " fetche. @Rowcount " Fo count the number of fetche or upate or elete rows. Fo chec; whether cursor is opene or not. @Iso+en " 123. +hat are logical files* %eo log files, )ontrol file an /ata file. 121. Fell about "KA function. "KA function return e?pression if e?pression1 is not null, return e?pression if e?pression1 is null. Synta?:9 "KA(string1, value if not null, value if null& $?: Select nvl(supplier@city, Ucomplete, UnCa&' 12. +hat is null* "ull is inapplicable. "either space nor Eero where column is not null. "ull is an inaccessible. 12. +hat is /ual table* /ual is a ummy table is create by oracle server which has one row an one column. 120. )an you create view without base table*
Pes, +e can create view without base table. 122. +hat are the ifferent types of GJF$% HG5"S* A$7F GJF$% HG5", %5#BF GJF$% HG5" 124. +hy are you using constraints* )onstraints prevent invali ata entry into table. 126. )an you call cursor in store function an store proceure* Pes, we can call cursor in store function an store proceure. 128. Bow many normaliEation is there* +hat are they* Fotally normaliEations only. 1. !voi %eunancy . 7unctional /epenency. . Fransient /epenency. 12. +hat uery use to isplay a particualr ine?* Select I from user@ine?es where ine?@name=!V 143. )an you write to isplay recors between two ates without any error* Pes, we can isplay recors between two ates without any error. Select I from emp where hireate between 169/$)983 an 9H!"98' 141. +hat is ecoe* /ecoe means internally if selection logic. 14. +hat is isolation* /elete the parent recor, if chil recor is present. 14. +hat is nvl function* Fo assign a value to null fiel. "vl(e?pression1,e?pression& (1& 5f e?pression1 is null, nvl function returns e?pression. (& 5f e?pression1 is not null, nvl function returns e?pression1. (& $?pression1 an $?pression are ifferent ata types oracle converts e?pression the ata type of e?pression1. 140. +hat is system table space* +hen it will be create* $very oracle atabase contains tablespace which name System tablespace. 5t is create automatically when the atabase is create. 142. /ifference between public proceure an private proceure. ublic proceures can eclare in pac;age specification an also we can use anywhere in pac;age. -ut private proceure can be eclare in pac;age boy an we can use this pac;age part only. So this is calle private proceure.
144. +hat is schema* Schema is a collection of atabase object of a user. 146. )an you create trigger for view* Pes, +e can create triggers for view. 148. Bow can you see all triggers* Select I from user@triggers 14. 5n which moes are pass by reference an pass by value in the following parameter moes. 5" parameter X ass by reference GJF parameter X ass by value. 14. +hat is uery for the following output* 49!%9334 1:3:3 Ans" select localtimestamp from ual' 163. +hat is the output for the following uery* Select nullR83I133R33I03 from ual' Ans" Null 161. 5f you rop the view, is it possible rop the corresponing base table at the same time* "o, -ase table is not roppe only view is roppe. 16. +rite about $?ists operator. Fhe $?ists operator is freuently use with correlate subueries. 5t tests whether a value is there. 5f the value e?ists it returns Frue' if it oes not e?ists it returns 7alse. Select empno,ename,job,eptno from emp e where e?ists(select empno from emp where emp.mgr=e.empno& orer by empno 16. Select empno,ename,sal from emp where sal between 333 an 333 and select empno,ename,sal from emp where sal>=333 an salQ=333 1ive sae results4 160. )an you create ine? on primary ;ey column of a table* "o, +e can not create ine? on primary ;ey column in a table.
162. Select ma?(sal&,min(sal&,avg(sal&,sum(sal& from emp' M!Y(S!A& M5"(S!A& !K#(S!A& SJM(S!A& 9999999999 9999999999 9999999999 9999999999 2333 833 138.826 22 164. Select roun(ma?(sal&&C,roun(min(sal&&C, roun(avg(sal&&C,roun(sum(sal&&C from emp'
%GJ"/(M!Y(S!A&&C %GJ"/(M5"(S!A&&C %GJ"/(!K#(S!A&&C %GJ"/(SJM(S!A&&C 999999999999999999999999999 999999999999999999999999999 9999999999999999999999999999 99999999999999999999999999999 233 033 1320.2 1064.2 166. +hat are levels of System /evelopment Aife )ycle process* 1. Strategy an !nalysis . /esign . -uil an /ocument 0. Fransition 2. rouction 168.
Ta=le o) )irst )1 ) 9999999999 9999999999 1 0
Ta=le o) second /1 / 9999999999 9999999999 2 4 6 8 +hat is result of this uery* Select e.I, .I from first e, secon '
Select e4?>d4? )ro )irst e> second d )1 ) /1 / 1 2 4 0 2 4 1 6 8 0 6 8
16. )an you create synonym for proceure, function an pac;age* Pes, +e can create synonym for proceure, function, pac;age an triggers. 183. )an you upate view it affects base table* Pes, 5t affects base table. 181. +hat is ifference between public synonym an private s ynonym* ublic synonym can be accesse by any user on the atabase. rivate synonym can be accesse by only by the owner. 18. +hen Uinvali@number e?ception raise*
Fhis e?ception is raise if the conversion of a character string to a number fails because the string oes not represent a vali number. 7or e?ample, inserting Ujohn for a column of type type number will raise this e?ception. 18. Bow many AG"# columns are allowe in a table* 5s it possible to use AG"# columns in +B$%$ clause or G%/$% -P* Gnly one AG"# column is allowe. 5t is not possible to use AG"# column in +B$%$ or G%/$% -P clause. 180. +hen we create function or proceure which coe is store in the atabase* (a&p9coe (b&source coe (c&p9coe an source coe (&"one Ans" Source coe 182. +hat are ACSDA )ursor $?ceptions* )ursor@alreay@open, 5nvali@cursor 184. +hat is ifference between view an synonym* Kiew Kiew is a atabase object that logically represents one or more atabase tables. S ynonym is a atabase object, which physically represents a table with ifferent name. 186. +hat is function* ! function is a plCsl bloc; that computes a value. 188. +hat is proceure* ! proceure is a plCsl bloc; that performs a specific action.
18. +hat is ACSDA* ACSDA is a structure language bloc; an can b e ivie into three parts are a eclarative part, an e?ecutable part an an e?ception part. 13. +hat is view* ! view is a virtual table. 11. )an you upate comple? view* Pes, +e can upate comple? view 1. +rite +rite a uery using cube an roll up operator. Select eptno,job,sum(sal& from emp group by rollup(eptno,job&'9999999 rollup(eptno,job&'999999999à%oll up operator. Select eptno,job,sum(sal& from emp group by cube(eptno,job&' 999999999à)ube operator.
1. Gne table has 0 rows, 2 columns an another table has rows an 0 columns, what result result will come how many rows an how many columns*(use carteasian prouct& 5t will isplay 1 rows an columns. 10. Bow o you you isplay current current value of seuence* Select ss.currval from ual'( Bere ss is a seuence& 12. +hat is global temporary table* #lobal Femporary Femporary table is a Session base table. 14. Bow o you run a proceure in sl sl prompt* +e can run a proceure in sl prompt using EBE&UTE comman. 16. Bow o you increase increase the spee of e?ecution by a uery* +e increase the spee of e?ecution b y a uery using uery optimiEation. 18. +rite uery to fin e?perience of all employees in months. Select months@between(sysate,hireate& employees@emperience from sample@emp' $MAGP$$S@$M$%5$")$ 99999999999999999999999999999999999999 8.3202 8 60 43.864 8.26068 .64881 1. Fell Fell about preicates. ! preicate is a search conition. ! preicate uses operators, e?pressions, an constants to specify the search conition. Fhe main preicates are %$A!F5G"!A, %$A!F5G"!A, -$F+$$", "JAA, $Y5SFS, A5V$ an 5". reicates are using in a B!K5"# B!K5"# or +B$%$ clause. 33. +hat is ifference between sl an slIplus. (1& Stanar 9 (&Fo table with atabase 9 (&Stanar uery lanuguage 9 (0&+ (0&+ee can can abb abbre reva vate te SDA SDA stat statem emen ents ts.9 .9
Gracle preparating. Jse for formatting purpose. $nvironment. +e can can abb abbre reva vate te the the sta state tem ments ents..
31. +hat are Gracle utilities* SQL?Loader " Fhis is Gracle utility that is use for loaing ata into an Gracle atabase. SDAIAoaer is esigne to be fle?ible an to accept ata in a variety of formats. E*+ort " Fhis program stores Gracle ata an table efinitions e?ternally in an Gracle9specific binary format. I+ort " Fhis program loas ata into the atabase from an Gracle e?port file.
3. +hat is ealoc;* +hen two user attempts to perform the actions that interface with on e another the situation is efine as ealoc;. 3. +hich ate function returns number value* Months@between. Fhis ate function ta;es vali ates an returns number of months in between them. 30. +hen will you nee create an ine? for a table* 1. Fhe column contains a wie range of values. . Fhe column contains a large number of null values. . Fhe column is use freuently freuently in the where clause or in a conition. 0. Fwo Fwo or more columns are freuently use together clause or a join conition. 32. +hen we a any ata to the table, +hat happene in rowi an rownum* +hen we a any ata to the table, the rowi an rownum will be incremente. 34. +hat is ifference between subuery an correlate subuer y* Subuery is a uery which is e?ecute once for entire parent statement. )orrelate subuery is a subuery which is e?ecute once for every row processe by outer o uter uery.
36. 5f u create a foreign ;ey through a master table, primary column name of master table an foreign ;ey column name of etail table must be same name or not* "o, "ee to be same name, they may be ifferent name. 38. +rite +rite coe for using cursor with parameter* (armeteriEe cursor& To dis+la0 t,e details o) e+lo0ees =elon1in1 to de+artent no 7C 1ettin1 salar0D;CCC4 eclare cursor c1(p1 number& is select empno,ename,sal,eptno from semp where eptno=p1' currec c1rowtype' begin open c1(13&' loop fetch c1 into currec' if c1foun then if currec.sal>2333 then [email protected]@line(currec.empnoZZ ZZcurrec.enameZZ ZZcurrec.salZZ ZZcurrec.eptno&' en if' else e?it' en if'
en loop' close c1' en' 3. Fell about connect by prior. +e can use connect by prior,retrieves rows in hierarchical orer. E*" Select level, ma?(sal& from emp where level= connect by prior sal > sal group by level 13. +rite uery list the names of employees, who are more than 2 years ol in the organiEation. Select ename from emp where(sysate X hireate&>(2I42&' 11. +rite all employees who have at least one person reporting to them. Select empno,ename,job,eptno from emp e where e?ists(select empno from emp where emp.mgr=e.empno& orer by empno' 1. +rite a uery to create a table from another table without u plicate rows. )reate table bb as select istinct I from b' 1. +rite a uery to count ifferent ata values in a column. Select eptno,sum(ecoe(job,)A$%V,1,3&& )A$%VS,sum(ecoe(job,M!"!#$%,1,3&& M!"!#$%S, count(ecoe(job,)A$%V,1,M!"!#$%,1&& FGF!A 7%GM $M #%GJ -P /$F"G' 10. )an you o %L operations for synonyms of table* Pes, +e can o /MA operations for synonyms of table. 12. )an you set primary ;ey for whole table* Pes. +e can set primary ;ey for whole table. 224. )an u use more than one return type in store function* Pes, +e can use more than one return type in store function. 16. Fell about segment. ! segment is a set of e?tents allocate for a certain logical structure. 18. +hat is output of this uery* Select count(I& from emp' Select count(abc& from emp' Select count(a1b1c1& from emp'
Out+ut" 7: Out+ut" 7: Out+ut" 7:
1. +hat is the ma?imum S5L$ allowe for each type* Fhe Ma?imum SiEe is up to .
3. +hat are uses of neste tables* "este tables are syntactically array in c. 1.
(a&select I from table (b&Select col1 from table (c&Select col1, col from table. (&Select col1, col, col from table. ere w,ic, .uer0 ta-e less tie to e*ecute or w,ic, one e*ecutes )ast5 Select col1 from table.
Ans"
. +hat is output of this uery* Select 1 from ual union select a from ual. GJFJF: $%%G% at line 1: G%!93330:
from(select
from(select
. $?plain Moularity, %eusability an Maintainablity. %odularit0" Subprograms allow us to brea; a program into manageable, well efine logical moules. Subprograms once e?ecute can be use in any number of Reusa=lit0" applications. %aintaina=lit0" Subprograms can simplify maintenance, because if a subprogram is affecte, only its efinition changes. 3. +hat is siEe of "umber ata type*
ANS" 8 igits
1. +hat is the fastest way of accessing a row in a table* Jsing %G+5/ . +rite a uery top9n salary for all columns. Select I from(select I from emp orer by sal esc& where rownumQ= . +hich one is the oracle statement* a&orere b&group by c&"/$Y &!ll@rows Ans" b 0. 5n SDA function for emp table of hireate column which three statements are correct. (a&select count(hireate& from emp (b&select avg(hireate& from emp (c&select ma?(hireate& from emp (&select sum(hireate& from emp (e&select min(hireate& from emp ANS" a, c, e 2. +hich of the following column is a pseuo column* (a&curval (b&%owi (c&ne?t val ANS" a, b, c. (&!@month 4. +hich one comman is /SQL?PLUS comman* (a& insert (b& upate (c& elete (& select (e& escribe (f& rename ANS" e.9 escribe. 6. +hat happene to a view, when the base table is altere* +e roppe one column from base table it will isplay the following output came. ANS" view FS&OTT4ONE6SSF ,as errors4 8. Select first.currval from sys.ual.+hat is output of this uery* (a&it will isplay current value of ine?. (b&it will isplay current value of seuence (c&it will isplay current value of view
(&it will isplay error.
ANS" =
. Select ename from emp where ename li;e U@! +hat is result of this uery* $"!M$ 9999999999 +!%/ M!%F5" H!M$S 03. +hat is ifference between rename an alias* %ename is a permanent name given to a table or column whereas !lias is a temporary name given to a table or column which oes not e?ist once the SDA statement is e?ecute. 01. Bow can you import from te?t file to atabase* +hich comman using* +e can import from te?t file to atabase through FG!/ tool using 5M comman. 0. +hy are you creating trigger for view* +e create trigger for view upate the comple? view. 0. )an a view contain any ata* "o, ! view oes not can contain ata. 00. Fell about public proceure an private proceure. Pac-a1e S+eci)ication . Pac-a1e 3od04 roceure ? roceure ? roceure y roceure y roceure E roceure E roceure s roceure ; Bere we eclare roceure ?, proceure y an proceure E.(ac;age specification part& +e can use anywhere in the moules of project. So this is calle Pu=lic +rocedure. +e eclare proceure s, an proceure ;(ac;age -oy&. +e can use this part only. +e can not use in the moules of project. So this is calle Private +rocedure4 Fhat is pac;age specification part is calle Pu=lic Procedure an pac;age boy is calle Private Procedure4 02. )an you upate the comple? views* Pes, +e can upate in comple? view. 04. Bow o you run a proceure in sl prompt* +e run a proceure in sl prompt using $?ecute comman or run comman. $?ample:9 $?ecute Qproceure name> (G%& %un or Start Qpath name> e?: c:[sample.sl' 06. 5n cost base optimiEation, what is meaning of cost* )ost means Ue?ecution time
08. +rite uery for secon ma?imum salary. +hich part is first e?ecute* Select ma?(sal& from emp where salQ(select ma?(sal& from emp&' ere ain .uer0 is e*ecuted )irst4 0.
proceure 1 int ? proceure (that is y=?& proceure (Bere can we call the variable ? from proceure 1 to proceure i.e&y=? an can we call variable ? to proceure & ANS" "o, +e can not call variable ? from proceure 1 to proceure an proceure . 23. 5f i want to see your ata* Bow can i see that* Jsername4Gbject. 21. +e eclare three proceures in the pac;age specification section. -ut we use only two proceures in the pac;age boy. +ill it show $rrors* Ans" "o, 5t will not show error. 2. Bow can you lin; two tables without foreign ;ey* Jsing join we can lin; two tables. 2. +hat is e?ternal file* +e use e?cept atabase file is calle e?ternal file. 20. +hat can be the ma?imum siEe of ACSDA bloc;* Select I from ba@object@siEe where name = proceure@name' 22. +e eclare two proceures in the pac;age specification. -ut we implemente three proceures in pac;age boy. 5t will show error or not* Ans" "o, 5t will not show error. 24. Bow o you isplay particular information from proceure* Jsing 3%S6OUTPUT4PUT6LINE 26. Fell about System #lobal !rea(S#A& I ! group of share memory structures. I Fhe information store within an S#! is ivie into several areas of memory. (1& Fhe atabase buffer cache. (& Fhe %eo Aog buffer. (& Fhe share pool (0& %euest an response ueues (if multithreae server is use& (2& Gther miscellaneous information. 28. 5f any synta? error in insert statement, values are inserte or not. "o, Fhe values are not inserte. 2. +hat are implicit cursors attributes.
1. Slfoun . Slnot foun . Slrowcount 0. Slisopen. 43. Gracle atabase structure. I ! atabase is a collection of ata files an programs that manipulate those ata files. I Fwo types of information are store in an oracle atabase: (1& Jser ata, relevant to a particular application (& System ata, which the atabase nees to manage itself. I )omponents. " Fhese are Gperating System files an contain all the (1& /atabase file atabase ata. (& )ontrol file " )ontain information that is reuire by the %/-MS to access an manipulate the ata. " Fhese files recor all the transaction on the atabase. (& %eo Aogs file (0& Fablespaces " ! atabase is ivie into logical units calle tablespaces. (2& Segments " ! segment is a set of e?tents allocate for a certain logical structure. (4& $?tents " !n e?tent is a specific number of contiguous ata bloc;s, obtaine in a single allocation, use to store a specific type of information. 41. +hen foun is true. @'ound " fetche.
Fo chec; whether cursor has fetche any row. Frue if rows are
4. +rite a uery to count all jobs an employees ifferent ata values in a column. select eptno,sum(ecoe(job,!"!APSF,1,3&&!"!APSFS, sum(ecoe(job,)A$%V,1,3&&)A$%VS, sum(ecoe(job,M!"!#$%,1,3&&M!"!#$%S, sum(ecoe(job,%$S5/$"F,1,3&&%$S5/$"F, sum(ecoe(job,S!A$SM!",1,3&&S!A$SM!"S, count(ecoe(job,!"!APSF,1,)A$%V,1,M!"!#$%,1,%$S5/$"F,1, S!A$SM!",1&& FGF!A from emp group by eptno 4. +hich case we use a 'ULL OUTER GOIN * +e want all unmatche ata from both tables.
40. +hat is 5nline view* ! subuery in from clause of a select statement is calle 5nline view. 42. /raw the $ntity %elationship ($9%& iagram.
NA%E
SH%3OL
$ntity
'UN&TION
/ata object in the system uniu ientifiable by ientifier has att that escribe it.
!ttribute %elationship
/escribes an entity. %elates two entities uniuely 5entifie -y the ientifier.
44. Bow can you a not null constraint into a table* Say the synta?. At )irst To add a colun wit, NOT NULL constraint> t,e ta=le ust =e e+t04 !lter table Qtable@name> moify Qcolumn@name ata t ype(siEe&> not null' E*a+le" !lter table test moify no number not null' 46. /ifference between inner join an outer join Inner oin" Hoining the table with itself. OuterGoin" Hoin has one between two tables. 48. 5f you roppe a table corresponing ine? is roppe or not* Pes, ine? also roppe. 4. 5f you roppe a table corresponing constraint is roppe or not* Pes, constraint also roppe. 63. Fell about snapshot. ! snapshot is a table that contains the result of uery of one or more tables or views locate on a remote atabase. 61. Fell about savepoint Savepoints are li;e mar;ers to ivie a very lengthy transaction into smaller ones. Fhey are use to ientify a point in a transaction to which we can later rollbac;. 5t enables rolling bac; part of a transaction. Ma?imum five save points are allowe. 6. Bow o you transfer the ata from a e?cel table to sl table. FG!/ atmosphere is a tool. +e can o all activities as such li;e that oracle. 6. Bow much ma?imum arguments can you pass in parameter list of proceure* 133 60. )an you o /MA operations in ACSDA proceure*
Pes, +e can o /MA operations in ACSDA in proceure. 62. +hat is the efault percentage of )FJS$/ an )F7%$$ in a table* 43 an 03. 64. Fell about eui9join,non9eui9join,outer9join. Hoining two tables by euating two common columns. E.uiGoin" Hoining two tables base on conition other than euating two NonE.uiGoin" common columns. Hoin two tables in such a way that uery can also retrieve rows that OuterGoin" o not have the corresponing join value in the other table. Sel)Goin" Same table with ifferent alias. 66. +rite synta? for creation of foreign ;ey constraint an alteration of foreign ;ey constraint. &reation o) )orei1n -e0 constraint" )reate table Qtable@name>(col1 ata type references Qtable@name>(column@name& E*a+le" )reate table test0(emp@no number(4& references test(emp@no& Alteration o) )orei1n -e0 constraint" !lter table Qtable@name> a constraint Qconstraint@name> foreign ;eyQcolumn@name> referencesQtable@name>Qcolumn@name>' !lter table b@etail a constraint jj foreign ;ey(no& references E*a+le" a@mast(no&' Alteration o) Onelete&ascade constraint" !lter table orer@master a constraint f;@coe foreign ;ey(vencoe& references venor@master(vencoe& Gn elete cascae' 68. Fhe following proceure will be e?ecute or not* 5f is it not e?ecute what error will throw* /eclare vname emp.enametype' -egin select ename into vname from emp' en' ANS" 5t will not e?ecute. 5t will throw e*act )etc, returns ore t,an re.uested nu=er o) rows
6. +hat are types of partition* (a& %ange partition. (b& Bash partition. (c& )omposite partition. 83. +rite uery for the following output:9 /$F"G M!A$ 7$M!A$ FGF!A
9999999 9999999999 9999999999 9999999999 13 1 3 1 1 3 1 1 select eptno,sum(ecoe(se?,male,1&&male,sum(ecoe(se?,female,1&&female, sum(ecoe(se?,male,1,female,1&&Fotal from b@mast group by eptno' 81. 5s it possible to allow five columns in plCsl table* Pes, 5t is possible to allow five columns in plCsl table. 8. +hat is partition* ! single logical table can be split into a number of physically separate pieces base on ranges of ;ey values. 8. +hy o you ma;e table partition* (a& Fo implement very large tables in ata warehouse environments. (b& Fo support parallel e?ecution of insert, elete an upate operations. (c& Fo manage atabase availability. (& Fo perform aministrative tas;s incrementally. 80. +hat are restrictions of )ursor Kariables* (a& %emote subprograms can not return the value of a cursor variable. (b& ACSDA tables can not store cursor variables. (c& )ursor variables can not be use with ynamic SDA in roI). (& Fhe uery associate with a cursor variable in the G$"\7G% statement. 82. +rite synta? for creation of partition. )reate table Qtable name> (column name ata type, column name ata type,..& partition by range(column name& (partition Qpartition name> values less than Qvalue>, partition Qpartition name> values less than Qvalue>&' E*a+le" )reate table venor@etail(orerno varchar(2&, oate ate, vencoe varchar(2&, o@status char(1&, el@ate ate& partition by range(orerno& (partition om1 values less than(o313&, partition om values less than(o33&&' 84. +hat is a mutating table* ! mutating table is a table that is currently being moifie by an upate, or a elete or an insert statement.
86. !fter inserte values into a table, we give rollbac; after then commit. Fhe values are inserte or not. Fhe values are not inserte. 88. Fo fin the total amount of each sales person which shoul be more than the ma?imum amount for the following table* SNA%E A%OUNT ! 0333 333 ! 333 ) 333 ) 333 333 Select sum(amount& from stable having sum(amount&>(select ma?(amount& from stable&group by sname' 8. +hat are avantages of ACSDA* (a& Support the eclaration an manipulation of object types an collections (b& !llows the calling of e?ternal functions an proceures. (c& )ontains new libraries of built9in pac;ages. (& Support for SDA. (e& Bigher prouctivity (f& -etter performance (g& ortability (h& 5ntegration with oracle 3. Gne table is create using create comman, after we will not give commit statement. Fable is store or not. Fable is store. -ecause //A commans are automatically committe. 1. +hat is ifference between view an table* (1& ! view oes not have own ata. -ut a table have own ata. (& ! view is a virtual table but table is real table. (& +e can not truncate the view but we can truncate the base table.
. #ive name for implicit cursor* !ns: Sl cursor is a implicit cursor. . Synonym occupies any table space* Pes, Synonym occupies table space. 0. ] ] ] ] ]
^ ^ ^ ^ ^ +e wrote a proceure li;e this. !t the compile time it will show error at thir loop. -ut thir loop is not a program, that is a comment line. "e?t time the control, where it will be go. Ans" 5t will go to previous loop. 2. Kiew occupies any table space* "o, Kiew oes not occupy any table space.
4. +hat is Duery optimiEation* Duery optimiEation is the process of choosing the most efficient way to e?ecute a SDA statement. 6. +hat are types of Duery optimiEation* 1. %ule base optimiEation. . )ost base optimiEation. 8. +hat are various joins use while writing subuery* (a& Self9join (b& $ui9join (c& Guter join . +hat are analytical functions* %an;, /ense@ran;, Fop@", )ume@/ist, ercent@%an; an %ow@"umber 33. +hat are uses of synonyms* 1. Simplify SDA statements . Bie name an owner of object. . rovie location transparency for remote objects. 0. rovie public access to an object. 31. )an you truncate view* "o, +e can not truncate view. 3. Select count(I&,count(comm&,count(null&. +hat is output of this uery* Bere commission is null. SDA> select I from test' &O%% 13 rows selecte. SDA> select count(I&,count(comm&,count(null& from test' )GJ"F(I& )GJ"F()GMM& )GJ"F("JAA& 9999999999 99999999999999999999 9999999999999999999 13 3 3
3. Bow can u fin out particular columns are ine?e or not* Select ine?@name, column@name from user@in@columns where table@name=/$F' 30. +hat is ine? cluster* ! cluster with an ine? on the cluster ;ey.
32. +hat is Fablespaces* ! atabase is ivie into logical units calle tablespaces. 34. +hat is )ollection* ! )ollection is li;e that array. 36. +here proceure an function store* /ata ictionary. 38. +hat are ifferences between varray an neste table* 1. Karry have a ma?imum siEe. -ut "este Fables o not have an e?plicit ma?imum siEe. . +hen store in the atabase, Karry retain the orering an subscript values for the elements. -ut "este Fables o not retain the orering an subscript values for the elements. 3. +hat is caniate ;ey* ! table is create without any ;ey is calle caniate ;ey. 13. +hat are ACSDA ata types* -oolean, binary@integer, number, character, raw, long raw, rowi an AG1. +hat are SDA ata types* )har, varchar, number, ate, raw, long, long raw an AG1. +hat are ACSDA attributes* type an rowtype. 10. +hat are types of triggers* 1. /MA triggers . System triggers . 5nstea of triggers 12. +hat is pragma* ragma is a compiler irective. 14. +rite a proceure coe. create or replace proceure item(orers varchar& is tyhan number' relevel number'
ma?level number' begin
select ty@han,re@level,ma?@level into tyhan,relevel,ma?level from itemfile where itemcoe=orers' if tyhan Q relevel then upate itemfile set ty@han=relevelRtyhan where itemcoe=orers' else [email protected]@line(itemlevel o;&' en if' e?ception when no@ata@foun then [email protected]@line(no ata returne&'
en' 16. +rite a function coe. create or replace function itmes(it varchar& return number is args number' tyhan number' relevel number' ma?level number' begin select ty@han,re@level, ma?@level into tyhan,relevel,ma?level from itemfile where itemcoe=it' if(tyhanRrelevel&>ma?level then args:=ma?level' return args' else args:=(tyhanRrelevel&' return args' en if' en' 18. !fter elete values into a table, we give truncate. +hat is output* Fhe values are available on the table or not. !ns: Fhe values are not available. 1. +hich of the below ata ictionary views will tell you whether a given proceure vali or not. (a&user@proceure -&user@object (c&user@object@status (&user6source4
3. +rite a pac;age coe. )reate or replace pac;age boy pac;@me as proceure orer@proc(orno varchar& is stat char(1&'
-egin select ostatus into stat from orer@master where orerno=orno' if stat=p then [email protected]@line(Upening orer&' else [email protected]@line(Ucomplete orer&' en if' $n orer@proc' function orer@fun(ornos varchar& return varchar is icoe varchar(2&' ocoe varchar(2&' tyor number' tyel number' begin select ty@or,ty@el,itemcoe,orerno into tyor,tyel,icoe,ocoe from orer@etail where orerno=ornos' if tyorQtyel then return ocoe' else return icoe' en if' en orer@fun' en pac;@me 1. Bow o u alter user* !lter user hr ientifie by hr account unloc;' . +hat are the 5/$s use in your project* 5ntegrate /evelopment $nvironment.
74 Foa Fool 84 lCsl eveloper.
. +rite a trigger coe(%ow level trigger& create or replace trigger orers before insert on orer@etail for each row eclare orno orer@etail.orernotype' begin select orerno into orno from orer@etail where ty@orQty@el' if orno=o331 then raise@application@error(93331, Uenter some other number&' en if' en' 222. +hat is ifference between simple view an comple? view* Simple view )omple? view
1. Gne table
1. More than one table.
22. )an you insert base table using view* Pes, +e can insert base table using view. 4. +hat are parts of a trigger* 1. Frigger $vent . Frigger )onstraint
. Frigger !ction
6. )an you o /MA operation in rea only view* "o, +e can not /MA operation in rea only view. 8. +hat is use of %evo;e comman* %evo;e comman can be use to return privileges. . )an u use pac;age instea of proceure* Pes, +e can use pac;age instea of proceure. 3. +hat is ifference between uniue ine? an composite ine?* (a& -y using uniue ine? we can create ine? on single column of a table but using composite ine? we can create ine? for multiple columns. (b& Jniue ine? occupy more memory but )omposite ine? occupy less memory. 1. +hat are uses of local an global ine?es* Aocal an #lobal ine?es are use in partitione tables. . +hat is oops concept* GGS concept is Gbject Griente rogramming System. 5t is a new way of organiEing an eveloping programs. 5t consists of encapsulation, abstraction, polymorphism (overloaing& an inheritance. . ! view is create from multiple tables. Bow o u ;now tables of this view* Select I from user@views' (through this uery& 0. +hat is Gverloaing (olymorphism&* olymorphism is a process of ma;ing an object to a variety of jobs.6 2. +hat are built9in pac;age names* 1. /-MS@!A$%F . /-MS@!A5)!F5G"@5"7G . /-MS@!D W /-MS@!D!/M 0. /-MS@//A 2. /-MS@/$S)%5-$ 4. /-MS@AG6. /-MS@AG)V 8. /-MS@GJFJF
. /-MS@5$ 13. /-MS@%G+5/ 11. /-MS@S$SS5G" 1. /-MS@SB!%$/@GGA 1. /-MS@SDA 10. /-MS@F%!"S!)F5G" 12. /-MS@JF5A5FP 4. +hat are composite types* )omposite types are recor, ine? by table, varray an neste tables. 6. +hat is Karray* 7i?e number of elements. 8. +hat is "este table* "este table is li;e array in c. . +hat are non9schema objects* Segments, e?tents, users, tablespaces, roles, profiles, conte?ts an irectories. 03. +hat is reo log file* %eo Aogs
" Fhis log file recor all the transaction on the atabase.
01. +hich parameter moe is efault* 5" parameter moe. 0. +hat is ifference between cycle an no cycle* &0cle" )ycle generate values from the beginning after reaching either its ma?imum or minimum value. No &0cle" "o )ycle oes not generate values from the beginning after reaching either its ma?imum or minimum value. 0. +hat is output of this uery" select substr(ambe;ar,91& from ual' SDA> select substr(ambe;ar,91& from ual' Out+ut" S 9 % 00. +hy we nee rowi* (a& %owi is use to fin uplicate recors in the table. (b& %owi is use to give aress for corresponing row. (c& %owi provies fastest way of removing ata from the table. 221. +hat is use of bin variable*
-in variable use to pass run9time values into plCsl bloc;. 04. Fell about when others e?ception. Fhis e?ception hanler rather than efining a separate hanler for every e?ception type. Fhis e?ception hanler hanles all error not alreay hanle in the bloc;. Fhis e?ception hanler shoul always be the last e?ception hanler in the bloc;. Fhis e?ception hanler is use to help to ;now what error has e?actly occurre. 06. Pou have a table in which 13 columns are there. 5n all the ten columns you have the ine?. +hich of the following is relate to* 1. 5nsert values are faster. . Jpate values are slower. . Select operations can be one faster. 0. /elete operations can not be one. Ans" Select operations can be one faster. 08. +hat is ine?* 5ne? is an optional structure associate with a table to have irect access to the rows which can be create to increase the performance of ata retrieval. 0. +hat is /ata /ictionary* Fhe /ata /ictionary of an Gracle atabase is a set of tables an views that are store as a rea only reference about the atabase. 5t stores information about the logical an physical structure of the atabase. 23. +hat is output of this uery* Select coun t (1& from ual' Ans" )GJ"F(1& 999999999 1 21. +hat is output of this uery* Select 1 from ual' Ans" 1C1
2. +hat is output of this uery* Select coun t(a& from ual Ans" )GJ"F(!& 9999999999 1 2. +hat is output of this uery* Select count(a& from ual Ans" $%%G% at line 1: G%!93330:
22. +hat is output of this uery* Select 1 from ual union select a from ual' Ans" $%%G% at line 1: G%!93330:
28. +hat is ifference between oracle i an 13g* ere 13g Ug means grit computers. +e can not rollbac; the roppe table in oracle i. -ut we can rollbac; the roppe table in oracle 13g. 2. +hat is ifference between replace an translate* Re+lace" 9 %eplace returns char with every occurrence of search string replace with replacement string. %eplace function replaces. SQLD select re+lace($a=ed-ar$>$ed$>$rrrrrrr$! )ro dual
%$A!)$(!M-$ 9999999999999 !mbrrrrrrr;ar Translate" Franslate returns char with all occurrences of each character in from string replace by its corresponing character in to string. SQLD select translate($a=ed-ar$>$ed$>$rrrrrrr$! )ro dual F%!"SA!F 999999999999999 !mbrr;ar
43. +rite synta? for table creation. (a& )reate table Qtable@name>(column@name1 atatype(siEe&,column@name atatype(siEe&&' (b& )reate table Qtable@name> as select Qcolumn@name> from Qanother@table@name> 41. +hat is ifference between to@char an to@ate function* To6c,ar function converts ate to a value of varchar atatype. (a! SQLD select to6c,ar(s0sdate>$ddt, Fo)F )ont, 0000$! )ro dual FG@)B!%(SPS/!F$,//FB< 9999999999999999999999 13th of july 336
(b&. SQLD select to6c,ar(,iredate>$ddt, Fo)F )ont, 0000$! )ro e+ w,ere e+noJK9< FG@)B!%(B5%$/!F$,//FB 9999999999999999999999 16th of ecember 183
To6date function converts char or varchar atatype to ate atatype. SQLD select to6date($anuar0 8K 7$> $ont,dd0000$! )ro dual FG@/!F$( 999999999 69H!"9
4. +rite uery to isplay empname,comm from employee, if employee has no commission put commission is empty. Select ename,comm,nvl(comm,3& from emp' 4. Gne view is create from multiple tables, client how o you ;now columns of tables (+hich tables& Select I from user@views' 40. +rite a uery who got more than the avg sal from emp. Select I from emp where sal>all(select avg(sal& from emp group b y eptno&' 42. +rite uery to isplay employee name an empno, who are reporting to ;ing. Select a.empno,a.ename,b.empno,b.ename from emp a, emp b where a.mgr=b.empno an b.ename=V5"#' 44. Bow o u fin user name* +e fin through show user. 46. +hat are %/-MS pac;ages* %/-MS pac;ages are Gracle, Sybase, 5ngress, Jnify an 5nformi?. 48. )an u create foreign ;ey on composite primary ;ey of a table* "o, +e can not create foreign ;ey on composite primary ;ey of a table. 4. +rite a uery uniue job for all employees in the eptno is 3 an locate loc the column. Select istinct a.job,b.loc from emp a,ept b where a.eptno=b.eptno an a.eptno=3' 63. +hat is use of local an global ine?* /ifference between local an global ine?* Aocal an #lobal ine?es are use in partitione tables. Aocal ine? is partitione ine? an #lobal ine? is non9partitione ine?. 61. +hich global temporary table is efault table* Gn commit elete row is a efault temporary tab le.
6. +hat is output of this uery* eclare l1 real' l integer' l float' l0 pls@integer' l2 integer' begin null' en' OUTPUT" "JAA 6. +hat is OLTP an &E* GAF99à Gn Aine Fransaction rocessing. /)$999à /istribute )omputing $nvironment. 60. +hat are ifferent #uis available in the mar;et* Ms9winows, ower builer, /eveloper 333, K- an K)RR 62. "umber (I,& will wor;* Pes, 5t will wor;. 5t will ta;e efault 8 igits for I. 64. Pou are use store proceure an pac;age in your project* +hich is one is best* ac;age. 1 66. +hat is Self9referential 5ntegrity constraint* 5f a foreign ;ey references a parent ;ey of the same table is calle self9referential integrity constraint.
68. +hat are types of view triggers* !n its restrictions. 1. 5nstea of insert row . 5nstea of upate row . 5nstea of elete row. Fhey are available only at the row level an not at the statement level. Fhey can be applie only to views an not to table. 6. )an you create instea of trigger for a table* "o, +e can not create instea of trigger for a table. 83. Bow proceures return values* roceures return values through parameters.
81. +hat is ifference between plCsl table an plCsl recor* 1. ACSDA table is li;e an array. -ut ACSDA recor is li;e structure.
8. +hich /atabase objects use in your project* 7unctions, roceures an ac;ages. 8. +hy o we call oracle is a %/-MS* Gracle satisfies the 1 )os rules. So, oracle is calle as a %elational /atabase Management System(%/-MS& 80. ! table is create. !fter create tables the values are inserte to it, after upate some columns, after elete two rows. "ow we want to give rollbac;. +hat is result* Ans" no rows selecte 82. )an you create pac;age specification without pac;age boy* Pes, we can create pac;age specification without pac;age boy 84. +hat objects are use in pac;age* roceures, functions, cursor, variable an e?ception. 86. 7unction returns more than one values* Pes, 7unction returns more than one values. 88. Fwo tables are there. Gne is emp an another one is ept. 5 want all recors of emp table as well as i want recor of ept table of ept no 13 only. +rite the uery for the above result. Select a.I,b.I from emp a, ept b where a.eptno=13 an b.eptno=13' 8. )an you inclue an implement one proceure into pac;age* Pes, +e can inclue an implement one proceure into pac;age. 3. )an you alter the table synonym* "o, +e can not alter the synonym of table. 1. +hy are you auiting ata moification* Fo fin who create trigger. . Bow many types of e?ceptions* 7our types. (a& re9efine e?ceptions (b& "on reefine e?ceptions (c& Jser9efine e?ceptions ("ote: "on reefine e?ceptions X ragma $?ception init&
1. . . 0.
. +hat is ifference between store proceure an pac;ag e* Store proceure is a stan alone object. -ut pac;age is not a stan alone object. +e can pass parameter in store proceure but we can not pass parameter in pac;age. Store proceure oes not have specification section an boy section. -ut pac;age have these section. +e can o overloaing in store proceure but we can not o overloaing in pac;age.
2.
+e eclare cursor in proceure, the performance is slow. +e eclare cursor in pac;age, the performance is fast. -ecause in proceure every time it will retrieve ata from atabase. -ut in pac;age every time it will retrieve ata from S#!.S#! (System #lobal !rea& is a buffer memory. ac;age has S#! temporary. 4. ! cursor eclare in a pac;age specification is global an can be accesse by other proceures or proceures in a pac;age. ! cursor eclare in a proceure is local to the proceure that can not be accesse by other proceures
0. +hat is system triggers* System triggers are create while atabase is create. 2. 5f you truncate the table, what will happen corresponing view* !ns: "o rows selecte. 4. 5n public proceures are a1, a, a. "ow they are series a, a1, a. "ow can u call proceure a into a1* Pes, +e can o. )reate or replace proceure a1 is begin 9999 a(&' en a1' 6. +hat is pseuo recor* "ew an ol ualifiers(correlation ientifirers& are available in triggers is calle pseuo recor. 8. +hat is a )artesian prouct* ! )artesian prouct is the result of an unrestricte join of two or more tables. . Fwo atabases a an b. 5 want the ata of b into a. Jsing 5MC$Y. 033. +hat is ifference between %ownum an %owi* (1& %ownum is a ynamic. -ut %owi is a static. (& 5f we elete recor from the atabase %owi also elete but %ownum is not elete. 031. Bow o you fin ata type an ata length of columns of ata type* Select column@name, ata@precision,ata@length,ata@scale,ata@type from cols where table@name=Fable@name' 03. +hat is synta? of atabase lin;* )reate atabase lin; Qlin;@name> connect to Quser@name> ientifie byT passwor using Qslnet@string>'
03. +hat is output for the following plCsl program* /eclare 5 char(13&:=B$AAG' H char(13&:=B$AAGS' true char(13&' false char(13&' -egin 5f 5=H Fhen [email protected]@line(true&' $lse [email protected]@line(false&' $n if' $n' Ans" 'alse 030. +hich atabase lin; is efault* rivate atabase lin; is efault atabase lin;. 032. +hich bac;groun process is running in /MA operation* /atabase writer 034. +hat is single tier, ouble tier an multi tier* client only9999>single tier. client an server9999>ouble tier. client,server an meiator999 >multi9tier. 036. !ny other way to allow commit statement in trigger* Jsing pragma autonomous@transaction. 038. +hat is Gverloaing of proceures* 5t is a process of ma;ing a proceure to a variety of jobs.
03. !fter creating a table, we inserte some values into the table an then upate some values. 5 want commit . upate statement 0. %ollbac; to Qsavepoint name>(refer to step & 2. commit. 013. 5f you use elete comman, how is it eletes internally* Jsing rollbac; statement. 011. +hat is ifference between varchar an varcahr* Karchar is !"S5 prouct.
Karchar is G%!)A$ prouct. 01. 5f no constraint is assigne, what constraint is efault assigne in a column of a table* "ull constraint. 01. +hat is rimary constraint(shortly&* "ot nullRuniue ;eyRine? ==> rimary ;ey constraint. 010. +hat is ata moel* Fhe logical ata structure evelope uring the logical atabase esign process is a ata moel or entity moel. 012. +hich operator to ta;e e?ecute less time: An0 or E*ists E*its o+erator because e?its return -oolean value an return any value. 014. +hat are isavantages of pac;ages an triggers* isadvanta1es o) +ac-a1es" 1. +e can not reference remote pac;age variables irectly or inirectly. . +e cannot reference host variable insie pac;a ge . +e are not able to grant a proceure in pac;age 0. Jnnecessary moule loaing. 2. Jnable to loa large pac;ages into memory. 4. Jnable to compile large pac;ages. 6. Aac; of synonym support. 8. "o version control. isadvanta1es o) tri11er" 1. +riting more number of coes. . 5t is easy to view table relationships, constraints, ine?es, store proceure in atabase but triggers are ifficult to view. . Friggers e?ecute invisible to client9application. Fhey are not visible or can be trace in ebugging coe. 0. 5t is har to follow their logic as it they can be fire before or after the atabase insertCupate happens. 2. 5t is easy to forget about triggers an if there is no. 4. /ocumentation it will be ifficult to figure out for new eve lopers for their e?istence. 6. Friggers run every time when the atabase fiels are upate an it is overhea on system. 5t ma;es system run slower.
016. +hat are the return values of functions SDA)G/$ a n SDA$%%M* Fhese are built9in functions. SDA)G/$ returns current error coe. SDA$%%M returns current error message. 018. +hat are types of trigger* 1. /MA triggers. . System triggers.
. 5nstea of triggers. 01. 5s it possible to use commit or rollbac; in e?ception part* Pes, we can use commit or rollbac; in e?ception bloc;' E*a+le" /eclare s varchar(&' -egin upate test set a = aR13' select a into s from test where a=3' e?ception when others then rollbac;' [email protected]@line(testZZ s&' $n' 03. +hat is output of this uery: select empno,ename from epts union select eptno,name from ept $M"G $"!M$ 9999999999 99999999999999 13 !))GJ"F5"# 3 %$S$!%)B 3 S!A$S 03 G$%!F5G"S 64 SM5FB 60 !AA$" 621 +!%/ 6244 HG"$S 6420 M!%F5" 648 -A!V$ 668 )A!%V $M"G $"!M$ 9999999999 99999999999999 6688 S)GFF 68 V5"# 6800 FJ%"$% 6864 !/!MS 633 H!M$S 63 7G%/ 60 M5AA$%
18 rows selecte.
01. +hat is output of this uery" Select ename, empno from emp union select name,eptno,loc from ept. 5t will not be e?ecute because uery bloc; has incorrect number of result columns. 0. )an u write proceure without formal parameters* Pes, +e can write proceure without formal parameters. 0. +hat is output of this uery* Select 1 from a (Bere a is table& Ans" 1 9999999999
1 00. +hat is output of this uery* Select 1 from a where 1=' (Bere a is table& Ans" no rows selecte 02. +hat is output of this uery* Select I from emp,ept where 1=' Ans" no rows selecte 04. )an u use rowi instea of rownum* "o, +e can not use rowi instea of rownum. 06. )an u create synonym for same name as object name* "o, +e can not create synonym for same object name. 08. +rite uery for the following tables use left outer join an right outer join. F1' )1 ) 999999 99999 1 aa bb cc 0 2 ee 4 ff F' )1 ) 9999999 99999 1 a b 2 c 6 !ns: Select a.c1,a.c,b.c1,b.c from t1 a, t b where a.c1=b.c1(R&' 999999999999àAeft outer Hoin )1 ) )1 ) 9999999999 9999999999 99999 1 aa 1a cc b 2 ee 2c 4 ff 0 bb !ns: Select a.c1,a.c,b.c1,b.c from t1 a, t b where a.c1(R&=b.c1 9999999999999à%ight outer join
)1 ) )1 ) 9999999999 9999999999 99999 1 aa 1a cc b 2 ee 2c 6 0. )an u call a proceure in a select statement* "o, +e can not call a proceure in a select statement.
03. +rite a uery to get the sum of salary of each epartment for each job. -y using table emp. TA3LE" E%P 9999999999999999999999 $M"G $"!M$ )GMM /$F"G 9999999999 9999999999 9999999999 99999999999 60 !AA$" 3 621 +!%/ 3 6244 HG"$S 3 6420 M!%F5" 3 648 -A!V$ 3 668 )A!%V 13 6688 S)GFF 3 68 V5"# 13 6800 FJ%"$% 3 6864 !/!MS 3 633 H!M$S 3
$M"G $"!M$ )GMM /$F"G 9999999999 9999999999 9999999999 9999999999 9999 63 7G%/ 3
HG999999999
M#% 9999999999
B5%$/!F$ 999999999 9999999999
S!A 9999999999
S!A$SM!"
648
397$-981
1433
33
S!A$SM!"
648
97$-981
123
233
M!"!#
%$68
39!%981
62
S!A$SM!"
648
89S$981
123
M!"!#
%$68
319M!P981
823
M!"!#
%$68
39HJ"981
023
!"!APSF
6244
19!%986
233
169"GK981
2333
%$S5/$"F S!A$SM!"
648
389S$981
1233
)A$%V
6688
9M!P986
1133
)A$%V
648
39/$)981
23
HG-
M#%
B5%$/!F$
9999999999
999999999
6244
39/$)981
999999999 !"!APSF
S!A 9999999999 333
1033
3
60 13 64 3
M5AA
%$)A$%V
668
9H!"98
133
SM5FB
)A$%V
63
169/$)983
833
SA%PLE OUTPUT 999999999999999999999999 99 HGSJM@/$F13 SJM@/$F3 SJM@/$F3 SJM@/$F03 99 999999999 9999999999 9999999999 9999999999 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 99 !"!APSF 4333 99 )A$%V 133 133 23 99 M!"!#$% 023 62 823 99 %$S5/$"F 2333 99 S!A$SM!" 2433 ANSWER" S$A$)F job,
sum(ecoe(eptno,13,sal&& /$F13, sum(ecoe(eptno,3,sal&& /$F3, sum(ecoe(eptno,3,sal&& /$F3, sum(ecoe(eptno,03,sal&& /$F03 7%GM scott.emp #%GJ -P job
01. -y Jsing tables, Store6In)oration an #eo1ra+,0 1.1. +rite the a uery to fin total sales for each store 1.. +rite the a uery to get the sales information by region 1.. +rite the a uery to fin out the sales amount for all of the stores Ta=le" Store6In)oration 999999999999999999999999999999999999 store6nae Sales ate 999999999999999999999999999999999999999999999999999 Aos !ngeles 1233 Han93291 San /iego 23 Han93691 Aos !ngeles 33 Han93891 -oston 633 Han93891 Ta=le" #eo1ra+,0 999999999999999999999999 re1ion6nae store6nae 9999999999999999999999999999999999999999 $ast -oston $ast "ew Por; +est Aos !ngeles +est San /iego Answer 747"
S$A$)F store@name, SJM(Sales&7%GM Store@5nformation #%GJ -P store@name Result7" 9999999 store@name Sales 9999999999999999999999999999999999 Aos !ngeles 1833 San /iego 23 -oston 633 Answer 748" S$A$)F !1.region@name %$#5G", SJM(!.Sales& S!A$S 7%GM #eography !1, Store@5nformation ! +B$%$ !1.store@name = !.store@name #%GJ -P !1.region@name Result8" 99999999 %$#5G" S!A$S 9999999999999999999999999999999999 $ast 633 +est 323 Answer 749" S$A$)F !1.store@name, SJM(!.Sales& S!A$S 7%GM #eography !1, Store@5nformation ! +B$%$ !1.store@name = !.store@name (R& #%GJ -P !1.store@name Result9" 99999999 store@name S!A$S 9999999999999999999999999999999999 -oston 633 "ew Por; Aos !ngeles 1833 San /iego 23
0. TA3LE" &ustoer 999999999999999999999999 &olun T0+e 9999999999999999999999999999999999999999999999999 7irst@"ame char(23& Aast@"ame char(23& !ress char(23& )ity char(23& )ountry char(2& -irth@/ate ate 1.1. )reate an ine? on column
1.. )reate an ine? on both columns <&it0 and &ountr0 < Answer 747" )%$!F$ 5"/$Y IB6&USTO%ER6LAST6NA%E on )JSFGM$% (Aast@"ame& Answer 748" )%$!F$ 5"/$Y IB6&USTO%ER6LO&ATION on )JSFGM$% ()ity, )ountry&
0. +rite plCsl coing for upating emp table using cursor* eclare )ursor for@cur is select empno,ename,job,mgr,hireate,sal,comm,eptno from emp where empno=64 cust@no for@currowtype' begin for cust@no in for@cur loop upate emp@emp set comm=1333 where [email protected]' en loop' en' 00. +rtie a store proceure gets the employee coe as parameter, chec; the va lue is e?ists in the table. Shoul write e?ception the value is there or not. )reate or replace proceure test@proc(emp@no in number& is emp@name varchar(3&' emp@job varchar(3&' emp@mgr number' emp@hireate ate' emp@sal number' emp@comm number' emp@eptno number' begin select ename,job,mgr,hireate,sal,comm,eptno into emp@name,emp@job,emp@mgr,emp@hireate, emp@sal,emp@comm,emp@eptno from emp where empno=emp@no' e?ception when no@ata@foun then [email protected]@line(Fhis recor is not available here&' en' 02. )an you pass parameter in cursor* Pes, +e can pass parameter in cursor. (i.e& parameteriEe cursor. 04. +hat is /-MS@GJFJF.JF@A5"$*
/-MS@GJFJF.JF@A5"$ is a pac;age name. 06. Bow o you elete a column* Jse !lter comman.
08. )an u change 130 to 1333* +hat uery can u use* Pes, +e can change 130 to 1333. SDA> select trunc(130,9& from ual'
F%J")(130,9& 99999999999999 1333
0. +hat is output of coe* begin elete from emps where ename=ename' en' Ans" 5t elete emps table. 003. Bow many triggers are use in a single table* 1 triggers. 001. +hat is ifference between pctuse an pctfree parameter* ct use99à ercentage of use ata bloc;. ct unuse999àercentage of unuse ata bloc;. 00. Bow can you store large ata* +e can store large ata through )AG-. 00. Select I from(select ename from emp&a where999this uery e?ecute or it will throw any error. Pes, Fhis uery will be e?ecute. E*a+le" Select I from(select ename from emp&where rownumQ=' 000. +rite uery to fin more than 2 employees from two tables* SDA> select a.ename,a.empno,b.eptno,b.name from emp a, ept b where a.eptno=b.eptno' $"!M$ $M"G /$F"G 9999999999 9999999999 9999999999 99999999999999 SM5FB 64 3 !AA$" 60 3 +!%/ 621 3 HG"$S 6244 3 M!%F5" 6420 3
/"!M$ 9999999999999 %$S$!%)B S!A$S S!A$S %$S$!%)B S!A$S
-A!V$ )A!%V S)GFF V5"# FJ%"$% !/!MS $"!M$ 9999999999 H!M$S 7G%/ M5AA
%$648 668 6688 68 6800 6864
3 13 3 13 3 3
$M"G /$F"G 99999999999999 99999999999999 633 3 63 3 60 13
S!A$S !))GJ"F5"# %$S$!%)B !))GJ"F5"# S!A$S %$S$!%)B /"!M$ 99999999999999 S!A$S %$S$!%)B !))GJ"F5"#
002. +hat result in count(I& an count(empno& from emp will give same result* )ount(I& count all incluing null. -ut count(empno& count e?cept nullable value. 004. Fhe following proceure will be e?ecu te or not* -egin select ename into vname from emp' en !ns: "o, 5t will not be e?ecute. Fhe error is ientifier K"!M$ must be eclare. 006. Fhe following program, after e?ecuting e?cep tion, control will go to proceure or not* eclare emp@name emp.enametype' begin select ename into emp@name from emp where empno=6' test(3&' e?ception when no@ata@foun then [email protected]@line(recor not foun&' en' Ans" !fter e?ecuting e?ception control will come out this program. 008. +hat is no copy* "o copy is a clause or moifier. 00. +hat is output of this uery* Select I from emp where rownum>2 Ans" no rows selecte 023. Bow o u create materialiEe view* )reate materialiEe view pp for upate as select I from emp' 021. Bow o u start an close the atabase*
Start the atabase:9 Jse startu+ comman )lose the atabase:9 Jse s,utdown iediate comman 02. Bow o u create user* )reate user ambe;ar ientifie by ambe;ar efault tablespace users temporary tablespace temp uota unlimite on users' 02. ! table has no ata' i want insert ata to that table. Bow can u insert ata into that table through plCsl* begin insert into a1 values(,ravi&' en' 020. "ow u elete a table sample. !fter eleting this table a new table sample1 is create an values are also inserte. "ow u want give rollbac;. "ow sample table have ata values or not. "o, Sample table have no ata values. (!fter applying l commans previous wor; autocomitte& 022. "ow u elete a table sample. !fter eleting this table, insert values for another one table sample1 (!lreay create table, but it has no ata&. "ow u want give rollbac;. "ow sample table have values or not. Pes, Sample table have ata values. -ut Sample1 table have no ata values. 024. Bow o u populate ata, from one table to another table using plCsl table* eclare valueinsert table1rowtype' type itemtype is table of table1rowtype ine? by binary@integer' item@tab itemtype' i binary@integer:=3' begin for valueinsert in (select I from table1& loop i:=iR1' item@tab(i&.i:=valueinsert.i' item@tab(i&.name:=valueinsert.name' insert into table values(item@tab(i&.i,item@tab(i&.name&' en loop' en' SQLD select ? )ro ta=le8 5/ "!M$ 9999999999 9999999999 1 thanga
026. rogram:9 /eclare
i@number number not null:=33' begin i@number:=133' [email protected]@line(UFhe value of i@numberZZi@number&' en' Out+ut o) Pro1ra" 1. 1 . 133 . "o output 0. $rror 2. )an not etermine Ans" 133 028. )reate seuence test increment by 1 start with 1 ma?value 13 minvalue 1 cycle cache 0' Bere what is mean of Ucache* +hat is use of Ucache* Ans" )ache means buffer memory. Fhe cache pre9allocates a set of seuence nu mbers an retains them in memory. 02. 5f we have a table as shown below* E+6id Nae #ender 1 muthu m thanga m ;eerthiga m 0 muthu m 2 sumathi f +hich of the above is the primary ;ey* 1. emp@i . emp@i an "ame . can not be etermine 0. #ener Ans" $mp@i 043. +hat is use of Karchar atatype* 1. 5t stores alphanumeric values as well as numeric values. . 5t saves memory space when compare to char.
041. /raw the G%!)A$ !rchitecture.
04. )an a null value is euivalent to a null* #ive e?amples. "o, ! null value is not euivalent to a null. E*a+le(7!" eclare i varchar(13&:=null' j varchar(13&:=null' begin if i=j then [email protected]@line(true&' else
Salar0 1333 333 0333 333 333
[email protected]@line(false&' en if' en'
E*a+le(8!" eclare i number(13&:=null' j number(13&:=null' begin if i=j then [email protected]@line(true&' else [email protected]@line(false&' en if' en'
04. +hat is output of this uery* SDA> select count(I& from emp where rownum=1 group by ename' )GJ"F(I& 9999999999 1 040. +hat is output of this uery* SDA> select instr(ambe;ar,e,1,& from ual' 5"SF%(!M-$/V!%,$,1,& 9999999999999999999999999 3 042. +hat is output of this uery* SDA> select substr(this is the,4& from ual' SJ-SF% 999999 is the 044. +hat is siEe of long ata type* #046. +hat is siEe of varchar ata type* 0333 bytes. 048. +hat is output of this uery* eclare i number(&' begin
for i in 1..13 loop savepoint test' insert into a values(i&' en loop' rollbac; to test' commit' en' Ans"
SDA> select I from a' ! 9999999999 1 0 2 4 6 8 04. +hat is output of this uery* eclare i number' begin i:=iR1' for i in 1..133 loop e?it when i>133' en loop' i:=iR1' en' Ans" "JAA 063. +hich comman can be use to show all objects* Jser@objects. $?ample:9 Select I from user@objects 061. Bow o u isplay recors from more than one table* Jsing joins 06. +hat is ifference between on commit elete rows an o n commit preserve rows* !fter creation of both tables we apply commit statement, on commit elete rows ata are elete. -ut on commit preserve rows ata are not elete. 06. +hat is ifference between where clause an hav ing clause*
Baving clause is use to fin aggregate function. -ut where clause is not use to fin aggregate function. 060. Bow o u store ata or how o u maintain table* +e can store ata of table use ata type. 062. +hat are types of AG-* 1. -AG-: Jse to store binary objects( Fo store photo files& . )AG-: Jse to store character ata( Fo store boo; files& . ")AG-: Jse to store Jnicoe ata. 0. -75A$: Jse to store binary objects(Fo store movie files&. ("ote: AG- use to store unstructure ata(te?t, graphic images, v ieo clips an soun wave forms& 064. )an u create referential constraint for not null column of parent table* "o, +e can not create referential constraint for not null column of parent table. 066. +hat is mean of U%!)* %eal !pplication )luster. 068. +hat are physical atabase structure objects an logical atabase structure objects* hysical atabase structure objects are ata file, control file an reo log file. Aogical atabase structure objects are tables, views, seuences, ine?es, proceure, function an pac;ages. 06. Bow o u run a ynamic sl* Jse e?ecute immeiate. 083. +hy o not use implicit cursor* 5t will slow performance. 081. +hat is efalut value of boolean* Ans" null 08. +hat is ifference between snapshot an materialiEe view* 1. Snapshot is a static. -ut materialiEe view is a ynamic. . ! snapshot use to save uery result of a remote atabase tables. -ut materialiEe view is use to save uery result of a local atabase tables. 08. Bow can u import ata from te?t file to atabase* +e can import ata from te?t file to atabase using JFA@75A$, sl loaer an ata pump. 080. Select empno,ename,sal,comm from emp orer by ' +hat is mean of orer by * 5t will isplay orer by ename(orer by means n column& 082. Select empno,ename,sal,comm from emp orer by ' +hat is mean of orer by *
5t will isplay orer by sal(orer by means r column& 084. #ive names for prgamas* 1. !utonomous transaction. . $?ception init. 086. )an u use out parameter in function* Pes, +e can use out parameter in function. 088. +hat is pac;age* ac;age is a container which contains relate objects. 08. +rite names for /ate function, "umeric function, )haracter function, )onversion function an Miscellaneous function. ate )unctions" !@months,Aast@ay,Months@between,%oun,"e?t@ay,Fruncate,#reatest an "ew@time. &,aracter )unctions" 5nitcap, Aower, Jpper, Atrim, %trim, Franslate, %eplace, Aength, /ecoe an )oncatenation operator. Nueric )unctions" !bs, )eil, )os, )osh, $?p, 7loor, ower, Mo, %oun, Frunc, Sign an Srt. &onversion )unctions" Fo@)har, Fo@ate an Fo@number. %iscellaneous )unctions" Ji,Jser,"KA an KsiEe. #rou+ )unctions" !verage, Minimum, Ma?imum, Sum, )ount, #roup by an Baving clause. 03. )reate a pac;age specification an pac;age boy calle %G/@!)V that contains !//@%G/, J/@%G/,/$A@%G/ proceures an D@%G/ function. create or replace pac;age +rod6+ac- is +rocedure add6+rod (v@proi in prouct.proitype,v@escrip in prouct.escrip type&' +rocedure u+d6+rod (v@proi in prouct.proitype,v@escrip in prouct.escrip type&' +rocedure del6+rod (v@proi in prouct.proitype&' )unction .6+rod (v@proi in prouct.proitype& return varchar' en pro@pac;' create or replace pac;age boy +rod6+ac- is +rocedure add6+rod (v@proi in prouct.proitype,v@escrip in prouct.escrip type& is begin insert into prouct(proi,escrip& values(v@proi,v@escrip&'
en a@pro' +rocedure u+d6+rod (v@proi in prouct.proitype,v@escrip in prouct.escrip type& is begin upate prouct set escrip=v@escrip where proi=v@proi' if slnotfoun then raise@application@error(933,no proucts upate&' en if' en up@pro' +rocedure del6+rod (v@proi in prouct.proitype& is begin elete from prouct where proi=v@proi' if slnotfoun then raise@application@error(933,no proucts elete&' en if' en el@pro'
'unction .6+rod (v@proi in prouct.proitype& return varchar is v@escrip prouct.escriptype' begin select escrip into v@escrip from prouct where proi=v@proi' return(v@escrip&' en @pro' en pro@pac;'
01. )an u orer the column values using column alias* Pes, +e can orer the column values using column alias. E*a+le" Select ename,job,salR13 annsal from emp orer by annsal' 0. 5s %ename /MA or /)A or //A or F)A statement* %ename is a /MA statement. 0. +hat is ifference between primary ;ey an foreign ;e y* 1. rimary ;ey oes not accept null value but foreign ;ey accept null value. . rimary ;ey oes not accept uplicate value but foreign ;ey accept uplicate value. 00. +hat is output of this uery* Select sum(sal& from test. (Bere totally two rows. Gne row sal is 1333 an another row sal is null& 5t will isplay sum sal. Fhat is it will isplay 1333 only.
02.
eclare begin insert into sam values(,0333&' insert into sam values(0,333&' en' Bere if any synta? errors in secon insert statement, the values are committe or not. !ns: Fhe values are not committe.
04. #ive e?ample for !ssociate array or plCsl table. eclare type list@of@names@t is table of person.first@nametype ine? by pls@integer' happyfamily list@of@names@t' l@row pls@integer' begin happyfamily(33333&:=$A5' happyfamily(912363&:=Steven' happyfamily(9333&:=)hris' happyfamily(88&:=Keva' l@row:=happyfamily.first' while(l@row is not null& loop [email protected]@line(happyfamily(l@row&&' l@row:=happyfamily.ne?t(l@row&' en loop' en' 06. +hen a atabase set to be a %/-MS* (a& !ll mappings(relationship& are supporte which is calle %/-MS. (b& !ll rules are supporte which is calle a %/-MS. 08. Bow many percentage of oracle use in web* 6 0. Bow many versions are available in oracle* Gracle999>Gracle999>4.3999>6.3999>6.999>8.3999>8i999>i999>13g999>11g 233. +hat are attributes of opps* (a& /ata !bstraction. (b& /ata $ncapsulation. (c& /ata 5nheritance. (& /ata olymorphism. 231. +hat are type of functions* 7unctions can be classifie into two types are system function an user9efine functions. System functions are classifie into two types are single row function an multiple row function.
Single row functions are classifie into char,numeric,ate,conversion an general functions. 23. +hat is ecoe* /ecoe tells internally if selection logic. $?amples:9 (a&. Select ecoe(job,cler;,salR233,sal& from emp' $?palanation:9 if job=cler; sal=salR233 else sal (b&. Select ecoe(job,cler;,salR233, S!, salR33,m;tg,salR133, sal& from emp' 23. +hat are features of oracle i* nullif,nvl,coalese an case. 230. +hat are ata types available in oracle* char,varchar,varchar,ate,number(int an ec&,long,long row,)AG-,-AG-,")AG-, -75A$,")har,"Karchar,rowi,Fimestamp,Fimestamp with F, Fimestamp with AF 232. +hat is ifference between tab an cat* Fab comman isplays only tables,views an synon yms. -ut catalog comman(cat& isplays complete catalog of all objects in the object. 234. Bow many columns can we create in a single table* 130 columns. 236. Bow many columns can we inclue in a select statement* 034 columns. 238. Bow many tables we can join* 24 tables we can join in a single select statement(we shoul not give more than 2 tables. -ecause it reuces the performance. Fhe processor can not be wor;e. 23. Bow many subueries can we give* SDA S$%K$%999> 4 sub ueries. G%!)A$99999999999> 22 sub ueries. ("ote: Single processor oes not ta;e more than 8 ueries. otherwise it is hang.& 213. +hat coul be the siEe of processor* 18 mb. 211. Bow many parameters can we pass in a proceure*
133 parameters. 21. +hat is subuery* +hat are they* Gutput of inner uery access input of outer uery. Fhey are single row subuery, multiple row subuery, multi9column subuery,scalar sub9uery, 5nline uery an )orrelate sub9uery. 21. +hat is %ownum* %ownum is a pseuo column. 5t is a ynamic column create by oracle server or not by us. 210. +hat is %owi* %owi is a uniue he?aecimal value that gets generate as shown as in the recor inserte into the atabase. 212. +hat is ifference between commit an rollbac;* )ommitte transactions can not be rollbac;e. %ollbac;e transactions can not be committe. 214. Fell about constraints. rimary ;ey:9 "ot nullRJniueR5ne?. "ot null ;ey:9 5t accepts uplicate value an must have value. Jniue ;ey:9 5t is a istinct value an accpt null value. $?ample:9 assport number an emp@i. ("ote: More than one null value accept in oracle only. SDA S$%K$% accept only one null value.& 216. +hat is ifference between column level constraint an table leve l constraint* 5n the column level constraints, constraints names are given b y system. -ut table level constraitns, constraint names are given by user. 218. +hat are ;eywors of !lter comman* /rop,! an Moify. 21. +hat is use of log* Aog is use to write one ml operations.(Fhis is only possible /elete only& 23. +hat is view* Kiew is a logical subset of a table or view is a virtual table. 21. +hat view contains* Kiew contains compile select statement. Kiew oes not contain recors. 2. +hat are avantages of view* 1. 7irewallCSecurity. . /ata 5nepenence. . Fo ma;e comple? ueries.
2. +hat is neste view* "este view means more view is create. 20. Bow many views create for a table* +e can create ma?imum 22 views. 22. +hat is seuence* Seuence is sharable object. 5t is usually use to generate values for primary column. 24. +e create a seuence for a table. +e inserte more recors that table. "ow we want give rollbac;. "ow seuence is rollbac;e or not* Gnly recors are rollbac;e. -ut seuence not rollbac;e. 26. )an we share seuence from one table to another table. +e shoul not share the seuence for table. -ut we shoul share the seuence for user. 28. +hen will you create ine?* /os (a& Foo large table. (b& Q1C r of the recor. (c& !ny column of freuently use Uwhere conition. (& rimary ;ey an Jniue ;ey. (e& Master table. Fable.
/o not (a& Foo small table. (b& Y (c& Y (& 7oreign ;ey (e& Fransactional
2. Bow many ine?es can you create for a single table* +e can create ma?imum 22 ine?es for a single table. 23. +hat are types of ASDA bloc;* 1. !nonymous -loc;(Jnname bloc;& . "on anonymous -loc;("ame bloc;& (a& 7unction (b& roceure 21. +hen will you write proceure* !n +hen will you write a function* 5f you want perform a tas; write proceure. 5f you want computer a value write function. 2. +hat is use of type* Fype is a plsl attribute which is use to eclare the same ata type of bac; en table column. 2. +hat is cursor* +hat are types of cursor* )ursor is a private SDA wor; area. )ursor can be categoriEe into two types are 5mplicit cursor an $?plicit cursor. 20. )an you call a proceure into a trigger* Pes, +e can call proceure into a trigger.
22. )an you call a trigger into a proceure* "o, +e can not call a trigger into a proceure. +e o not nee call a trigger into a proceure. 24. +hy o you write proceure* +e write proceure for insert, upate an elete recors of table. 26. +hat is atabase server* /atabase server is a program which serves atabase services to another computer. 28. +ho is $n user* $n user is a person which use prouct (client&. 2. !n !pplication encounters four types of atas. 1. Simple structure ata. X Banle by oracle relational moel. . )omple? structure ata. X Banle by oracle object relational feature (collections, references an user9efine types& . Semi9structure ata. X Banle by ueuing technology (!vance uery& 0. Jn9structure ata. X Banle by AG203. Fell about coalesce function. 5t will return not null e?pression if e?pression evaluate not null, it will return null if e?pression evaluate null. $?:9 Select coalesce (aress1, aress, aress& result from suppliers. )oalesce function is li;e that if then else statement:9 5f aress1 is not null then %esult=aress1' $lsif aress is not null then %esult=aress' $lsif aress is not then %esult=aress' $lse %esult=null' $nif' "ote: Fhe coalesce function will compare each value, one by one.
201. )an you use l statements in plCsl* Bow* Pes, +e can use l statements in plCsl use ynamic sl. 20. 5s foreign ;ey allow uplicate values* Pes.
20. +hat is mutating error* +hen we will o ml operation for trigger firing table, mutating error will arise.
200. +hat are two ways to create global temporary table* wa0 7" create global temporary table my@temp@table (column1 number, column number& on commit elete rows' wa0 8" create global temporary table my@temp@table (column1 number, column number& on commit preserve rows' 202. +hat are types of global temporary table* 1. Gn commit elete rows. . Gn commit preserve rows. 204. +hat is ifference between anonymous bloc; an non anonymous bloc;* 1. !nonymous bloc;s are unname ACSDA bloc;s. "on !nonymous bloc;s are name ACSDA bloc;s. $?. Subprograms. . !nonymous bloc;s can not be calle from other plCsl bloc;s. -ut "on !nonymous bloc;s can be calle from other plCsl bloc;. . !nonymous bloc;s are not store in atabase. -ut "on !nonymous bloc;s are store in atabase. 0. !nonymous bloc;s are beginning with ;eywor is /$)A!%$ or -$#5". -ut "on !nonymous bloc;s are starte with ;eywor is )%$!F$. 206. +hat are the pre9reuisites to moify column an ata type of table* Fo moify ata type of a column the column must be empty. Fo a a column with "GF "JAA constraint, table must be empty. 208. )an you write having clause without using group by function* "o, +e can not write having function without using group by function. 20. +hat is loc;ing* Aoc;ing is a mechanism. 5t prevents ata from estructive interaction. 223. +hat is two9phase commit* !ll atabase servers in a istribute atabase either commit or rollbac;.
221. )an you write a cursor into a proceure* Pes, +e can write a cursor into a proceure. 22. 5n plCsl bloc; which parts are manatory* -egin an $n. 22. Fell about %!+ an AG"# %!+. %!+ is euivalent to K!%)B!%. AG"# %!+ is euivalent to AG"#. 220. !s applications can evelop to inclue increasingly richer semantics, they encoun ter the nee to eal with the following ;ins of ata: (a& Simple structure ata 9 5t can be fit into simple tables. (b& )omple? structure ata9collections, references, user9efine types. (c& Semi9structure ata9!vance Dueueing, eal with Messages. (& Jnstructure ata 9te?t, graphic images, still vieo clips, full motion vieo, an soun waveforms . 222. )an you eclare or efine more than one cursor in a proceure* Pes, +e can eclare or efine more than one cursor in a proceure. 224. 5s %owi physical ata structure or logical ata structure* hysical Structure. 226. +hat is use of AS@5"F$#$%* AS@5"F$#$% values reuire less storage an provie better performance than "JM-$% values. So we can use AS@5"F$#$%. Gracle i introuce -inary@5nteger. Gracle 13g introuce AS@5"F$#$%.-ut both are same. 228. +hat is use of host variable an bin variable* Bost variable: select I from emp where rownumQWa minus select I from emp where rownumQWb' Bere Wa an Wb are host variables. oste 18th May 31 by Sunil V M 3
Add a coent
Oracle 3A E*c,an1e
(I ,ave ,ad all o) t,e disadvanta1es re.uired )or success4 Larr0 Ellison!
•
)lassic
•
7lipcar
•
MagaEine
•
Mosaic
•
Siebar
•
Snapshot
•
Fimeslie
•
%ecent
•
/ate
•
Aabel
•
!uthor
)luster /efinition )luster /efinition Han th "ormaliEation with $?ample "ormaliEation with $?ample Han th %ef )ursor $?ample %ef )ursor $?ample Han 8th %!5S$@!A5)!F5G"@$%%G% %!5S$@!A5)!F5G"@$%%G% Han 8th +rapping 9 ACSDA +rapping 9 ACSDA Han 8th roceure 9 ACSDA roceure 9 ACSDA Han 4th
)ursor 9 ACSDA )ursor 9 ACSDA Han 4th "este -loc;s 9 lCSDA "este -loc;s 9 lCSDA Han 4th 7unction 9 lCSDA 7unction 9 lCSDA Han 2th $Y$)JF$ 5MM$/5!F$ in ACSDA $Y$)JF$ 5MM$/5!F$ in ACSDA Han 2th /ata#uar #eneral Duestions /ata#uar #eneral Duestions "ov 1st )reating a uplicate atabase with ifferent name using %M!" )reating a uplicate atabase with ifferent name using %M!" "ov 16th G%!93131: 5"SJ775)5$"F %5K5A$#$S G%!93131: 5"SJ775)5$"F %5K5A$#$S "ov 16th G%!933802: M$MG%P@F!%#$F not supporte on this system G%!933802: M$MG%P@F!%#$F not supporte on this system "ov 16th %M!" 5")%$M$"F!A -!)VJ %M!" 5")%$M$"F!A -!)VJ "ov 14th changing the hostname Solaris 11 changing the hostname Solaris 11 "ov 14th Simple -ac;up an %ecovery using %M!" Simple -ac;up an %ecovery using %M!" "ov 14th G%!93332: error in ientifying controlfile G%!93332: error in ientifying controlfile "ov 12th )onfiguring "etwor; !apter in Solaris 11 )onfiguring "etwor; !apter in Solaris 11 "ov 12th Bow to enable SSB %oot Aogin 5n Solaris 11 Bow to enable SSB %oot Aogin 5n Solaris 11 "ov 12th Manually )loning an $?isting Gracle /atabase on Ainu? Manually )loning an $?isting Gracle /atabase on Ainu? "ov 12th $9mail !ress Kaliation an /omain "ame $?traction
$9mail !ress Kaliation an /omain "ame $?traction May r SDA join types SDA join types May n 1L39104 $?am #uie 1L39104 $?am #uie May n 1 Fhe SDAH loajava Jtility Fhe SDAH loajava Jtility May 1st Secure7iles: Fhe "ew AG-s Secure7iles: Fhe "ew AG-s May 1st Gracle ACSDA 5nterview Duestion an !nswers Gracle ACSDA 5nterview Duestion an !nswers May 18th Len )ore for Gracle v Len )ore for Gracle v May 10th Fransportable Fablespaces Fransportable Fablespaces May 10th SDA /eveloper .1 Scheuler (/-MS@S)B$/JA$%& Support SDA /eveloper .1 Scheuler (/-MS@S)B$/JA$%& Support May 10th SDA /eveloper .1 /ata ump +iEars SDA /eveloper .1 /ata ump +iEars May 10th Schema Gwners an !pplication Jsers Schema Gwners an !pplication Jsers May 10th Store Gutlines an lan Stability Store Gutlines an lan Stability May 10th %enaming or Moving Gracle 7iles %enaming or Moving Gracle 7iles May 10th %ecompiling 5nvali Schema Gbjects %ecompiling 5nvali Schema Gbjects May 10th %eclaiming Jnuse Space in /atafiles %eclaiming Jnuse Space in /atafiles May 10th artitioning an $?isting Fable using /-MS@%$/$75"5F5G" artitioning an $?isting Fable using /-MS@%$/$75"5F5G"
