SQL Lesson 13: Inserting Rows

Learn to add new data to databases using INSERT statements and understand database schemas.

SQL Lesson 13: Inserting Rows

We've spent quite a few lessons on how to query for data in a database, so it's time to start learning a bit about SQL schemas and how to add new data.

What is a Schema?

We previously described a table in a database as a two-dimensional set of rows and columns, with the columns being the properties and the rows being instances of the entity in the table. In SQL, thedatabase schema is what describes the structure of each table, and the datatypes that each column of the table can contain.

Example: Database Schema

For example, in our Movies table, the values in the Year column must be an Integer, and the values in the Title column must be a String.

This fixed structure is what allows a database to be efficient, and consistent despite storing millions or even billions of rows.

Inserting New Data

When inserting data into a database, we need to use an INSERT statement, which declares which table to write into, the columns of data that we are filling, and one or more rows of data to insert. In general, each row of data you insert should contain values for every corresponding column in the table. You can insert multiple rows at a time by just listing them sequentially.

Insert statement with values for all columns

INSERT INTO

mytable

VALUES

(value_or_expr, another_value_or_expr, ...),

(value_or_expr_2, another_value_or_expr_2, ...),

...;

In some cases, if you have incomplete data and the table contains columns that support default values, you can insert rows with only the columns of data you have by specifying them explicitly.

Insert statement with specific columns

INSERT INTO

mytable

(column, another_column, ...)

VALUES

(value_or_expr, another_value_or_expr, ...),

(value_or_expr_2, another_value_or_expr_2, ...),

...;

In these cases, the number of values need to match the number of columns specified. Despite this being a more verbose statement to write, inserting values this way has the benefit of being forward compatible. For example, if you add a new column to the table with a default value, no hardcoded INSERT statements will have to change as a result to accommodate that change.

In addition, you can use mathematical and string expressions with the values that you are inserting.
This can be useful to ensure that all data inserted is formatted a certain way.

Example Insert statement with expressions

INSERT INTO

boxoffice

(movie_id, rating, sales_in_millions)

VALUES

(1, 9.9, 283742034 / 1000000);

Key INSERT Concepts:

Schema Compliance: Data must match column types and constraints
Complete vs Partial: Can specify all columns or just some with defaults
Multiple Rows: Insert several rows in a single statement
Expressions: Use calculations and functions in VALUES
Auto-increment: Some columns (like ID) may be automatically generated

Exercise

In this exercise, we are going to play studio executive and add a few movies to the Movies to our portfolio. In this table, the Id is an auto-incrementing integer, so you can try inserting a row with only the other columns defined.

Since the following lessons will modify the database, you'll have to manually run each query once they are ready to go.

Table: movies (Read-only)

Table: boxoffice (Read-only)

Query results

Exercise 13 — Tasks

Add the studio's new production, Toy Story 4 to the list of movies (you can use any director)
Toy Story 4 has been released to critical acclaim! It had a rating of 8.7, and made 340 million domestically and 270 million internationally. Add the record to the BoxOffice table.

Solve all tasks to continue to the next lesson.

Progress0 of 2 tasks completed