Adding, Changing, and Deleting Data
Adding, Changing, and Deleting Data
Querying and Manipulating Data 2-27
In
, you will use the
DECODE
function to assign possible salary increases
based on the
job_id
value.
Example 245 Using the DECODE Function
SELECT first_name || ' ' || last_name "Name",
job_id "Job", salary "Current Pay",
DECODE(job_id,
'PU_CLERK', salary * 1.10,
'SH_CLERK', salary * 1.15,
'ST_CLERK', salary * 1.20,
salary) "Proposed Salary"
FROM employees;
The results of the query appear.
Name Job Current Pay Proposed Salary
-------------------------- ---------------- --------------- -------------------
...
Alexander Khoo PU-CLERK 3100 3410
...
Julia Nayer ST_CLERK 3200 3840
...
Winston Taylor SH_CLERK 3200 3680
...
107 rows selected
Your result shows that the values in the '
Proposed Salary
' column have been
adjusted based on the
job_id
value.
Adding, Changing, and Deleting Data
Adding, changing and deleting operations in the database are commonly called Data
Manipulation Language (DML) statements:
An
INSERT
statement adds new rows to an existing table.
An
UPDATE
statement modifies the values of a set of existing table rows.
A
DELETE
statement removes existing rows from a table.
Because these statements change the data in your table, Oracle recommends that you
use transaction management to group all dependent DML statements together.
Inserting Information
When you use the
INSERT
statement to add a row of data to a table, the data inserted
must be valid for the data type and size of each column of the table.
The general syntax of the
INSERT
command looks like the following. Note that the list
of values has to be in the same order as the columns of the table.
INSERT INTO
table_name
VALUES
(
list_of_values_for_new_row
);
See Also:
Oracle Database SQL Language Reference for information about the
CASE
function
Oracle Database SQL Language Reference for information about the
DECODE
function
