1)Display the records between two range
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
(or)
select rownum, empno, ename from emp where rownum <=&upto
minus
select rownum, empno, ename from emp where rownum<&Start
output:
SQL> select rownum, empno, ename from emp where rownum <=&upto
2 minus
3 select rownum, empno, ename from emp where rownum<&Start;
Enter value for upto: 4
old 1: select rownum, empno, ename from emp where rownum <=&upto
new 1: select rownum, empno, ename from emp where rownum <=4
Enter value for start: 1
old 3: select rownum, empno, ename from emp where rownum<&Start
new 3: select rownum, empno, ename from emp where rownum<1
ROWNUM EMPNO ENAME
------ ----- ----------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
2)Display the number value in Words
SQL> select sal,(to_char(to_date(sal,'j'),'jsp')) as words from emp where deptno=10
2 ;
SAL WORDS
--------------- ------------------------------------------------------------------------------
2450 two thousand four hundred fifty
5000 five thousand
1300 one thousand three hundred
3)To view installed Oracle version information
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> desc V$version;
Name Null?
---------------------------------------------------------------------------------------------------
BANNER
4)Find out nth highest salary from emp table
SQL> SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B
2 WHERE a.sal<=b.sal);
Enter value for n: 2
old 1: SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B
new 1: SELECT DISTINCT (a.sal) FROM EMP A WHERE 2 = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B
SAL
---------------
3000
5)Display Odd/ Even number of records
Odd number of records:
SQL> select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------- ---------- --------- --------------- --------- --------------- --------------- -----
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7 rows selected.
Even number of records:
SQL> select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------- ---------- --------- --------------- --------- --------------- --------------- -----
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7 rows selected.
6)Other way to replace query result null value with a text
SQL> set null 'n/a'
SQL> select comm from emp where deptno=10
2 ;
COMM
---------------
n/a
n/a
n/a
To Reset:
SQL> set NULL ''
SQL> select comm from emp where deptno=10
2 ;
COMM
---------------
8)How do I eliminate the duplicate rows ?
delete from table_name where rowid not in (select max(rowid) from table group by
duplicate_values_field_name);
SQL> delete from emp where rowid not in (select max(rowid) from emp group by deptno);
11 rows deleted.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------- ---------- --------- --------------- --------- --------------- --------------- -----
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
9)To clear the sql propmpt screen we can use
sql>cl scr
sql>clear screen
sql>cl screen
sql>clear scr
10)Change SQL prompt name
SQL> set sqlprompt "raj>"
raj>set sqlprompt "sql>"
11)display 1st and last records of a table
SQL> select * from emp where rownum=1 union select * from emp where rowid in(select max(rowid) from emp)
or
select * from emp where rowid in((select max(rowid) from emp),(select min(rowid) from emp))
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---- --------- ---- ---- ------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
