8]how to delete set of records at a time where all the records having same values , except the Rowid differs?

delete from testsal where rowid in
(select min(rowid) from testsal group by grade
having count(*)>1);

6] Why output give error message when i write select stmt, ROWNUM=2 in where clause.?

Reply : ROWNUM will always work for < or <= only, neither for = nor for > nor for >= as ROWNUM always start giving the rows of output result from 1 irrespective of the rownum values in a table. So, when u write ROWNUM=2 for any statement, query will retrive some records say row 'n'  and give it a value as ROWNUM= "1" , thats why it will never give any result for ROWNUM=2.

I hope i m able to explain the concept of rownum, but still if there is any confusion, feel free to ask further

can you explain by giving some example

Regards

Mohit

Q:) Why output give error message when i write select stmt, ROWNUM=2 in where clause ?

Reply:) Hi Mohit, U try to  login thru scott username and lets take table "DEPT" for example. I hope u must be having this table . For eg- it contains total 4 rows.u run the query in the following sequence, u will come to know about the difference and the reason why ROWNUM=2 dint work :

a) select rownum,a.* from dept a;

b) select rownum,a.* from dept a where rownum<3;

c) select rownum,a.* from dept a where rownum=3;

got some idea ??????????

look, when query (b) is run, it will return 2 rows and save them as in output buffer for eg and assign them rownum as 1,2,3,......

but when query (c) is run,it should return the row having rownum=3 according to you,but what happened is that it also tries to return some rows and save them and assigns the returned result as rownum=1 , thats why it can never return any rows for rownum=3 or rownum>3 .

i hope this will clear your doubt ,but if there is still any confusion, feel free to ask .

Q:) Displaying Experience  eg- 5 years 7 months 9 days?

Reply:) I've tried it and I m able to do it for years and months only , not for days . Here is the query for this

select trunc(months_between(end_date,start_date)/12) || ' years ' ||
 trunc(trunc(months_between(end_date,start_date))-
trunc((months_between(end_date,start_date)/12))*12)||' months '
 experience,start_date,end_date,employee_id from job_history

You can follow this example-

 select floor((trunc(sysdate)  - to_date('01-JAN-01'))/365)  years,
floor(mod((trunc(sysdate)- to_date('01-JAN-01')),365)/30 ) months ,
mod(mod((trunc(sysdate)- to_date('01-JAN-01')),365),30) days from dual;

delete from tablename

 where column_name=(select max(column_name) from tablename group by column_names

having count(column1||column2>1);

eg:tablename:student(name,id,age,addr)

Sql> delete from student

where id=(select max(id) from student group by id

having count(id||name)>1);

I have to modify this query because every month doesn't have exactly 30 days.
so in this case the number of days displayed is wrong. We have to take it as
30.42 (which is avg of number of days in a month).

Updated version is as follow

select floor((trunc(sysdate)-to_date('01-jan-01'))/365)||'years' ,floor(mod((trunc(sysdate)-to_date('01-jan-01')),365)/30)||'months',trunc(mod(mod((trunc(sysdate)-to_date('01-jan-01')),365),30.42))||'days'
from dual