lesson 59 Database engineering · advanced

DDL: create, alter & inspect a table

Create and alter a table, then inspect its structure.

the idea

DDL (data definition language) defines database objects. CREATE TABLE names columns, types and keys; ALTER TABLE adds a column to an existing table. After running DDL, inspect the schema itself (sqlite_schema, pragma_table_info) as well as the rows you insert.

Dataset: Developer Platform

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Read a stored CREATE statement. Run this. SQLite keeps the DDL for pull_requests in sqlite_schema—this is how you inspect how an existing table was defined.

   step-1.sql Run

   sql›

   SELECT sql
   FROM sqlite_schema
   WHERE name = 'pull_requests';

2. 2

   Create a table and insert rows. This lab uses a disposable copy of the database. You define release_notes, insert a row, then SELECT it back to confirm DDL and DML worked together.the pattern

   example.sql Run

   sql›

   CREATE TABLE release_notes (note_id INTEGER PRIMARY KEY, repository_id INTEGER NOT NULL REFERENCES repositories(repository_id), body TEXT NOT NULL);
   INSERT INTO release_notes VALUES (1, 1, 'First release');
   SELECT *
   FROM release_notes;

common mistake

A SELECT that returns the expected constants does not create the table; the check inspects the schema.