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.

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.

Comments

Make a Comment or View Existing Comments Using Disqus

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