Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

The Basics of Creating Tables in SQL

Introduction
Modifying table structure
6. Different ways to remove a column
Adding NOT NULL constraints
Summary

Instruction

Fantastic! Now, let's analyze two SQL statements that may look similar at first sight. Suppose the flight table was coded as follows:

CREATE TABLE flight (
  id integer PRIMARY KEY,
  booking_ref varchar(8),
  airplane varchar(16),
  operated_by varchar(32)
);

We want to remove the operated_by column. As you may recall, we've learned the following syntax:

ALTER TABLE flight DROP COLUMN operated_by;

However, could we also delete the entire table and create it again without the given column?

DROP TABLE flight;
CREATE TABLE flight (
  id integer PRIMARY KEY,
  booking_ref varchar(8),
  airplane varchar(16)
);

You see, both methods result in a table named flight which contains three columns: id, booking_ref, and airplane. However, ALTER TABLE keeps the data in the other, unchanged columns, while DROP TABLE + CREATE TABLE removes all the data from all the columns. Keep this information in mind whenever you need to change the structure of a table.

Exercise

Remove the column city from the store table by dropping the entire table and creating it again.

Stuck? Here's a hint!

Type:

DROP TABLE store;
CREATE TABLE store (
  id integer PRIMARY KEY,
  opening_date date
);