SQLite vs SQL: A Students Perspective

Introduction

SQLite is a C-language library that easily executes a small, fast, self-contained, high-reliability, and full-featured SQL database engine. As a result, SQLite has become one of the most used database engines in the world. SQLite is assembled into all mobile phones and most modern computers, and comes bundled with numerous other applications that people use every day.

Structured Query Language (SQL) is a domain-specific language used in application programming. Designed for managing data stored in a relational database management system (RDBMS), it is quite useful in handling structured data, i.e., data incorporating relations among entities and variables.

SQL offers two main advantages over older read–write APIs such. Firstly, it introduces the notion of accessing multiple records with one single command. Secondly, it eliminates the necessity to specify how to reach a record, e.g., with or without an index.

So Why Use SQLite in Academia?

SQLite is a lightweight variant of SQL, and as noted on the SQLite official website: Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. In addition, SQLite strives to provide local data storage for individual applications and devices.

As such, SQLite is remarkably straightforward to both set up and run, while a standard SQL engine requires setting up an entire operational server, with complex configuration settings. Therefore, by downloading an application several megabytes in size, students can quickly run SQL-like queries on any database they want to.

SQLite Syntax Difference

SQLite is a considerably more ‘tolerant’ language than traditional SQL, so queries that typically raise an error in SQL, will be extrapolated and run successfully with SQLite.

Next, we will go over some of students’ most common errors and how autograders tend to complain about them.

Specifically:

• There is support for LEFT OUTER JOIN but not RIGHT OUTER or FULL OUTER.
• To get equivalent output to RIGHT OUTER you can reverse the order of the tables (i.e. A RIGHT JOIN B is the same as B LEFT JOIN A.
• While it isn’t required to complete this assignment, the equivalent to FULL OUTER JOIN can be done by UNIONing RIGHT OUTER and LEFT OUTER
• There is no regex match (~) tilde operator. You can use LIKE instead.
• There is no ANY or ALL operator.

Most Common SQL Errors

• Use the alias directly in the WHERE/ HAVING clause.

SELECT birthyear, AGG(col1) AS temp, …

FROM studentclass

GROUP BY birthyear

HAVING temp > “orary”

…

The problem here is that SELECT is applied after the TAs are “GROUP BY”ed and filtered by “HAVING”.

At the stage of “HAVING”, the SQL engine doesn’t understand the alias “temp” in the SELECT clause yet.

• “==”

Something that students tend to learn early in any Computer Science class is that computers start at index 0, and “=” is the assignment operator rather than comparison.

This convention will be broken in the SQL world, where students should use “=” for direct comparison.

• (INNER | { LEFT | RIGHT | FULL } [OUTER]) JOIN without a join condition

In SQL, only NATURAL JOIN does not require a join condition as it automatically infers the common column names. It is the language’s rule that you are required to give some conditions with the ON clause.

• GROUP BY without aggregate

This is one of the most common mistakes made by students using SQLite.

Now, let’s look at the following example, where we are trying to gain insight into the attendance rate of each student in class 101, displayed with their sid, number of appearances in sections, and their names.

SELECT s.sid, SUM(a.attendance) AS attend_rate, s.name

FROM 101_students s INNER JOIN section_attendance a

ON s.sid = a.sid

GROUP BY s.sid

…

In this SQL query, s.sid will be recognized without any issue, as it’s the GROUP BY key; the same for SUM(a.attendance), as it is the Aggregate column.

But how about s.name? It doesn’t fall into either of the permissible categories, so it is incorrect to use it here.

OK, So is SQLite Untrustworthy Then?

Students may be concerned that some code that executes successfully in the SQLite engine will fail the autograder review?

As SQLite is a commercial use database engine, it is fault-tolerant; that is to say, it detects many syntax issues. The ones noted above are just slightly more demanding syntax rules in SQL.

Therefore if your SQL code passes the SQLite check and follows the rules you have been taught in your class, you should be as good as gold.