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 |
