Supporting reference and preserved earlier practice. Open the current classroom lesson →
SUPPORTING SQL REFERENCE · LESSON 16
Lesson 16: Turn the ERD into CREATE TABLE
Learn to create new database tables using CREATE TABLE statements and define table schemas with data types and constraints.
Practice database: Empty database → your new table. Each change exercise starts from its own original database when you run or check a change. SELECT previews inspect your current copy. Changes stay in this browser; the live schema shows their effect.
Read the explanation, then use the practice section below. Generic syntax and illustrative tables explain the idea; the exercise schema shows the exact names available in Empty database → your new table.
When you have new entities and relationships to store in your database, you can create a new database table using the CREATE TABLE statement.
The structure of the new table is defined by its table schema, which defines a series of columns. Each column has a name, the type of data allowed in that column, an optional table constraint on values being inserted, and an optional default value.
If there already exists a table with the same name, the SQL implementation will usually throw an error, so to suppress the error and skip creating a table if one exists, you can use theIF NOT EXISTS clause.
Table data types
Different databases support different data types, but the common types support numeric, string, and other miscellaneous things like dates, booleans, or even binary data. Here are some examples from SQLite:
| Data type | Description |
|---|---|
| INTEGER, BOOLEAN | The integer datatypes can store whole integer values like the count of a number or an age. In some implementations, the boolean value is just represented as an integer value of just 0 or 1. |
| REAL, DOUBLE, FLOAT | The floating point datatypes can store more precise numerical data like measurements or fractional values. Different types can be used depending on the floating point precision required for that value. |
| CHARACTER(num_chars), VARCHAR(num_chars), TEXT | TEXT stores text. In this SQLite engine, VARCHAR(20) does not enforce a maximum length; use a CHECK constraint when length must be limited. |
| DATE, DATETIME | SQLite has no dedicated date/time storage class. Applications commonly use ISO-formatted text, integer timestamps, or Julian-day real values and choose a consistent timezone convention. |
| BLOB | Finally, SQL can store binary data in blobs right in the database. These values are often opaque to the database, so you usually have to process them in your application directly. |
Table constraints
We aren't going to dive too deep into table constraints in this lesson, but each column can have additional table constraints on it which limit what values can be inserted into that column. This is not a comprehensive list, but will give you a sense of what you can do.
| Constraint | Description |
|---|---|
| PRIMARY KEY | This means that the values in this column are unique, and each value can be used to identify a single row in this table. |
| AUTOINCREMENT | For integer values, this means that the value is automatically filled in and incremented with each row insertion. Not supported in all databases. |
| UNIQUE | This means that the values in this column have to be unique, so you can't insert another row with the same value in this column as another row in the table. Differs from the `PRIMARY KEY` in that it doesn't have to be a key for a row in the table. |
| NOT NULL | This means that the inserted value can not be `NULL`. |
| CHECK (expression) | This allows you to run a more complex expression to test whether the values inserted are valid. For example, you can check that values are positive, or greater than a specific size, or start with a certain prefix, etc. |
| FOREIGN KEY | This is a consistency check which ensures that each value in this column corresponds to another value in a column in another table. For example, if there are two tables, one listing all Employees by ID, and another listing their payroll information, the `FOREIGN KEY` can ensure that every row in the payroll table corresponds to a valid employee in the master Employee list. |
Database Cleanup Notice
Since we're working with CREATE TABLE operations, each time you run the lesson, we start with a fresh empty database. This ensures you can practice creating tables from scratch without conflicts.
Exercise
In this exercise, you'll need to create a new table for us to insert some new rows with relevant information.
Try a worked example
CREATE TABLE movies (
id INTEGER PRIMARY KEY,
title TEXT,
director TEXT,
year INTEGER,
length_minutes INTEGER
);Read the query, predict its result, then inspect the rows or schema it produces.
Open example in playground ↗TRY IT YOURSELF
Lecture 16: Creating Tables
Empty database → your new table · Build and change a database
Each change exercise starts from its own original database when you run or check a change. SELECT previews inspect your current copy. Changes stay in this browser; the live schema shows their effect.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
YOUR LESSON DATABASE
Tables, keys & sample rows
PK = primary key · FK = declared foreign key. Expand a table to see its columns and the first five stored rows.
Loading schema…
Query results
Run a query to explore. Check my answer checks the selected exercise.Run a query to explore. Check my answer checks the selected exercise.
Changes affect this exercise copy only · Every change run starts fresh · Ctrl / ⌘ + Enter runs your query