Database (SQLite)

Big idea

• In most databases the column owns the type and rejects bad values.

• SQLite is different: a declared type is a preference, not a rule. A column declared INTEGER will happily hold the word hello.

• If you don't know this, SQLite can give you a total that is quietly wrong.

The five storage classes

Every value is stored as exactly one of these:

Five. That's the whole list. No boolean, date, money, or fixed-length string type.

Missing types and what to do instead

Boolean

No TRUE/FALSE type. Store 0 = false, 1 = true as INTEGER. Newer SQLite accepts the words TRUE/FALSE in SQL and turns them into 1/0.

Date and time

No date type. Store as TEXT like '2026-09-23 14:30:00' (or as a number). The text form sorts correctly with a plain ORDER BY because the biggest unit comes first. This gets its own topic later.

Money

No money type. REAL is a trap: floating point can't hold 0.10 exactly. Store whole cents as INTEGER (1050, not 10.50) and divide when displaying.

Type affinity (the surprising part)

The declared column type is the column's affinity: what it would like to hold. SQLite tries to convert values to it but doesn't enforce it.

CREATE TABLE student (
    id    INTEGER,
    name  TEXT,
    mark  REAL
);

INSERT INTO student VALUES (1, 'Amina', 72.5);
INSERT INTO student VALUES ('two', 'Bello', 'absent');

Both inserts succeed, with no error or warning. 'two' sits in an INTEGER column and 'absent' in a REAL one.

Why this bites: the wrong total

The PDF's example:

CREATE TABLE sale (item TEXT, amount INTEGER);

INSERT INTO sale VALUES ('bread', '120');
INSERT INTO sale VALUES ('milk',  '85');
INSERT INTO sale VALUES ('rice',  ' 200');   -- note the leading space
INSERT INTO sale VALUES ('sugar', '64');

SELECT SUM(amount) FROM sale;

PDF's claim: the leading space stops ' 200' converting, so it's stored as TEXT. SUM treats text as 0, giving 269 instead of 469. No error and no warning, just a believable wrong total.

Finding what's really there: typeof()

The declared type doesn't tell you, so ask each value:

SELECT item, amount, typeof(amount) FROM sale;

bread   120     integer
milk    85      integer
rice     200    text        <-- there it is (per the PDF)
sugar   64      integer

typeof() is THE tool for this topic. When a number won't add up, a comparison returns nothing, or a sort looks strange, check the real types first.

Making the type a rule again

To reject bad values instead of converting them, add a constraint (covered in its own topic later):

CREATE TABLE sale (
    item    TEXT    NOT NULL,
    amount  INTEGER NOT NULL CHECK (typeof(amount) = 'integer')
);

Bad rows are now rejected at insert time: "you find out in September instead of December."

The one exception: INTEGER PRIMARY KEY

CREATE TABLE student (
    id    INTEGER PRIMARY KEY,
    name  TEXT
);

A column declared exactly INTEGER PRIMARY KEY:

• becomes an alias for SQLite's internal row id (rowid),

• must hold an integer, so this is the one place the type is enforced.

INT PRIMARY KEY does not get this behaviour. It's an ordinary column, because the exception requires the exact word INTEGER.

Quick review

1. Name the five storage classes. NULL, INTEGER, REAL, TEXT, BLOB.

2. How do you store a boolean? INTEGER 0/1.

3. A date? TEXT like '2026-09-23 14:30:00' (sorts correctly).

4. Money? Whole cents as INTEGER, because REAL can't hold 0.10 exactly.

5. Does INSERT ... ('two', ...) into an INTEGER column fail? No. It's stored as TEXT.

6. Affinity of VARCHAR(50)? TEXT. BOOLEAN? NUMERIC. DATE? NUMERIC. FLOAT? REAL. BIGINT? INTEGER. No type at all? BLOB. STRING? NUMERIC.

7. '42' into an INTEGER column? Stored as integer 42. 42 into a TEXT column? Stored as text '42'.

8. What does BLOB affinity convert? Nothing.

9. Function to see a value's real type? typeof().

10. How do you force a column to reject non-integers? CHECK (typeof(col) = 'integer').

11. INTEGER PRIMARY KEY vs INT PRIMARY KEY? Only the exact word INTEGER makes it a rowid alias with an enforced type.

Quiz logistics

Handwritten (blank or dark blue pen, one side of white paper). Scan with Google Drive, name it T01.pdf, and upload it to hand_in.

Big idea

• A SQLite database is one file. Copy the file = copy the database.

• One connection can open more than one file. ATTACH adds a database to the connection; DETACH removes it.

• Result: you can query across several database files in one statement.

Databases you already have

Every connection starts with two:

main

The database you opened. SELECT * FROM student really means SELECT * FROM main.student.

temp

Scratch database for this connection only, thrown away on disconnect. CREATE TEMP TABLE puts tables here.

Key point: the database.table prefix is the whole mechanism. ATTACH just adds more names you can put before the dot.

Attaching

ATTACH DATABASE 'archive.db' AS archive;

'archive.db'

The filename, in quotes. Relative to where you started sqlite3, or an absolute path. If the file doesn't exist, SQLite creates it empty without complaining.

AS archive

The alias used in queries. Doesn't have to match the filename.

Then:

SELECT * FROM archive.sale;
SELECT * FROM main.sale;

.databases

Why use it (three uses)

1. Moving rows between files. Use an ordinary INSERT ... SELECT, with no export, CSV or script needed:

   ATTACH DATABASE 'last_year.db' AS old;
   INSERT INTO main.sale
   SELECT * FROM old.sale
   WHERE date >= '2025-01-01';

2. Comparing two versions of the same data:

   ATTACH DATABASE 'backup.db' AS backup;
   SELECT id FROM main.student
   EXCEPT
   SELECT id FROM backup.student;   -- students added since the backup

   Swap the two sides to find the students who were deleted.

3. Splitting a database that has grown. Archive old rows to a second file, delete them from the first, and attach the archive when someone needs the history.

Rules and limits

Detaching

DETACH DATABASE archive;

• No quotes. You name the alias, not the file.

Detaching does NOT: delete the file, empty it, or undo anything written to it.

Detaching DOES: free the alias for reuse, release the file lock, and make archive.anything an error again.

Closing the connection detaches everything automatically. DETACH matters when you need the name or file released while you keep working.

Quick review

1. What does SELECT * FROM student actually read? main.student

2. What happens if you ATTACH a misspelled filename? A new empty DB is created; there is no error until a query fails with "no such table".

3. How do you see what's attached? The .databases shell command.

4. Quotes in ATTACH vs DETACH? ATTACH quotes the filename; DETACH takes the unquoted alias.

5. Default attach limit? 10.

6. Can you ATTACH inside BEGIN ... COMMIT? No.

7. Which two names are reserved? main and temp.

8. Does DETACH delete data? No. It only drops the connection to the file.

Quiz logistics

Handwritten (blank or dark blue pen, one side of white paper). Scan with Google Drive, name it T02.pdf, and upload it to hand_in.

Big idea

• DROP TABLE removes a table completely: definition, columns, every row. The space is marked free.

• No undo and no confirmation. Inside a transaction you can still ROLLBACK; outside one, only a backup file can save you.

• The topic is mainly about what else goes with it, and DROP vs DELETE (emptying vs removing).

The statement

DROP TABLE student;

Error: no such table: student

DROP TABLE IF EXISTS student;

What else disappears (memorise this)

Why views survive: a view is a stored query, not stored data, so there's nothing to clean up. SQLite leaves a query that can no longer run.

PRAGMA foreign_keys = ON;

DROP vs DELETE (the key distinction)

"Clear the table" almost always means DELETE, not DROP. Dropping and recreating only works if you have the exact CREATE TABLE, and any index, trigger or constraint you forget to recreate is silently missing.

Quick review

1. Can you undo DROP TABLE? Only with ROLLBACK inside a transaction; otherwise you need a backup.

2. What does IF EXISTS change? A missing table becomes a no-op instead of an error.

3. Danger of IF EXISTS? It hides typos in the table name.

4. Indexes / triggers / views after a drop? Indexes and triggers are dropped; views are NOT dropped and break.

5. Why aren't views dropped? A view is a stored query, not stored data.

6. Default foreign key enforcement in SQLite? OFF, and it's per connection.

7. Clear all rows but keep the table? DELETE FROM student;

8. Does SQLite have TRUNCATE? No. DELETE without WHERE is optimized instead.

Quiz logistics

Handwritten (blank or dark blue pen, one side of white paper). Scan with Google Drive, name it T03.pdf, and upload it to hand_in.

Big idea

An operator combines values into a new one: +, =, AND.

The two rules that bite (both fail silently with believable answers):

1. Integer ÷ integer throws away the remainder.

2. Comparing anything to NULL is never true, not even NULL = NULL.

Arithmetic

SELECT 7 / 2;        -- 3     both integers
SELECT 7.0 / 2;      -- 3.5   one is real
SELECT 7 / 2.0;      -- 3.5   either side works
SELECT 7 * 1.0 / 2;  -- 3.5   the usual trick on columns

SELECT total * 1.0 / count AS average FROM summary;

Comparison

NULL: the rule that catches everyone

NULL means unknown. It does not mean zero, empty, or blank.

SELECT 5 = NULL;       -- NULL, not 0
SELECT NULL = NULL;    -- NULL, not 1
SELECT 5 + NULL;       -- NULL
SELECT 'abc' || NULL;  -- NULL

Is 5 equal to an unknown number? We don't know, so the answer is NULL.

WHERE keeps only rows where the condition is true. NULL isn't true, so comparing with NULL keeps nothing:

SELECT * FROM student WHERE phone = NULL;   -- always returns nothing
SELECT * FROM student WHERE phone IS NULL;  -- correct

SELECT * FROM student WHERE mark != 50;

SELECT * FROM student WHERE mark != 50 OR mark IS NULL;

Logical operators

Precedence: AND binds tighter than OR (like * before +).

WHERE course = 'CSCI 3610' AND mark > 70 OR mark > 90

means (3610 students over 70) OR (anyone at all over 90), which is probably not what was intended.

Joining text

|| concatenates. There is no + for strings in SQLite.

SELECT first_name || ' ' || last_name AS full_name FROM student;

It follows the NULL rule: if last_name is NULL, the whole full_name is NULL. The fix is COALESCE (see Expressions).

Quick review

1. SELECT 7 / 2;? 3 (integer division).

2. Two ways to get 3.5? 7.0 / 2 or 7 * 1.0 / 2.

3. SELECT 5 / 0;? NULL, not an error.

4. Does BETWEEN 1 AND 10 include 1 and 10? Yes, it's inclusive.

5. NULL = NULL? NULL.

6. Correct way to find missing phones? WHERE phone IS NULL.

7. Why does mark != 50 miss rows? NULL marks give unknown, not true.

8. Which binds tighter, AND or OR? AND.

9. String concatenation operator? ||.

10. 'abc' || NULL? NULL.

Quiz logistics

Handwritten (blank or dark blue pen, one side of white paper). Scan with Google Drive, name it T04.pdf, and upload it to hand_in.

Big idea

• An expression is anything that produces a value: 42, price * 1.2, UPPER(name), or a whole SELECT in brackets.

• Operators (topic 4) join expressions together. This topic covers what they join, plus the two decision/type tools: CASE and CAST.

Where an expression can appear

Anywhere a value is expected, an expression will do.

SELECT   price * 1.2           -- in the column list
FROM     product
WHERE    price * 1.2 > 100     -- in the filter
ORDER BY price * 1.2 DESC;     -- in the sort

Literals

CASE: making a decision in a query

CASE is SQL's if. It's the most useful thing in this topic.

SELECT name,
       mark,
       CASE
           WHEN mark >= 70 THEN 'Distinction'
           WHEN mark >= 50 THEN 'Pass'
           ELSE                 'Fail'
       END AS grade
FROM   student;

CASE
    WHEN mark >= 50 THEN 'Pass'          -- 85 matches HERE
    WHEN mark >= 70 THEN 'Distinction'   -- never reached
    ELSE 'Fail'
END

CAST: changing a type on purpose

SQLite converts types on its own (topic 1). CAST asks for a conversion explicitly.

SELECT CAST('42'  AS INTEGER);   -- 42
SELECT CAST(9     AS REAL);      -- 9.0
SELECT CAST(3.7   AS INTEGER);   -- 3   truncated, not rounded
SELECT CAST('abc' AS INTEGER);   -- 0   no error

Everyday use is forcing real division (from topic 4):

SELECT CAST(total AS REAL) / count FROM summary;

Handling NULL inside an expression

COALESCE fixes the concatenation trap:

SELECT first_name || ' ' || COALESCE(last_name, '') AS full_name
FROM student;

NULLIF prevents division by zero (the 0 becomes NULL, so the result is NULL):

SELECT total / NULLIF(count, 0) FROM summary;

A subquery is an expression too

A bracketed SELECT that returns one value can go anywhere a value can:

SELECT name, mark,
       mark - (SELECT AVG(mark) FROM student) AS above_average
FROM   student;

Subqueries get their own topic later.

Quick review

1. What is an expression? Anything that produces a value.

2. Where can expressions go? Anywhere a value is expected: SELECT list, WHERE, ORDER BY, and more.

3. How do you write it's as a literal? 'it''s'.

4. 'name' vs "name"? Text value vs column name.

5. CASE with no ELSE and no match? NULL.

6. Which WHEN wins? The first true one.

7. CAST(3.7 AS INTEGER)? 3 (truncated).

8. CAST('abc' AS INTEGER)? 0.

9. COALESCE(NULL, NULL, 'x', 'y')? 'x'.

10. NULLIF(5, 5)? NULLIF(5, 3)? NULL; 5.

11. Safe division when count may be 0? total / NULLIF(count, 0).

Quiz logistics

Handwritten (blank or dark blue pen, one side of white paper). Scan with Google Drive, name it T05.pdf, and upload it to hand_in.

Big idea

• WHERE decides which rows you get. The rest of the SELECT decides what you see about them.

• SQLite takes each row in turn, tests the condition against that one row, and keeps the row only if the answer is true.

Row by row, on its own

SELECT name, mark FROM student WHERE mark > 50;

For each row SQLite asks is this row's mark greater than 50? and keeps or drops it. The rows know nothing about each other.

That's why this fails:

SELECT name FROM student WHERE mark > AVG(mark);   -- error

While one row is being tested, there is no average. The average is a fact about the whole table, and a single row can't see the whole table. Use a subquery (later topic):

SELECT name FROM student WHERE mark > (SELECT AVG(mark) FROM student);

True, false, and the third answer

A condition has three possible results, and only true keeps the row:

This is the NULL problem from Operators, showing up where it matters:

SELECT COUNT(*) FROM student WHERE mark > 50;    -- 22
SELECT COUNT(*) FROM student WHERE mark <= 50;   -- 12
-- 22 + 12 = 34, but the table has 40 rows. Six marks are NULL.

What you can put in it

Everything from Operators and Expressions:

WHERE mark > 50
WHERE mark BETWEEN 50 AND 70              -- inclusive at both ends
WHERE course IN ('CSCI 3610', 'CSCI 2020')
WHERE mark IS NULL
WHERE mark IS NOT NULL
WHERE name LIKE 'A%'                      -- topic 8
WHERE mark * 1.1 > 60                     -- an expression
WHERE CASE WHEN retake THEN mark + 5 ELSE mark END > 50

WHERE takes an expression, not just a column. Anything that produces a value can be tested.

SELECT mark * 1.1 AS adjusted FROM student WHERE adjusted > 60;

SELECT mark * 1.1 AS adjusted FROM student WHERE mark * 1.1 > 60;

NOT, and where it goes

WHERE NOT (mark > 50)
WHERE mark NOT BETWEEN 50 AND 70
WHERE course NOT IN ('CSCI 3610')
WHERE name NOT LIKE 'A%'

NOT does not rescue NULL rows. NOT (NULL) is still NULL, so the row is still dropped. If you want the unknowns, ask for them:

WHERE NOT (mark > 50) OR mark IS NULL

Two habits worth forming

1. Count before you filter. Run SELECT COUNT(*) FROM student; first. Then if a filter returns 34 of 40, you notice the missing six.

2. Write the condition in words first. "Students in 3610 who scored over 70, and anyone at all who scored over 90" tells you where the brackets go. The SQL is only as right as the sentence.

Quick review

1. How does WHERE evaluate a condition? One row at a time, on its own.

2. Why does WHERE mark > AVG(mark) fail? A single row can't see the whole table's average. Use a subquery.

3. The three results of a condition? True (kept), false, NULL (both dropped).

4. Why might > 50 and <= 50 not add up to the row count? NULL marks are dropped by both.

5. Can WHERE test an expression? Yes, anything that produces a value.

6. Can WHERE use a SELECT alias? Not in standard SQL; it runs before the column list. (SQLite allows it, but don't rely on it.)

7. Does NOT bring back NULL rows? No. Add OR col IS NULL.

8. Two habits? Count before filtering; write it in words first.

Quiz logistics

Handwritten (blank or dark blue pen, one side of white paper). Scan with Google Drive, name it T06.pdf, and upload it to hand_in.

Big idea

One condition is easy. Faults start when you join two, and they're not syntax errors: everything here runs fine and returns the wrong rows.

Three causes of almost all of them:

1. AND binds tighter than OR.

2. NOT doesn't distribute the way English does.

3. A range needs two conditions joined by AND, not one.

Precedence: the rule and the habit

AND binds tighter than OR, like * before +.

WHERE course = 'CSCI 3610' AND mark > 70 OR mark > 90

is read by SQLite as

WHERE (course = 'CSCI 3610' AND mark > 70) OR (mark > 90)

3610 students over 70, or anybody at all over 90. A physics student with 95 is in the result.

If you meant 3610 students over 70 or over 90:

WHERE course = 'CSCI 3610' AND (mark > 70 OR mark > 90)

The range mistake

The most common wrong WHERE in student SQL:

WHERE 50 < mark < 70        -- runs, and is nonsense

No error. SQLite works left to right: 50 < mark gives 0 or 1, then compares that to 70. 0 < 70 and 1 < 70 are both true, so every row passes, including marks of 5 and 200.

Correct forms:

WHERE mark > 50 AND mark < 70
WHERE mark BETWEEN 51 AND 69     -- BETWEEN is inclusive, so bounds shift

BETWEEN is not a drop-in replacement for two strict comparisons. (And 51 AND 69 only matches the strict version for whole-number marks: a mark of 50.5 passes the first and fails the second.)

One column, two values

Reads perfectly in English, always wrong:

WHERE course = 'CSCI 3610' AND course = 'CSCI 2020'

"Students in 3610 and 2020." Always returns nothing. One row has one course, and one value can't equal two different strings.

WHERE course = 'CSCI 3610' OR course = 'CSCI 2020'
WHERE course IN ('CSCI 3610', 'CSCI 2020')     -- same, tidier

AND is right when the conditions are about different columns: in 3610 and scoring over 70.

NOT, and what it does to a pair

NOT flips true/false and leaves NULL as NULL.

Negating a compound condition flips the operator as well as the comparisons. This is De Morgan's law:

The AND becomes an OR. "Not (in 3610 and over 70)" = "not in 3610, or not over 70". That catches more than people expect: a 3610 student who scored 60 satisfies it.

WHERE NOT (course = 'CSCI 3610' AND mark > 70)
WHERE course != 'CSCI 3610' OR mark <= 70

NULL through a compound condition

AND can be decided by a false; OR can be decided by a true, without knowing the other side.

And from WHERE: a row whose condition is NULL is dropped, so compound conditions on NULL columns quietly lose rows.

WHERE mark > 50 AND course = 'CSCI 3610'

A 3610 student with no recorded mark isn't returned, and nothing says so.

Write it in words first

Every fault here is a translation error. Translate deliberately:

Say the sentence, decide its shape, then write it.

Quick review

1. Which binds tighter, AND or OR? AND.

2. How does SQLite read a AND b OR c? (a AND b) OR c.

3. What does WHERE 50 < mark < 70 return? Every row.

4. Two correct ways to write that range? mark > 50 AND mark < 70; mark BETWEEN 51 AND 69 (whole numbers).

5. course = 'A' AND course = 'B'? Always empty. Use OR / IN.

6. NOT (a AND b) equals? NOT a OR NOT b.

7. false AND NULL? false. true OR NULL? true.

8. true AND NULL? false OR NULL? Both NULL.

9. Habit that prevents all of these? Write it in words first, and bracket mixed AND/OR.

Quiz logistics

Handwritten (blank or dark blue pen, one side of white paper). Scan with Google Drive, name it T07.pdf, and upload it to hand_in.

Big idea

• = matches a whole value exactly. LIKE and GLOB match a pattern: starts with A, ends in .pdf, contains kano.

• Same job, different wildcards and different rules about capital letters. Choosing between them is most of the skill.

LIKE

WHERE name LIKE 'A%'       -- begins with A
WHERE name LIKE '%a'       -- ends with a
WHERE name LIKE '%kano%'   -- contains kano anywhere
WHERE name LIKE 'A%a'      -- begins with A and ends with a
WHERE code LIKE '___'      -- exactly three characters

LIKE is case-insensitive for ASCII letters. 'a%' and 'A%' return the same rows.

GLOB

Command-line wildcards, and case-sensitive, always.

WHERE name GLOB 'A*'                 -- capital A only
WHERE file GLOB '*.pdf'              -- ends in .pdf, lower case only
WHERE code GLOB '[A-Z][A-Z][0-9]'    -- two capitals then a digit

Character classes are the reason to reach for GLOB. LIKE can't say any one of these characters.

Choosing between them

Matching a literal % or _

Use ESCAPE to say this one isn't a wildcard:

WHERE note LIKE '%50\%%' ESCAPE '\'

Read in pieces: % any run · 50 · \% a real percent sign · % any run. ESCAPE '\' makes the backslash mean next character is ordinary. Any character can be the escape; backslash is conventional.

GLOB has no ESCAPE. Put the character in a class instead: '*[*]*' matches anything containing a literal asterisk.

Why a leading wildcard is slow

WHERE name LIKE 'A%'     -- can use an index on name
WHERE name LIKE '%a'     -- cannot; every row must be examined

An index is sorted, so starts with A is a range it can jump to. Ends with a isn't a range, so it's a full scan.

Invisible on 40 rows; on 4 million it's instant vs. a minute. Avoid a leading wildcard when you can, and know you've chosen a full scan when you can't. (Indexes get their own topic later.)

Quick review

1. LIKE wildcards? % any run (incl. none), _ exactly one.

2. GLOB wildcards? *, ?, [...], [^...].

3. Is LIKE case-sensitive? No, for ASCII letters. Accented letters are.

4. Is GLOB case-sensitive? Yes, always.

5. Does '_mina' match mina? No, _ needs exactly one character.

6. What can GLOB do that LIKE can't? Character sets/ranges.

7. How do you match a literal % with LIKE? ESCAPE, e.g. LIKE '%50\%%' ESCAPE '\'.

8. Literal * with GLOB? Put it in a class: '*[*]*'.

9. Why is LIKE '%a' slow? No index range to jump to: full scan.

10. Which one for searching typed names? LIKE. For codes? GLOB.

Quiz logistics

Handwritten (blank or dark blue pen, one side of white paper). Scan with Google Drive, name it T08.pdf, and upload it to hand_in.

Big idea

• There is no natural order. Not insertion order, not primary-key order, not the order you saw last time. Want an order? Ask for one.

• ORDER BY sorts. LIMIT takes the first few.

• LIMIT without ORDER BY is meaningless. That's the fault in this topic.

ORDER BY

SELECT name, mark FROM student ORDER BY mark;               -- ascending (default)
SELECT name, mark FROM student ORDER BY mark DESC;          -- descending
SELECT name, mark FROM student ORDER BY course, mark DESC;

The third sorts by course, then within each course by mark descending. Read multi-column sorts as group by the first, then arrange inside it.

SELECT name FROM student ORDER BY mark NULLS LAST;

LIMIT

SELECT name, mark FROM student ORDER BY mark DESC LIMIT 5;            -- top five
SELECT name, mark FROM student ORDER BY mark DESC LIMIT 5 OFFSET 10;  -- 11th to 15th

OFFSET skips rows before taking them. That's paging: page 3 at ten per page is LIMIT 10 OFFSET 20. (Offset = (page − 1) × page size.)

Every LIMIT needs an ORDER BY. If you can't say what order you want, you don't yet know what question you're asking.

ORDER BY mark DESC, name ASC LIMIT 3

Quick review

1. Default row order with no ORDER BY? None. It's undefined.

2. Default direction of ORDER BY? ASC.

3. ORDER BY a, b DESC: what's descending? Only b.

4. Can ORDER BY use an alias? Yes; it runs after the column list.

5. Where does SQLite put NULL ascending? First. Descending? Last.

6. Why does '10' sort before '9'? Text compares character by character. Check typeof().

7. Rows 11–15? LIMIT 5 OFFSET 10.

8. Page 3 at 10 per page? LIMIT 10 OFFSET 20.

9. What's wrong with LIMIT alone? Arbitrary rows; always add ORDER BY.

10. How do you make ties deterministic? Add a tiebreaker column.

Quiz logistics

Handwritten (blank or dark blue pen, one side of white paper). Scan with Google Drive, name it T09.pdf, and upload it to hand_in.

Big idea

A function takes values and returns one. There are two kinds, and telling them apart is the whole topic. The difference explains GROUP BY (topic 11).

Scalar functions

Work row by row, like any expression, and go anywhere an expression can.

SELECT SUBSTR('Kano', 1, 2);   -- 'Ka'  (starts at 1)
SELECT ROUND(3.75, 1);         -- 3.8
SELECT CAST(3.75 AS INTEGER);  -- 3     (truncates)

Aggregate functions

SELECT AVG(mark)                 FROM student;   -- 64.2  (sum / 34)
SELECT SUM(mark) * 1.0 / COUNT(*) FROM student;   -- 54.6  (sum / 40)

SELECT name, AVG(mark) FROM student;   -- one row, and name is arbitrary

Quick review

1. Scalar vs aggregate? One row in/one out vs many rows in/one out.

2. SUBSTR('Kano', 1, 2)? 'Ka'. Counting starts at 1.

3. ROUND(3.75, 1) vs CAST(3.75 AS INTEGER)? 3.8 vs 3.

4. Are UPPER/LOWER Unicode-aware? No, ASCII only.

5. COUNT(*) vs COUNT(mark)? All rows vs non-NULL marks.

6. Do SUM and AVG count NULLs? No, they ignore them.

7. Average treating missing marks as 0? SUM(mark) * 1.0 / COUNT(*).

8. What does SELECT name, AVG(mark) FROM student give in SQLite? One row with an arbitrary name (most DBs error).

9. Which function fixes leading spaces? TRIM.

Quiz logistics

Handwritten (blank or dark blue pen, one side of white paper). Scan with Google Drive, name it T10.pdf, and upload it to hand_in.