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

 * 1) Read  Data manipulation language.
 * 2) Read  Insert (SQL).
 * 3) Read  Update (SQL).
 * 4) Read  Delete (SQL).
 * 5) Read  Truncate (SQL).
 * 6) Read  Merge (SQL).

Multimedia

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

Schema Diagrams

 * 1) Review HR and OE schema diagrams.
 * 2) Review the Oracle: HR and OE schema diagrams.

HR DML Statements

 * 1) Insert records using INSERT.
 * 2) Insert the region Antarctica using the following query:
 * 3) Select all records from regions to verify the results.
 * 4) Insert all countries as a new region using the following query:
 * 5) Select all records from regions to verify the results.
 * 6) Update records using UPDATE.
 * 7) Update the region Antarctica using the following query:
 * 8) Select all records from regions to verify the results.
 * 9) Update all country regions to upper case using the following query:
 * 10) Select all records from regions to verify the results.
 * 11) Update employees to give each employee a 5% salary increase using the following query:
 * 12) Select all records from employees to verify the results.
 * 13) Delete records using DELETE.
 * 14) Delete the region Antarctica using the following query:
 * 15) Select all records from regions to verify the results.
 * 16) Delete the country regions using the following query:
 * 17) Select all records from regions to verify the results.
 * 18) Delete all records from job history using the following query:
 * 19) Select all records from job history to verify the results.
 * 20) 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.
 * 21) Select all records from job history to verify the results.
 * 22) Truncate records using TRUNCATE.
 * 23) Truncate all job history using the following query:
 * 24) Select all records from job history to verify the results.
 * 25) 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.
 * 26) Select all records from job history to verify the results.

OE DML Statements

 * 1) Insert records using INSERT.
 * 2) Update records using UPDATE.
 * 3) Delete records using DELETE.
 * 4) 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 () VALUES();
 * UPDATE syntax: UPDATE SET =, ... [WHERE ];
 * DELETE syntax: DELETE FROM [WHERE ];
 * TRUNCATE TABLE syntax: TRUNCATE TABLE ;
 * MERGE syntax: MERGE INTO USING ON WHEN MATCHED THEN UPDATE SET = [, ...] WHEN NOT MATCHED THEN INSERT () VALUES ();

Assessments

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