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


Kaggle Survey Of Data Scientists
08/02/2021

Kaggle's survey of data scientists reveals that the vast majority of data scientists are under 35 years of age, two-thirds have a graduate degree, and most have less than 10 years coding experience. I [ ... ]



Amazon Donates $15 Million To Code.Org
24/02/2021

Amazon has announced a $15 million donation to Code.org to support the development and launch of a new equity-minded Advanced Placement Computer Science programming curriculum.


More News

square

 



 

Comments




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

Last Updated ( Thursday, 14 January 2021 )