Create a local SQL database and practice filtering, grouping and missing values.
Before starting: Basic SQL SELECT and tables.
Practice project: Extend the examples into a small SQLite study demo. Explain the setup, one normal case and one failure or boundary case.
Start here: concepts and guided practice
- Create and query an in-memory SQLite table
SQLite is an embedded SQL database engine, not a separate programming language. This example creates a table, inserts two rows and returns the one whose points reach five. ORDER BY defines the result order.
SQLite 3 CLI or Python sqlite3; disposable in-memory database
CREATE TABLE scores (name TEXT NOT NULL, points INTEGER NOT NULL); INSERT INTO scores VALUES ('Ada', 7), ('Lin', 4); SELECT name, points FROM scores WHERE points >= 5 ORDER BY name;Check the output
One row: Ada | 7Change the filter threshold to 8 and predict no result rows.
- SQLite: count records in each group
GROUP BY collects rows sharing a key. COUNT(*) counts all rows in each group. ORDER BY makes the displayed order explicit.
SQLite 3; run each complete example in a fresh in-memory database.
CREATE TABLE practice (subject TEXT NOT NULL); INSERT INTO practice VALUES ('SQL'), ('Web'), ('SQL'); SELECT subject, COUNT(*) AS lessons FROM practice GROUP BY subject ORDER BY subject;Check the output
SQL | 2 Web | 1Add another Web row and predict both counts as 2.
- SQLite: count rows versus known values
COUNT(*) counts rows, while COUNT(score) counts only non-NULL score values. A missing score differs from zero; zero is counted as a known value.
SQLite 3; run each complete example in a fresh in-memory database.
CREATE TABLE scores (score INTEGER); INSERT INTO scores VALUES (0), (7), (NULL); SELECT COUNT(*) AS rows_total, COUNT(score) AS known_scores FROM scores;Check the output
rows_total | known_scores 3 | 2Replace NULL with 0 and predict known_scores = 3.
Open the SQLite learning path on DevStudyHub · Play coding games on this blog
No comments:
Post a Comment
Please do not enter any spam link in the comment box.