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

  1. 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 | 7

    Change the filter threshold to 8 and predict no result rows.

  2. 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 | 1

    Add another Web row and predict both counts as 2.

  3. 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 | 2

    Replace NULL with 0 and predict known_scores = 3.

Open the SQLite learning path on DevStudyHub · Play coding games on this blog

logoblog

No comments:

Post a Comment

Please do not enter any spam link in the comment box.