Oracle SQL Fundamentals/Manipulating Data

This lesson introduces manipulating data.

Objectives and Skills

edit

Objectives and skills for the manipulatin data portion of Oracle SQL Fundamentals I certification include:[1]

  • 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

edit
  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

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

Activities

edit

Schema Diagrams

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

HR DML Statements

edit
  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

edit
  1. Insert records using INSERT.
  2. Update records using UPDATE.
  3. Delete records using DELETE.
  4. Truncate records using TRUNCATE.

Lesson Summary

edit
  • 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.[2]
  • An SQL INSERT statement adds one or more records to any single table in a relational database.[3]
  • 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.[4]
  • 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.[5]
  • 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.[6]
  • An SQL MERGE statement inserts records or updates existing records based on the given condition.[7]
  • INSERT syntax: INSERT INTO <table1>(<column(s)>) VALUES(<value(s)>);[8]
  • UPDATE syntax: UPDATE <table1> SET <column> = <value>, ... [WHERE <condition>];[9]
  • DELETE syntax: DELETE FROM <table1> [WHERE <condition>];[10]
  • TRUNCATE TABLE syntax: TRUNCATE TABLE <table1>;[11]
  • 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)>);[12]

Assessments

edit

See Also

edit

References

edit