SQL Lesson 17: Altering tables

As your data changes over time, SQL provides a way for you to update your corresponding tables and database schemas by using the ALTER TABLE statement to add, remove, or modify columns and table constraints.

Adding columns

The syntax for adding a new column is similar to the syntax when creating new rows in the CREATE TABLE statement. You need to specify the data type of the column along with any potential table constraints and default values to be applied to both existing and new rows. In some databases like MySQL, you can even specify where to insert the new column using the FIRST or AFTER clauses, though this is not a standard feature.

Altering table to add new column(s)

ALTER TABLE mytable
ADD column DataType OptionalTableConstraint 
    DEFAULT default_value;

Removing columns

Dropping columns is as easy as specifying the column to drop, however, some databases (including SQLite) don't support this feature. Instead you may have to create a new table and migrate the data over.

Altering table to remove column(s)

ALTER TABLE mytable
DROP column_to_be_deleted;

Renaming the table

If you need to rename the table itself, you can also do that using the RENAME TO clause of the statement.

Altering table name

ALTER TABLE mytable
RENAME TO new_table_name;

Other changes

Each database implementation supports different methods of altering their tables, so it's always best to consult your database docs before proceeding: MySQL, Postgres, SQLite, Microsoft SQL Server.

Exercise

Our exercises use an implementation that only support adding new columns, so give that a try below.

Sorry but the SQLBolt exercises require a more recent browser to run.
Please upgrade to the latest version of Internet Explorer, Chrome, or Firefox!

Otherwise, continue to the next lesson: SQL Lesson 18: Dropping tables

Table: movies

Loading SQL...

RUN QUERY RESET

Exercise 17 — Tasks

Add a column named Aspect_ratio with a FLOAT data type to store the aspect-ratio each movie was released in.
Add another column named Language with a TEXT data type to store the language that the movie was released in. Ensure that the default for this language is English.

Stuck? Read this task's Solution.
Solve all tasks to continue to the next lesson.

Finish above Tasks