q2.sql - - Setup. DO NOT REMOVE. .headers on .separator ...

-- Setup. DO NOT REMOVE.
.headers on
.separator ','
DROP TABLE IF EXISTS sets;
DROP TABLE IF EXISTS themes;
DROP TABLE IF EXISTS parts;
DROP VIEW IF EXISTS top_level_themes;
DROP VIEW IF EXISTS sets_years;
DROP TABLE IF EXISTS parts_fts;
-- ***** ***** ***** ***** ***** ***** ***** ***** ***** ***** --
-- (a.i) Create tables
-- [insert your SQL statement(s) BELOW this line]
CREATE TABLE sets(
set_num TEXT,
name TEXT,
year INT,
theme_id INT,
num_parts INT
);
CREATE TABLE themes(
id INT,
name TEXT,
parent_id INT
);
CREATE TABLE parts(
part_num TEXT,
name TEXT,
part_cat_id INT,
part_material_id INT
);
-- [insert your SQL statement(s) ABOVE this line]
-- [the following statement(s) are for autograding. DO NOT
REMOVE.]
.tables
.print '~~~~~'

-- ***** ***** ***** ***** ***** ***** ***** ***** ***** ***** --
-- (a.ii) Import data
-- [insert your SQLite command(s) BELOW this line]
.import data/sets.csv sets
.import data/parts.csv parts
.import data/themes.csv themes
-- [insert your SQLite command(s) ABOVE this line]
-- [the following statement(s) are for autograding. DO NOT
REMOVE.]
.headers off
SELECT count(*) FROM sqlite_master WHERE type='table' AND
name='sets';
SELECT count(*) FROM sqlite_master WHERE type='table' AND
name='parts';
SELECT count(*) FROM sqlite_master WHERE type='table' AND
name='themes';
.headers on
.print '~~~~~'
-- ***** ***** ***** ***** ***** ***** ***** ***** ***** ***** --
-- (b) Create indexes
-- [insert your SQL statement(s) BELOW this line]
CREATE INDEX sets_index on sets(set_num);
CREATE INDEX parts_index on parts(part_num);
CREATE INDEX themes_index on themes(id);
-- [insert your SQL statement(s) ABOVE this line]
-- [the following statement(s) are for autograding. DO NOT
REMOVE.]
.indexes
.print '~~~~~'
-- ***** ***** ***** ***** ***** ***** ***** ***** ***** ***** --