Quizz

The full text of this chapter, to read or revisit. To practise, do the exercises.

β–Ά Do the exercises

Exercise 1 β€” Making a table

Which statement creates a table?

Options
β€’ CREATE TABLE gyms (gym_id INTEGER PRIMARY KEY, name TEXT);
β€’ INSERT TABLE gyms (gym_id INTEGER PRIMARY KEY, name TEXT);
β€’ NEW TABLE gyms (gym_id INTEGER PRIMARY KEY, name TEXT);
β€’ ADD TABLE gyms (gym_id INTEGER, name TEXT);

Hint : CREATE is the verb of DDL.

Exercise 2 β€” Two keywords

Complete the script so that it creates a gyms table with an automatic id and shows the inserted gym.

Hint : Create the table; the automatic id is the primary key.

Exercise 3 β€” A column for names

Which type suits a name column?

Options
β€’ TEXT
β€’ INTEGER
β€’ REAL
β€’ NAME

Hint : Anything written is text.

Exercise 4 β€” 6.75

Which type stores a weight like 6.75?

Options
β€’ REAL
β€’ INTEGER
β€’ TEXT
β€’ DECIMAL(2)

Hint : Decimals need REAL.

Exercise 5 β€” Skipping the id

gym_id is an INTEGER PRIMARY KEY. You insert a row without giving gym_id. What happens?

Options
β€’ The database assigns the next number itself
β€’ An error: a primary key is required
β€’ gym_id is NULL
β€’ gym_id is 0

Hint : That is the whole point of an automatic id.

Exercise 6 β€” Keyword to purpose

Match each keyword with its purpose.

Items to match
β€’ NOT NULL
β€’ DEFAULT
β€’ CHECK
β€’ REFERENCES
Definitions
β€’ Forbids missing values
β€’ Supplies a value when none is given
β€’ Validates a condition on the value
β€’ Links the column to another table's key

Exercise 7 β€” Required

name is TEXT NOT NULL. What does INSERT INTO gyms (city) VALUES ('Azure City'); produce?

Options
β€’ An error: NOT NULL constraint failed: gyms.name
β€’ A row with an empty name
β€’ A row with name = 'Azure City'
β€’ A row with name = NULL

Hint : No value for a required column.

Exercise 8 β€” Two rules

Complete the statement so that recruit names cannot repeat and levels must be positive.

Hint : No repeats, then a condition in parentheses.

Exercise 9 β€” What REFERENCES means

What does trainer_id INTEGER REFERENCES trainers(trainer_id) express?

Options
β€’ Each trainer_id must match an existing trainers.trainer_id (once foreign key checks are on)
β€’ The column copies the trainers table
β€’ The column is the primary key of trainers
β€’ The column must be unique

Hint : A foreign key points to a parent row.

Exercise 10 β€” Switched off

In SQLite, when are foreign keys actually enforced?

Options
β€’ After PRAGMA foreign_keys = ON; by default they are not checked
β€’ Always, as soon as REFERENCES is written
β€’ Never: SQLite ignores REFERENCES
β€’ Only inside CREATE TABLE AS SELECT

Hint : The declaration is stored; the check must be switched on.

Exercise 11 β€” An email column

Complete the script so that it adds an email column to trainers and shows Alex's (still empty) email.

Hint : The verb that adds something, then the kind of thing you add.

Exercise 12 β€” Rename what?

Which statement renames the hometown column of trainers to city?

Options
β€’ ALTER TABLE trainers RENAME COLUMN hometown TO city;
β€’ ALTER TABLE trainers RENAME TO city;
β€’ UPDATE trainers SET hometown = 'city';
β€’ ALTER COLUMN trainers.hometown TO city;

Hint : RENAME COLUMN for a column, RENAME TO for the table.

Exercise 13 β€” DELETE or DROP

You want the gyms table to disappear completely. Which statement?

Options
β€’ DROP TABLE gyms;
β€’ DELETE FROM gyms;
β€’ DELETE TABLE gyms;
β€’ ALTER TABLE gyms DROP;

Hint : DELETE empties, DROP removes.

Exercise 14 β€” Match the refusal

Match each attempt with the error it triggers (foreign key checks are on).

Items to match
β€’ Inserting a second 'Tide Gym' in a UNIQUE name column
β€’ Inserting a gym without a name (NOT NULL)
β€’ Inserting a level of 150 with CHECK (level BETWEEN 1 AND 100)
β€’ Inserting a badge for trainer 99, who does not exist
Definitions
β€’ UNIQUE constraint failed: gyms.name
β€’ NOT NULL constraint failed: gyms.name
β€’ CHECK constraint failed: level BETWEEN 1 AND 100
β€’ FOREIGN KEY constraint failed

Exercise 15 β€” Snapshot of the strong

Complete the script so that it creates strong_codemon from the CodΓ©mons of level 12 or more, then shows it.

Hint : CREATE TABLE … AS SELECT; 12 or more includes 12.

Exercise 16 β€” Default badges

badges INTEGER NOT NULL DEFAULT 0. You insert a trainer without giving badges. What is stored?

Options
β€’ 0
β€’ NULL
β€’ An error: badges is NOT NULL
β€’ The badges of the previous row

Hint : DEFAULT steps in before NOT NULL complains.

Exercise 17 β€” AUTOINCREMENT

What does AUTOINCREMENT change for an INTEGER PRIMARY KEY?

Options
β€’ An id is never reused, even after the row that had it is deleted
β€’ Ids start at 0 instead of 1
β€’ Ids are generated at random
β€’ Nothing: ids were already automatic

Hint : Automatic in both cases; reuse is the difference.

Exercise 18 β€” Gone for good

Complete the script so that it removes the gyms table without failing if it does not exist, then lists the remaining tables.

Hint : The verb that removes a table, then the words that skip the error when it is missing.

Exercise 19 β€” Catalog

What does SELECT name FROM sqlite_master WHERE type = 'table'; return?

Options
β€’ The names of the tables in the database
β€’ The names of the columns of every table
β€’ The rows of the table named master
β€’ An error: sqlite_master is not a table

Hint : The database keeps its own list of tables.

Exercise 20 β€” Statement to effect

Match each statement with its effect.

Items to match
β€’ CREATE TABLE
β€’ ALTER TABLE
β€’ DROP TABLE
β€’ INSERT INTO
Definitions
β€’ Defines a new table
β€’ Changes the structure of an existing table
β€’ Removes a table and its rows
β€’ Adds rows to a table

Exercise 21 β€” Who first?

badges.trainer_id references trainers. In which order must the tables be created and filled?

Options
β€’ trainers first, then badges: a parent must exist before its children
β€’ badges first, then trainers
β€’ The order does not matter
β€’ Both in the same CREATE TABLE statement

Hint : The referenced row has to be there.

Exercise 22 β€” Levels between 1 and 100

Complete the statement so that level only accepts values from 1 to 100.

Hint : A condition constraint, and BETWEEN wants AND.

Exercise 23 β€” Cannot be dropped

Which column can ALTER TABLE codemon DROP COLUMN … not remove?

Options
β€’ codemon_id, the PRIMARY KEY
β€’ capture_date, because it contains a NULL
β€’ type, because it is NOT NULL
β€’ level, because it is an INTEGER

Hint : The key that identifies rows cannot go.

Exercise 24 β€” Default for the old rows

ALTER TABLE trainers ADD COLUMN level INTEGER NOT NULL DEFAULT 1; β€” what do the existing trainers get in level?

Options
β€’ 1
β€’ NULL
β€’ 0
β€’ An error: existing rows cannot get a NOT NULL column

Hint : The DEFAULT fills the column for the rows already there.

Exercise 25 β€” Rows and structure

Which statement keeps the gyms table but removes all its rows?

Options
β€’ DELETE FROM gyms;
β€’ DROP TABLE gyms;
β€’ ALTER TABLE gyms DROP ROWS;
β€’ TRUNCATE gyms;

Hint : DELETE works on rows; SQLite has no TRUNCATE.

Back to the course