Introduction to the ALTER, INSERT INTO, UPDATE & DELETE Statements:

Today, we will try to see the:

ALTER, INSERT, UPDATE and DELETE SQL keywords and we will try to illustrate their use with example.

ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE table_name
DROP COLUMN column_name

If you want to change/modify the data-type of a column of an existing table, you can use the ALTER key word as follows:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

Now let us see the above syntax in use with an example. Assume you just created a table called ‘tbl_student’ using the following CREATE TABLE statement…

CREATE TABLE tbl_student(

            id int auto_increment,

            name varchar(50) not null,

            id_number varchar(20) not null,

            city varchar(30) not null,

            primary key(id)

);

Then you realize that you forgot to add one column called ‘CGPA’ to the table. So to add a new column to the existing table ‘tbl_student’ you can use:

ALTER TABLE tbl_student
ADD cgpa int;

When you execute this statement, the table will have a new column ‘cgpa’ appended to it. But then you again saw one error. A student CGPA is a floating number. E.g student can have 3.45 as a cgpa. But the data type of cgpa in tbl_student is int. So to change the ‘int’ to a ‘float’ data type, you need to do the following:

ALTER TABLE tbl_student
ALTER COLUMN cgpa float

If you want to change and decide that you don’t want to store the cgpa column in tbl_student, you need to only drop/delete the ‘cgpa’ column with out affecting the rest of the columns in the table. To do this, you need to use the DROP key word with ALTER. See the example given below:

ALTER TABLE tbl_student
DROP COLUMN cgpa

Executing the above statement will eliminate the ‘cgpa’ column and leave the rest of the table intact.

This key word is one of the DML (Data Manipulation Languages). It strictly deals with the content of a table and not with the underlying table structure. This key word is used to add/insert a new record to the table specified in the statement. The INSERT INTO SQL statement has two forms (syntax). These are given below:

Version One: (Here you don’t have to specify the order of the columns of the table. You just give the name of the table where the new record is supposed to be added followed by the actual data values to be inserted.)

INSERT INTO table_name
VALUES (value1,value2,value3,...);

Version Two: (In the second version, you need to specify the order of the columns followed by the actual data values you want to add to the table.)

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

Let us assume the example table we have created in the ALTER TABLE section. We will use the tbl_student schema (table structure) to insert data to it. In the beginning, the table contains no data, hence the table is empty.

First we will try to use the version one approach, and next we will use the version two approaches to insert new record to the table.

Version One:

INSERT INTO tbl_student

VALUES(0,’Alemu Teshome’,’STD/1234/02’,’Addis Ababa’);

Version Two:

INSERT INTO tbl_student (Name, Id_Number, City)

VALUES(‘Aster Degu’, ‘STD/2345/02’,’Jimma’);

As you can see from the above two examples, we are adding two new records to tbl_student but using two different versions of the INSERT INTO statement.

The first version always assumes to insert data to all columns of the table. Hence you need to pass a value to all columns of the table (full record value). You might have seen we have used 0 as the id value for the table. This column is defined as auto increment, so the value you pass will not be saved to the table. Rather, an automatically generated PK will be saved. All you need to do in such cases is to use a data value that has the same data type as the auto incremented value. In this case we are using int data type.

The second version of the INSERT INTO statement is used to specifically determine the columns you want to add data to. If you see the example again, we have left the id (auto incremented) value out and we have started from the ‘Name’ of the student table. Hence, data insertion will begin from the specified column name.

Therefore, you need to understand when to use the two different versions of the INSERT INTO statement. (N.B: You need to check if you have specified the columns as NOT NULL when creating them. In such cases, executing such an insert statement might generate an error.)

This key word is one of the DML (Data Manipulation Languages). It strictly deals with the content of a table and not with the underlying table structure. This statement is used to manipulate the content of a table (data only). You should remember that UPDATE is only used to modify an existing record. If there is no record, update statement will NOT insert a new record. The syntax of the update statement is given below:

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

In the SET clause, you are expected to specify which column’s content you would like to modify in the WHERE clause, you are expected to specify the condition that will identify which records from the table to update. If you leave out the WHERE clause, all the records of the table will be updated regardless of any condition. Let us see an example here:

UPDATE tbl_student

SET Name = ‘Solomon Teka’

WHERE Id_Number = ‘STD/1234/02’

Here only a record with Id_Number = ‘STD/1234/02’ will be modified. That is, the name value will be replaced with the new data value ‘Solomon Teka’. Executing this statement will not have any effect to the rest of the records of tbl_student. But if you leave out the WHERE Id_Number = ‘STD/1234/02’ part, then all records of tbl_student will have same name. i.e, ‘Solomon Teka’.

This key word is one of the DML (Data Manipulation Languages). It strictly deals with the content of a table and not with the underlying table structure. The DELETE statement is used to delete rows in a table. The syntax of the delete statement is given below:

DELETE FROM table_name
WHERE some_column=some_value;

Just like the UPDATE statement, if you leave the WHERE clause, all the records of the specified table will be deleted. Let us see an example where we try to remove the record with Id_Number ‘STD/2345/02’ from tbl_student.

DELETE FROM tbl_student

WHERE Id_Number = ‘STD/2345/02’

Executing the above statement will remove the record with a student Id_Number ‘STD/2345/02’. Only this record is expected to be deleted. If there is no student record with the given Id_Number, then the delete will not affect the other records of the table. But if you omit the WHERE condition, all the records of the table will be deleted. So you need to always be extra careful when dealing with DELETE statements.