Oracle SQL Fundamentals/Manipulating Data
This lesson introduces manipulating data.
Objectives and Skills
editObjectives 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- 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
editActivities
editSchema Diagrams
edit- Review HR and OE schema diagrams.
- Review the Oracle: HR and OE schema diagrams.
HR DML Statements
edit- 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.
- Insert the region Antarctica using the following query:
- 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.
- Update the region Antarctica using the following query:
- 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.
- Delete the region Antarctica using the following query:
- 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.
- Truncate all job history using the following query:
OE DML Statements
edit- Insert records using INSERT.
- Update records using UPDATE.
- Delete records using DELETE.
- 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
editSee Also
editReferences
edit- ↑ Oracle: Database 11g: SQL Fundamentals I Exam Topics
- ↑ Wikipedia: Data manipulation language
- ↑ Wikipedia: Insert (SQL)
- ↑ Wikipedia: Update (SQL)
- ↑ Wikipedia: Delete (SQL)
- ↑ Wikipedia: Truncate (SQL)
- ↑ Wikipedia: Merge (SQL)
- ↑ Wikipedia: Insert (SQL)
- ↑ Wikipedia: Update (SQL)
- ↑ Wikipedia: Delete (SQL)
- ↑ Wikipedia: Truncate (SQL)
- ↑ Wikipedia: Merge (SQL)