Learning SQL Everyday: Day 3
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.
1. The ALTER
keyword:
This key word is one of the DDL (Data Definition Languages).
We can use the ALTER keyword to change the basic structure of a database table.
When we use the ALTER keyword with a table, it can be used to add, delete, or
modify columns of the selected table.
The syntax is:
ALTER TABLE table_name
ADD column_name datatype
ADD column_name datatype
If you want to delete/remove a column from an existing table,
you can use the ALTER keyword as follows:
ALTER TABLE table_name
DROP COLUMN column_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
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;
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
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
DROP COLUMN cgpa
Executing the above statement will
eliminate the ‘cgpa’ column and leave the rest of the table intact.
2. INSERT INTO
Keyword:
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,...);
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,...);
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.)
3. The UPDATE
Statement.
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;
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’.
4. The DELETE
Statement.
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;
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.