Ludi!!
Ispol'zyite Oracle's analytic functions i ne stroite problemy,
kotorye potom neobhodimo geroicheski preodolevat'!

SELECT channel_desc, calendar_month_desc,
TO_CHAR(TRUNC(SUM(amount_sold),-6), '9,999,999,999') SALES$,
TO_CHAR(SUM(quantity_sold), '9,999,999,999') SALES_Count,
RANK() OVER (ORDER BY trunc(SUM(amount_sold), -6) DESC, SUM(quantity_sold)
DESC) AS col_rank
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND
sales.cust_id=customers.cust_id AND
sales.time_id=times.time_id AND
sales.channel_id=channels.channel_id AND
times.calendar_month_desc IN ('2000-09', '2000-10') AND
channels.channel_desc<>'Tele Sales'
GROUP BY channel_desc, calendar_month_desc;
-----
SELECT channel_desc, calendar_month_desc,
TO_CHAR(TRUNC(SUM(amount_sold),-6), '9,999,999,999') SALES$,
RANK() OVER (ORDER BY trunc(SUM(amount_sold),-6) DESC)
AS RANK,
DENSE_RANK() OVER (ORDER BY TRUNC(SUM(amount_sold),-6) DESC)
AS DENSE_RANK
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND
sales.cust_id=customers.cust_id AND
sales.time_id=times.time_id AND
sales.channel_id=channels.channel_id AND
times.calendar_month_desc IN ('2000-09', '2000-10') AND
channels.channel_desc<>'Tele Sales'
GROUP BY channel_desc, calendar_month_desc;
----
SELECT channel_desc, country_id,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$,
RANK() OVER (PARTITION BY GROUPING_ID(channel_desc, country_id)
ORDER BY SUM(amount_sold) DESC) AS RANK_PER_GROUP
FROM sales, customers, times, channels
WHERE sales.time_id=times.time_id AND
sales.cust_id=customers.cust_id AND
sales.channel_id= channels.channel_id AND
channels.channel_desc IN ('Direct Sales', 'Internet') AND
times.calendar_month_desc='2000-09'
AND country_id IN ('UK', 'US', 'JP')
GROUP BY CUBE( channel_desc, country_id);
------
SELECT calendar_year AS YEAR, calendar_quarter_number AS QTR,
calendar_month_number AS MO, SUM(amount_sold),
RANK() OVER (ORDER BY SUM(amount_sold) ASC NULLS FIRST) AS NFIRST,
RANK() OVER (ORDER BY SUM(amount_sold) ASC NULLS LAST) AS NLASST,
RANK() OVER (ORDER BY SUM(amount_sold) DESC NULLS FIRST) AS NFIRST_DESC,
RANK() OVER (ORDER BY SUM(amount_sold) DESC NULLS LAST) AS NLAST_DESC
FROM (
SELECT sales.time_id, sales.amount_sold, products.*, customers.*
FROM sales, products, customers
WHERE
sales.prod_id=products.prod_id AND
sales.cust_id=customers.cust_id AND
prod_name IN ('Ruckpart Eclipse', 'Ukko Plain Gortex Boot')
AND country_id ='UK') v, times
WHERE v.time_id (+) =times.time_id AND
calendar_year=1999
GROUP BY calendar_year, calendar_quarter_number, calendar_month_number;
-----
SELECT calendar_month_desc AS MONTH ,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$,
NTILE(4) OVER (ORDER BY SUM(amount_sold)) AS TILE4
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND
sales.cust_id=customers.cust_id AND
sales.time_id=times.time_id AND
sales.channel_id=channels.channel_id AND
times.calendar_year=1999 AND
prod_category= 'Men'
GROUP BY calendar_month_desc;
-----
SELECT c.cust_id, t.calendar_month_desc,
TO_CHAR (SUM(amount_sold), '9,999,999,999') AS SALES ,
TO_CHAR(AVG(SUM(amount_sold))
OVER (ORDER BY c.cust_id, t.calendar_month_desc ROWS 2 PRECEDING),
'9,999,999,999') AS MOVING_3_MONTH_AVG
FROM sales s, times t, customers c
WHERE
s.time_id=t.time_id AND
s.cust_id=c.cust_id AND
t.calendar_year=1999 AND
c.cust_id IN (6380)
GROUP BY c.cust_id, t.calendar_month_desc
ORDER BY c.cust_id, t.calendar_month_desc;
------
etc..

Razdel - Oracle Data warehouse.

Version:: SQL:: 8i/9i
PLSQL - 9i