WiseTome.com

Data Modification Tasks

Again, for sake of illustration, I would assume that the following table exists in the database:

Let us first look into some of the data modification commands that can be issued using SQL.

Say, we need to insert another employee, by the name Ram to this table with department as 'Programming' and employee number as '6005'. This could be achieved by the following:

INSERT INTO emp_data ( empid, empname, dept ) VALUE ( '6005', 'Ram', 'Programming' );

Now to check the database, we can give a specific query to fetch all the Programmers, that we learnt in the last part:

SELECT * FROM emp_data WHERE dept = 'Programming' ORDER BY empname DESC;

This would fetch us the following result:

As you can see, Ram has been added to the database.

Now, let us say, there were last minute changes, and Ram was actually needed in the Security department. There would be two methods to do this. Delete the particular row (which we would discuss a bit later) and insert another fresh row, or change the row in the table using the UPDATE query as follows:

UPDATE empdata SET dept = 'Security' WHERE empid = '6005';

The WHERE clause is required to locate the row, else the database would change the department of all the employees. Since Ram has the employee id of 6005, we can use the above query or alternatively, we may use:

UPDATE empdata SET dept = 'Security' WHERE empname = 'Ram';

You may check the effect of the command by issuing the following specific query:

SELECT FROM empdata WHERE empname = 'Ram';

This would give the following result:

Now, let us assume that the particular row for Ram needs to be deleted from the table altogether. This can be achived by giving the following SQL command:

DELETE empdata WHERE empname = 'Ram';

That would remove the row for Ram from the database table.

Control Tasks

Now let us look into two command that controls the database. They are COMMIT and ROLLBACK. It should be noted that any changes done on the database in a session, is not permanent. If it is required to make them permanent, then one should issue a commit command:

COMMIT;

As the changes done are not permanent without an commits, they can also be undone using the rollback command:

ROLLBACK;

These two control commands in SQL help in managing and controling a transaction. A transaction is a set of SQL actions that when performed, is a business action unit. The actual understanding of transaction would involve more study, and for now, we can understand that these are kind of 'confirm' and 'undo' commands in SQL.