12.12 Compare with all values in the result of subquery

 

Based on data in a certain table, get records through comparing values of a specified field with all corresponding values in the result of the subquery. According to Employee table, find employees who get salaries higher than those of all employees in Sales department.

ID NAME DEPT SALARY
1 Rebecca R&D 7000
2 Ashley Finance 11000
3 Rachel Sales 9000
4 Emily HR 7000
5 Ashley R&D 16000

SPL script:

A
1 =connect(“db”)
2 =A1.query@x(“select * from Employee”)
3 =A2.select(DEPT:“Sales”).max(SALARY)
4 =A2.select(SALARY>A3)

A1 Connect to the database.
A2 Query Employee table.
A3 Get the highest salary among employees of Sales department.
A4 Get records of employees whose salaries are higher than those of all employees in Sales department.

Execution result:

ID NAME DEPT SALARY
5 Ashley R&D 16000
20 Alexis Administration 16000
22 Jacob R&D 18000
47 Elizabeth Marketing 17000