research-article

Errors and Complications in SQL Query Formulation

Authors:

Tero VartiainenAuthors Info & Claims

ACM Transactions on Computing Education (TOCE), Volume 18, Issue 3

Article No.: 15, Pages 1 - 29

https://doi.org/10.1145/3231712

Published: 09 August 2018 Publication History

Abstract

SQL is taught in almost all university level database courses, yet SQL has received relatively little attention in educational research. In this study, we present a database management system independent categorization of SQL query errors that students make in an introductory database course. We base the categorization on previous literature, present a class of logical errors that has not been studied in detail, and review and complement these findings by analyzing over 33,000 SQL queries submitted by students. Our analysis verifies error findings presented in previous literature and reveals new types of errors, namely logical errors recurring in similar manners among different students. We present a listing of fundamental SQL query concepts we have identified and based our exercises on, a categorization of different errors and complications, and an operational model for designing SQL exercises.

References

[1]

Alireza Ahadi, Vahid Behbood, Arto Vihavainen, Julia Prior, and Raymond Lister. 2016. Students’ syntactic mistakes in writing seven different types of SQL queries and its application to predicting students’ success. In Proceedings of the 47th ACM Technical Symposium on Computing Science Education (SIGCSE’16). ACM Press, New York, New York, 401--406.

Digital Library

[2]

Alireza Ahadi, Julia Prior, Vahid Behbood, and Raymond Lister. 2015. A quantitative study of the relative difficulty for novices of writing seven different types of SQL queries. In Proceedings of the 2015 ACM Conference on Innovation and Technology in Computer Science Education (ITiCSE’15). ACM Press, New York, New York, 201--206.

Digital Library

[3]

Alireza Ahadi, Julia Prior, Vahid Behbood, and Raymond Lister. 2016. Students’ semantic mistakes in writing seven different types of SQL queries. In Proceedings of the 2016 ACM Conference on Innovation and Technology in Computer Science Education (SIGCSE’16). 272--277.

Digital Library

[4]

Stefan Brass and Christian Goldberg. 2005. Semantic errors in SQL queries: A quite complete list. J. Syst. Softw. 79, 5 (2005), 630--644.

Digital Library

[5]

Peter Brusilovsky, Sergey Sosnovsky, Michael V. Uydelson, Danielle H. Lee, Vladimir Zadorozhny, and Xin Zhou. 2010. Learning SQL programming with interactive tools: From integration to personalization. ACM Trans. Comput. Educ. 9, 4 (2010), 367--376.

Digital Library

[6]

R. B. Buitendijk. 1988. Logical errors in database SQL retrieval queries. Comput. Sci. Econ. Manag. 1, 2 (1988), 79--96.

Digital Library

[7]

Gretchen Irwin Casterella and Leo Vijayasarathy. 2013. An experimental investigation of complexity in database query formulation tasks. J. Inf. Syst. Educ. 24, 3 (2013), 211--221. Retrieved from http://jise.org/Volume24/24-3/pdf/Vol24-3pg211.pdf.

[8]

Ugur Cetintemel, Nesime Tatbul, Kristin Tufte, Hao Wang, Stanley Zdonik, Jiang Du, Tim Kraska, Samuel Madden, David Maier, John Meehan, Andrew Pavlo, Michael Stonebraker, and Erik Sutherland. 2014. S-store: A streaming NewSQL system for big velocity applications. Proc. VLDB Endow. 7, 13 (2014), 1633--1636.

Digital Library

[9]

Anthony Cleve, Maxime Gobert, Loup Meurice, Jerome Maes, and Jens Weber. 2015. Understanding database schema evolution: A case study. Sci. Comput. Program. 97, P1 (2015), 113--121.

Digital Library

[10]

Christopher J. Date. 1983. Critique of the SQL database language. SIGMOD Rec. 14, 3 (Nov 1983).

Digital Library

[11]

Alireza Ebrahimi. 1994. Novice programmer errors: Language constructs and plan composition. Int. J. Hum. Comput. Stud. 41 (1994), 457--480.

Digital Library

[12]

Ramez Elmasri and Shamkant B. Navathe. 2016. Fundamentals of Database Systems (7th. ed.). Pearson.

[13]

Sally Fincher, Josh Tenenberg, and Anthony Robins. 2011. Research design : Necessary bricolage. Comput. Sci. Educ. (2011), 27--32.

Digital Library

[14]

Michael M. Gorman. 2005. Is SQL a real standard anymore? (October 2005). Retrieved June 2016 from https://www.wiscorp.com/is_sql_a_real_standard.pdf.

[15]

Linda Grandell, Mia Peltomäki, Ralph Johan Back, and Tapio Salakoski. 2006. Why complicate things? Introducing programming in high school using Python. Conf. Res. Pract. Inf. Technol. Ser. 52 (2006), 71--80.

Digital Library

[16]

Sharon L. Greene, Susan J. Devlin, Philip E. Cannata, and Louis M. Gomez. 1990. No IFs, ANDs, or ORs: A study of database querying. Int. J. Man. Mach. Stud. 32, 3 (Mar. 1990), 303--326.

Digital Library

[17]

Eric Gregoire, Richard Ostrowski, Bertrand Mazure, and Lahkdar Sais. 2005. Automatic extraction of functional dependencies. Theory Appl. Satisf. Test. 3542 (2005), 122--132.

Digital Library

[18]

Maria Hristova, Ananya Misra, Megan Rutter, and Rebecca Mercuri. 2003. Identifying and correcting java programming errors for introductory computer science students. ACM SIGCSE Bull. 35, 1 (Jan. 2003), 153.

Digital Library

[19]

Hsiu-Fang Hsieh and Sarah E. Shannon. 2005. Three approaches to qualitative content analysis. Qual. Health Res. 15, 9 (2005), 1277--1288.

[20]

Ykä Huhtala, Juha Kärkkäinen, Pasi Porkka, and Hannu Toivonen. 1999. Tane: An efficient algorithm for discovering functional and approximate dependencies. Comput. J. 42, 2 (Feb 1999), 100--111.

[21]

Ihab F. Ilyas, Volker Markl, Peter Haas, Paul Brown, and Ashraf Aboulnaga. 2004. CORDS: Automatic discovery of correlations and soft functional dependencies. In Proceedings of the 2004 ACM International Conference on Management of Data (SIGMOD’04). ACM Press, New York, New York, 647.

Digital Library

[22]

ISO/IEC. 2016. ISO/IEC 9075-2:2016, “SQL - Part 2: Foundation”.

[23]

Eranki L. N. Kiran and Kannan M. Moudgalya. 2015. Evaluation of programming competency using student error patterns. In Proceedings of the International Conference on Learning and Teaching in Computing and Engineering. IEEE, 34--41.

Digital Library

[24]

A. J. Ko and B. A. Myers. 2003. Development and evaluation of a model of programming errors. In Proceedings of the IEEE Symposium on Human Centric Computing Languages and Environments. IEEE, 7--14.

Digital Library

[25]

Charles R. Litecky and Gordon B. Davis. 1976. A study of errors, error-proneness, and error diagnosis in cobol. Commun. ACM 19, 1 (Jan. 1976), 33--38.

Digital Library

[26]

Victor M. Matos and Rebecca Grasser. 2002. Teaching tip a simpler (and better) SQL approach to relational division. J. Inf. Syst. Educ. 13, 2 (2002), 85--88. Retrieved from http://jise.org/Volume13/Pdf/085.pdf.

[27]

Jim Melton. 2002. SQL:1999: Understanding Relational Language Components. Morgan Kaufman.

Digital Library

[28]

Antonija Mitrovic. 1998. Learning SQL with a computerized tutor. ACM SIGCSE Bulletin 30, 1 (1998), 307--311.

Digital Library

[29]

Thomas H. Park, Brian Dorn, and Andrea Forte. 2015. An analysis of HTML and CSS syntax errors in a web development course. ACM Trans. Comput. Educ. 15, 1 (2015), 4:1--4:21.

Digital Library

[30]

Julia Prior. 2003. Online assessment of SQL query formulation skills. Proceedings of the 5th Australasian Computing Education Conference 20 (2003), 247--256. Retrieved from http://dl.acm.org/citation.cfm?id=858403.858433.

Digital Library

[31]

Gary B. Randolph. 2003. The forest and the trees: Using oracle and SQL server together to teach ANSI-standard SQL. Design (2003), 234--236.

Digital Library

[32]

Julian Rith, Philipp S. Lehmayr, and Klaus Meyer-Wegener. 2014. Speaking in tongues: SQL access to NoSQL systems. Proceedings of the 29th Annual ACM Symposium on Applied Computing (SAC’14) (2014), 855--857.

Digital Library

[33]

Yasin N. Silva, Isadora Almeida, and Michell Queiroz. 2016. SQL: From traditional databases to big data. In Proceedings of the 47th ACM Technical Symposium on Computing Science Education (SIGCSE’16). ACM Press, New York, New York, 413--418.

Digital Library

[34]

John B. Smelcer. 1995. User errors in database query composition. Int. J. Hum. Comput. Stud. 42, 4 (Apr. 1995), 353--381.

Digital Library

[35]

Andreas Stefik and Susanna Siebert. 2013. An empirical investigation into programming language syntax. ACM Trans. Comput. Educ. 13, 4 (2013), 1--40.

Digital Library

[36]

Josh Tenenberg and Robert McCartney. 2010. Why discipline matters in computing education scholarship. ACM Trans. Comput. Educ. 9, 4 (2010), 1--7.

Digital Library

[37]

Heikki Topi, Kate M. Kaiser, Janice C. Sipior, Joseph S. Valacich, J. F. Nunamaker, Jr., G. J. de Vreede, and Ryan Wright. 2010. Curriculum Guidelines for Undergraduate Degree Programs in Information Systems. Technical Report. New York, NY, USA. https://dl.acm.org/citation.cfm?id=2593310.

[38]

Geoff Walsham. 2006. Doing interpretive research. Eur. J. Inf. Syst. 15, 3 (2006), 320--330.

[39]

Charles Welty. 1985. Correcting user errors in SQL. Int. J. Man. Mach. Stud. 22, 4 (1985), 463--477.

[40]

Charles Welty and David Stemple. 1981. Human factors comparison of a procedural and a nonprocedural query language. ACM Trans. Database Syst. 6, 4 (1981), 626--649.

Digital Library

[41]

Li-Yan Yuan, Lengdong Wu, Jia-Huai You, and Yan Chi. A demonstration of rubato DB: A highly scalable NewSQL database system for OLTP and big data applications. Proceedings of the 2015 ACM International Conference on Management of Data (SIGMOD’15). 907--912.

Digital Library

Cited By

Aerts WFletcher GMiedema D(2024)A Feasibility Study on Automated SQL Exercise Generation with ChatGPT-3.5Proceedings of the 3rd International Workshop on Data Systems Education: Bridging education practice with education research10.1145/3663649.3664368(13-19)Online publication date: 9-Jun-2024
https://dl.acm.org/doi/10.1145/3663649.3664368
Wang JChen STang ZLin PWang Y(2024)False Positives and Deceptive Errors in SQL Assessment: A Large-Scale Analysis of Online Judge SystemsACM Transactions on Computing Education10.1145/365467724:3(1-23)Online publication date: 28-Mar-2024
https://dl.acm.org/doi/10.1145/3654677
Miedema DTaipalus TAjanovski VAlawini AGoodfellow MLiut MPeltsverger SYoung TMonga MLonati VBarendsen ESheard JPaterson J(2024)Curriculum Analysis for Data Systems EducationProceedings of the 2024 on Innovation and Technology in Computer Science Education V. 210.1145/3649405.3659529(761-762)Online publication date: 8-Jul-2024
https://dl.acm.org/doi/10.1145/3649405.3659529
Show More Cited By

Index Terms

Errors and Complications in SQL Query Formulation
1. Social and professional topics
  1. Professional topics
    1. Computing education
      1. Computing education programs
        Computer science education
      2. Model curricula

Recommendations

Semantic errors in SQL queries: A quite complete list
Special issue: Quality software

We investigate classes of SQL queries which are syntactically correct, but certainly not intended, no matter for which task the query was written. For instance, queries that are contradictory, i.e. always return the empty set, are obviously not ...
Incorporating NoSQL into a database course

This article introduces the concepts of Big Data and NoSQL and describes a semester long web-based project that uses both a relational database (Oracle 11g) and a NoSQL (MongoDB) database for an undergraduate database course. The relational database ...
Translating SQL Into Relational Algebra: Optimization, Semantics, and Equivalence of SQL Queries

In this paper, we present a translator from a relevant subset of SQL into relational algebra. The translation is syntax-directed, with translation rules associated with grammar productions; each production corresponds to a particular type of SQL ...

Comments

Please enable JavaScript to view thecomments powered by Disqus.

Information & Contributors

Information

Published In

cover image ACM Transactions on Computing Education

ACM Transactions on Computing Education Volume 18, Issue 3

September 2018

120 pages

EISSN:1946-6226

DOI:10.1145/3266362

Editor:
Chris Hundhausen
Washington State University, USA

Issue’s Table of Contents

Copyright © 2018 ACM.

Permission to make digital or hard copies of all or part of this work for personal or 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 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 [email protected]

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 09 August 2018

Accepted: 01 December 2017

Revised: 01 October 2017

Received: 01 September 2016

Published in TOCE Volume 18, Issue 3

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article
Research
Refereed

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

44
Total Citations
View Citations
854
Total Downloads

Downloads (Last 12 months)151
Downloads (Last 6 weeks)12

Reflects downloads up to 16 Nov 2024

Other Metrics

View Author Metrics

Citations

Cited By

Aerts WFletcher GMiedema D(2024)A Feasibility Study on Automated SQL Exercise Generation with ChatGPT-3.5Proceedings of the 3rd International Workshop on Data Systems Education: Bridging education practice with education research10.1145/3663649.3664368(13-19)Online publication date: 9-Jun-2024
https://dl.acm.org/doi/10.1145/3663649.3664368
Wang JChen STang ZLin PWang Y(2024)False Positives and Deceptive Errors in SQL Assessment: A Large-Scale Analysis of Online Judge SystemsACM Transactions on Computing Education10.1145/365467724:3(1-23)Online publication date: 28-Mar-2024
https://dl.acm.org/doi/10.1145/3654677
Miedema DTaipalus TAjanovski VAlawini AGoodfellow MLiut MPeltsverger SYoung TMonga MLonati VBarendsen ESheard JPaterson J(2024)Curriculum Analysis for Data Systems EducationProceedings of the 2024 on Innovation and Technology in Computer Science Education V. 210.1145/3649405.3659529(761-762)Online publication date: 8-Jul-2024
https://dl.acm.org/doi/10.1145/3649405.3659529
Taipalus TGrahn HMonga MLonati VBarendsen ESheard JPaterson J(2024)Building Blocks Towards More Effective SQL Error MessagesProceedings of the 2024 on Innovation and Technology in Computer Science Education V. 110.1145/3649217.3653552(241-247)Online publication date: 3-Jul-2024
https://dl.acm.org/doi/10.1145/3649217.3653552
Haroon SBrown CGulzar M(2024)DeSQL: Interactive Debugging of SQL in Data-Intensive Scalable ComputingProceedings of the ACM on Software Engineering10.1145/36437611:FSE(767-788)Online publication date: 12-Jul-2024
https://dl.acm.org/doi/10.1145/3643761
Miedema DLiut MFletcher GAivaloglou E(2023)“There is no ambiguity on what to return”: Investigating the Prevalence of SQL MisconceptionsProceedings of the 23rd Koli Calling International Conference on Computing Education Research10.1145/3631802.3631821(1-12)Online publication date: 13-Nov-2023
https://dl.acm.org/doi/10.1145/3631802.3631821
Taipalus TGrahn H(2023)Framework for SQL Error Message Design: A Data-Driven ApproachACM Transactions on Software Engineering and Methodology10.1145/360718033:1(1-50)Online publication date: 23-Nov-2023
https://dl.acm.org/doi/10.1145/3607180
Yang SHerman GAlawini AAivaloglou EFletcher GMiedema D(2023)Mining SQL Problem Solving Patterns using Advanced Sequence Processing AlgorithmsProceedings of the 2nd International Workshop on Data Systems Education: Bridging education practice with education research10.1145/3596673.3596973(37-43)Online publication date: 23-Jun-2023
https://dl.acm.org/doi/10.1145/3596673.3596973
Langlands EMorales-Trujillo MLaakso MMonga MSimon Sheard J(2023)Learning from Errors: An Empirical Study on the Impact of Gamification on SQL Query FormulationProceedings of the 2023 Conference on Innovation and Technology in Computer Science Education V. 110.1145/3587102.3588821(341-347)Online publication date: 29-Jun-2023
https://dl.acm.org/doi/10.1145/3587102.3588821
Taipalus TMiedema DAivaloglou ELaakso MMonga MSimon Sheard J(2023)Engaging Databases for Data Systems EducationProceedings of the 2023 Conference on Innovation and Technology in Computer Science Education V. 110.1145/3587102.3588804(334-340)Online publication date: 29-Jun-2023
https://dl.acm.org/doi/10.1145/3587102.3588804
Show More Cited By

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Article

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Issue’s Table of Contents