12 Selects of christmas (the fourth instalment)

9. Unnesting a nested table

Okay, first I'll build a nested table using 10G's COLLECT operator.
Then I'll unnest it again..
A pointless exercise really, but then I haven't actually found a point to nested tables in the database anyway.

select m.table_name, c.column_value from
(select table_name,
       cast ((select collect(column_name)
        from user_tab_columns u
        where u.table_name = t.table_name) as sys.dbms_debug_VC2coll) cols
from user_tables t) m, table(cols) c

10. ROLLUP

select data_type, nullable, count(*)
from user_tab_columns
group by rollup (data_type, nullable)

As well as the counts for the individual data_type/nullable entries, it also gives subtotals for each data type, plus a grand total

11. CUBE

select data_type, nullable, count(*)
from user_tab_columns
group by cube (data_type, nullable)

In addition to the ROLLUP results, this will also gives subtotals for nullable.