Sub query is a query whose return values are used in filtering conditions of the main query.

globalanil18

 Profile Answers by globalanil18 Questions by globalanil18

• Mar 31st, 2007

Hi Guys,A subquery is a SELECT statement that is embedded in a clause of another SQL statement, Called the parent statement.The subquery (inner query) returns a value that is used by the parent statem...

SaratKumar Puranam

• Apr 10th, 2006

Subquery is the one which is executed once for the entire parent statement.

Answer Question  Select Best Answer

Correlated sub query is a sub query which has reference to the main query.

globalanil18

 Profile Answers by globalanil18 Questions by globalanil18

• Mar 31st, 2007

Hi Guys,The Oracle Server performs a correlated subquery when the subquery references a column from a table reffered to in the parent statement. A correlated subquery is evaluated once for each row pr...

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 7th, 2006

when a sub query executes for each row its called a corelated sub query, in other words when a sub query takes the input from outer query for executation then execute and passes the result to outer query then outer query executes.

Answer Question  Select Best Answer

globalanil18

 Profile Answers by globalanil18 Questions by globalanil18

• Mar 31st, 2007

Hi Guys,A subquery is a SELECT statement that is embedded in a clause of another SQL statement, Called the parent statement.The subquery (inner query) returns a value that is used by the parent statem...

SaratKumar Puranam

• Oct 10th, 2005

Subqueries will be executed once for the entire parent statement.

Answer Question  Select Best Answer

nabajit_oracle

• Mar 5th, 2007

Commit to like making all the changes permanently in the Server...But in case of GO statement which is available In SQL Server(TSQL), the set of steps before the GO statement is complied at once and s...

Varun Dixit

• Nov 10th, 2006

Hi Shweta,My mail id is varun.dixit@india.birlasoft.com

Answer Question  Select Best Answer

Nikhil_4_Oracle

• Mar 1st, 2007

--This code will insert a image into database   create or replace PROCEDURE INSERT_BLOB(filE_namE IN VARCHAR2,dir_name varchar2)IStmp number;f_lob     bfile;b_lob &n...

Answer Question  Select Best Answer

Murugavel

• Feb 6th, 2007

Only one long col allowed in a table.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

NO, Long column can not used in where, order by clause A table can have only one long column

Answer Question  Select Best Answer

raghav_sy

 Profile Answers by raghav_sy Questions by raghav_sy

• Jan 24th, 2007

hi,some additions to Ram pratap answer:There are actually two types of triggers: DATABASE and APPLICATION triggers.Database triggers are of three types:1)DML trigeers: we can have a total of 12 DML t...

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 4th, 2006

cascading is used where primary/foreign key relation:by using cascade we can delete child record or can be set to null or default values

Answer Question  Select Best Answer

iasritesh

 Profile Answers by iasritesh Questions by iasritesh

• Jan 15th, 2007

Theta Joins  Theta joins are joins that use any of the comparison operators: Greater than, denoted by > Greater than or equal to, denoted by >= Less than, denoted by < Less than or ...

Answer Question  Select Best Answer

pratima

• Dec 26th, 2006

Hi Rampratap, U r telling abt the efficiency of view @ mutating table concept. can u pls explain it with an example?Thanks Pratima

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 7th, 2006

The advantages of View are:security:  only selected column from a table made available to view.Complaxity: write a complex query and assign this query to a view so next time no need to write that...

Answer Question  Select Best Answer

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

select a.*, level from emp astart with mgr is nullconnect by mgr = prior empno;

Atal

 Profile Answers by Atal Questions by Atal

• Sep 16th, 2005

 The start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts).

Answer Question  Select Best Answer

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

NVLselect empno, ename, mgr, nvl(to_char(mgr),'no manager') from emp;

koi.milgaya

• Jul 10th, 2005

NVL or Decode

Answer Question  Select Best Answer

ROWID is a pseudo column attached to each row of a table. It is 18 character long, blockno, rownumber are the components of ROWID.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

rowid is a unique value assigned by system automatically when ever a insert statement gets successful.rowid contains the address of data file,data block, object id, rownumber

tarun1282

 Profile Answers by tarun1282 Questions by tarun1282

• Apr 27th, 2006

Rowid is a uniquely identified number to each row which is given by the oracle server automatically

Answer Question  Select Best Answer

CYCLE specifies that the sequence continues to generate values after reaching either maximum or minimum value. After pan ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum...

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

No cycle:  after reaching to max val sequence will not give nextvalcycle     after reaching to max val sequence will restart with startwith option vlues

tarun1282

 Profile Answers by tarun1282 Questions by tarun1282

• Apr 27th, 2006

In a sequence if we use cycle:- the sequence will repeat after the last sequence number with the first sequence number.in a sequence if we use no cycle:-the sequence will not repeat and continue to upward state.

Answer Question  Select Best Answer

The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE constraint/DISABLE constraint.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

Alter table modify constraint disable/enable

samiksc

 Profile Answers by samiksc Questions by samiksc

• Jan 19th, 2006

Alter table disable constraint unique(col1)

Answer Question  Select Best Answer

The integrity constraints are stored in USER_CONSTRAINTS.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

User_constraints, dba_constraints, all_constraints

Answer Question  Select Best Answer

Only one LONG columns is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

Only one LONG columns is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause

Answer Question  Select Best Answer

CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW,BLOB, CLOB,NCLOBFOR ORACEL 9I AND ABOVE.TIMESTAMP, TIMESTAMP WITH TIME ZONE, YEAR TO DAY, DAY TO SECOND

Answer Question  Select Best Answer

SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

Savepoint is used to give some break points between multiple transactions so that if some transaction is required to be rolledback then some of transaction will still available in buffer. commit will save the data and remove all savepoints

Answer Question  Select Best Answer

Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

Foreign key constraints is know as referential integrity. which refers to a primary or ( unique ,not null) column

Answer Question  Select Best Answer

INTERSECT returns all distinct rows selected by both queries.MINUS - returns all distinct rows selected by the first query but not by the second.UNION - returns all distinct rows selected by either queryUNION ALL - returns all rows selected by either query, including all duplicates.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

union:  distinct result set of two queries.union all : result set of two or more queries including duplicatesminus: result set of first query  "-" resultset of second queryintersect : common result set of both query;

Answer Question  Select Best Answer