Question { 3442 }

what is an associative array with example?

Answer

It is a type of composite data type in pl sql .. The other
two composite types are nested tables and varrays .. example :
TYPE type_name IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
name type_name;

It is also called as index by tables.
Now "name" can be given any values like :

name('raja'):=100;

Question { Sonata, 13912 }

How to retrieve a second highest salary from a table?
Note:Suppose salaries are in duplicate values
eg:
Name Sal
Malli 60000
Pandi 60000
Rudra 45000
Ravi 45000

Answer

You can also do it by the following query ..

select * from (select name,salary,rank() over(order by
salary desc as r) from employee) where r = 2;

The above query returns the 2nd highest salary from the table.

Question { HCL, 11848 }

I Have A Table Like This.

Cityno Cityname Mails
1 Bangalore 8km
2 Hsr Layout 20km
3 Mejistic 30km
4 Jayadeva 55km
5 Itpl 80km
6 Hebbal 115km

I Have Data Like This

I Want O/p Like This
Distance No.ofcity
0-50km 3
51-100km 2
101-150km 4

And So On

Answer

SQL> select * from quest;

Cityno cityname mails
---------- -------------------- ----------
1 bangalore 8km
2 hsr layout 20km
3 mejistic 30km
4 jayadeva 55km
5 itpl 80km
6 hebbal 115km

6 rows selected.

SQL> with data as (
2 select level as lvl,lag(level,1,0) over(order by level) as pre_lvl
3 from dual
4 where mod(level,50) = 0
5 connect by level <=150)
6 select count(cityno),pre_lvl||'-'||lvl
7 from quest
8 ,data
9 where to_number(replace(mails,'KM',0))/10 between pre_lvl and lvl
10 group by lvl,pre_lvl;

Question { 5392 }

I have a table emp. There is only one column in the table.
In that , there are only three rows in that column.
The value in the first row is 'A' and the value in the
second row is 'B' and the third row is 'C'. Now, my question
is ,

How will you write a select query to display the output as
B
C
A

Note: order by cannot be used coz it gives us output as CBA.
But the output should be BCA.

Answer

Garv : Thats right :) Thank you for your answer :)