Individual Records

Firstly, find out Tom’s age; then, find out how many years Tom is older than David; then find out how many days earlier Tom came on board than Harry did.

SQL

To find out Tom’s age:

SELECT age FROM employee WHERE name='Tom'

To find out how many years Tom is older than David:

SELECT (SELECT age FROM employee WHERE name='Tom') -
       (SELECT age FROM employee WHERE name='David')
FROM dual

To find out how many days earlier Tom came on board than Harry did:

SELECT (SELECT join_date FROM employee WHERE name='Harry') -
       (SELECT join_date FROM employee WHERE name='Tom')
FROM dual

You can’t carry out a SQL computation in separate query calls, which means the computational result of one SQL statement cannot be used by another one. 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@1(name:"Tom") =A2.age
3 =A1.select@1(name:"David") =B2-A3.age
4 =A1.select@1(name:"Harry") =interval(A2.join_date,A4.join_date)

SPL supports using individual records as variable values, thus it enables users to make full use of the intermediate results.

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.