Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
βΆ Do the exercisesExercise 1 β Making a table
Which statement creates a table?
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?
TEXTINTEGERREALNAMEHint : Anything written is text.
Exercise 4 β 6.75
Which type stores a weight like 6.75?
REALINTEGERTEXTDECIMAL(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?
Hint : That is the whole point of an automatic id.
Exercise 6 β Keyword to purpose
Match each keyword with its purpose.
NOT NULLDEFAULTCHECKREFERENCESExercise 7 β Required
name is TEXT NOT NULL. What does INSERT INTO gyms (city) VALUES ('Azure City'); produce?
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?
Hint : A foreign key points to a parent row.
Exercise 10 β Switched off
In SQLite, when are foreign keys actually enforced?
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?
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?
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).
UNIQUE constraint failed: gyms.nameNOT NULL constraint failed: gyms.nameCHECK constraint failed: level BETWEEN 1 AND 100FOREIGN KEY constraint failedExercise 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?
0NULLHint : DEFAULT steps in before NOT NULL complains.
Exercise 17 β AUTOINCREMENT
What does AUTOINCREMENT change for an INTEGER PRIMARY KEY?
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?
Hint : The database keeps its own list of tables.
Exercise 20 β Statement to effect
Match each statement with its effect.
CREATE TABLEALTER TABLEDROP TABLEINSERT INTOExercise 21 β Who first?
badges.trainer_id references trainers. In which order must the tables be created and filled?
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?
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?
1NULL0Hint : 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?
DELETE FROM gyms;DROP TABLE gyms;ALTER TABLE gyms DROP ROWS;TRUNCATE gyms;Hint : DELETE works on rows; SQLite has no TRUNCATE.