(Continued from previous part...)

60. I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text ?Not Applicable? want to display, instead of blank space. How do I write the query?

SQL> select nvl(to_char(comm.),'NA') from emp;
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA

61. Oracle cursor : Implicit & Explicit cursors

Oracle uses work areas called private SQL areas to create SQL statements. PL/SQL construct to identify each and every work are used, is called as Cursor. For SQL queries returning a single row, PL/SQL declares all implicit cursors. For queries that returning more than one row, the cursor needs to be explicitly declared.

62. Explicit Cursor attributes

There are four cursor attributes used in Oracle cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

63. 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.

64. 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

65. To view installed Oracle version information

SQL> select banner from v$version;

66. 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
/
Salary Sal in Words
------- -------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.

67. 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

(Continued on next part...)