GeekInterview.com
Series: Subject: Topic:

SQL*Plus interview Questions

Showing Questions 1 - 20 of 133 Questions
First | Prev | | Next | Last Page
Sort by: 
 | 

What is the function of 'force' in view?

Asked By: michelle | Asked On: Mar 17th, 2006

Answered by: murali on: Apr 18th, 2013

views are created by without base tables is called force views...

Answered by: Mayank Ahuja on: Nov 1st, 2011

The function of force in a view is to create a view forcefully witout the existence of a Schema

What is the maximum number of triggers, can apply to a single table

Asked By: Interview Candidate | Asked On: Aug 25th, 2005

Answered by: shirish on: Apr 2nd, 2013

Depends on maximum number objects in the database.

Answered by: Navneet Jha on: Jan 9th, 2013

You can create N numbers of triggers on a table but the maximum possible types trigger can be applied is 3*2*2=12

(Insert/Update/Delete= 3, Before/After= 2, Row Level/Statement Level=2)

What is rowid

Asked By: Interview Candidate | Asked On: Jul 9th, 2005

Star Read Best Answer

Editorial / Best Answer

Answered by: Zimmy

Answered On : Jul 9th, 2005

ROWID is the psedo columns indicate the stored location of the data physically in the database.

Answered by: vikas on: Dec 21st, 2012

but according to you it has 16 digits ... and according to my oracle 10g it is showing 18 digits. like AAAC9EAAEAAAABXAAA .. why??

Answered by: Ravi Gajula on: Jun 17th, 2009

The Address Field of the index is called ROW ID

Format: BBBBBBB.RRRR.FFFF
FFFF: Unique Number given by oracle engine to each Data file
BBBBBBB: each Data File is further divided in Data Blocks
RRRR: Data Blocks can store one or more Records

Explain the different types of joins

Asked By: Interview Candidate | Asked On: Sep 9th, 2005

Star Read Best Answer

Editorial / Best Answer

Answered by: krishna420

View all answers by krishna420

Member Since Jul-2008 | Answered On : Jul 23rd, 2008

There are 5 types of joins available in sql server

1) inner join
2) outer join( left outer join,Right outer join)
3) Full Join
4) Cross Join
5) Self Join

1) inner Join : Two tables having the common column values go with inner join.

Example:
select * from emp inner join empsalary
on
emp.empid=empsalary.empid

2) outer join:
Outer join has two sub types left outer join,Right outer join
Left outer join: Taking Left side values of the table checking into right side
select * from emp left outer join empsalary
on emp.empid=empsalary.empid
Right Outer join:Taking Right side values of the table checking left side
select * from emp right outer join empsalary
on emp.empid=empsalary.empid

3) Full join :
Full join is combined with left outer join ,Right outer join.
select * from emp full join employee
on emp.empid=employee.empid

4) cross Join: cross join is an carteasion product ,its like matrix format here it wont come on condidtion

select * from emp cross join employee

Self join :

Self join can check within the table called self join.

Answered by: zunaid on: Oct 12th, 2012

Different types of joins

Natural join
Inner join
outer join
cross join
equi join
non equi join
self join

Answered by: vamsi krishna devineni on: Nov 27th, 2011

Mainly we uses some joins in the real time environment.. that is 1) Inner join 2) left outer join 3) right outer join 4) full outer join 5) cross join i will explain in easy way so that u can underst...

What is difference between truncate & delete

Asked By: Interview Candidate | Asked On: Sep 7th, 2005

Answer posted by scott on 2005-05-25 18:30:04: truncate is a DDL command and cannot be rolled back. All of the memory space is released back to the server. delete is a dml command and can be rolled back.  both commands accomplish identical tasks (removing all data from a table), but truncate...

Star Read Best Answer

Editorial / Best Answer

Answered by: Prangya Sahu

Answered On : Nov 21st, 2005

1>TRUNCATE is a DDL command whereas DELETE is a DML command.

2>TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.

Answered by: saravanan on: Sep 18th, 2012

delete & truncate command will delete entries from a table.. where as we can delete a single entry using delete but incase of truncate it wont be possible..

using truncate will delete the log files,buffer storage in a table permanently but incase of delete it wont

Answered by: ch.v.sambasivarao on: Jul 10th, 2012

TRUNCATE- truncate is a DDl command,it deletes the records permanently AND it cannot delete specific record.

DELETE- delete is DML command,it delete the records temporarily AND get it the record by using command called rollback,it can delete specific record.

Difference between view and table

Asked By: sbagai2001 | Asked On: May 31st, 2006

Answered by: Raj sr on: Sep 8th, 2012

In view also update, delete, insert fuctions possible only if it satisfies view condition.

Answered by: Shiv Bhanu Singh on: Oct 13th, 2011

View is a virtual table which do not have any physical existence but table is stored in the database. Tables have physical existence. Views are associated with tables.

How many autonumber columns are allowed?

Asked By: naeem | Asked On: Jun 14th, 2006

Answered by: shailendra on: Aug 22nd, 2012

You can add 1024 column in the one table.

Answered by: vik_ora on: Apr 17th, 2008

Hi guys I am new to Oracle. Thank you for sharing your knowledge.

What is cycle/no cycle in a sequence

Asked By: Interview Candidate | Asked On: May 25th, 2005

Answer posted by scott on 2005-05-25 18:39:48: when you create a sequence with cycle option, you are saying that when the sequence reaches its maxvalue, it will start over at the minvalue. This is not wise if using the sequence for primary key creation.  when you create a sequence with nocycle...

Answered by: deepanath on: Aug 15th, 2012

Sequence is SHOULD start from MINVLUE as per common logic. BUT it goes back to 1. anyone tried that ? I DID

Answered by: kiranapps on: Jun 24th, 2010

no cycle: The sequence cannot generate more values after the max value is reached.cycle: The sequence can generate more values after the max value is reachedIf max value is 1000 then if cycle is ...

What is a join ? Explain the different types of joins ?

Asked By: Interview Candidate | Asked On: Sep 7th, 2004

Join is a query which retrieves related columns or rows from multiple tables.Self join - joining the table with itself.Equi join - joining two tables by equating two common columns.Non-equi join - joining two tables by equating two common columns.Outer join - joining two tables in such a way...

Answered by: swapna on: Mar 16th, 2012

Inner joins returns all rows from both tables where there is a match whereas outer join is used in the situations where it is desired to select all rows from the table on the left.

Answered by: hiranmayee on: Sep 9th, 2008

In Full Outer Join we get both matched and unmatched data from both the tables
whereas Cartesian join is a join in which each row of one table is cross multiplied with each row of the second table

What are the more common pseudo-columns

Asked By: Interview Candidate | Asked On: Aug 24th, 2005

Answer posted by scott on 2005-05-25 18:43:29: select rownum, rowid from sometable

Answered by: Ankush Gupta on: Feb 10th, 2012

Common Pseudo columns are CURRVAL,NEXTVAL,ROWID,ROWNUM,SYSDATE etc.

Answered by: laxman on: Oct 9th, 2011

pseudocolumns are uid,user,sysdate,rownum,rowid,level,rowscn

What is difference between char and varchar2

Asked By: Interview Candidate | Asked On: Aug 16th, 2005

Star Read Best Answer

Editorial / Best Answer

Answered by: srinivasan

Answered On : Aug 16th, 2005

A varchar2 datatype, when stored in a database table, uses only the space  
allocated to it. If you have a varchar2(1999) and put 50 bytes in the table, we  
will use 52 bytes (leading length byte). 
 
A char datatype, when stored in a database table, always uses the maximum length  
and is blank padded. If you have char(1999) and put 50 bytes into it, it will  
consume 2001 bytes (leading length field is present on char's as well). 
 
In the database -- a CHAR is a VARCHAR that is blank padded to its maximum  
length. 

Answered by: AISHWARYA on: Jan 24th, 2012

char : In char we need to specify the character length before using it ,no further changes can be done in char length and if charlength s not up to the specified length then rest of the spaces will be...

Answered by: srinivasan on: Aug 16th, 2005

A varchar2 datatype, when stored in a database table, uses only the space  allocated to it. If you have a varchar2(1999) and put 50 bytes in the table, we  will use 52 bytes (leading length...

Difference between a view and materialized view

Asked By: shravanamsharat | Asked On: Jun 1st, 2006

Answered by: SURESH REDDI on: Aug 19th, 2011

View is a logical representation of data--- m.view it is physical duplicate representation of data
view dont holds data---m.view holds data
in view for every event on base tables view automatically update immediately---- in m.v we can update for a certain period of time


Answered by: selvaspak on: Dec 31st, 2010

A view is nothing but a SQL query, takes the output of a query and makes it appear like a virtual table, which does not take up any storage space or contain any dataBut Materialized views are schema o...

What is ref cursor when we use ref cursor andadvantage of ref cursor

Asked By: shankar rao | Asked On: Jun 10th, 2006

Answered by: suresh reddi on: Aug 19th, 2011

Using ref cursor we can pass different values( i.e by changing parameters) to the cursor which is declared in the package specification from the package body. Ref cursors are of 2 types a) weak r...

Answered by: sandip01 on: May 25th, 2010

Ref cursor is generally used to return value to client side programme.
The advantage of ref cursor is it can associate with different quries at runtime.

What is referential integrity constraint

Asked By: Interview Candidate | Asked On: Jul 15th, 2005

Star Read Best Answer

Editorial / Best Answer

Answered by: Shivani Goel

View all questions by Shivani Goel   View all answers by Shivani Goel

Member Since Jun-2009 | Answered On : Jun 23rd, 2009

Referential integrity constraint is that which depend upon the parent and child relationship.  In this one of the column have the primary key constraint and one of the column of another table have the foreign key constraint. So you cannot delete the column value which having the foreign key constraint until you never delete its related primary key column value from parent table. If you tried to do this it will show you error related to referential integrity constraint.   But if you still want to delete value from child table but never want to delete parent table column value then that purpose you can use 'on delete cascade' option.

Answered by: kierthi on: Aug 16th, 2011

A feature provided by relational database management systems (RDBMS's) that prevents users or applications from entering inconsistent data. Most RDBMS's have various referential integrity rules that y...

Answered by: Shivani Goel on: Jun 23rd, 2009

Referential integrity constraint is that which depend upon the parent and child relationship.  In this one of the column have the primary key constraint and one of the column of another table h...

Query date prompt in excel

Asked By: pamschertz | Asked On: Jun 15th, 2011

Have a ms excel 2003 query that works tied to an excel spreadhseet. Want to know how I can have it ask in excel for a date, where the date will go into the query. Like a prompt asking through date: ???? And you can enter the date, or even if a way you could make the query search automatically enter the...

How to get /select the nth row from the table ?How to select first n rows ,last n rows from a table

Asked By: muthukumar | Asked On: Aug 16th, 2006

Answered by: sandip.dhopat on: Jun 9th, 2011

Select nth Row:select * from(select t1.c1, t1.c2, .... , rank(col_name) over (order by col_name) rnkfrom table_name t1)where rnk = n;To select top n rows in a order.select * from(select t1.c1, t1.c2, ...

Answered by: p.mamta on: Aug 30th, 2010

How to get the nth row from the table ?select distinct(Salary),* from Employee A where n=(select count(distinct(Salary)) from Employee b where b.Salary <= a.Salary);how to  select  first ...

How many long columns are allowed in a table

Asked By: Interview Candidate | Asked On: Jul 16th, 2005

Star Read Best Answer

Editorial / Best Answer

Answered by: Gayathri Panchangam

View all answers by Gayathri Panchangam

Member Since Aug-2008 | Answered On : Sep 5th, 2008

Only one column can have LONG data type. And that column cannot be used in WHERE clause or in ORDER BY clause. Also we cannot add primary key constraint to that column only not to the whole table, that means we will give primary key to any column in the table except the column having the long data type. If we give, we get the error ORA-02269: key column cannot be of LONG datatype.

Answered by: Gayathri Panchangam on: Sep 5th, 2008

Only one column can have LONG data type. And that column cannot be used in WHERE clause or in ORDER BY clause. Also we cannot add primary key constraint to that column only not to the whole table, tha...

Answered by: Nikhil_4_Oracle on: Mar 5th, 2007



Hey all,
 
 You can have only one column of long in table , And this table can`t have 
 Primary key Constraint.
             --Nikhil 

What are the data types allowed in a table

Asked By: Interview Candidate | Asked On: Aug 26th, 2004

Star Read Best Answer

Editorial / Best Answer

Answered by: rajesh

Answered On : Sep 14th, 2005

binaryBigintbitChardatetime
decimalFloatimageIntMoney
ncharNtextnvarcharNumericReal
smalldatetimesmallintsmallmoneysql_variantsysname
texttimestamptinyintvarbinaryvarchar
uniqueidentifier
these are the data types used in sql table

Answered by: gdurga on: Jun 15th, 2009

Dec(p,s), desimal(p,s), double precision, foat, int, inteager, real, smallint, numeric(p,s), number(p,s), char(size), varchar2(size), long, raw, long raw, date, timestamp, urowid(size), rowid, boolean, nchar(size) , nvarchar2(size), bfile, blob, clob, nclob...

Answered by: rajesh on: Sep 14th, 2005

binaryBigintbitChardatetime
decimalFloatimageIntMoney
ncharNtextnvarcharNumericReal
smalldatetimesmallintsmallmoneysql_variantsysname
texttimestamptinyintvarbinaryvarchar
uniqueidentifier
these are the data types used in sql table

What is on delete cascade

Asked By: Interview Candidate | Asked On: May 30th, 2005

Star Read Best Answer

Editorial / Best Answer

Answered by: bharath

Answered On : May 30th, 2005

when the user deletes a record in the master table , all the corresponding records in the detail table along with the records in the master table will be deleted.

Answered by: G.D.S.Prasad on: Jun 21st, 2008

ON DELETE CASCADE option permits deletions of referenced key values in the parent table and automatically deletes dependent rows in the child table to maintain referential integrity.

Answered by: deva on: Sep 20th, 2006

its not with the data of the table, rather with the associated constraints of the table

What is an integrity constraint

Asked By: Interview Candidate | Asked On: Aug 9th, 2005

Star Read Best Answer

Editorial / Best Answer

Answered by: Shivani Goel

View all questions by Shivani Goel   View all answers by Shivani Goel

Member Since Jun-2009 | Answered On : Jun 23rd, 2009

Integrity constraint is that which depend upon the parent and child relationship in which parent column have the primary constraint and child column have the foreign key constraint.
You cannot delete the any value from column that have foreign key constraint until you never delete its related primary key column value, that is a integrity constraint.
If you try to do this it will show you error related to integrity constraint.

Answered by: Shivani Goel on: Jun 23rd, 2009

Integrity constraint is that which depend upon the parent and child relationship in which parent column have the primary constraint and child column have the foreign key constraint. You cannot d...

Answered by: Rossitza Kanovska on: Aug 9th, 2005

An integrity constraint is a rule that restricts the values in a database. There are six types: A NOT NULL constraint prohibits a database value from being null.  A unique constraint prohibi...

First | Prev | | Next | Last Page

 

 

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Ads

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.