sql_Queries

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