BlocklySQL - Visual SQL Programming
Written by Nikos Vaggalis   
Thursday, 14 January 2021

BlocklySQL is a block-based, visual programming tool like Scratch but for SQL. It is the outcome of academic research at the University of W├╝rzburg on the kind of errors that students who are novice in writing SQL queries make.

The research has shown that the top three errors are: wrong syntax (21%), undefned column name (13%), and wrong use of GROUP BY-clause(10%), mostly syntactic errors.Thus the idea was that a block-based editor that would eliminate such errors would make learning SQL easier.

While there are already some similar tools, BlocklySQL comes with two extra advantages; it is open source and free and it can be customized with blocks being added or subtracted to match the a given domain. For example, the live demo is written to accommodate the MySQL sample database classicmodels, a retailer of scale models of classic cars database which contains typical business data such as customers, products, sales orders, sales order line items, etc.

There is also another demo run against the daily updated weather and football database dbup2date, unfortunately the link  no longer works.

Before you try it out you have to know its limitations first. Currently only SELECT statements are supported and further

  • with one table

  • with two or more tables using joins

  • using aggregate functions (COUNT, SUM, AVG, MAX, MIN)

  • using GROUP BY (with HAVING)

  • using ORDER BY

  • subqueries using IN

The advantage is that while student use blocks to enter SQL, they can then see, and also export, the auto-generated SQL in order to learn the actual syntax.


Since it's open source you can easily add it to your own site and adapt it to your domain specific scenario by using Javascript since BlocklySQL under the hood is based on Google's Blockly web-based visual programming editor. For example new blocks can be added by creating new_block.js in the folder sql/js/sql_blocks to enter the block's definition and function.

The project is available on GitHub.

 blocklsql-logo

More Information

BlocklySQL on GitHub

live demo

Related Articles

Analytic SQL for Developers

LiteCLI - SQLite Client with Autocomplete

 

 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Banner


Akka Adds Database Sharding Support
23/05/2024

Lightbend, the company behind Akka, has released Akka version 24.05. Lightbend produces cloud-native microservices frameworks while Akka allows for building distributed applications.



At What Age Do Programmers Write Languages?
29/05/2024

This is just one of many question that can be answered by reference to PLDB, the programming language database devised and maintained by Breck Yunits, himself the author of Scroll, an o [ ... ]


More News

C book

 

Comments




or email your comment to: comments@i-programmer.info

Last Updated ( Thursday, 14 January 2021 )