The SQLite exercises on Exercism appear to prioritize alignment with the exercises in the other language tracks over presenting database-oriented problems for which SQLite is best suited. After having solved a few of the SQLite exercises, I understand the typical form to be something like “Given inputs A, B, and C in a single row in table T, update the Result in the same row.” For example, in the Bottle Song exercise, the goal is to update the bottle-song table, setting the result column according to the inputs given in the start_bottles and take_down columns, where each test case operates on a single row in the table. I found the Bottle Song exercise to be fun and interesting, but I’m not sure whether such exercises are relevant to the real world.
In contrast, the SQL-related problems on HackerRank appear to be structured more like real-world database problems. HackerRank does not offer SQLite exercises, but it does offer MySQL exercises in several categories, including query exercises. For the MySQL query exercises, the goal is to construct a query producing the expected results from a given set of input tables with multiple rows of data, in which the test cases operate on all input rows.
Any thoughts from the community on adding more exercises to the SQLite track that are more database-oriented? I am by no means an expert on databases or SQL, but I could certainly contribute a few basic query problems patterned after those on HackerRank.
Exercism uses a model where all tracks (languages) share practice problems from a shared problem repository. While track specific practice problems can be added, we generally do not do so.
In contrast, the concept exercises are custom written for the track to showcase/practice a specific aspect of the language. Concept exercises are paired with a concept doc/lesson and are part of the syllabus.
SQLite would definitely benefit from a syllabus … but no one as of yet volunteered to build out the syllabus for it. There’s been discussions around it (eg here and here), but no commitment.
I’m happy to see someone(s) volunteer to build out the syllabus. I’m less keen on practice exercises which are not part of the problem spec.
I would be happy to help. I am not a SQLite domain expert, so I would need to rely on others to help review the content, but I think my writing skills are pretty good. Is a syllabus something that can be developed incrementally, or do we need to agree upon the complete outline before starting work on it?
The syllabus docs talk about creating a first concept exercise and building out from there. What would you think about me starting on a first concept exercise – probably a simple query from a table with a few rows?
Also, in general I think the existing exercises might be more relevant if they were structured as queries instead of inserting the results into a table. Could I propose such a change to the Hello World exercise to begin with?
How would that impact the testing framework? How would the tests work with that change?
Yes, concepts should be built one at a time A simple SELECT seems like the natural place to start.
I believe (and hope) I can speak on behalf on all the SQLite maintainers by saying we would welcome a PR which puts together a simple concept doc/exercise.
Not sure yet, but if I can get the first basic query concept exercise to work with the test framework, then I think the Hello World and other practice exercises could be easily adapted.
I would strongly suggest either (1) figuring out how you’d make an existing exercise work with a SELECT and not an UPDATE or (2) working on a concept using the existing setup. Don’t try to do both at the same time. That’s setting yourself up for unneeded pain.
Great suggestion, @IsaacG! I am leaning towards (1) at this point. But first, I think I want to spend more time solving the existing exercises, to get a better feel for the existing infrastructure.
From the perspective of understanding the test setup, the tests are all set up more or less the same way across all the exercises. Once you solved one, you’ve seen how the testing works for them all. If you want to further understand how the tests work, you’d want to look at the test code. Solving more exercises isn’t going to teach you much additional information about how the test setup works.
Not sure yet, but I will try to adapt a few other exercises in a similar manner.
Off the top of my head, maybe tests that have more than one test case could be collapsed down to a single test case operating on a single table, where each row of the table has input data that reflects one of the original test cases. I’ll attempt to demonstrate what I mean in a PR on the Leap exercise.
Sorry, I think I went off the rails a bit there. I didn’t fully understand the role of the shared problem-specifications repo until now. I will close the Hello World PR and instead start working on a simple query concept doc/exercise.
For the basic query/SELECT concept, I’m thinking about covering only the basics of the FROM and WHERE clauses, and maybe the DISTINCT modifier. How does that sound?
Also, I’m thinking about starting with just the document for now, and deferring the exercise to a later time (the exercise itself is probably simple enough, but I expect it will require some not-so-simple changes to the test framework). Does that make sense?
I would suggest skipping DISTINCT from the first intro to SELECT. Given how the tests are structured, an UPDATE (see Hello World) may make a more logical starting place.
Typically concept docs are paired with an exercise. You could work on the doc first, though. However, we may hold off on merging the PR until the exercise is also present.
A simple