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.

SQL

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.

SPL

	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)

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