SQL/PLSQL INTERVIEW QUESTION AND ANSWER 1. SQL:- Structured query language is a specialized language for Updating, Deleting, and Requesting information from Database. Developed by IBM in!"#$s. * as bot* 2. PL/SQL:% &'(S)' is a procedural language t*at *as interactive S)' S)' and procedural procedural programming programming language language constructs suc* as interaction, conditional branc*ing. 3. ORACLE:% +*e racle Database is an ob-ect relational Data base management system produced and mared by racle /orporation. 4. META-DATA:% Meta%data is data about data. Meta data is structured data 0*ic* describes t*e c*aracteristics of a resource. 5. Query:% +o retrieve data from t*e data base table is called query. . A!!r"#u!e: % It is a particular property, 0*ic* describes t*e entity. $. %"e&':% 1very table is broen up into smaller entities called 2eld. 34ield 34ield in t*e customers table consists of ID, 5ame, 6ddress, 6ge and Salary. (. T)#&e:% +able is a container t*at *olds information about lie items. 4or%17am8 % 6n 1mployee table 0ould contain t*e same basic details on eac* employee 5ame, +itle, Department and So n. *. T)#&e-S+),e:% +.S is a logical Storage structure at t*e *ig*est level t*at stores all t*e data in data 2les.
1. D)!)-)e:% 6 D.B is a logical /ollection of relevant relevant data, re9ecting some in*erent meaning, representing some aspect of real 0orld.
11. DMS:- It is a collection of programs t*at enables user to create and maintain a Database.
12. RDMS:- RDBMS stands for Re Relational lational Database Management System. It rganizes data into related ro0s and columns.
13. Se0ue,e:- Sequence is a user created Database ob-ect t*at can be s*ared integers.
by multiple user to generate unique
14. E!"!y:- It is a t*ing in t*e real 0orld 0it* an independent e7istence.
15. Re&)!""+:- It is an association among t0o or o r more entities.
1. C&u:- 6 column is a vertical entity in a table t*at contains all information associated 0it* a speci2c 2eld in a table.
1$. Nr)&")!":- 5ormalization is a process 0it* 0*ic* 0e can reduce t*e redundancy redundancy . R 5ormalization means t*e process in 0*ic* ro0 data is converted into 2ltered data. :. 548% + 548% +able able t*at contains one or more more repeating groups identi2es and remove repeating group. :. ;548% Describe a table i.e. fully functionally dependent.
1(. W)! " V"e6 V"e677 Ty+e Ty+e 8 9"e6
1. D)!)-)e:% 6 D.B is a logical /ollection of relevant relevant data, re9ecting some in*erent meaning, representing some aspect of real 0orld.
11. DMS:- It is a collection of programs t*at enables user to create and maintain a Database.
12. RDMS:- RDBMS stands for Re Relational lational Database Management System. It rganizes data into related ro0s and columns.
13. Se0ue,e:- Sequence is a user created Database ob-ect t*at can be s*ared integers.
by multiple user to generate unique
14. E!"!y:- It is a t*ing in t*e real 0orld 0it* an independent e7istence.
15. Re&)!""+:- It is an association among t0o or o r more entities.
1. C&u:- 6 column is a vertical entity in a table t*at contains all information associated 0it* a speci2c 2eld in a table.
1$. Nr)&")!":- 5ormalization is a process 0it* 0*ic* 0e can reduce t*e redundancy redundancy . R 5ormalization means t*e process in 0*ic* ro0 data is converted into 2ltered data. :. 548% + 548% +able able t*at contains one or more more repeating groups identi2es and remove repeating group. :. ;548% Describe a table i.e. fully functionally dependent.
1(. W)! " V"e6 V"e677 Ty+e Ty+e 8 9"e6
on e or more table or :
178% =e 1mp%Id> 1mp name> name> 1mp addr> Select emp ? id id from from emp> emp> Synta78% /reate )uery8 % Selectfrom vie0 ? 1M& 0*ere empid bet0een C## and ###>
+0o types of
1*. W)! " I'e;7 Ty+e 8 I'e;.
:Inde78 % It is a Sc*ema ob-ect 0*ic* contains entry for eac* value t*at appears in t*e inde7 column of t*e table. R % Inde7 is a data structure t*at improves t*e speed of operations in a table. Synta78% /reate inde7 inde7 name on table name @column nameA. 178% /reate inde7 ab?name on student @Stud?idA>
+ypes of Ind Inde78% e78% @A. /lustered 3 Inde7 or 5ormal 3 Inde78% % /lustered Inde7 is a special type of inde7 t*at records t*e 0ay records in t*e table are p*ysically stored. % +*e leaf nodes of a clustered inde7 contain t*e data page. % /lustered Inde7 is p*ysically stored a table can *ave clustered Inde7. @;A. 5on 3 /lustered % Inde7 or Bitmap 3 Inde78% % 5on%/lustered Inde7 is a special type of inde7 in 0*ic* t*e logical order of t*e inde7 does not matc* t*e p*ysical stored order of t*e ro0s on dis. % +*e leaf nodes of a non%clustered inde7 do not consist of t*e data page. % 5on%/lustered Inde7 is logically stored a table can *ave ;! 5on%/lustered Inde7. @EA. 4unctional based % Inde78% %+*ey are based on e7pressions. %1nable query to evaluate value returned by an e7pression. e7pression. @A. Domain % Inde78% % +*ey +*ey are Inde7 0*ic* are instances of an application application speci2c Inde7 of type Inde7.
2. D"
6 +able is a list of all records. 6 +able is an actual entity 0*ic* contains ro0s of a data.
:
•
6 vie0 is a subset of a table based on t*e parameters of t*e vie0. 6 vie0 is t*e result of a query creating a subset of ro0s and columns from t*e present table.
21. D""> = %r)>e!)!" • •
&aging occurs in memory 0*ereas. 4ragmentation occurs on dis.
22. D"
In DBMS t*ere is no relations*ip in all tables. In RDBMS t*ere is diFerent relations*ip b(0 diFerent table.
23. D"
S)' server ;### is t*e product of Microsoft but racle is t*e product of racle /orporation. In S)' only DM', DD' etc. In &'(S)' &rocedure, trigger, function etc.
24. D"
4unctions return a single value t*roug* return statement. 4unction use t*roug* select, Update or Delete Statement. 4unction can$t support DM', DD'. 4unction accepts only in parameter. 4unction returns a value.
:&rocedure8%
•
• • • •
&rocedure can return one or more t*an one values t*roug* parameter. &rocedure cannot. &rocedure support DM', DD'. &rocedures tae in, out, in out parameter. &rocedure does not return a value.
25. D"
Stored &rocedure is precompiled e7ecution plan. Stored &rocedure return t*e value or not. It is precompiled code, it is stored in database. S.& returns al0ays integer value by default zero.
:4unction8% • • • •
=*ereas 4unctions are not. 4unction can return a value. It need compile 0*enever it 0ill run. 4unction return type could be scalar or table or table value.
2. D">er = Pr,e'ure :+rigger8% • • • • •
De2ned 0it* create trigger. Implicitly invoed. /ommit, save point and rollbac are not allo0ed. Data dictionary contains source code in user triggers. +/' commands are not allo0ed.
:&rocedure8% • •
De2ned 0it* create procedure. 17plicitly invoed.
• • •
/ommit, save point and rollbac are allo0ed. Data dictionary contains source code in user source. +/' commands are allo0ed.
2$. D">er : Stored &rocedure8% • • • • •
Stored &rocedure can give one (& parameter. Stored &rocedure may return a value. In Stored &rocedure you can pass parameter. Gou can 0rite a Stored &rocedure in a trigger. Stored &rocedure is 0ritten for a data% base.
: +rigger8% • • • • •
+rigger cannot give any (& parameter. But +rigger not. But in +rigger you cannot. But in +rigger Gou cannot 0rite a Stored &rocedure. +rigger is 0ritten an individual table or vie0.
2(. D"
6llo0 null values and doesn$t allo0 duplicate values. 5ot accept null values. 6llo0 null value. Unique ey create non%cluster inde7.
: &rimary ey8% • • • •
Does not allo0 bot* null and duplicate values. 6ccept only null value. Doesn$t allo0 null values. &rimary ey create cluster inde7.
2*. D"
It is a private ob-ect. nly t*e user 0*o created can access it. If t*e base table is dropped Synonym is dropped automatically.
: 6lias8% • • •
It is a global ob-ect. 6ny user can access it. If t*e base table is dropped 6lias 0ill not drop automatically.
3. D"
Delete is a DM' /ommand. Delete can use 0*ere condition. It deletes speci2ed data. Rollbac is possible. +rigger 0ill be 2red.
: +RU5/6+18% • • • • •
+runcate is a DD' /ommand. +runcate cannot use 0*ere condition. It removes all t*e data. Rollbac is not possible. 5o +rigger 0ill be 2red.
: DR&8%
• • • •
Drop is t*e DD' Statement. Drop commands remove t*e table from data dictionary. Rollbac is not possible. 5o +riggers 0ill be 2red.
31. D"
• • •
It ends t*e current transaction by maing all pending data c*anges permanent. +o commit t*e S)' operations done so far. /ommit t*e c*anges from t*e transactions. /ommit is used to save t*e c*anges permanently on t*e server.
: Rollbac8% •
• • •
It ends t*e current transaction by discarding all pending data c*anges. +o revoe t*e S)' operations done so far. 6ny c*anges from t*e transactions. Rollbac is used to undo t*e c*anges and restore pervious state.
: Save &oint8% • •
It mars t*e save point 0it* t*e current transaction. +o perform line by line transaction 0e are using save point.
32. D"
It is used 0*en 0e 0ant t*e database to be s*ared 0it* ot*er user. Hrant 0ill give permission to t*e user on database. H./ is used to provide access or privileges on t*e database ob-ects to t*e user. Synta78% S)'HrantJ&rivilege name,J&rivilege name;, on Jb-ect name +o JUser name 178 % S)' Hrant select on 1%mp to 1nd users.
•
• •
•
•
: Revoe /ommand8% It is used 0*en 0e 0ant one database to stop s*aring t*e information 0it* ot*er user. Revoe 0e can remove t*e permission. R./ removes user access rig*ts or privileges to t*e database ob-ects. Synta78% S)'HrantJ&rivilege name,J&rivilege name;, on Jb-ect name from JUser name 178 % S)' Revoe insert, delete on 1mp from operators.
•
• •
•
•
:&rivilege8 % 6bility to compile t*e function( procedure. 6bility to e7ecute t*e function( procedure directly.
33. D"
Decode can 0or 0it* only scalar
:/6S18% •
• •
/ase can 0or 0it* logical operators, predicates K searc* able sub queries. /ase can 0or as a &'(S)' construct. /ase e7pects data type consistency.
•
/ase is a statement.
34. D"
•
•
It accepts ; parameters if t*e st parameter is null t*en it returns t*e ;nd. 5<' function substitutes a value 0*en a null value is encountered. If e7pr is null, t*en 5<' return e7p;. If e7pr is not null, t*en 5<' returns e7pr
: 5<';8% •
•
•
It accepts E parameters if t*e st parameter is null t*en it returns t*e Erd else t*e ; nd. 5<'; substitute a value 0*en a null value is encountered as 0ell as 0*en a non%null value is encountered. If e7pr is null, t*en 5<'; returns e7prE. If e7pr is not null, t*en 5<'; returns e7pr;.
35. D"
•
+ype is declared for t*e column level. +ype record means 0e can fetc* record from more t*an one table. L +ype is used to c*ange t*e size of any data type but only for one record.
: L Ro0 +ype8% • • •
Ro0 +ype is declared at t*e record level. Ro0 +ype is used to *old t*e total ro0 values. LRo0 +ype is used to declare a record t*at represents a particular ro0 of a table.
3. D"
•
Replace entire string at a time. Returns string if no matc* found. Replace provides functionality related to t*at provided by t*e +ranslate function. Replace function replaces single c*aracter 0it* multiple c*aracters.
: +ranslate8% • • •
•
+ranslate replace c*aracter one 3 to % one basis. Returns null if no matc* found. +ranslate provides single 3 c*aracter one % to 3 one substitution. +ranslate function replaces single c*aracter 0it* single c*aracter only.
3$. D"
:17ists8% •
1IS+S is much faster than I5 (when the subquery results is very large)
• •
Returns true if a subquery contains any ro0s. +*e uter query 0ill ran 2rst and t*en only sub query.
:In8% •
•
•
I5 is faster than 1IS+S (when the subquery results is very small) Returns true if a speci2ed value matc*es any value in a subquery or a list. +*e sub query 0ill run 2rst and t*en only outer query.
3(. W)! " Su# 0uery Ty+e 8 Su# 0uery : Sub query8 % Sub query can be de2ned as a select query t*at returns single or multiple values. +ypes of Sub query8% @A Single ro0 Sub query8 % +*ese query return only one ro0 from t*e inner select statement is called Single ro0 sub query. 178 % N 8 J> J> > N> JN. : Select from emp 0*ere salary N @select salary from emp 0*ere emp idN @#AA> @;A Multi ro0 Sub query8 % +*ese query return more t*an one ro0 from inner select statement. 178% I5> 65G> 6''. : Select. e name, sal, Dept no from emp 0*ere sal in @select min @salA from emp group by dept noA> Synta78 % S)' Select Select 'ist from +able 5ame 0*ere /olumn 5ame operator @select select 'ist from +able 5ameA>
3*. W)! " Curr Ty+e 8 Curr. % /ursor is a mec*anism by 0*ic* you can assign a name to a OSelect StmtP and manipulate t*e information 0it*in t*e S)' Statement. % /ursor is a private S)' are used to e7ecute S)' Statement. +0o +ypes of /ursor8% @A. Implicit % /ursor8 % It is automatically declared by racle server. It is private memory area allocated by .S. @;A. 17plicit 3 /ursor8 % It is declared by programmer or user. It is easy to trap error.
%ur S!e+ ! +er8r e;+&","! ,urr:-
@A/ursor Declaration8 % In t*e Declaration section. @;A/ursor pening8 % In t*e 17ecution section. @EA/ursor 4etc*ing8 % 4etc* t*e data from cursor into &'(S)' variables or records in 17ecution Section. @A/ursor /losing8 % /lose t*e cursor in t*e 17ecution section before you end t*e &'(S)' Bloc.
B Curr A!!r"#u!e 8ur Ty+e:@A. @;A. @EA. @A.
S)' L Ro0 /ount8 % 5umber Return. S)' L Is pen8 % +(4 Return. S)' L 4ound8 % +(4 Return. S)' L 5ot 4ound8 % +(4 Return.
BCurr A'9)!)>e:% Using /ursor to getting multiple values. % ne /ursor *as multiple Select Statement.
BCurr D")'9)!)>e:% It is returned by only one ro0 at a time. % It gives less performance. % 1ac* time 0e fetc* a ro0 from t*e /ursor, 0*ere as a normal cursor select statement query maes only one trip. BCurr E;)+&e:(( Declaring a cursor (( /ursor 1M& ?/ursor is Select 1M& no, 1 name, Sal from 1M&> Begin (( pening a cursor (( pen 1M& ? /ursor> 'oop
(( 4etc*ing records from a cursor (( 4etc* 1mp ? /ursor into (( +esting e7it /onditions (( 17it 0*en 1mp ? /ursor L 5ot 4ound If @ 1'S1 DBMS?ut put. put?'ine @ 1nd if> 1nd 'oop> (( /losing t*e /ursor (( /lose 1M& ? /ursor> DBMS?utput. put?line @ODonePA> 1nd>
4. W)! " Tr">>er Le9e& 8 Tr">>er Ty+e 8 Tr">>er : +rigger8 % 6 +rigger is a &l(S)' bloc structure 0*ic* is 2red 0*en a DM' statement lie, Insert, Delete, Update is e7ecuted on a data base table. 6 trigger is triggered automatically 0*en an associated DM' statement is e7ecuted. OR
% +rigger is lie as procedure. +*is procedure 0ill call at some events and occurring. 'ie Insert, Update, Delete. % 178% 6+M Mac*ine .
% 178% /reate table product@&roduct?id number @CA, &roduct? name >er:@A. Ro0 level of +rigger8 % 6n event is triggered for eac* ro0 updated, inserted or deleted. 178 % /reate or Replace +rigger Deptupdate after update on Dept 4or eac* ro0.
@;A. Statement level of +rigger8% % 6n event is triggered for eac* S)' Statement e7ecuted. 178 % /reate or Replace +rigger Dept Delete after Delete on Dept. B Tree Ty+e 8 Tr">>er:@A. Before % +rigger8% +*ese trigger 2re before any transaction are implemented lie Insert, Update and Delete.
@;A. 6fter % rigger8% +*ese trigger 2re after any transaction is implemented lie Insert, Update and Delete, @EA. Instead of +rigger8 % +*ese are special bind of trigger t*at is de2ned on data base vie0.
41. W)! " " Ty+e 8 " oin8 % oin is a query t*at combines ro0s from t0o or more tables, vie0s or materialized vie0s. % 6 oin is performed 0*enever multiple tables appear in t*e queries from clause. 178% oin queries are used to select data for multiple tables and display t*at data in a single (&. Synta78% =*ere +able . /olumn N +able ;. /olumn ;. 178% S)' Select 1mp no, 1 name, D name, 'oc from 1mp, Dept>
r8 % S)' Select 1mp no, 1 name, Sal, Hrade from 1mp, sal Hrade>
B%ur Ty+e 8 ":1. E0u" r S"+&e r Ier ":- Returns t*e ro0s from t0o or more tables -oined toget*er based upon a equality conditions in t*e 0*ere clause. E;:- Select from emp, dept 0*ere emp dep no N dept . dept no> 2. N - E0u" ":- Returns t*e ro0s from t0o or more tables based upon a relations*ip ot*er t*an t*e equality conditions in t*e 0*ere clause. E;:- Select 1. 1 name, 1. Sal, S Hrade 4rom 1mp 1, Sal Hrade S 0*ere 1. Sal Bet0een S. 'osal 65D S.Tisal> 3. Se&8 - ":- Self 3 oin -oins a table to itself. Basically it is for t*e Tierarc*ical relations*ip. E;:- Select 1. 1 name, 1;. 1 name from 1mp 1, 1mp 1; 0*ere 1 . Mgr N 1;. 1M& no> Sy!);:- S)' Select /olumns 4rom +able +, +able +; 0*ere +. /olumn N +;. /olumn ;. 4. Ou!er - ":- uter oin combines t0o or more tables returning t*ose ro0s from one table t*at *ave no direct matc* in t*e ot*er table. E;:- Select 1. 1 name , D. Dept 5ame from 1mp 1, Dept D 0*ere e. dept no N D. Dept no@A> Sy!);:- Select +able. /olumn, +able;. /olumn from +able, +able; 0*ere +able. /olumn N +able;. /olumn @A>
42. W)! " Pr,e'ure Ty+e 8 Pr,e'ure
&rocedure8% &rocedure is a type of sub program t*at performs an actions. % &rocedure may return one or more values t*roug* parameters or may not return at all. BTree Ty+e 8 Pr,e'ure:-
@A. In 3 Mode8% It is read only value from calling environment. @;A. ut 3 Mode8% It is 0rite only value from calling environment. @A. In (ut3 Mode8% It is read and 0rite only value from calling environment. B S!re' F Pr,e'ure:- Stored 3 &rocedure is a sequence of statement t*at perform speci2c function.
43. W)! " %u,!" Ty+e 8 %u,!" 4unction8 % 4unction is al0ays returns a value using t*e return statement. B T6 Ty+e 8 %u,!":-
@ASingle Ro0 4unction8 % +*ese functions return a single result for every ro0 of a queried table or vie0. % Single ro0 function are used to manipulate data Items. @;AMulti Ro0 4unction8% +*ese functions manipulate group of ro0s and return one result per group of ro0s. % Multi ro0s function or aggregate functions are SUM> M6> MI5> 6 /U5+.
44. W)! " ASCII - %u,!" It returns t*e 6S/II representation in t*e c*aracter Database set of 4irst /*aracters of t*e /T6R. Synta78 % S)' S1'R/+ 6S/II @6$A, 6S/II @6&&'1$A 4RM DU6'>
45. G6 M)y !y+e 8 SQL " Or),&e +*ere are 2ve types of S)' in racle. @A. Data Retrieval statement8% % Select Statement. @;A. Data Manipulation 'anguage @DM'A statement8% % It is used to retrieve, store, modify, delete, insert and update data in database. % Insert> Update> Delete Statement.
@EA. Data De2nition language @DD'A statement8% % It is used to create and modify t*e structure of database ob-ects in database. % /reate> 6lter> Drop> Rename> +runcate Statement. @A. +ransaction /ontrol 'anguage @+/'A Statement8% % It is used to manage diFerent transaction occurring 0it*in a database. % /ommit> Rollbac> Save point Statement. @CA. Data /ontrol 'anguage @D/'A statement8% % It is used to create roles, permissions and referential integrity as 0ell it is used to control access to database by searc*ing it. % Hrant> Revoe Statements.
4. W)! " Se&e,!7 Ier!7 De&e!e = U+')!e S!)!ee! H Se&e,! S!)!ee!:- It is used to select data from a data base.
% +*e select statement is used to retrieve Data from ne or More, +ables> b-ect tables> b-ect Materialized vie0s. Sy!);8% select column ? name from table ? name
6nd8 % Select from table ? name E;8% select 2rst ? name from student ? details>
r Select name, age, salary from employee 0*ere age C#> H Ier! S!)!ee!:- It is used to add ne0 ro0s of data to a table. Sy!);8% Insert into table ? name @/olumn, /olumn;, /olumnEA E;8% Insert into employee @Id, 5ame, Dept, 6ge, Salary locationA H De&e!e S!)!ee!:- It is used to delete ro0s from a table. Sy!);8% Delete 4rom table 3 name W 0*ere conditionX H U+')!e S!)!ee!:- It is used to update e7isting records in a table. Sy!);8% Update table ? name set column N
4$. W)! " A&") Ty+e 8 A&") % 6liases are de2ned for columns and table. % 6lias is an 6lternate 5ame given for any racle b-ect. % 6lias in racle are t0o types8%
/olumn 6lias8 % /olumn 6lias renames a column *eading in a query. % 6lias *eading appears in upper casing by Default. +able 6lias8 % +able 6lias Renames t*e riginal name of t*e table in a S)' Statement. % +able 6lias are very important 0*en 0oring 0it* self -oins. % +*e +able 6lias is applied for t*e current S)' Statement only. Sy!);8% Select /olumn ? 5ame @sA from table ? name as alias ? name. E;8% Select 2rst ? name 6s name from Student ? details>
(&8% Ra*ul S*arma, Ra-nis* r, &riya /*andra, 6s*utos* mis*ara.
4(. W)! )re @ru+ #y7 Or'er y7 = )9"> #y
@ru+ y8% % +*e Hroup by clause is used along 0it* t*e group functions to retrieve data grouped according to one or more columns. 4or 178 % If U 0ant to no0 t*e total amount of salary spent on eac* department, t*e query 0ould be8% Synta78 % Select column, sum @/olumn ;A from Olist of tableP Hroup ByP /olumn 3 listP> 178% Select dept, sum @SalaryA from employee group by dept>
Or'er y8% +*e order by clause is used in a select statement to sort results eit*er in ascending or descending order. racle sorts query results in ascending order by default. 4or 178% If U 0ant to sort t*e employee table by salary of t*e 1mployee, t*e S)' query 0ould be.
178 % Select name, salary from employee order by salary.
G)9"> y8 % Taving clause is used to 2lter data based on t*e group functions. +*is is similar to 0*ere condition but is used 0it* group functions. Hroup functions cannot be used in 0*ere clause but can be used in *aving clause. 178 % Select dept, Sum @SalaryA from employee group by dept Taving Sum @SalaryA ;C###.
4*. W)! " 6ere ,&)ue % =*ere clause is used 0*en you 0ant to retrieve speci2c information from a table e7cluding ot*er irrelevant data. 178% +*e 0*ere clause is used to e7tract only t*ose records t*at ful2ll a speci2ed criterion. Synta78 % Select column ? name from table ? name 0*ere column ? name operator value. 178% Select from persons 0*ere city N &6+56$
5. W)! " O+er)!r Ty+e 8 O+er)!r 1. A' F O+er)!r: - 4or a ro0 to be selected all t*e speci2ed condition must be true.
178 % Select 1name, Sal, dept no, ob from 1M& 0*ere dept noN ;# 6nd ob N M656H1R$> 2. Or F O+er)!r: - 4or a ro0 to be selected at least one of t*e conditions must be true.
178 % Select 1nema, Sal, dept no, ob from 1M& 0*ere dept noN ;# r ob N M656H1R$> 3. N! F O+er)!r: - 4or a ro0 to be selected t*e speci2ed condition must be false.
178 % Select 1name, sal, ob from 1M& 0*ere 5ot ob N M656H1R$>
51. 6)! " I O+er)!r7 e!6ee )' O+er)!r = I Nu&& O+er)!r H I O+er)!r: - +*is operator can be used upon any data type.
% +*is perator is used to test for values in a speci2ed list. 178% Select 1name, Sal, -ob from 1mp 0*ere 1name I5 @4RD$,$6''15$A> H e!6ee A' O+er)!r: - +*is perator is used to display ro0s based on a range of value.
% +*e 'o0er 'imit s*ould be declared 2rst. 178% Select 1name, Sal, ob from 1mp =*ere sal Bet0een ### 65D C##> H I Nu&& O+er)!r:- +*is perator test for 5ull
% It is t*e only operator t*at can be used to test for 5ull$s. % +*e 5egation is Is 5ot 5ull. 178% Select 1name, Dept no, /omm 4rom 1mp =*ere /omm is 5ull.
52. W)! " L"?e O+er)!r % +*e lie perator is used to searc* for a matc*ing c*aracter patterns. % 'ie perator is used to list all ro0s in a table 0*ose column value matc* a speci2ed pattern @LA. % +*e /*aracter patterns matc*ing operation is referred as a 0ild card searc*.
178% Select 1name, ob, 4rom 1mp 0*ere 1name 'ie SL$ >
53. W)! " Sy')!e % It is a date function t*at returns current date and time. % Sysdate is generally selected upon a dummy table. 178% S)' Select Sysdate from Dual>
54. W)! " Se! O+er)!r Set perators are used to combine information of similar data type for one or more t*an one table. DiFerence types of set operator. . Union. ;. Union all. E. Interacted. . Minus.
55. W)! " Peu' F C&u % &seudo 3 /olumn is an act as a data base table column, but it$s not a actual column in a table. =e can fetc* values from a pseudo column. % &seudo 3 /olumn be*aves lie a table column, but is not actually stored in a table. E;8% User> User Id> Ro0 num> Ro0 Id> 'evel> 5e7t /urval> Sysdate
5. W)! " E;,e+!" Ty+e 8 e;,e+!" % 17ception is an identi2er in &'(S)' raised during t*e e7ecution of bloc. % Run time error is called e7ception. +*ree types of 17ception.
. &rede2ned8% It can be only *andle. ;. User 3 De2ned8% It can be declare, raise *andle. E. 5on 3 &rede2ned8% De2ne and Tandle.
5$. W)! " P),?)>e Ty+e 8 P),?)>e % &acage is a met*od to *andle all t*e required function and procedure toget*er. % &acage cannot be e7ecuted. % &acage cannot be nested. It consists of t0o parts8% A. &acage 3 Speci2cation8 % &.S contains information about t*e content of pacage and global K public variable. ;A. &acage 3 Body8 % &.B contains t*e actual e7ecutable code for t*e ob-ect describe in t*e pacage speci2cation.
5(. W)! " L"!er)& Ty+e 8 L"!er)& 'iterals and /onstants value are synonyms to one anot*er and refer to a 27ed data value. +ypes of 'iterals recognized by racle are8% A. +e7t 'iterals. ;A. Integer 'iterals. EA. 5umber 'iterals. A. Interval 'iterals. 178% 1mployee Information Manager$ s speci2cation 5$ 5c*ar 'iteral.
5*. W)! " Syy Ty+e 8 Syy
Synonyms is an alternative name for ob-ects suc* as +ables, 178 % S)' /reate Synonyms 1M& Info for Scott. 1M&> +ypes of Synonyms8% A. &rivate Synonym. ;A. &ublic Synonym.
. W)! " Mer>e S!)!ee! +*e Mere Statement is used to select ro0s from one or more sources for update or Insert into one or more table. 178% S)' /reate +able My Bonus @1mp no 5umber, Bonus 5umber Default ##A>
1. W)! " M)!er")&"e' V"e6 % 6 M.< is a data base ob-ect t*at contains t*e result of a query. % M.< are used in data 0are *ouses and data Marts. 178 % S)' Hrant )uery Re0rite +o S/++> S)' Hrant /reate Materialized vie0 +o S/++>
2. W)! " O#e,! T)#&e % b-ect +ables are created by using t*e user de2ned data types. % In b-ect table eac* ro0 or record is treated as an ob-ect. Synta78 % /reate or replace +ype J +ype 5ame as ob-ect @/olumn 5ame Data +ype @SizeA, /olumn 5ame ; Data +ype @SizeA, /olumn 5ame 5 Data +ype @SizeAA,
3. W)! " O#e,! V"e6 = I&"e V"e6 b-ect
4. D"
Synonym8 % Synonym is an alternate name for a base table or vie0. 1ac* user can assign *is or *er o0n synonyms for any table or vie0 t*at 0as created by some ot*er fully quali2ed name.
;A. Unique /onstraints. EA. &rimary Yey /onstraints. A. 4oreign Yey /onstraints. CA. /*ec /onstraints. VA. Default ( Ref /onstraints.
. W)! )re N! Nu&&7 U"0ue7 Pr")ry Jey7 %re"> Jey7 Ce,? C!r)"! )' De8)u&! +!" 5ot 5ull8% 6 5ot 5ull /onstraints pro*ibits a column from containing 5ull Unique8% Same as primary ey but can accept one null ey only. 178% S)' /reate table &romotions@ &romo ? Id 5umber@VA, &romo 5ame &rimary Yey8% Uniquely identify eac* entity in t*e table and does not contains null K duplicate values. 178% S)' /reate table 'ocations @'ocation Id 5umber @A, St 6ddress
4oreign Yey8% 6 4.Y is one or more column 0*ose value must e7it in t*e primary ey of anot*er table /*ec /onstraints8% It de2nes a condition t*at eac* ro0 Must satisfy. % +o satisfy t*e constraint, eac* ro0 in t*e table must mae t*e condition eit*er true or unno0n. Default ption8% +*e Default option is given to maintain a default value in a column. % +*e Default value can be literal, an e7pression or a S)' function. % +*e Default e7pression must matc* t*e data type of t*e column. /omposite ey8% +*e /ombination of t0o or more t*an t0o eys 0*ic* identi2es entities uniquely. Super Yey8 % 6ll possible ey 0*ic* can identify entities in t*e table uniquely. 6lternate Yey8% Rest of t*e condition ey 0*ic* is not t*e primary ey.
$. W)! " @ru+ 8u,!" Hroup function is built in S)' functions t*at operate on groups of ro0s and return one value for t*e entire group. +*ese functions are8 % /ount> Ma7> Min > 6vg > Sum> Distinct.
(. W)! " Cu! = D"!",!
/ount8% +*is function returns t*e number of ro0s in t*e table t*at satis2es t*e condition speci2ed in t*e 0*ere condition. 178 % If U 0ant t*e 5o of employees in a particular department, t*e query 0ould be8 % Select /ount @A from employee 0*ere dept N 1lectronics$>
Distinct8% +*is function is used to select t*e distinct ro0s. 178% If U 0ant to select all distinct department names from employee table, t*e query 0ould be8% Select Distinct dept 4rom employee> % +o get t*e count of employees 0it* unique name, t*e query 0ould be8% Select count @DIS+I5/+ 5ameA from employee>
*. W)! )re M"7 M);7 A9er)>e )' Su %u,!" Minimum @MI5A 4unction8% +*is function is used to get t*e minimum value from a column. 3 It ignores 5ull values. 178% +o get t*e minimum salary dra0n by an employee t*e query 0ould be8% % Select MI5 @SalaryA from employee. Ma7imum @M6A 4unction8% +*is function is used to get t*e ma7imum value from a column. It ignores 5ull values. 178% +o get t*e ma7imum salary dra0n by an employee t*e query 0ould be8% % Select M6 @SalaryA from employee. 6verage @6vgA 4unction8% +*is function is used to get t*e average value of a numeric column. % It ignores 5ull values. 178% +o get t*e average salary t*e query 0ould be 8% % Select 6vg @SalaryA from employee. Sum 4unction8% +*is function is used to get t*e sum of a numeric column. % It ignores 5ull values.
178% +o get t*e total salary given out to t*e employee t*e query 0ould be 8% % Select Sum @SalaryA from employee.
$. W)! " C9er" %u,!" Ty+e 8 C9er" %u,!" +*e /onversion function converts a value from one data type to anot*er. +0o types of /onversion function8% A. Implicit Data +ype /onversion8% Implicit Data +ype /onversion 0or according to t*e conversion speci2ed by oracle. % /T6R to D6+1 conversions succeed only if t*e c*aracter strings represent t*e default format of date. DD 3 M5 % GG. % <6R/T6R; ( /T6R 3 5UMB1R. %<6R/T6R; ( /T6R 3 D6+1. ;A. 17plicit Data +ype /onversion8% S)' &rovides t*ree functions to convert a value from one data type to anot*er. % +*e e7plicit conversion functions are8% . +o 3 /T6R 3 +o /*aracter /onversion. . +o 3 D6+1 3 +o D6+1 /onversion. . +o 3 5UMB1R 3 +o 5umber /onversion.
$1. W)! " A>>re>)!e r @ru+ 8u,!" % +*ese functions return a single ro0 based on group of ro0s. % +*ese functions can appear in select lists and *aving clauses only.
$2. W)! " S!)')r' 'e9")!"K Cu! %u,!" Standard deviation8%
% It returns t*e standard deviation of t*e column. % It ignores 5ull
/ount 4unction8% % It returns t*e number of ro0s in t*e query. % If $ is used returns all ro0s, Including duplicated and 5U''S. 178% Select /ount @A 4rom 1mp> % Select /ount @SalA, /ount @/ommA from 1mp>
$3. W)! " D)!e %u,!" 6dd Mont* 4unction8% % It returns t*e Date D$ plus or minus n$ Mont*s. % +*e argument n$ can be any positive or 5egative Integer. 178% S)' Select sysdate , 6dd% Mont*s @Sysdate,;A from Dual> Mont* bet0een 4unction8% % It returns number of mont*$s b(0 dates d$ and d;$ % If d$ is later t*an d;$, t*e result is 5e7t Day 4unction8% % It returns t*e date of t*e st 0ee day 5amed by /*ar, +*at is latter t*an t*e Dated$. % +*e c*ar must be a day of t*e 0ee in t*e sessions date language. 178% S)' Select sysdate , 5e7t ? Day @Sysdate, =1D$A from Dual> 'ast Day 4unction8% % It returns t*e date of t*e last day of t*e mont* t*at constants D$.
% Mostly used to determine *o0 many days are left in t*e current Mont*. 178 % S)l Select Sysdate, 'ast ? Day @SysdateA 'ast day from dual>
$4. W)! " Su# S!r">K I S!r"> %u,!" Sub String8% Returns speci2ed c*aracters from c*aracter value, Starting from a speci2ed position m$ to n$ c*aracters long. 178% Select 6B/D14HT$ string , SUBS+R @6B/D14HT$,E,A sub string from DU6'> In Sting 4unction8% It returns t*e 5umeric position of a named c*aracter. 178% Select S+RI5H$ String InS+R @S+RI5H$, R$A In string from Dual>
$5. W)! " LPADK RPAD %u,!" '&6D8% &ads t*e c*aracter value rig*t -usti2ed to a total 0idt* of n$ c*aracter positions. % +*e default padding c*aracter is space. Synta78% '&6D @/*ar, n, c*ar;$A 178% Select $page$ string '&6D @&age$, C, $A ' &added from Dual> R&6D8% &ads t*e c*aracter value left -usti2ed to a total 0idt* of n$ c*aracter positions. % +*e default padding c*aracter is space. Synta78% R&6D @/*ar, n, c*ar;$A 178% Select $page$ string R&6D @&age$, C, $A R &added from Dual>
$. W)! " LTRIMK RTRIM %u,!"
' +RIMS 4unction8% It enables to +RIM Teading c*aracters from a c*aracter string. % 6ll t*e leftmost c*aracters t*at appear in t*e set are removed. Synta78% ' +RIM @/*ar, S1+A 178% Select ob, ' +RIM @ob, M65$A ' +rimmed 4rom 1mp =*ere ob 'ie M656H1R$ R +RIMS 4unction8% 8% It enables t*e trimming of trailing c*aracters from a c*aracter string. % 6ll t*e Rig*t most c*aracters t*at appear in t*e set are removed. Synta78% R +RIM @/*ar, S1+A 178% Select R +RIM @ob, 1R$A, ob 4rom 1mp =*ere ' +RIM @B, M65$A 'IY1 H1R$>
$$. W)! " TRIMK REPLACEK Tr)&)!e )' CGR %u,!" +RIM 4unction8% It enables to trim Teading or trailing c*aracters or bot* from a c*aracter string. % If 'eading is speci2ed concentrates on leading c*aracters. % If +railing is speci2ed concentrates on +railing c*aracters. Synta78% +rim @'eading ( +railing( Bot*, +rim c*ar 4rom +rim SourceA 178% Select MI+TSS$ String +RIM @S$ 4rom$ MI+TSS A +rimmed 4rom Dual>
R1&'6/1 4unction8% It returns t*e every occurrence of searc* string replaced by t*e replacement string.
% It substitutes one string for anot*er as 0ell as removes c*aracter strings. Synta78% Replace @/*ar, Searc* ? Str, Replace ? StrA 178% Select 1 name, Replace @B, M65$, D6M$A Replaced from 1mp 0*ere ob N M656H1R$ > +ranslate 4unction8 % Used to translate c*aracter by c*aracter in a string. Synta78 % +ranslate @/*ar, 4rom, +oA 178 % Select ob, +R65S'6+1 @ob, &$, A 4rom 1mp 0*ere ob N &R1SID15+> /T6R 4unction8 % It returns a c*aracter *aving t*e 6S/II 1quivalent to n$. % It returns t*e 1quivalent for n$ in data base c*aracter set or 5ational /*aracter set. Synta78 % /TR @n USI5H 5/T6R ? /SA 178 % Select /TR @V"AQQ /TR @VCAQQ /TR @ZA Sample 4rom Dual> $(. W)! " OLAPK ROLLUP
'6&8% Some features for query processing in R6/'1 include t*e use of 5'I51 656'G+I/6' &R/1SSI5H @'6&A Upon t*e Data base. % '6& features are useful for Data 0are*ousing and data mart applications. % '6& operations are performance en*ancements. +& ? 5 )U1RI1S. HRU& By. /UB1.
R''U&8% It is a group by operation and is used to produce subtotals at any level of t*e aggregation. % +*e generated sub +otals ORolled UpP to produce Hrant total. Synta78% Hroup by rollup @/olumn , /olumn ;A 178% Select Dept no, SUM @S6'A from 1mp group by Rollup @Dept noA> $*. W)! )re U"K U" A&&K I!ere,! )' Meu
Union8% Returns t*e combined ro0s from t0o queries, sorting t*en and removing duplicates. 178% Select ob 4rom 1M& 0*ere Dept 5o N ;#> Union % Select region ? name from regions Union select region ? name from regions> Union 6ll8% Returns t*e combined ro0s from t0o queries, 0it*out sorting or removing duplicates. 178% Select ob 4rom 1M& 0*ere Dept 5o N E#> Union 6ll % Select region ? name from regions Union all select region ? name from regions> Intersect8% Returns only t*e ro0s t*at occur in bot* queries, result sets, sorting t*en and removing duplicates. 178% Select ob 4rom 1M& 0*ere Dept 5o N E#> Intersect % Select region ? name from regions Intersect select region ? name from regions> Menu8% Returns only t*e ro0s in t*e 2rst result set t*at do not appear in t*e second result set , sorting t*en and removing duplicates. 178% Select ob 4rom 1M& 0*ere Dept 5o N E#> Menu
% Select region ? name from regions Menu select region ? name from regions>
(. W)! " D)!) Ty+e Ty+e 8 D)!) Ty+e % +*e Data +ype associates a 27ed set of properties 0it* t*at value stored. % +*e Data type de2nes t*e domain of values t*at eac* column can contain. % +*e
% +*e data is stored in strings 0it* Byte collections as values. % racle supports bot* single K multi byte c*aracter sets. @A. /T6R Data %+ype8% +*e Size s*ould be speci2ed. % It speci2es 27ed lengt* c*aracter strings. % +*e Default lengt* is Byte and t*e ma7imum is ;### Bytes. % +*e size of a c*aracter can range from Byte to Bytes depending on t*e database c*aracter set.
@;A. 5 /T6R Data %+ype8% Ma7imum lengt* is determined by t*e 5ational c*aracter set de2nition. % Minimum size is ;###Bytes and size *as to be speci2ed. % 4irst time de2ned in R6/'1 !i and contains Unicode data only. @EA. <6R /T6R; % Data +ype8% Speci2es t*e variable lengt* c*aracter string. % Minimum size is Byte and t*e ma7imum size is ### Bytes. % It occupies only t*at space for 0*ic* t*e data is supplied. @A. 5 <6R /T6R; % Data +ype8% 4irst time de2ned in R6/'1 !i. % It is de2ned for Unicode data only. % +*e minimum size is Byte and ma7imum size is ### Bytes. @CA. 5umeric % Data +ype8% 2. Nu#er D)!) !y+e:- It stores zero, positive and negative 27ed and 9oating point number.
% +*e precision can range from to EZ t*e scale can range from Z to ;". % +*e range of magnitude is8% .# # % E# to !.! [ ! # ;C. B . %&)! D)!) Ty+e:- It facilitates to *ave a decimal points any0*ere from t*e 2rst to t*e last digit or can *ave no decimal point at all.
W4loating 3 point number 0it* precision of ;V binary @or EZ decimalA.X 3. L> = R)6 D)!) !y+e:- +*is data type stores variable lengt* c*aracter strings.
% It is used to store very lengt* +e7t strings.
% +*e lengt* of 'ong
% +*e Information revealed by date is. % /entury, Gear, Mont*, Date, Tour, Minute, Second. % +*is is eit*er lengt* zero, if t*e column is empty or " bytes. B. T"e !)+ D)!) Ty+e:- It is an e7tension of t*e D6+1 Data +ype.
% It stores t*e data in t*e form of % /entury, Gear, Mont*, Date, Tour, Minute, Second. % W+*is is lengt* zero if t*e column is empty or up to bytes.X B. R)6 = L> R)6 D)!) Ty+e:- Ra0 K 'ong Ra0 Data +ypes are intended for storage of binary data or byte strings.
% Ro0 and 'ong ra0 are variable lengt* data types. % +*e racle converts t*e Ra0 K 'ong ra0 data into Te7adecimal form. % W e O#e,! D)!) !y+e:- +*e Built in 'B data types are8%
B'B /'B 5/'B % +*ese data types store t*e Data Internally. %+*e 'B Data types can store large and unstructured data lie +e7t, Image,
/'B Data +ype8% It Stores single byte and multi byte c*aracter data. 5/'B Data +ype8% It Stores Unicode data using t*e 5ational c*aracter set. . R6 I' D)!) Ty+e:- 1ac* ro0 in t*e data base *as an address.
% +*e ro0s address can be queried using t*e pseudo column R= ID. % W6 value coded in base V t*at is t*e pointer to t*e location of a ro0 in a table.X
(2. W)! " Crre&)!e' u#0uery Ans:- A correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from outer query. The subquery is evaluated once for each row rocessed by the outer query.
!"
A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.
S1'1/+ =T1R1
p.product?name p.product?id
N
4RM
@S1'1/+
product
o.product?id
p 4RM
order?items =T1R1 o.product?id N p.product?idA>
(3. W)! " @u"'e&"e ! Ue @.%
o
