Listagg

This function brings all the columns into a single column seperated by any delimiter :

syntax:

SELECT LISTAGG(last_name, ';') 

WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list"
from table 

this function is introduced in oracle 11g and prior to that this can be acheived by using the following 

WITH Q AS (     SELECT 1 X FROM DUAL  UNION ALL     SELECT 2   FROM DUAL  UNION ALL     SELECT 3   FROM DUAL  UNION ALL     SELECT 9   FROM DUAL   ) SELECT WM_CONCAT(Q.X) FROM Q

challenging Queries

table t has 1 column year :

t:

1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005

select min(year),max(year) from t returns :
1994 2005

how to display all the year s without using a select * from query?

select(select min(year) from t +rownum-1 

from all_objects) 

where rownum<=

      (select max(year) -min(year)+1from  t);