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 Wikipedia: Data manipulation language.
2. Read Wikipedia: Insert (SQL).
3. Read Wikipedia: Update (SQL).
4. Read Wikipedia: Delete (SQL).
5. Read Wikipedia: Truncate (SQL).
6. Read Wikipedia: Merge (SQL).

Multimedia

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

Activities

Schema Diagrams

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

HR DML Statements

1. Insert records using INSERT.
   1. Insert the region Antarctica using the following query:
      INSERT INTO REGIONS (REGION_ID, REGION_NAME) VALUES (5, 'ANTARCTICA');
   2. Select all records from regions to verify the results.
   3. 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);
   4. Select all records from regions to verify the results.
2. Update records using UPDATE.
   1. Update the region Antarctica using the following query:
      UPDATE REGIONS SET REGION_NAME = 'Antarctica'
WHERE REGION_ID = 5;
   2. Select all records from regions to verify the results.
   3. Update all country regions to upper case using the following query:
      UPDATE REGIONS SET REGION_NAME = UPPER(REGION_NAME)
WHERE REGION_ID > 10;
   4. Select all records from regions to verify the results.
   5. Update employees to give each employee a 5% salary increase using the following query:
      UPDATE EMPLOYEES SET SALARY = SALARY * 1.05;
   6. Select all records from employees to verify the results.
3. Delete records using DELETE.
   1. Delete the region Antarctica using the following query:
      DELETE FROM REGIONS
WHERE REGION_ID = 5;
   2. Select all records from regions to verify the results.
   3. Delete the country regions using the following query:
      DELETE FROM REGIONS
WHERE REGION_ID > 10;
   4. Select all records from regions to verify the results.
   5. Delete all records from job history using the following query:
      DELETE FROM JOB_HISTORY
   6. Select all records from job history to verify the results.
   7. 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.
   8. Select all records from job history to verify the results.
4. Truncate records using TRUNCATE.
   1. Truncate all job history using the following query:
      TRUNCATE TABLE JOB_HISTORY;
   2. Select all records from job history to verify the results.
   3. 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.
   4. 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 <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

References

← Set Operators

Oracle SQL Fundamentals

Transactions →