Sql Question and answer

1. Suppose customer table is there having different columns
like customer no, payments.What will be the query to select top three max payments.

SELECT customer_no, payments from customer C1
WHERE 3<=(SELECT COUNT(*) from customer C2
WHERE C1.payment <= C2.payment)


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);

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);
For Eg:-
Enter value for n: 2
SAL
---------
3700

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.

14. Display Odd/ Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
Output:-
1
3
5

Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
Output:-

2
4
6

'How do you find the numbert of rows in a Table ?'
A bad answer is count them (SELECT COUNT(*) FROM table_name)

A good answer is :-
'By generating SQL to ANALYZE TABLE table_name COUNT STATISTICS by querying Oracle System Catalogues (e.g. USER_TABLES or ALL_TABLES).

The best answer is to refer to the utility which Oracle released which makes it
unnecessary to do ANALYZE TABLE for each Table individually.


minvalue.sql
Select the Nth lowest value from a table

select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') < 'col_name')
group by level;

Example:

Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second lowest salary:

-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level

maxvalue.sql
Select the Nth Highest value from a table

select level, max('col_name') from my_table where level = '&n' connect by prior ('col_name') > 'col_name')
group by level;

Example:

Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second highest salary:

-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level