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%”
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
Subquery Improving readability
Common Table Expressions How to define complex logic/intermediate result set once and reference it when needed
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.

1 Like

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:

1 Like