CareerCup

I can only come up with a solution in PL/SQL to do it. Were you able to do it in SQL?

Run the query, and do the computations on the results you get.

There is a way to do this with only basic SQL queries.

In fact, I believe that's how these aggregate functions are implemented behind the scenes. But I can't recall how. Some Googling will turn up an answer I'm sure.

use cursor to get the sum and avg
use row_number cooperating with count to find the median

I have implemented the sum function using recursive with clause in Oracle SQL, similarly it can be modified for average (sum/count)

WITH s (iteration, num_sum)
     AS (SELECT row_id iteration, xn.num
           FROM (    SELECT NUM,
                            ROW_NUMBER () OVER (ORDER BY num) row_id
                       FROM xxtest_num) xn
          WHERE xn.row_id = 1 -- first row in the table
         UNION ALL
         SELECT s1.iteration + 1, s1.num_sum + xn1.num
           FROM s s1,
                (     SELECT NUM,
                            ROW_NUMBER () OVER (ORDER BY num) row_id
                       FROM xxtest_num) xn1
          WHERE s1.iteration <= 10 -- stopping condition count of rows
          AND s1.iteration + 1 = xn1.row_id)
SELECT num_sum
  FROM (  SELECT *
            FROM s s1
        ORDER BY num_sum DESC)
 WHERE ROWNUM = 1;