Sql/plsql Tought Interview Questions

Experts post the tough sql/plsql interview questions with answers

okay, i’ve got a couple of tough ones, i will post them and you can try to answer them as practice

  1. what is the main reason why people post the following message on database discussion forums: “how do i delete all but one of the duplicate records in my table?”

  2. what would you do if you were told you could not use an auto_increment as your primary key?

  3. what’s the difference between a table scan and an index scan?

  4. suppose you had a foreign key that was NULL – is this wrong or not, and why?

  1. if auto_increment was a randon, unique number generator instead of a sequential number generator, would this be a hindrance to the way you write applications?

  2. if you had to choose between table locks and transactions, which would you choose?

foriegn key can set null only when the parent table is deleted

actually, a foreign can can be null if the application requires it

consider this –

create table prisons
( id integer not null primary key
, name varchar(37)
);
insert into prisons values
 (1,'Sing Sing') , (2,'Attica') , (3,'Leavenworth');

create table sitepoint_members
( id integer not null primary key
, name varchar(37)
, last_prison_attended integer [b]null[/b]
, foreign key member_prison (last_prison_attended)
       references prisons (id)
);

you definitely want NULL there, right? or are you saying that each new row in sitepoint_members must have a non-null value for the last_prison_attended column?