SQL*Plus interview Questions
What is the maximum number of triggers, can apply to a single table
Depends on maximum number objects in the database.
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)
Read Best Answer
Editorial / Best Answer
Answered by: Zimmy
Answered On : Jul 9th, 2005ROWID is the psedo columns indicate the stored location of the data physically in the database.
but according to you it has 16 digits ... and according to my oracle 10g it is showing 18 digits. like AAAC9EAAEAAAABXAAA .. why??
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
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.
Different types of joins
Natural join
Inner join
outer join
cross join
equi join
non equi join
self join
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
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...
Read Best Answer
Editorial / Best Answer
Answered by: Prangya Sahu
Answered On : Nov 21st, 20051>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.
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
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
In view also update, delete, insert fuctions possible only if it satisfies view condition.
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?
You can add 1024 column in the one table.
Hi guys I am new to Oracle. Thank you for sharing your knowledge.
What is cycle/no cycle in a sequence
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...
Sequence is SHOULD start from MINVLUE as per common logic. BUT it goes back to 1. anyone tried that ? I DID
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 ?
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...
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.
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
Answer posted by scott on 2005-05-25 18:43:29: select rownum, rowid from sometable
Common Pseudo columns are CURRVAL,NEXTVAL,ROWID,ROWNUM,SYSDATE etc.
pseudocolumns are uid,user,sysdate,rownum,rowid,level,rowscn
What is difference between char and varchar2
Read Best Answer
Editorial / Best Answer
Answered by: srinivasan
Answered On : Aug 16th, 2005A 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.
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...
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
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
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
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...
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
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.
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...
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...
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
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, ...
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
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.
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...
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
Read Best Answer
Editorial / Best Answer
Answered by: rajesh
Answered On : Sep 14th, 2005| binary | Bigint | bit | Char | datetime |
| decimal | Float | image | Int | Money |
| nchar | Ntext | nvarchar | Numeric | Real |
| smalldatetime | smallint | smallmoney | sql_variant | sysname |
| text | timestamp | tinyint | varbinary | varchar |
| uniqueidentifier |
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...
| binary | Bigint | bit | Char | datetime |
| decimal | Float | image | Int | Money |
| nchar | Ntext | nvarchar | Numeric | Real |
| smalldatetime | smallint | smallmoney | sql_variant | sysname |
| text | timestamp | tinyint | varbinary | varchar |
| uniqueidentifier |
Read Best Answer
Editorial / Best Answer
Answered by: bharath
Answered On : May 30th, 2005when 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.
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.
its not with the data of the table, rather with the associated constraints of the table
What is an integrity constraint
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.
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...
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...
User Panel
Categories
Interview Coaching
Ads
Connect
Ads
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.
NH
views are created by without base tables is called force views...
The function of force in a view is to create a view forcefully witout the existence of a Schema