Managing data in Oracle 10g using SQL

Learning objective

After completing this topic, you should be able to recognize how to manage data using the INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK commands.

1. The INSERT command

SQL contains data manipulation language (DML) statements, that are used to insert, update, delete, commit, and roll back data in the database.

The INSERT statement is used to create a new row in a table. That new row can then be populated with data called from another table, using a subselect command.

In some situations, the source and target tables will have the same structure, and you can just copy the row without changing it.

Suppose you wanted to use an INSERT statement to copy information about a particular department's employees from the EMPLOYEES table to the DEPT_80 table. You've already typed a SELECT statement that retrieves all columns with 80 as the department ID from the EMPLOYEES table.

MISSING CODE (SELECT * from employees
where department_id = 80);

You now need to add the code that copies the rows from the EMPLOYEES table into the DEPT_80 table.

MISSING CODE (SELECT * from employees
where department_id = 80);

You type the command INSERT into dept_80 to complete the INSERT statement.

The data has been copied from the EMPLOYEES table, and added to new rows in the DEPT_80 table.

In other cases, you will want to reorder the data from the source table before inserting it into the target table. Or you might want to copy only some of the data from the source table.

INSERT into just_names (first, last)
(SELECT first_name, last_name from employees);

In these situations, you can specify the columns that will be populated in the target table, and then the columns that will be copied in the source table.

For example, this code populates the first and last columns of the JUST_NAMES table with the data from the FIRST_NAME and LAST_NAME columns of the EMPLOYEES table.

There are certain integrity constraints that must be obeyed when you are inserting data into particular types of columns:

• FOREIGN KEY columns
• NOT NULL columns
• UNIQUE key columns
• PRIMARY KEY columns
• CHECK columns

FOREIGN KEY columns

You can only insert a value into a FOREIGN KEY column if that value exists in the parent table.

NOT NULL columns

Some of the fields in a row of data may be empty. A row containing empty fields can't be inserted into a table if it would mean that one of those empty fields is added to a column that has a NOT NULL constraint.

UNIQUE key columns

Some tables contain the constraint that every value in a particular column must be unique. You can't insert a row into a table if it contains the same value in a field as an existing row. The exception to this is NULL. Each null is treated as a unique value, so a UNIQUE key column can contain several null entries.

PRIMARY KEY columns

The data entered into a PRIMARY KEY-constrained column must be unique, and the PRIMARY KEY field cannot be a NULL value.

CHECK columns

If the column has a CHECK constraint, then the inserted entry must meet the condition it specifies.

Question

Which of these constraints apply to INSERT statements?

Options:

1. FOREIGN KEY entries must match an entry in the parent table
2. NOT NULL columns must contain a value in the inserted row
3. PRIMARY KEY columns must contain a unique value, or null, in the inserted row
4. UNIQUE columns must contain a unique value, or null, in the inserted row

Answer

FOREIGN KEY entries must match an entry in the parent table, NOT NULL columns must contain a value in the inserted row, and UNIQUE columns must contain a unique value, or null, in the inserted row.

Option 1 is correct. The FOREIGN KEY column in the target table is related to a PRIMARY KEY column in another table. The new entry in this column must maintain this relationship, by matching a primary key value in the parent table.

Option 2 is correct. The inserted row must contain a value in any NOT NULL columns, or an error will be returned.

Option 3 is incorrect. The PRIMARY KEY field is used to distinguish the rows in a table from each other. This means that it must contain a unique value, and the field may not be left blank, or have a null value.

Option 4 is correct. If a column has the UNIQUE constraint, then the inserted value may not match an existing value in the column. However, each null entry is treated as a unique value.

Question

You want to create some new rows in the DEPT_80 table, and populate them with employee data from columns with a department ID of 100 in the EMPLOYEES table.

Which option enables you to complete this task?

Options:

1. You complete the command by typing INSERT into dept_80. You then click Execute.
2. You complete the command by typing INSERT to dept_80. You then click Execute.

Answer

You complete the command by typing INSERT into dept_80 to copy the data into the DEPT_80 table. You then click Execute.

2. The UPDATE command

The UPDATE command allows you to set a new value for selected rows in a table.

Suppose you wanted to award a 10 percent pay rise to all of the employees in department 90. This involves updating the EMPLOYEES table.

The SET command defines the change that will be made, and the WHERE clause specifies which rows will be modified.

Note

If no WHERE clause is included, then all rows will be updated, but if no row agrees with the WHERE clause, none of the rows will be changed.

You now need to input the piece of code that specifies the table to be updated.

MISSING CODE
SET salary = salary * 1.1
WHERE department_id = 90

You type update employees, and click Execute to update the table.

The EMPLOYEES table has now been updated.

Some integrity constraints must be obeyed when you are updating the data in a table:

• FOREIGN KEY columns
• NOT NULL columns
• UNIQUE columns
• PRIMARY KEY columns
• CHECK columns

FOREIGN KEY columns

The FOREIGN KEY column must contain a value that matches a value in the related table's primary key column. The DML statement will return an error if your update results in a foreign key value that doesn't correspond to a primary key.

NOT NULL columns

You cannot update a row so that it contains a null value in a NOT NULL column.

UNIQUE columns

The updated rows must still have a unique value in any columns with a UNIQUE constraint. In this context, however, a null value is always unique.

PRIMARY KEY columns

The PRIMARY KEY column must contain a unique value that is not NULL.

CHECK columns

Any CHECK constraints that have been placed on a column must also be met after the update.

Question

Which of these points about constraints apply to UPDATE statements?

Options:

1. FOREIGN KEY entries must match an entry in the parent table
2. NOT NULL columns must contain a value in the inserted row
3. UNIQUE columns must contain a unique value, or null, in the inserted row
4. Updated columns no longer need to meet the values set by CHECK constraints

Answer

FOREIGN KEY entries must match an entry in the parent table, NOT NULL columns must contain a value in the inserted row, and UNIQUE columns must contain a unique value, or null, in the inserted row.

Option 1 is correct. The FOREIGN KEY column in the target table is related to a PRIMARY KEY column in another table. The updated entry in this column must maintain this relationship, by matching a primary key value in the parent table.

Option 2 is correct. The updated row must contain a value in any NOT NULL columns, or an error will be returned.

Option 3 is correct. If a column has the UNIQUE constraint, then the updated value may not match an existing value in the column. However, each NULL entry is treated as a unique value.

Option 4 is incorrect. The CHECK constraints applied to a column are not affected by updates. If you try to change a value to one that isn't allowed by the CHECK constraint, an error will be returned.

Question

Suppose you wanted to increase the salaries of employees in department 90 by 20 percent.

Which of the following options enables you to complete this task?

Options:

1. You type UPDATE employees to change the data in the EMPLOYEES table and then press Execute
2. You type UPDATES employees to change the data in the EMPLOYEES table and then press Execute

Answer

You type UPDATE employees to change the data in the EMPLOYEES table and then press Execute.

3. The DELETE command

The DELETE command allows you to remove one or more rows from a database table.

Suppose you have closed down one of your warehouses, selling off the stock it contained. You discover you still have some records in your inventory pointing to that warehouse, so you decide to delete all of these ghost records. The WHERE clause specifies which rows are to be deleted.

MISSING CODE

WHERE warehouse_id = 10

You now need to input the piece of code that specifies which table the rows are to be deleted from.

MISSING CODE

WHERE warehouse_id = 10

You type the command DELETE from inventories, and click Execute, to delete the records.

The code searches through the table for any records that match the WHERE clause, and deletes them.

If no records matching the clause are found, then no rows will be deleted. If no WHERE clause is specified, all of the rows in the table will be deleted.

The integrity of the database means that some rows may not be deleted from a table.

If a table row has an entry in the FOREIGN KEY column, and this table is linked with another using a row which is a primary key, it can't be deleted.

Question

Suppose you are rationalizing your Orders table, and want to remove all orders that have become more than six months overdue (signified in the database by a status of 5).

Which of the following options enables you to complete this task?

Options:

1. You use the command
   
DELETE from orders
where order_duration = 5
   
   to remove the database entries and then press Execute.
2. You use the command
   
DELETE from orders
where order_status = 5
   
   to remove the database entries and then press Execute.

Answer

You use the command

DELETE from orders
where order_status = 5

to remove the database entries and then press Execute.

4. The COMMIT and ROLLBACK commands

The changes that are made using DML commands are not applied to the database immediately, but are held in a pending state instead.

The user who issued the DML statements can see the changed data, but other users will see the data as it was before the changes were made.

However, while one user's changes are still pending, no one else can make any changes to that data. The rows are automatically locked by Oracle.

Question

You have added some new customer orders to the Orders table which are still in a pending state. What implications does this have for the database?

Options:

1. The changed data is only visible to you
2. The changed data is visible to all users
3. Only you can issue DML statements that affect the changed rows
4. All users can issue DML statements that affect the changed rows

Answer

The changed data is only visible to you, and only you can issue DML statements that affect the changed rows.

Option 1 is correct. While the changes are still in a pending state, they are only visible to you. Any other users viewing the data will see the original, unchanged data.

Option 2 is incorrect. While the changes are still pending, other users will only see the unchanged data. The changes only become publicly visible after they have been committed to the database.

Option 3 is correct. When you change a row in a table, Oracle locks that row until the change is applied to the database. You can still change the row, but other users can't.

Option 4 is incorrect. Other users can only see the unchanged version of the rows from the database, and they can't make any changes to those rows as long as there are pending changes outstanding. Once all pending changes have been applied, other users can make their own changes.

Pending changes can be applied to the database using the COMMIT command. This unlocks the rows, so that they can be changed by other users.

Suppose you've made some changes to the database and you want to make them permanent.

You type the COMMIT command and click Execute to apply changes to the database.

The database has been updated.

Alternatively, you can use the ROLLBACK command to undo the pending changes, so that the database remains in its original state. This will also make the rows available to other users.

You type the ROLLBACK command and click Execute to undo your changes.

Your changes have been deleted.

Question

You have deleted some orders from the ORDERS table, and now want to make the changes permanent.

Type the code that applies your changes to the database.

Answer

You use the COMMIT command to apply your changes to the database.

Summary

The data manipulation language (DML) statements in SQL allow you to add, remove, and edit the data in tables. The INSERT command allows you to create a new row in a table, and a subselect command then populates that row with data from another table. This new table entry is subject to the existing integrity constraints.

The UPDATE command allows you to select some or all of the rows in a table, and change the values in a selected column. This command is also subject to integrity constraints.

You can remove rows from a table using the DELETE command. This command includes a WHERE clause, which specifies which of the table's rows will be deleted. You can delete all of the table's rows by omitting the WHERE clause.

The changes made using these DML statements are not applied to the database immediately. They are kept in a pending state, during which time the changes are not visible to, and the affected rows can't be changed by, other users. You can undo these changes using the ROLLBACK command, or apply them to the database with the COMMIT command.