Public · freeGlobal preparation

SQLite documentation — SQL Language Expressions, SELECT and Aggregate Functions

Official SQLite reference for ordinary NULL comparisons, outer-join filters, DISTINCT, grouping and aggregate denominators.

Global generic documentation; no Hong Kong specific fact asserted.

What you can learn and practise

Each summary applies to the named source unit. Practice and feedback listed as confirmed are provided at source.

SQLite NULL comparisons and qualified three-valued logic

Analytics & data · L1 · en

Suitable for
Readers preparing to explain tabular-data behaviour who meet the stated prerequisites.
Learning goals
  • State, for a given SQLite predicate, whether a row with a NULL operand is returned, and say why.
  • Rewrite a filter that uses '= NULL' into one that behaves as intended, using IS NULL or IS.
  • Predict whether a NOT IN test against a subquery containing NULLs returns any rows, then confirm it by running the query.
Knowledge prerequisites
You must know what a NULL represents (missing/unknown, not zero and not empty string) before the rules mean anything.; You must know that a WHERE clause keeps only rows where the predicate is true — the exclusion rule is the mechanism behind every trap here.
Pathline suggested preparation
For optional local checks, have a SQLite engine available and know how to run one statement; use SELECT sqlite_version() to identify the installed version.
Duration
30 minutes (Pathline estimate)
Source practice

Source exercises: Not applicable

Source feedback: Not applicable

Executable practice: Not applicable

Source answers
Not applicable

This status applies only to this unit; check its limitations for task coverage. Feedback is listed separately.

Access for this unit
Public and free. The reviewed source page was readable without registration. Optional local software setup is separate from reading access.
Environment
Platform requirements not confirmed. Read the documentation in a browser. Optional local checks require SQLite; inspect its version. Wider platform availability was not evaluated.

Next action: Open SQL Language Expressions §2 and §8. Explain how ordinary =, IS NULL and NOT IN behave when a value is NULL.

Open this source unit ↗

Pathline practice suggestions

These are suggested actions; they do not imply a source exercise, automatic feedback or completion record.

  • Pathline reading idea: explain = NULL, IS NULL, NULL AND 0, NULL OR 1 and NOT IN with a NULL-bearing set in your own words.

Limitations to check

  • These are original Pathline reading ideas. The source is reference documentation; provider exercises, answers, grading and interactive feedback are not offered by this directory.
  • Optional examples use invented data. Internal research fixtures are not public downloads or evidence of learner mastery.
  • Duration is an unmeasured researcher estimate. Learner outcomes and professional human review have not been evaluated.
  • The language pages are unversioned. The research runtime differed from the homepage release; verify your installed version before checking an example.
Learning source and review reference

Source version: Unversioned current SQLite language documentation; homepage release is not the edition of every language page. · Unit ER-20261013-01-WP06-U0001, version 2 · Review ER-20261013-02-A0-R1-FINAL-SQLITE-V2-20261014-002

SQLite outer joins: ON, WHERE and the low-value matched counterexample

Analytics & data · L2 · en

Suitable for
Readers preparing to explain tabular-data behaviour who meet the stated prerequisites.
Learning goals
  • Explain, in your own words, when SQLite adds the NULL-filled unmatched rows of an outer join.
  • Predict the row count difference between putting a right-table filter in ON versus in WHERE.
  • Rewrite an accidental inner join back into a genuine LEFT JOIN without changing the intended grain.
Knowledge prerequisites
You need to know what INNER JOIN returns before the outer-join difference is visible.; You need the three-valued-logic rule from the SQLite NULL-comparison reading unit: a NULL in the comparison makes the predicate not-true, so the row is dropped.
Pathline suggested preparation
Complete the SQLite NULL-comparison reading unit first. Have two tables with a deliberately asymmetric relationship (some left rows with no right match).
Duration
30 minutes (Pathline estimate)
Source practice

Source exercises: Not applicable

Source feedback: Not applicable

Executable practice: Not applicable

Source answers
Not applicable

This status applies only to this unit; check its limitations for task coverage. Feedback is listed separately.

Access for this unit
Public and free. The reviewed source page was readable without registration. Optional local software setup is separate from reading access.
Environment
Platform requirements not confirmed. Read the documentation in a browser. Optional local checks require SQLite; inspect its version. Wider platform availability was not evaluated.

Next action: Open SELECT §2.1 and §2.3. Compare a right-table filter in ON with the same filter in WHERE, including a left row whose matching right value fails the filter.

Open this source unit ↗

Pathline practice suggestions

These are suggested actions; they do not imply a source exercise, automatic feedback or completion record.

  • Pathline reading idea: sketch two small invented tables, including an unmatched left row and a left row with only a low-value match; explain which rows survive each filter placement.

Limitations to check

  • These are original Pathline reading ideas. The source is reference documentation; provider exercises, answers, grading and interactive feedback are not offered by this directory.
  • Optional examples use invented data. Internal research fixtures are not public downloads or evidence of learner mastery.
  • Duration is an unmeasured researcher estimate. Learner outcomes and professional human review have not been evaluated.
  • The language pages are unversioned. The research runtime differed from the homepage release; verify your installed version before checking an example.
Learning source and review reference

Source version: Unversioned current SQLite language documentation; homepage release is not the edition of every language page. · Unit ER-20261013-01-WP06-U0002, version 2 · Review ER-20261013-02-A0-R1-FINAL-SQLITE-V2-20261014-002

SQLite DISTINCT, deterministic deduplication and denominator grain

Analytics & data · L2 · en

Suitable for
Readers preparing to explain tabular-data behaviour who meet the stated prerequisites.
Learning goals
  • State whether SELECT DISTINCT treats two NULLs as the same value, and justify it from the comparison rule SQLite uses.
  • Choose between count(*) and count(column) for a stated denominator and explain what each would count.
  • Detect a fan-out by comparing a row count against a count of the distinct entity key before aggregating.
Knowledge prerequisites
Understand row and entity grain before interpreting join counts.; Know grouping and missing-value semantics before selecting a denominator.
Pathline suggested preparation
Complete the SQLite NULL-comparison reading unit. Have a table with at least one duplicated entity key and at least one NULL.
Duration
40 minutes (Pathline estimate)
Source practice

Source exercises: Not applicable

Source feedback: Not applicable

Executable practice: Not applicable

Source answers
Not applicable

This status applies only to this unit; check its limitations for task coverage. Feedback is listed separately.

Access for this unit
Public and free. The reviewed source page was readable without registration. Optional local software setup is separate from reading access.
Environment
Platform requirements not confirmed. Read the documentation in a browser. Optional local checks require SQLite; inspect its version. Wider platform availability was not evaluated.

Next action: Open SELECT §2.6, then the count entries in Built-in Aggregate Functions. State the row or entity grain before choosing a denominator.

Open this source unit ↗

Pathline practice suggestions

These are suggested actions; they do not imply a source exercise, automatic feedback or completion record.

  • Pathline reading idea: distinguish DISTINCT values from identity deduplication; choose an explicit entity key, sorting order and survivor rule for an invented example.
  • Pathline reading idea: explain the denominator difference between count(*) and count(column), including NULL values.

Limitations to check

  • These are original Pathline reading ideas. The source is reference documentation; provider exercises, answers, grading and interactive feedback are not offered by this directory.
  • Optional examples use invented data. Internal research fixtures are not public downloads or evidence of learner mastery.
  • Duration is an unmeasured researcher estimate. Learner outcomes and professional human review have not been evaluated.
  • The language pages are unversioned. The research runtime differed from the homepage release; verify your installed version before checking an example.
Learning source and review reference

Source version: Unversioned current SQLite language documentation; homepage release is not the edition of every language page. · Unit ER-20261013-01-WP06-U0003, version 2 · Review ER-20261013-02-A0-R1-FINAL-SQLITE-V2-20261014-002

Recommended next action

Read Expressions §2, SELECT §2.3/§2.6 and aggregate count/avg entries. Explain which rows a stated filter excludes and what its denominator counts.

Access and scope

The reviewed documentation pages were publicly readable without registration. Pathline provides original reading guidance; source availability may change.

What to check at source

SQLite language pages are unversioned; check the installed engine version before comparing behaviour. Reading guidance does not establish learner mastery.

Sources and review record

https://www.sqlite.org/lang_expr.html
Last body review
Last source observation
· Readable document; HTTP code not reported
Review reference
ER-20261013-02-A11-R1-SQLITE-V2-001

Discussion

No thread yet. Ask the first question and it will stay attached to this page.

Start the discussion

Sign in and confirm your university email to post. Reading is open.