SQL vs. Bandicoot v5

Published by Julius Chrobak on 2012 09 21.

This is an update of my previous blog on the SQL vs. Bandicoot comparison. You can find here are one-to-one comparisons of the well known SQL expressions and the Bandicoot v5 language.

SELECT FROM

This is the most used expression in SQL. It returns all the rows from a table, allows you to define which columns to pick, or define new derived columns:

select * from books

books

select title, author from books

project (title, author) books

select b.*, 2011 - year as age
  from books b

extend (age = 2011 - year) books

or everything together

select title, author, 2011 - year as age
  from books

project (title, author, age)
         (extend (age = 2011 - year) books)

WHERE

select *
  from books
 where author = 'John Irving'

select (author == "John Irving") books

JOIN

select *
  from books natural join stores

join books stores

note: there are several different join operators defined in SQL. Bandicoot supports only natural inner join (a full match on equality of all the columns with the same name and type):

GROUP BY

select genre, 
       count(*) as cnt,
       avg(pages) as pages
  from books
 group by genre

summary (cnt = cnt, pages = (avg pages 0.0))
         books
         (project genre books)

All together (i.e. creating a function)

To get a better picture how this all works in reality I show you an example of a complete Bandicoot function. Let's imagine you have a simple database of books and stores and you want to find out what is the average price of books per author providing the genre you are interested in:

fn Prices(g string) {author string, avgPrice real}
{
    var onSell = join stores (select (genre == g) books);

    return summary (avgPrice = (avg price 0.0))
                     onSell
                     (project author onSell);
}

To compare this with SQL I would need to pick a specific implementation of procedural language (ie Oracle's PL/SQL or Postgres' PL/pgSQL). To leave it simple I only write the select statement which executes the same calculation:

select author, avg(price) as avgPrice
  from books natural join stores
 where genre == g
 group by author