See discussions, stats, and author profiles for this publication at: https://www.researchgate.
net/publication/336754811
BlocklySQL: A new block-based editor for SQL
Conference Paper · October 2019
DOI: 10.1145/3361721.3362104
CITATIONS READS
8 2,552
5 authors, including:
Andre Greubel Martin Hennecke
Humboldt-Universität zu Berlin University of Wuerzburg
21 PUBLICATIONS 63 CITATIONS 35 PUBLICATIONS 71 CITATIONS
SEE PROFILE SEE PROFILE
Matthias Ehmann
University of Bayreuth
28 PUBLICATIONS 53 CITATIONS
SEE PROFILE
All content following this page was uploaded by Andre Greubel on 28 January 2020.
The user has requested enhancement of the downloaded file.
BlocklySQL - A new block-based editor for SQL
Nicolai Pöhner, Timo Schmidt, André Greubel, Matthias Ehmann
Martin Hennecke University of Bayreuth
University of Würzburg Bayreuth, Germany
Würzburg, Germany matthias.ehmann@uni-bayreuth.de
{nicolai.poehner,andre.greubel,martin.hennecke}@
uni-wuerzburg.de
ABSTRACT The analysis of the data reveals that the students’ SQL queries
This paper introduces BlocklySQL, a new block-based editor for included many syntactic errors (54%). When investigating these
SQL. BlocklySQL was developed on the basis of Google’s JavaScript errors more closely, the top three errors are: wrong syntax (21%),
library Blockly, a tool for developing own block-based editors, undefined column name (13%), and wrong use of GROUP BY-clause
which comes with several technological and pedagogical advan- (10%) [2]. The high percentage of syntactic errors shows that a
tages. BlocklySQL is also integrated into the learning platform block-based editor, which tries to eliminate such kinds of errors,
dbup2date, which provides daily updated databases for the use in might be useful for the learning of SQL.
CS classrooms.
2.2 Existing Tools
KEYWORDS Unsurprisingly, other researchers have already developed tools
Computer Science Education, Block-based programming, Databases, which combine block-based programming with SQL. One popular
SQL example is SQLSnap!. SQLSnap! is an extension of Snap! 1 , which
provides blocks for the the creation of SQL queries in Snap!. These
ACM Reference Format: queries can be commited to a MySQL database. Data can either be
Nicolai Pöhner, Timo Schmidt, André Greubel, Martin Hennecke and Matthias
used from the example databases or included from .txt-files.
Ehmann. 2019. BlocklySQL - A new block-based editor for SQL. In 14th
Workshop in Primary and Secondary Computing Education (WiPSCE’19), Oc-
tober 23–25, 2019, Glasgow, Scotland Uk. ACM, New York, NY, USA, 2 pages.
https://doi.org/10.1145/3361721.3362104
1 INTRODUCTION
Block-based programming is a popular approach to introduce learn-
ers to programming. In comparison to textual programming, it
prevents tedious syntactic errors (e.g. missing semicolon at the end
of a statement). These kinds of errors can lead to high frustration Figure 1: Example of a SQL query with SQLSnap!: It returns
in the learning process of students. By avoiding those kinds of er- the top five students with the best grades of all courses (in
rors block-based programming promotes a higher emphasis on the German).
semantics of a programming task.
2 RELATED WORK 2.3 Advantages
2.1 Students’ Problems with the Learning of Compared to other block-based editors for SQL such as SQLSnap!,
SQL BlocklySQL provides technological and pedagogical advantages:
In multiple studies [1][2], Ahadi et al. investigated students’ errors • Technological
of writing database queries (SELECT-statements) in SQL. Their – Flexibility & Extensibility: Users can customise their
sample consists of more than 161000 queries from more than 2300 version of BlocklySQL to fit their needs. For example, new
(undergraduate) students. The queries have been collected and blocks can be added (or manipulated) easily either manu-
assessed with the online tool AsseSQL [5]. ally using JavaScript or with the help of Google’s Developer
Tools.2
Permission to make digital or hard copies of all or part of this work for personal or – Open Source: Publicly available code allows developers
classroom use is granted without fee provided that copies are not made or distributed
for profit or commercial advantage and that copies bear this notice and the full citation to use BlocklySQL in their own websites and projects.
on the first page. Copyrights for components of this work owned by others than ACM
must be honored. Abstracting with credit is permitted. To copy otherwise, or republish,
to post on servers or to redistribute to lists, requires prior specific permission and/or a
fee. Request permissions from permissions@acm.org. 1 Snap! can be found online under https://snap.berkeley.edu/, last accessed 19th June,
WiPSCE’19, October 23–25, 2019, Glasgow, Scotland Uk 2019.
© 2019 Association for Computing Machinery. 2 For further information on the Blockly Developers Tools go to
ACM ISBN 978-1-4503-7704-1/19/10. . . $15.00 https://developers.google.com/blockly/guides/create-custom-blocks/blockly-
https://doi.org/10.1145/3361721.3362104 developer-tools, last accessed 19th June, 2019.
WiPSCE’19, October 23–25, 2019, Glasgow, Scotland Uk
Figure 2: Example of a SQL query with BlocklySQL: It returns the locations of weather stations, where more than five gale-
force winds (wind speed > 32.7 m/s) have been recorded (in German). Queries can also be attached with comments and blocks
can be collapsed (e.g. FROM-clause) to improve readability.
• Pedagogical football and 25000 downloads for the weather.
– Adequacy of purpose: Whereas SQLSnap! is meaningful On this platform, SQL queries can be typed into a text field and
when using results of database queries in a programming sent to the server via an HTML-form . The server conducts the
exercise, the purpose of BlocklySQL is to support begin- SQL query and returns the corresponding result. In cooperation
ners in learning SQL. Less blocks are presented to reduce with the University of Bayreuth, we extended the learning platform
the cognitive load of the students (you could even develop by integrating BlocklySQL as an alternative interface. The learn-
different levels of difficulty) and they do not have to deal ing platform is available here3 . SQL queries in BlocklySQL can be
with empty clauses as in SQLSnap! which could even re- transformed to regular SQL syntax and saved to or loaded from
inforce students’ misconceptions. .xml-files.
– Exportability: Block-based programs can be extracted
to regular SQL syntax and displayed in the browser to 5 OUTLOOK & FUTURE WORK
simplify the transition from block-based to textual SQL This paper presented BlocklySQL, a new block-based editor for the
statements. learning of SQL. Future work consists of evaluating our tool with
teachers and students and extending it with new features. Even
3 IMPLEMENTATION though INSERT, UPDATE, and DELETE-Statements for data manip-
BlocklySQL does not (yet) support the whole syntax of SQL. Because ulation are also part of our curriculum, we decided not to include
of our curriculum, we focused on the following elements of the them in BlocklySQL so far as, because of the user permissions of
SELECT-statement: the database management system (DBMS) in dbup2date, students
• with one table cannot perform any manipulations on the data in our database and
• with two or more tables using joins consequently the statements would not have any effect. Regardless,
• using aggregate functions (COUNT, SUM, AVG, MAX, MIN) this would be possible with the right user permissions.
• using GROUP BY (with HAVING)
• using ORDER BY REFERENCES
[1] Ahadi, A. et al. (2016): "Students’ Semantic Mistakes in Writing Seven Different
• subqueries using IN Types of SQL Queries". In: Clear, A., Cuadros-Vargas, E. (eds.): Proceedings of the
2016 ACM Conference on Innovation and Technology in Computer Science Education.
4 EXAMPLE PROJECT: INTEGRATION INTO ACM, New York, pp. 272 - 77.
[2] Ahadi, A et al. (2016): "Students’ Syntactic Mistakes in Writing Seven Different
THE ONLINE LEARNING PLATFORM Types of SQL Queries and its Application to Predicting Students’ Success". In:
Alphonce, T., Tims, J. (eds.): Proceedings of the 47th ACM Technical Symposium on
"DBUP2DATE" Computing Science Education. ACM, New York. pp. 401 - 06.
The project dbup2date at the University of Bayreuth, Germany de- [3] Blockly. http://bit.ly/2I1fWRN, last accessed 19th June 2019.
[4] Modrow, E. (2015): "Neues von BYOB/Snap!". In: Koerber, B. et al. (eds). LOG IN:
veloped a learning platform for the use in CS classrooms, which Informatische Bildung und Computer in der Schule. Volume 181/182. pp. 128 - 137.
provides daily updated databases for the topics football and weather. [5] Prior, J. (2014): "AsseSQL: an Online, Browser-Based SQL Skills Assessment Tool".
This learning platform is quite popular in Germany. In 2018, more In: Cajander, A., Daniels, M. (eds.): Proceedings of the 2014 ACM Conference on
Innovation and Technology in Computer Science Education. ACM, New York. p.
than 300000 SQL queries were committed to the database football. 327.
80000 SQL queries were committed to the database weather. Addi-
tionally, the platform was also used for downloading the database 3 The learning platform dbup2date is available online: https://dbup2date.uni-
files (either as .sql- or .csv-file): 92000 download for the database bayreuth.de/blocklysql/ (until now only in German), last accessed 19th June, 2019.
View publication stats
