# Oracle SQL Fundamentals/Manipulating Data

This lesson introduces manipulating data.

## Objectives and Skills

Objectives and skills for the manipulatin data portion of Oracle SQL Fundamentals I certification include:
* Manipulating Data
** Describe each data manipulation language (DML) statement
** Insert rows into a table
** Update rows in a table
** Delete rows from a table

## Readings

* Read Wikipedia: Data manipulation language.
* Read Wikipedia: Insert (SQL).
* Read Wikipedia: Update (SQL).
* Read Wikipedia: Delete (SQL).
* Read Wikipedia: Truncate (SQL).
* Read Wikipedia: Merge (SQL).

## Multimedia

* YouTube: PL SQL Tutorial DML and TCL statements (Theory)
* YouTube: PL SQL Tutorial DML and TCL statements (Hands On)

## Activities
### Schema Diagrams

* Review HR and OE schema diagrams.
** Review the Oracle: HR and OE schema diagrams.

### HR DML Statements

* Insert records using INSERT.
** Insert the region Antarctica using the following query: INSERT INTO REGIONS (REGION_ID, REGION_NAME) VALUES (5, 'ANTARCTICA');
** Select all records from regions to verify the results.
** Insert all countries as a new region using the following query: INSERT INTO REGIONS (REGION_ID, REGION_NAME) (SELECT ROWNUM + 10, COUNTRY_NAME FROM COUNTRIES);
** Select all records from regions to verify the results.
* Update records using UPDATE.
** Update the region Antarctica using the following query: UPDATE REGIONS SET REGION_NAME = 'Antarctica' WHERE REGION_ID = 5;
** Select all records from regions to verify the results.
** Update all country regions to upper case using the following query: UPDATE REGIONS SET REGION_NAME = UPPER(REGION_NAME) WHERE REGION_ID > 10;
** Select all records from regions to verify the results.
** Update employees to give each employee a 5% salary increase using the following query: UPDATE EMPLOYEES SET SALARY = SALARY * 1.05;
** Select all records from employees to verify the results.
* Delete records using DELETE.
** Delete the region Antarctica using the following query: DELETE FROM REGIONS WHERE REGION_ID = 5;
** Select all records from regions to verify the results.
** Delete the country regions using the following query: DELETE FROM REGIONS WHERE REGION_ID > 10;
** Select all records from regions to verify the results.
** Delete all records from job history using the following query: DELETE FROM JOB_HISTORY
** Select all records from job history to verify the results.
** To restore job history, open the sample schema scripts folder you created in the Introduction. Open the schema/oe/hr/hr_popul.sql file and copy all statements that insert records into the JOB_HISTORY table. Run those statements to restore the data.
** Select all records from job history to verify the results.
* Truncate records using TRUNCATE.
** Truncate all job history using the following query: TRUNCATE TABLE JOB_HISTORY;
** Select all records from job history to verify the results.
** To restore job history, open the sample schema scripts folder you created in the Introduction. Open the schema/oe/hr/hr_popul.sql file and copy all statements that insert records into the JOB_HISTORY table. Run those statements to restore the data.
** Select all records from job history to verify the results.

### OE DML Statements

* Insert records using INSERT.
* Update records using UPDATE.
* Delete records using DELETE.
* Truncate records using TRUNCATE.

## Lesson Summary

* A data manipulation language (DML) is a family of syntax elements used for selecting (SELECT), inserting (INSERT), updating (UPDATE) and deleting (DELETE) data in a database.
* An SQL INSERT statement adds one or more records to any single table in a relational database.
* An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.
* An SQL DELETE statement removes one or more records from a table. Either all records are removed, or a subset may be chosen using a condition.
* An SQL TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms.
* An SQL MERGE statement inserts records or updates existing records based on the given condition.
* INSERT syntax: INSERT INTO <table1>(<column(s)>) VALUES(<value(s)>);
* UPDATE syntax: UPDATE <table1> SET <column> = <value>, ... [WHERE <condition>];
* DELETE syntax: DELETE FROM <table1> [WHERE <condition>];
* TRUNCATE TABLE syntax: TRUNCATE TABLE <table1>;
* MERGE syntax: MERGE INTO <table1> USING <table2> ON <condition> WHEN MATCHED THEN UPDATE SET <column> = <value> [, ...] WHEN NOT MATCHED THEN INSERT (<column(s)>) VALUES (<value(s)>);

## Assessments

* Flashcards: Quizlet: Oracle 1Z0-051 Exam - Insert/Update/Delete Statements

## See Also

* Oracle: Database 2 Day Developers Guide - DML Statements and Transactions