SQL Lesson 16: Creating Tables

Learn how to create new tables and define their structure using CREATE TABLE statements.

Loading Database...

SQL Lesson 16: Creating tables

When you have new entities and relationships to store in your database, you can create a new database table using the CREATE TABLE statement.

CREATE TABLE

IF NOT EXISTS mytable (

column DataType TableConstraint DEFAULT default_value,

another_column DataType TableConstraint DEFAULT default_value,

…

);

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	The text based datatypes can store strings and text in all sorts of locales. The distinction between the various types generally amount to underlaying efficiency of the database when working with these columns. Both the CHARACTER and VARCHAR (variable character) types are specified with a max number of characters that they can store (longer values may be truncated), so can be more efficient to store and query with big tables.
DATE, DATETIME	SQL can also store date and time stamps to keep track of time series and event data. They can be tricky to work with especially when manipulating data across timezones.
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.

Query results

Run a query to see results

Exercise 16 — Tasks

Create a table to hold information about the movies in our database which has a Movies table with the following columns:
• Id (Integer)
• Title (Text)
• Director (Text)
• Year (Integer)
• Length_minutes (Integer)

Hint: Remember to define the Id as a primary key

Create another table named Boxoffice which has the following columns:
• Movie_id (Integer)
• Rating (Real)
• Domestic_sales (Integer)
• International_sales (Integer)

Solve all tasks to continue to the next lesson.

Progress0 of 2 tasks completed