INTERVIEW QUESTIONS - PART 2

9. Find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B
WHERE a.sal<=b.sal);
Enter value for n:
2 SAL --------- 3700

10. Explicit Cursor attributes
There are four cursor attributes used in Oracle cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

11. Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements. 2. All are Boolean attributes.

12.Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. ' (to_char(to_date(sal,'j'), 'Jsp')) ' only.'))
"Sal in Words" from emp
/

13. Display Odd/ Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6