SQL> select ename , sal from emp a where 1 = ( select count(*) from emp b
where b.sal > a.sal);
ENAME SAL
---------- ----------
JONES 2975
We can generalized this query for other highest salary.
SQL> select ename , sal from emp a where N-1 = ( select count(*) from emp b where b.sal > a.sal);where N-1 is the any number which you want to select rank wise salary.for example if we want to select third highest salary then N-1 becomes 2 andif we want fourth then it becomes 3.
|
view
|
synonym
|
|
1.
It doesn’t contain data itself.
2.view occupies no
space for data
3.view has only query
|
1. It contain data itself.
2.synonym occupy space for data
3.synonym can be used as an alias name for the table name
|
|
Row id
|
RowNum
|
|
1.Physical address of the rows.
2.Rowid is permanent
3.Rowid is 16-bit hexadecimal
4.Rowid gives address of rows or records
5. Rowid is automatically generated
unique id of a row and it is generated at the time of insertion of row.
6. ROWID is the fastest means of
accessing data.
7. They are unique identifiers for the
any row in a table.
|
1. Rownum is the sequential number,
allocated to each returned row during query execution.
2. Rownum is temporary.
3.Rownum is numeric
4.Rownum gives count of records
5. Rownum is an dynamic value
automatically
retrieved along with select statement output.
6. It represents the sequential order in
which Oracle has retrieved the row.
|
|
Trigger
|
Procedure
|
|
1.
Trigger
does not accept parameters.
2.
A
trigger is executed implicitly by the oracle engine.
|
1. Procedure accepts
the parameters.
2.Procedure is
execute explicitly, called by a user
|