SQL Interview Questions

Editorial / Best Answer

Answered by: Sushant

The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

Editorial / Best Answer

Answered by: muralidhar_batta

View all answers by muralidhar_batta

Member Since Feb-2006 | Answered On : Feb 26th, 2006

varchar means fixed length character data(size) ie., min size-1 and max-2000

where as varchar2 means variable length character data ie., min-1 to max-4000

Editorial / Best Answer

Answered by: nirmal1in

View all questions by nirmal1in   View all answers by nirmal1in

Member Since Jan-2010 | Answered On : Jan 16th, 2010

This can be accomplished in following way:

Example: table - emp

For First n rows:

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a < 5 --- say n is 5 display first 5 records

For last n rows:

This query can be used for finding nth row also say n = 5

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a = 5

Editorial / Best Answer

Answered by: MANAS KUMAR ROUT ( USA ) (TCS) (Eli LILLY ) - PFIZ

Hey Ramesh,

I understand your interviewer's enthusiasm to know about the Foreign Key.  See foreign key definitions says its states the relationship of one attribute(Column) of one table to the corresponding column on the other table.

So one os a parent and other is child. PARENT is that table which holds the primary key and CHILD is table which holds the referential key. Hence every entry in the PRIMARY table is unique where as same entry might be repeating in the child table. BUT that same has to be present in the PARENT table (Primary KEY).

Now Going by definition Foreign key can be of two types :
========================================
1.  ON DELETE SET NULL
2.  ON DELETE SET CASCADE

DETAILS:
=======
On delete set null :
=======
When a foreign key is created by on delete set null definition then when you delete one row from the primary column (of parent table ) , then the corresponding entry in the foreign key table (Child table ) have the value "NULL" for that particular column.


ON Delete Set Cascade:
================
When the foreign key is created by this definition then when you delete the primary column (any one row -unique data) , then the Child table forcefully deletes all the rows in the child table having same value for that particular column.


I hope u got the crux of the Foreign key
Thanks
Manas Kumar Rout
SAS Consultant
Oracle Certified Associate
Unix Consultant

Editorial / Best Answer

Answered by: svsraju81

View all answers by svsraju81

Member Since Jul-2008 | Answered On : Jul 24th, 2008

Dual table is a temporary table, it contains a single row and single column. If we return a data from dual table it gives a single row.

Editorial / Best Answer

Answered by: biswaranjanbehera

View all answers by biswaranjanbehera

Member Since Jul-2008 | Answered On : Aug 6th, 2008

We can select the recent row enrtered by using the query below.
suppose the table name is employee;
then.
select * from employee where rowid=(select max(rowid) from employee).
This works fine because rowid is assigned for each and every record and in accending order. So the recent record's rowid is the man (rowid). Please send feedback with any concern.