SQLite Syllabus Concept Discussion [Feedback needed]

Hello,
Here is a list of concepts for which we can add exercises in the SQLite track. Feel free to add your ideas and suggestions.

Do let me know if the ordering feels off or if you want to add/delete a concept. The exercise examples are just a starting point, and we can definitely have more engaging ones once we have decided to agree on the topics.

Concept Teaches Notes/Example
Basics/Intro to RDBMS The relational model of rows and tables via the SELECT * query which shows all records getting selected and helps the student develop a mental model
CREATE How to create a table
Primary key constraint Why is it necessary? How to define it? Creating a table of students some of which have the same names and making Student ID unique that shows why the constraint is needed
NULL NULL & the concept of 3-way logic Example: In an Orders table, orders that haven’t shipped yet will have the shipping date as NULL
INSERT How to add one or multiple records to the table
SELECT How to get only the records we want Select the names of all students
DISTINCT Removing duplicate results Select distinct products from Amazon
LIMIT Extracting the number of rows returned Testing queries to verify if they are working as expected
OFFSET Skipping a specified number of rows before fetching the remaining This can be taught together with the LIMIT clause to implement pagination
Sorting results with ORDER BY Ordering records in ascending/descending order based on one or more columns It can be used with LIMIT to implement TOP N: Fetch TOP N Exercism users based on reputation.
WHERE (with conditions like AND/OR) Filtering based on the condition we want We have a table with customer data and we want to have personalized marketing campaigns for them, for example: people younger than 25 AND living in Delhi
Pattern Matching using LIKE Searching for a specified pattern in a string using wildcards Ex: searching for all Samsung Galaxy phone models on Amazon with the name LIKE “%galaxy%”
Math functions Functions to work with numeric values like ABS, ROUND, FLOOR, CEIL, etc.
String functions Functions useful for string values like REPLACE, SUBSTR, TRIM, etc.
COUNT How to count the number of values in a column Count the number of movies produced by a production company
MIN/MAX Get the min/max value from a column Highest-grossing movie produced by the production company
SUM Getting the total of “numeric” values in the table Total Revenue for all movies produced
AVERAGE Getting the average of all non-null values Average rating of Movie on IMDB
GROUP BY Grouping data by a column or set of columns Highest grossing movie by genre/total movies in 2023 by genre to know what the audience is consuming
Functions such as CONCAT, SUBSTRING, LENGTH How to use Common Utility functions Ex: From a table with orders in the following format: ORD-XXXXXX where XXXXXX represents a unique identifier, we want to extract the unique identifier portion from each order number.
Renaming columns with AS How to give descriptive name to columns Table has a first name and last name. Full Name can be fetched with CONCAT and renamed with AS
JOINS Combine data from multiple tables based on foreign key
INNER JOIN Combining only matching records Ex: Database with Customers and orders data. Customers who made the most number of orders
LEFT/RIGHT JOIN When you need rows that don’t match as well We want to know customers who haven’t ordered yet
UPDATE Updating one or more records based on some condition Someone changed their address, or we want to standardize data, eg: U.S.A/USA to United States of America
DELETE Deleting one or more records from the table Data cleanup: deleting records older than a specific date
DROP TABLE Dropping the table entirely Via the use case of a table being redundant because the schema changed or the table was temporary
ALTER TABLE How to change the structure: adding/removing columns, adding constraints Example: Table stores employee data like ID, name, and hiring date. But now we want to track employee address/email as well
dot commands meta commands usually used to interact with sqlite CLI itself Example: .save to save a database
Subquery Improving readability
Common Table Expressions How to define complex logic/intermediate result set once and reference it when needed
Recursive Table Expressions Traverse a hierarchical or tree like structure Example: We could use a recursive CTE to retrieve the hierarchical structure of files and directories within a file system
User-defined functions Creating custom functions Example: SELECT circle_area(5) which returns the area of a circle with radius 5
1 Like

+cc @BNAndras @glennj @vaeng

Thanks for bringing those topics together.

I would also include the math and string functions and say some things about the sqlite specific things that are unique for the language (those dot functions like .load) and what is different to other sql langauges (e.g. stored procedures).

2 Likes

Thanks for the feedback. Should I edit the post itself to include them?

Yes, please do so :slight_smile:

Maybe Common Table Queries could be separated into:

  • Ordinary Common Table Queries
  • Recursive Common Table Queries

I don’t know if that makes sense, but I feel that recursive queries are probably best left as a separate concept.

2 Likes

After pulling out my hair last night trying to port the reverse-string exercise, I strongly concur on separating the two as well. :slight_smile:

2 Likes

Added the suggestions. Apologies for the delay.

1 Like

Hi,
I just wanted to know if work has started on creating the exercises for this track. If yes, who should I get in touch with? I would like to contribute. Thanks!

I believe there has been no work towards writing a syllabus for this track. Would you like to write the concept docs?

I’m sort of not convinced that we’re well positioned to teach SQL here. What topics do we imagine the syllabus would cover?

Yes, definitely I can contribute to writing the concept docs, once agreement is reached on the topics to include.

Which topics do you suggest we must include and which ones do you have doubts on?

The table in the first part would be the concepts.

Hi @IsaacG @iHiD , is there any plan on adding more exercises based on these concepts (or even some other exercises)?

I defer to iHiD here. Personally I think it’d be nice to create a syllabus based on the above concepts. We’d want a matching exercise for each topic as the syllabus is written.

1 Like

Maybe add window functions as well to the concepts? They are kind of a big deal.

1 Like

Agreed, they are important!

Okay
@iHiD How do you think we should go about creating the syllabus?

I’d suggest starting by PRing a first Concept Exercise and getting feedback. I strongly recommend looking at other tracks to get a feel for what they’ve done.

In @IsaacG @BNAndras @glennj @vaeng, you have a lot of experienced creators so I’ll defer to their wisdom as how to best approach all this for SQLite! :slight_smile:

2 Likes