Slide 6.15: SQL ALTER TABLE statement (cont.)

SQL ALTER TABLE Statement (Cont.)

Look at the person table:

person_id	last_name	first_name	address	city
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to add a column named DOB in the person table. We use the SQL statement:

 ALTER TABLE person
   ADD DOB DATE;

Notice that the new column, DOB, is of type DATE and is going to hold a date. The data type specifies what type of data the column can hold. The person table will now look like this:

person_id	last_name	first_name	address	city
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to change the data type of the column named DOB in the person table. We use the SQL statement:

Notice that the DOB column is now of type CHAR and is going to hold a string.

 ALTER TABLE person
   MODIFY DOB CHAR(10);

Next, we want to delete the column named DOB in the person table. We use the SQL statement:

 ALTER TABLE person
   DROP COLUMN DOB;

The person table will now look like this:

person_id	last_name	first_name	address	city
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

Result:

The Database includes:

Tablename	Record
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

W3C WebSQL

WebSQL Browser Support