Data Manipulation

After learning how to create tables and modifying tables, it is time to learn how to work with data in the database tables. This section is about Data Manipulation Language which handles data manipulation e.g. inserting data into tables, deleting data from tables, and update data. These statements can be rolled back (discarded).

These are common operations we perform with the modern apps like WhatsApp, Instagram, Twitter, Snapchat, etc when we make posts, updating statuses, deleting posts, etc. Behind those buttons we click like the send button; lies SQL statements. We are going to see how these are used.

Inserting Data Into Tables

To insert data into the tables, we use the INSERT INTO keyword. This is followed by the table name and then proceeded with columns we need data to be inserted and finally the values we want to enter. The syntax for this is in two way:

1. One method is when we specify the columns we want to insert data into.

INSERT INTO table_name (column_name/s) VALUES (values);

With this method, we specify only the columns we need to insert data into. We may decide to leave out some of them. This method is used when we only need data to be inserted into some columns but not all columns. However, columns with NOT NULL and PRIMARY KEY constraints cannot be left without a value. They must be included among columns to be inserted data into.

For example, let us enter data into our facilitator’s table we created during table creation.

INSERT INTO facilitator (facilitator_id, first_name, last_name, gender) VALUES (1, 'Samuel', 'Ssendi', 'Male');

When inserting data, values in columns with NUMBER datatype should not be wrapped in quotes. For VARCHAR2 datatype, values must be wrapped in single quotes. We can only enter one record at ago.

2. The second method is, we do not specify the columns we want to insert data into. Below is the syntax

INSERT INTO table_name VALUES (values);

With this method, we have to insert data in all columns of the table and in the right order of columns (as per the table). It’s a tricky method because we’re inserting data blindly i.e. we may not know which column we’re inserting data into. This could cause some mistakes when a data value is put in a wrong column. We have to know the order of columns for example, for the faculty table, we have to know the first value will be for faculty_code, the second for name, etc. Another drawback with this method is we can’t leave any fill empty.

Lets insert one record in the faculty table.

INSERT INTO faculty VALUES ('FCI', 'Faculty of Computing and Informatics', 'Prof. Moya Musa');

Inserting Data Using A Query

We may decide to use existing data from other tables as data in our new tables. Just like created tables from existing tables, we can still do the same here where entering data. For example, lets use details in the departments table to fill the faculty table.

INSERT INTO faculty (faculty_code, name) SELECT department_id, department_name FROM departments;

Updating Data In Tables

Just like we are able to insert data into the tables, we can as well update the data we have inserted into the tables. To be able to do this, we use the UPDATE keyword.

UPDATE table_name SET column_name = new_value WHERE condition;

From the above query, we are updating a given table and we are picking a specific column whose value we want to update for example, we need to change the name of an employee in the first_name column in the employees table. We finally add the WHERE clause because if we don’t add it there, all records in the specified column will be updated (which in most cases not what we want).

For example, there is a change of department (to department 100) for employee whose employee_id is 164. Lets update this employee’s record in the employees table

UPDATE employees SET department_id = 100 WHERE employee_id = 164;

We can update multiple details in a record by using commas to separate the columns and values in the SET clause. For example, lets update the department id to 150 and last name to Nakaayi for employee 134.

UPDATE employees SET department_id = 150, last_name = 'Nakaayi' WHERE employee_id = 134;

Deleting Data From Tables.

To delete a row of data from a table, we use DELETE keyword. From the syntax below, if the WHERE clause is omitted, all records will be deleted.

DELETE FROM table_name WHERE condition;

Lets delete a record for facilitator 1 from the facilitator table

DELETE FROM facilitator WHERE facilitator_id = 1;

Deleting A Single Value From A Record.

To delete a single record from a record, we don’t use DELETE but rather UPDATE. We then set the new value to NULL. For example, We want to delete the manager_id value from the record of employee 145 in the employees table.

UPDATE employees SET manager_id = NULL WHERE employee_id = 145;

Deleting All Data In The Tables

We can use two commands to achieve this i.e. DELETE and TRUNCATE. With DELETE, we don’t add the WHERE clause like we have seen in the previous section about deleting data from tables. The beauty about this, the action is reversible i.e. it can be discarded or rolled back because DELETE falls under DML (Data Manipulation Language).

With TRUNCATE, the action is irreversible because TRUNCATE falls under Data Definition Language (DDL). TRUNCATE is faster than DELETE. Here is the syntax for TRUNCATE:

TRUNCATE TABLE table_name;

For example, lets delete all records from the customers table;

TRUNCATE TABLE customers;

Saving and Undoing Changes

To save the changes we have done with the DML statements, we use the COMMIT keyword and ROLL to discard or undo those changes. Both statements can be issued after the execution of a DML statement (INSERT, UPDATE, DELETE). This will save for COMMIT or undo for ROLLBACK the changes that have been made to data.

However fast it is, you should avoid using TRUNCATE to delete all data unless you’re sure of your action.

Summary

1. To enter data into the tables, we use an INSERT statements.

2. UPDATE statement is used to update data in the database tables.

3. DELETE statement is used to delete data from the database tables.

4. TRUNCATE will delete all data in the database.