Why SQL Struggles with Complex Queries and How SPL Offers a Solution

SQL was invented to make data‑querying easy for non‑technical users, using English‑like syntax, but it quickly shows limitations when queries become moderately complex.

Key shortcomings of SQL

Incomplete set semantics : SQL treats results as unordered sets and does not provide a collection‑type field, making it hard to store intermediate sets or perform set‑of‑sets operations.

Lack of ordering support : Because SQL inherits the mathematical notion of unordered sets, calculations that depend on rank or position (e.g., top‑N per product, median, consecutive days) require cumbersome work‑arounds such as subqueries, window functions, or self‑joins.

No object reference mechanism : Relationships between tables rely on foreign‑key equality, forcing multi‑table joins or nested subqueries for what would be a simple attribute access in an object‑oriented language.

Illustrative SQL examples

Finding salespeople who rank in the top 10 for both air‑conditioners and TVs using early‑SQL syntax:

select * from (select top 10 sales from sales_amount where product='AC' order by amount desc) intersect (select top 10 sales from sales_amount where product='TV' order by amount desc)

Using CTE to name the intermediate results:

with A as (select top 10 sales from sales_amount where product='AC' order by amount desc), B as (select top 10 sales from sales_amount where product='TV' order by amount desc) select * from A intersect B

When the number of products is unknown, a window‑function approach can be used:

select sales from (select sales, rank() over (partition by product order by amount desc) ranking from sales_amount) where ranking <= 10 group by sales having count(*) = (select count(distinct product) from sales_amount)

Even with window functions, queries become verbose and hard to read, especially for tasks like finding the median employee age, longest consecutive stock‑price rise, or employees sharing birthdays.

Introducing SPL

To overcome these obstacles, the Structured Process Language (SPL) was created. SPL treats collections as ordered, supports object references, and provides true set‑of‑sets semantics, allowing concise expressions of the same problems.

Example SPL snippets for the earlier tasks:

=employee.sort(birthday)

=stock_price.sort(trade_date)

=employee.group(month(birthday),day(birthday))

=score_table.group(subject)

SPL also offers an IDE with debugging features and a JDBC driver for seamless integration with Java applications.