Learning SQL in steps

Philip GARNER John MARIANI

School of Computing and Communications School of Computing and Communications
Lancaster University Lancaster University
Lancaster Lancaster
LA1 4WA LA1 4WA
United Kingdom United Kingdom

Abstract 2 The problem of learning SQL

Learning SQL is an important skill CS students are re-

Learning SQL is a common problem for many Computer quired to master, the ability to read, build and modify
Science (CS) students, the steps involved are quite differ- SQL is something which is required in many computing,
ent to those mastered when learning procedural or object- and some non-computing, professions. Although learning
oriented programming languages. The introduction of SQL is a crucial skill it is well recognised that it is is a
commercial products that include shortcuts into the learn- problematic area for many students.
ing environment can initially appear to benefit the student, There are many possible reasons that contribute towards
however, transferring these skills to a textual environment the difficulties of learning SQL. The declarative nature
can be difficult for many students. Computer Science stu- of SQL can be a difficult concept for students to grasp,
dents are required to build textual SQL queries because particularly if they are simultaneously learning procedural
the demands of complex queries can quickly out grow the or object-oriented programming languages alongside SQL
capabilities of graphical query builders available in many [12, 23].
software packages. SQL in Steps (SiS) is a graphical user Learning and visualising the database schema is also a
interface centred around the textual translation of a query; problematic area for many newcomers to SQL [17]. To be
this combination of a GUI and a clear representation of its able to effectively build a query over a relational database
textual meaning has the potential to improve the way in the user must first be able to visualise where the different
which users gain an understanding of SQL. SiS allows for elements of data required are and how they can be ex-
an incremental and evolutionary development of queries tracted. Join operations are common when querying rela-
by enabling students to build queries step by step until tional databases, they are required to extract information
their goal is reached. A planned evaluation of SiS hopes from directly or indirectly connected tables. Visualising
to quantify the extent to which the introduction of such a where these operations are necessary and how to execute
user interface into the learning environment can improve them is also a common problem for students learning SQL
the students’ understanding of the language. [11, 17, 20].
SQL contains many powerful functions and misconcep-
tions regarding any of these can lead to erroneous queries
or those which may not return the desired results when
1 Introduction executed on a different dataset [17].

3 Related work
Learning how to manipulate and retrieve information from
a relational database is a core part of any CS course; how- The extraction of information from relational databases is
ever, it is well recognised that this is a skill that many a well recognised problem that has prompted a broad range
students find difficult to master [24, 10, 2, 23, 22, 20, 16, of research over the past thirty years [24], two decades ago
17, 11, 13]. There have been numerous attempts to sim- there began a series of attempts to improve the way stu-
plify the process of learning SQL through the introduction dents learn how to interact with databases (e.g. [21, 11]).
of different software and educational techniques; however, The vast majority of the existing work can be divided into
these systems are not without fault. two categories: systems designed to analyse users queries
In this paper we first highlight the problem of learning and systems that use animation to illustrate the process
SQL before discussing some previous attempts to address of query execution.
this problem. In an attempt to avoid some of the flaws The analysis of queries involves parsing the user’s sub-
presented in the literature we present a web-based system mitted query and comparing it against an often predefined
called SQL in Steps (SiS) which is designed to allow stu- desired solution. Once analysis is complete these systems
dents to independently build SQL queries and explore the can offer useful instructions or guidelines for the student
functionality of the language. SiS utilises a graphical user to undertake or provide a mark for the query submitted.
interface which is closely coupled with the textual transla- The animation of queries involves taking a fully formed
tion of queries to allow users to build queries in a graphi- query and showing the various different steps taken to
cal environment while gaining a concrete understanding of achieve the final results. This process can be used to high-
how to construct textual queries. light the purpose of the various different clauses within an
SQL statement and allow the user to gain an understand- ing presented to the user without any intermediate steps.
ing of each component of a query. A typical set up for an animation based system would in-
SQL allows users to build databases and manipulate volve a textual input field where the query is entered; after
their contents through the use of various different state- execution the system will analyse the query and perform
ments. Many of the educational tools reviewed focus only a series of animations illustrating how the final result set
on the SELECT statement as it is the most commonly is found.
used and can become the most complex; there are also el-
Work such as eSQL [11] and SAVI [2] adopt a system
ements of the SELECT statement, such as the WHERE
similar to a programming language debugger in that they
clause, that are used in other SQL statements such as UP-
contain step operations to advance to the next animation
DATE and DELETE [17, 22, 11, 4, 2, 20, 5]. By gaining
and continue operations to view the final result set. The
an understanding of the SELECT statement the user is
user submits a query and, assuming the query contains no
likely to find the transition to other statements easier.
errors, the query is executed in stages allowing the user to
visualise how the final result set is achieved. eSQL uses an
3.1 Analysis of queries algorithm to select a subset of the entire database which
There are numerous systems that analyse the input pro- will accurately illustrate the execution of the query. The
vided, the following is a brief overview of a selection of steps involved in the animation of a query are generally
these existing systems. very similar and consist of animating each clause, these
SQLTutor [17] is an early example of a system designed animations are often performed in an order different to
to analyse queries submitted by students; the main focus that which they actually appear. The SELECT clause is
of SQLTutor is to improve upon the basic error messages the first component of a textual query but, for animation
provided by the majority of database applications. Er- purposes, it is more logical to illustrate this step towards
ror messages provided by database applications are a well the end of the animation.
recognised problem for new users of SQL [17, 22, 20, 2], The problem of animated systems is that they require
SQLTutor uses a series of manually predefined questions that a fully formed SQL query be submitted before anima-
and answers to provide more meaningful and contextual tion can take place. These systems seem more appropriate
error messages. for the illustration of examples rather then the process of
A more recent system which analyses users queries is learning SQL. Unless combined with meaningful error mes-
LEARN-SQL [1], this system focusses on the fact that sages or examples it could be difficult for a novice user to
there are often multiple possible solutions to a query. Be- “get off the ground” with many animated systems.
cause of this LEARN-SQL operates by comparing the out-
put of a users query against that of a model query rather
than the contents of the query itself. An additional benefit
of LEARN-SQL was an attempt to reduce the work load
of teachers by introducing automatic marking. 3.3 Commercial products
The analysis of students queries is frequently cou-
pled with a pool of predefined questions and answers In an attempt to avoid using a textual interface with little
[17, 22, 20], these are compiled by the teacher and ev- support for teaching SQL many institutes revert to using
ery time a student uses the system they are allocated a database management applications such as Microsoft Ac-
question. Once a user attempts a question their answer is cess [15], HeidiSQL [9] or phpMyAdmin [19]. The main
compared to the model answer and feedback or a score is problem with using such systems stems from the aims of
provided to the user. If the input of questions is detailed this software; they are designed for the management of in-
enough and the system has a comprehensive knowledge of formation within relational databases, not to teach users
SQL then it can offer clear feedback as to where the user how to construct textual queries. Such systems are “not
can improve their query. The downside of using pools of designed for educational purposes but for the professional
predefined queries is undoubtedly the time overhead of ini- management of databases” [8] and as a result they often
tially setting up the system as a teacher has to define and introduce problems when the users are required to trans-
answer all the questions before the system can be used; it fer their skills to textual queries [14, 10]. In addition, it is
could also be argued that this approach limits the desire possible for students to use the Query By Example builder
to explore the database and focusses on specific tasks. available in many software packages to form queries and
The analysis of users queries for feedback is often con- then use the SQL translation to submit the textual queries
tinued into providing assessment support for teachers of for grading without any understanding of their meaning
the language. Assessment is used in varying degrees in [3].
SQLator [23], WinRDBI [6], AsseSQL [20], LEARN-SQL
[1] and SQLify [5]. SQLator [23] and AsseSQL [20] use bi- Users can often fail to see the need for a textual sys-
nary gradings while Russell et al. [22] offers a percentage, tem when, from their perspective, the graphical system
SQLify [5] continues this to offer peer reviewing between performs all the necessary tasks with less opportunity for
students. error. Although the initial perception of graphical systems
may be that they offer the same capabilities of textual ones
but with less chance of error but this is not the case, as
3.2 Animation of queries
students move to more advanced queries they are likely to
The animation of queries can be used to illustrate how the build queries that are beyond the capabilities of graphical
final result of a query is achieved rather than it simply be- query builders.
 4 SQL in Steps (SiS) ising and visualising the result of an executed SQL state-
ment” [20]. This can lead students to make false assump-
In an attempt to address the issues uncovered in the liter- tions about a query they have written only to build on
ature SQL in Steps (SiS) was developed. The main focus those false assumptions until they execute their query and
of SiS is to allow the student to build an SQL SELECT attempt to debug the problem. Enabling a user to clearly
statement in small steps using a GUI while gaining an un- visualise the intermediate stages of a query is the basis
derstanding of the textual query they are building in the upon which many animated systems are created but SiS
background. The user interface is designed with the tex- continues this process further by illustrating every change
tual translation at the forefront. Every change made to made to the query rather than grouping them together.
the user interface prompts a change in the textual trans- The live updating UI allows the user to immediately iden-
lation which, in turn, refreshes the results of the query in tify the effect of each change made and also increases the
its current state. likelihood that they will identify the cause of any errors.
Cembalo et al. observed that students often solve proce- The user interface for SiS is intentionally not overly ab-
dural problems by breaking them down into smaller steps stract and closely follows the structure of an SQL clause.
and stated that this could not be done for SQL: “this ap- This allows the user to easily map the functions of the user
proach cannot be followed with SQL, because in a complex interface to the different components of an SQL statement.
query there are no intermediate steps to solve separately, For instance, to select attributes for the SELECT clause
but instead temporary sets of data which result from the involves selecting attributes using a check-box, selecting
execution of the different operators of the same query”[2]. relations to be used in the query is done by clicking on a
SiS introduces these intermediate steps and allows the stu- graphical representation of the database structure (Figure
dent to easily visualise the temporary sets of data which 1).
they can build upon to refine their query.
Bringing the textual translation of a query to the fore-
front and avoiding an excessively abstract user interface 4.2 Database visualisation
we address the difficulty of transferring from a graphical
to a textual environment. When learning SQL students may be exposed to a wide
variety of different database structures, they may be in-
Although SiS can loosely be categorised as an animated
troduced to more complex schemas as their understanding
system it is different to others previously discussed in that
increases. A common problem faced by many new users is
it guides the user towards incrementally building a query
the need to visualise the database schema before querying
before submitting it to view the results. This guidance
it; students should not be penalised for failing to remem-
through the process of building a query provides the user
ber the structure of a database but should be encouraged
with the steps taken to achieve the final result set, this
to learn how to best utilise the various different database
differs from other animated systems in that the steps are
schemas.
prompted entirely from the user and not through the use
of predefined stages. The student can choose to build their To familiarise the user with the structure of the database
SQL SELECT statement in any order they desire, for in- they are querying SiS presents them with a graphical rep-
stance, they could build the WHERE clause (and view its resentation of the database structure. This illustration
effects) then add some ordering information before contin- can be zoomed in and out to discover details such as the
uing to further refine the WHERE clause. attributes within a relation and the connections between
relations. As previously discussed, this part of the user in-
terface is not only used as a prompt when the user is inter-
4.1 Textual translations and live results rogating the database but is also used as a means to build
the FROM clause. The use of this visualisation, which
The aim of teaching SQL is to provide students with an un-
is visible at all times, provides the user with the ability
derstanding of SQL such that they are capable of building
to quickly build queries and explore the database without
textual queries independently, from the students perspec-
studying lengthy descriptions of the database schema.
tive this can initially seem a daunting task. As previously
discussed, attempts to simplify this process have involved
introducing students to graphical systems before transfer- 4.3 Automatic contextual help
ring to a textual user interface, however, this can make for
a difficult transition. Examples and demonstrations are an invaluable tool when
SiS attempts to avoid this problem by allowing the user learning any computer language [7], SQL is no exception.
to build a query using a simple user interface while the Descriptions of queries and examples of how their results
textual translation remains clear and up to date. SiS’ use can be achieved can provide an insight into new features
of a textual translation in conjunction with live results of SQL that are difficult to understand otherwise. How-
enables the user to gain confidence in the different clauses ever, if a student is learning within one domain and their
of SQL, their syntax and their effect on the results. If a examples are given within another they can disorient and
query were to contain an error or return no results this confuse the user. SiS includes the ability to provide con-
information is displayed to the user. textual examples for all clauses within the database the
Through the development of AsseSQL Prior et al. iden- user is using, SiS will analyse the structure and contents
tified the problem of students being unable to build a men- of the chosen database and automatically produce a series
tal picture of the intermediate stages in building an SQL of simple queries that can provide the user with an insight
query: “One of the difficulties for a student is conceptual- into the correct usage of each clause.
 Figure 1: The SiS learning environment

4.4 Customisable sults are returned. The understanding of Boolean expres-

sions is something that many novice users struggle with
To ensure SiS remains relevant and doesn’t overwhelm [18], CS students must build a strong understanding of
users large portions of it are customisable. Many of the such expressions to become confident in many key skills
features of SiS can be enabled/disabled within a configura- including writing SQL statements.
tion file; this allows an administrator to configure SiS such To allow the user to visualise their Boolean expressions
that it is only capable of simplistic queries using a limited in a more graphical sense SiS introduces a graphical rep-
set of SQL functionality if the more advanced features are resentation of Boolean expressions called the sandpit (Fig-
not required. ure 2). As with all elements of SiS the idea of the sandpit
The implementation of SiS using SQLite enables a wide is intentionally closely linked to its textual counterpart;
range of databases to be used by simply adding the each individual component of a Boolean expression is rep-
database files to a directory which the system can access. resented using a single widget on screen, the user can in-
This allows for the quick customisation of the system to put their criteria on this widget. Individual widgets are
meet the needs of various different users, as users become combined together by drawing boxes around them, these
more confident with SQL more complex databases can be boxes represent any Boolean expressions and their associ-
introduced, this allows teachers to easily expose students ated parenthesis, boxes can be in an AND or an OR state
to a wide variety of different database structures while they and can contain a mixture of both widgets and other boxes
learn SQL. enabling them to build complex Boolean expressions in a
graphical environment closely linked to its textual coun-
4.5 Web based terpart.

In recent years there has been a trend towards software

4.7 Limitations
that can be run in a web browser; ease of distribution and
cross platform compatibility make it an appealing option Mitrovic et al. identified how many elements of the SE-
for a wide array of applications. LECT statement are also found in other statements [17].
Like SQLator [23], SAVI [2], AsseSQL [20] and SQLify Because of this (like [17, 22, 11, 4, 2, 20, 5]) SiS focusses
[5] SiS has been developed from the ground up as a web entirely on the SELECT clause.
based tool, built using HTML, SQLite, JavaScript and Due to limitations introduced by the graphical repre-
PHP. The use of web based software means that students sentation of sub-queries SiS currently has limited support
do not have to install any specialist software and can access for them, it allows for the inclusion of sub queries in the
the tool from any computer connected to the internet. FROM clause alone.

4.6 Boolean visualisation 5 Typical use case of SiS

Building Boolean expressions is an important aspect of After selecting their chosen database the user is presented
creating an SQL query, they are used in the WHERE and with a graphical representation of the database and the UI
HAVING clauses of the SQL SELECT statement (along for building the SELECT clause. To explore the structure
with the WHERE clause in other statements) and a good of the database the user can use the zoom buttons above
understanding of them is crucial in ensuring the desired re- the graphical representation of the database schema, this
 Figure 2: The sandpit used for visualising Boolean expressions

will offer more or less information as required. If the graph niques alone. The undergraduate database course involves
becomes too crowded for the screen space available they introducing the students to various theoretical and prac-
can maximise the graph and view it in full screen mode. tical concepts, in the past the students have been taught
To build their first valid query the user must select a using the textual interface of Microsoft Access and a text
table (or series of connections for a query containing a based interface to SQLite or MySQL. The structure of the
join), as soon as this selection is made they can view the study will involve a small assessment of the students abil-
results of this simple query in the results panel below its ity at the start of the course (many will score very low
textual translation. After selecting a table or series of ta- as they may have little or no experience with relational
bles the contents of these are immediately shown to the databases), the sample will then be randomly divided into
user allowing them to adjust their FROM clause to en- two groups, the first will continue without any use of SiS,
sure they only use the information required for the query. the second will have access to SiS whenever they wish.
Details of the SELECT clause can be refined by choosing The databases set up with SiS will be the same as those
various attributes from the tick boxes available or adding databases the students will be learning with. At the end
customised ones using the aggregate function builder. Se- of the course the students will again be tested, if a greater
lecting the FROM tab at the top of the page allows the improvement is seen by those students using SiS then in-
user to customise the from clause, if the query only consists vestigations will be made to deduce whether this is as a
of one table this will offer little opportunity for customisa- result of the involvement of SiS or a simple coincidence.
tion but if multiple relations are used the user can change As well as attempting to quantify the benefits introduced
the join type, again viewing the results as the changes are by SiS a focus group involving a selection of SiS users
made. will take place after the study ends; the aims of this fo-
To filter out some of the records shown the user can cus group will be to gather qualitative data regarding the
select the WHERE (or the HAVING tab for filtering by positive and negative features of SiS as well as discussing
aggregate functions) tab and use the builder to construct future design iterations.
a sandpit of widgets and boxes representing a Boolean
expression, as with all aspects of SiS, and changes made
7 Conclusion
in the sandpit prompt immediate changes to the textual
translation and results.
In this paper we have provided a brief overview of some
The GROUP BY and ORDER BY tabs contain very existing technologies which have attempted to improve the
similar user interfaces to that on the SELECT tab, a series way in which students learn SQL. This existing work can
of check boxes can be used to build a grouping or ordering loosely be divided into two different categories, those sys-
clause. The LIMIT tab simply offers a simple input for two tems that operate by analysing submitted queries for feed-
numbers, the number of desired results and any offset. back and/or marking and those that use animation as a
If the user is unsure of how any of the clauses should means to break down the process of building SQL state-
be constructed or what their purpose is they can click the ments into easy to process steps. After highlighting some
question mark icon found on every tab. This will open the strengths and weaknesses within the existing literature we
contextual help which offers a described example of how introduce SQL in Steps (SiS), an online environment de-
to use the statement the user is struggling with. signed to improve the way in which users learn SQL by
combining a graphical user interface centred around a tex-
6 Further work tual translation with fast and frequent updates. Every
change made to the user interface prompts a change in
It is our intention to involve a selection of first year CS the textual translation of a query, this enables to user to
undergraduates with a trial involving the use of SQL in clearly see how changes to the UI force changes in the tex-
Steps. The user study will involve attempting to quantify tual query and, in turn, the results of the query. The fre-
the improvements gained by using SiS in conjunction with quency of updates allows the user to easily identify when
traditional teaching techniques over using traditional tech- errors have been introduced to the query allowing them to
quickly identify and understand them. Transferring users [11] R. Kearns, S. Shead, and A. Fekete. A teaching
from graphical user interfaces to textual ones is a well system for SQL. In Proceedings of the second Aus-
recognised problem, in an attempt to ease this the user tralasian conference on Computer science education
interface in SiS is intentionally not hugely abstract, the - ACSE ’97, pages 224–231, New York, New York,
close coupling between the UI and its textual equivalent is USA, July 1996. ACM Press.
intended to make the transition from graphical system to
textual as painless as possible. [12] V. Matos and R. Grasser. Teaching Tip A Simpler
The completion of a planned user study involving a se- (and Better) SQL Approach to Relational Division.
ries of first year undergraduates hopes to confirm the po- Journal of Information Systems Education, 13(2):85–
tential of introducing SiS into the learning process of SQL. 88, 2002.

[13] V. Matos, R. Grasser, and P. Jalics. The case of the

References missing tuple: teaching the SQL outer-join operator
to undergraduate information systems students. Jour-
[1] A. Abelló, M. E. Rodrı́guez, T. Urpı́, X. Burgués, nal of Computing Sciences in Colleges, 22(1):23–32,
M. J. Casany, C. Martı́n, and C. Quer. LEARN- Oct. 2006.
SQL: Automatic Assessment of SQL Based on IMS
QTI Specification. In 2008 Eighth IEEE International [14] J. Mayes and C. Fowler. Learning technology and
Conference on Advanced Learning Technologies, pages usability: a framework for understanding course-
592–593. IEEE, 2008. ware. Interacting with Computers, 11(5):485–497,
May 1999.
[2] M. Cembalo, A. De Santis, and U. Ferraro Petrillo.
SAVI: A new System for Advanced SQL Visualiza- [15] Microsoft. Microsoft Access - database software and
tion. In Proceedings of the 2011 conference on Infor- applications.
mation technology education - SIGITE ’11, page 165, [16] A. Mitrovic. A Knowledge-Based Teaching System
New York, New York, USA, Oct. 2011. ACM Press. for SQL. In ED-MEDIA 98, pages 1027–1032, 1998.
[3] J. Cigas and B. Kushan. Experiences with online SQL [17] A. Mitrovic. Learning SQL with a computerized tu-
environments. Journal of Computing Sciences in Col- tor. ACM SIGCSE Bulletin, 30(1):307–311, Mar.
leges, 25(5):251–257, May 2010. 1998.

[4] J. Danaparamita and W. Gatterbauer. QueryViz: [18] J. Nielsen. Search and You May
helping users understand SQL queries and their pat- Find. http://www.nngroup.com/articles/
terns. In EDBT/ICDT ’11, Uppsala, Sweden, 2011. search-and-you-may-find/, 1997. Accessed:
21st March 2014.
[5] M. de Raadt, S. Dekeyser, and T. Y. Lee. A sys-
tem employing peer review and enhanced computer [19] PhpMyAdmin. phpMyAdmin. http://www.
assisted assessment of querying skills. Informatics in phpmyadmin.net/home\_page/index.php. Accessed:
Education, 6(1):163–178, Oct. 2007. 28th May 2014.

[6] S. W. Dietrich, E. Eckert, and K. Piscator. WinRDBI: [20] J. C. Prior. Online assessment of SQL query formu-
A Windows-based Relational Database Educational lation skills. pages 247–256, Jan. 2003.
Tool. ACM SIGCSE Bulletin, 29(1):126–130, Mar.
[21] R. Rasala, V. K. Proulx, and H. J. Fell. From ani-
1997.
mation to analysis in introductory computer science.
[7] T. V. Gog, L. Kester, and F. Paas. Effects of worked ACM SIGCSE Bulletin, 26(1):61–65, Mar. 1994.
examples, example-problem, and problem-example [22] G. Russell and A. Cumming. Improving the student
pairs on novices’ learning. Contemporary Educational learning experience for SQL using automatic marking.
Psychology, 36(3):212–218, 2011. In Cognition and Exploratory Learning in Digital Age,
Jan. 2004.
[8] A. Grillenberger and T. Brinda. eledSQL. In Proceed-
ings of the 7th Workshop in Primary and Secondary [23] S. Sadiq, M. Orlowska, W. Sadiq, and J. Lin. SQLa-
Computing Education on - WiPSCE ’12, page 101, tor. ACM SIGCSE Bulletin, 36(3):223, Sept. 2004.
New York, New York, USA, Nov. 2012. ACM Press.
[24] B. Shneiderman. Improving the human factors as-
[9] HeidiSQL. HeidiSQL - MySQL and MSSQL made pect of database interactions. ACM Transactions on
easy. http://www.heidisql.com/. Accessed 28th Database Systems, 3(4):417–439, Dec. 1978.
May 2014.

[10] Karen Renaud and J. van Biljon. Teaching SQL -

Which Pedagogical Horse for This Course? volume
3112 of Lecture Notes in Computer Science, pages
244–256. Springer Berlin Heidelberg, Berlin, Heidel-
berg, 2004.