SQL interview question and answers

To see current user name
Ans: Sql> show user;

How to change sql prompt name
Ans: Sql> set sqlprompt "Scott>"

How to switch to DOS prompt
Ans: sql> host


How do i eliminate the duplicate rows?
Ans: delete from table_name where rowid not in (
select max(rowid) from table group by duplicate_value_field_name);


How do i display row number with records?
Ans: Select rownum. ename from emp;


To view installed oracle version information
sql> select banner from v$version;


display the number value in words
Ans: select sal, (to_char(to_date(sal,'j'),'jsp')) from emp


what is the output of SIGN function?
Ans: 1 for positive value, 0 for zero, -1 for negative value


What are the more common pseudoo-colums?
Ans: sysdate, user, uid, curval, nextval, rowid, rownum


which date function returns number value?
months_between