I have researched and compiled SQL “concepts” to serve as an initial discussion. I’m unsure if the terms and organization I used are appropriate or even relevant in the context. I also had doubts about a “Basics” category, and in this initial phase, I avoided trying to create a dependency-based map.
What would need to be added, removed, modified, redesigned, etc. in the following list?
Data Query Language (DQL)
SELECT (CASE, GROUP BY, HAVING, JOIN, ORDER BY, WHERE, subqueries)
Data Manipulation Language (DML)
DELETE, INSERT, UPDATE
Data Definition Language (DDL)
ALTER, CREATE, DROP, RENAME, TRUNCATE
Transaction Control Language (TCL)
BEGIN, END, COMMIT, ROLLBACK
Data Types
Numerical, String, Date and Time, Boolean, Binary, JSON
Table-Valued Function (TVF)
GENERATE_SERIES, JSON_EACH, JSON_TREE
My first impulse (so hot take…) here would be to omit the final 4 bullets, and also CTEs. These vary greatly in syntax by major vendor and implementation, so they are a tad problematic. And I am a little iffy on the JSON stuff. Not all DBs support that in the same way, and some DBs use BISON instead of JSON.
My other thought is that you might want to add some “wider” concepts like:
“declarative vs imperative” (In a query, you ask for the data, the RDBMS engine choose how it retrieves it.)
Stored procedures
I’m fine in remove it because sqlite does not support this feature directly, triggers are needed to implement something similar.
Triggers
I consider this topic very important. In fact, some discussions about Error Handling involve it.
Indexing and query optimization
I’m fine in remove it too.
JSON Manipulation
This topic is very important for the track. Many exercises use json data stored in columns, and I use json a lot to solve exercises, especially the more difficult ones. (Maybe we can create a separated category for these “strange” sqlite things).
For now, I’ll omit than all anyway.
About the wider concepts, I think I’ll omit them for now as well.
*But I’m not planning on going through all these concepts as if they were practice exercises. I prefer that teachers and/or mentors do that part. I’m just trying to reduce the work of structuring and compiling what needs to be done.
In that case, I feel there should be a JSON concept in the syllabus. Then you can have that concept be the prerequisite to unlocking the JSON-centric exercises. Students following the syllabus then get a concept exercise that can ease them into solving those other exercises.
The track maintainers would be the ones that are in charge of the syllabus and its overarching goals / implementation. Mentors aren’t generally involved because ideally the concept exercises should be straight-forward enough that folks are comfortable with the material and don’t need further assistance. Of course, contributors with some mentoring under their belt likely have a sense of what to look out for in a syllabus.
I’m not aware of anyone planning on creating a sqlite syllabus. Unless you’re planning on creating the concept docs and exercises, compiling a list of concepts might not have a ton of value.
SQL lite JSON support is different than for example PSQLs, but a lot of the concepts do translate to other SQL languages.
I also agree with @BethanyG that Window Functions should be included. It’s a hard concept that exists in most if not all SQL implementations. Luckily you have already listed it. If needed it could be split into multiple concepts later. SQL window functions: Rolling aggregates is an interesting resource!
I do think indexing is REALLY important but it’s hard to write exercises for, I think.
Yes, you’re right. I hadn’t thought much about it and my first idea was just “explain analyze”. But we would need big tables and I think it’s not a good idea…
Now, thinking beyond that, at least for some concepts, it will not be possible write tests using just sqlite/sql. Maybe shell-script could be enough but it would be a big change in how things are currently tested. I have no idea if all this freedom would be possible.
In TS per exercise the config meta file has flags which type of tests should be ran. You could do something similar and have the test runner read those files and act accordingly.
I’m not 100% sure about SQLite, but any other SQL DB I know allows inspection / manipulation of DB table structures and meta data. So for a concept exercise on indexing one could assert on these system table entries like they were user table data.
And, yes, I also think indexing is VERY important and “fluency in a language” of this kind should definitely include knowledge about the most fundamental data retrieval optimisation technique.
If I understood correctly, we would just test whether a certain index was created on a certain table, which is just a syntax test, right?
In this case, we could do:
CREATE TABLE tracks (
slug TEXT PRIMARY KEY,
title TEXT NOT NULL,
exercises INTEGER NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS tracks_title_idx ON tracks (title);
SELECT *
FROM pragma_index_list('tracks')
WHERE name = 'tracks_title_idx'
;
There other ways to query indexes and other “objects”, I have never explored this part much in sqlite.
, since I haven’t really kept up.
Yes, you’re right. I hadn’t thought much about it and my first idea was just “explain analyze”. But we would need big tables and I think it’s not a good idea…