Record set storage

Firstly, count the employees in the R&D department; secondly, count how many more employees there are in the Sales department than in the R&D department; thirdly, find out the discrepancy in the average employee ages between the R&D department and Marketing department.
To count the R&D employees:

    SELECT count(*) FROM employee WHERE dept='R&D'

To count how many more employees the Sales department has than R&D department has:

    SELECT (SELECT count(*) FROM employee WHERE dept='Sales') -
           (SELECT count(*) FROM employee WHERE dept='R&D')
    FROM dual

To find out the discrepancy in the average ages between R&D employees and Marketing employees:

    SELECT (SELECT avg(age) FROM employee WHERE dept='R&D') -
           (SELECT avg(age) FROM employee WHERE dept='Marketing')
    FROM dual

You can’t complete a SQL computation with multiple query calls, which means the computational result of one SQL statement cannot be used by another statement. Therefore, if users want to use the result of a statement, they would need to rewrite that statement as a nested subquery in the parent statement.

  A B
1 =demo.query(“select * from employee”)  
2 =A1.select(dept:”R&D”) =A2.len()
3 =A1.select(dept:”Sales”) =A3.len()-B2
4 =A1.select(dept:”Marketing”) =A2.avg(age)-A4.avg(age)

esProc supports using record sets as variable values, thus enabling users to make full use of the intermediate result.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.