Oracle9i SQL: DML and DDL

The Oracle SQL DML and DDL course is the third in a three-part series covering the Data Manipulation and Data Definition language statements supported by Oracle9i. This course introduces the participants to various objects in a database. The participants learn to create update and delete the database objects. The participants also learn to add rows update and delete existing rows from a table. The course also explains the use of external tables and how to create the external tables.

This is primarily online training This is an online eLearning or CBT training program Contact Serebra Learning Corporation for more information

Duration: 8 hours Training Presented in: English

Audience

The intended audience for this course are Database Administrators. Required prerequisite knowledge includes Oracle9i SQL: Basic SELECT Statements 61130 and Oracle9i SQL: Data Retrieval Techniques 61131.

Objective

• Identify the data structures in an Oracle server.
• Identify the rules for naming tables in a database.
• Identify the DML and transaction control statements.
• Insert rows in a table by using the INSERT statement.
• Add a column to a table in an Oracle database.
• Change the table structure by modifying the characteristics of an existing column.
• Identify the features of an Oracle sequence.
• Create a sequence by using the CREATE SEQUENCE statement.
• Identify the properties of views.
• Match the clauses of the CREATE VIEW statement with their functions.
• Create a public synonym by using the CREATE PUBLIC SYNONYM statement.
• Remove a synonym by using the DROP PUBLIC SYNONYM statement.
• Identify the features of Oracle database security.
• Create users in an Oracle database.
• Match the types of multitable INSERT statements with their uses.
• Insert rows into multiple tables by using the INSERT INTO statement unconditionally.

Topics Include

Unit 1: Creating Tables and Constraints

• Identify the data structures in an Oracle server.
• Identify the rules for naming tables in a database.
• Sequence the steps to create a table.
• Identify the rules for referencing a table in another user's schema.
• Match the Oracle datatypes with their definitions.
• Identify the properties of constraints.
• Match the constraint types with their definitions.
• Identify the characteristics of the different levels of constraints.
• Sequence the steps performed by the Oracle server during a primary key lookup with a foreign key value insert.
• Identify the features of the data dictionary.
• Create a table by using the CREATE TABLE statement.
• Create a table based on an existing table.
• Confirm the table that you created.
• View details of the tables created and owned by you by using the USER_CATALOG data dictionary table.
• Define the NOT NULL constraint by using the CONSTRAINT keyword.
• Define the UNIQUE constraint by using the CONSTRAINT keyword.
• Define the PRIMARY KEY constraint by using the CONSTRAINT keyword.
• Define the FOREIGN KEY constraint by using the CONSTRAINT keyword.
• Define a CHECK constraint by using the CONSTRAINT keyword.

Unit 2: Manipulating Data

• Identify the DML and transaction control statements.
• Insert rows in a table by using the INSERT statement.
• Insert special values into existing tables by using the INSERT INTO statement.
• Add rows to an existing table based on values from another table.
• Use the DEFAULT keyword in an INSERT statement.
• Restrict the rows added by the INSERT command by using the WITH CHECK OPTION clause in the subquery.
• Update existing rows in a table by using the UPDATE statement.
• Update all rows in a table by using the UPDATE statement.
• Modify values in a table based on values from another table by using a subquery.
• Update a table based on values from another table by using correlated subqueries.
• Delete rows from a table by using the DELETE statement.
• Delete rows from a table based on values from another table by using a subquery.
• Delete rows from a table by using correlated subqueries.
• Identify the causes that begin and end a transaction.
• Identify the SQL statements for controlling transactions.
• Identify the state of the data before and after a COMMIT operation.
• Use the ROLLBACK statement to discard pending changes in a transaction.
• Create a savepoint and use it as a marker.
• Identify the features of read consistency implemented by the Oracle server.
• Match the locking mechanisms with their features.
• Conditionally update and insert rows by using the MERGE command.

Unit 3: Altering Tables and Constraints

• Add a column to a table in an Oracle database.
• Change the table structure by modifying the characteristics of an existing column.
• Drop an existing column by using the DROP COLUMN clause in the ALTER TABLE statement.
• Add a constraint to an existing column by using the ALTER TABLE statement.
• Identify the information displayed by the data dictionary views.
• Identify the guideline to follow when dropping a PRIMARY KEY constraint.
• Manage existing constraints using the DISABLE and ENABLE keywords.
• Drop a table by using the DROP TABLE statement.
• Rename an existing table by using the RENAME statement.
• Remove all rows from a table by using the TRUNCATE TABLE statement.
• Add comments for a table in the data dictionary by using the COMMENT statement.

Unit 4: Implementing Sequences

• Identify the features of an Oracle sequence.
• Create a sequence by using the CREATE SEQUENCE statement.
• View information on sequences by using the USER_SEQUENCES data dictionary view.
• Use the NEXTVAL psuedocolmn to add values to rows.
• Identify the features of a cached sequence.
• Modify a sequence by using the ALTER SEQUENCE statement.
• Remove a sequence by using the DROP SEQUENCE statement.

Unit 5: Implementing Views

• Identify the properties of views.
• Match the clauses of the CREATE VIEW statement with their functions.
• Create simple views by using the CREATE VIEW statement.
• Create views based on two tables by using the CREATE VIEW statement.
• Drop a view by using the DROP VIEW statement.
• Add the primary key constraint to a view by using the CREATE VIEW statement.
• Add the UNIQUE constraint to an existing view by using the ALTER VIEW statement with the ADD CONSTRAINT clause.
• Identify the restrictions on implementing constraints on views.
• Identify the rules that restrict DML operations on views.
• Create a view by using the WITH CHECK OPTION clause.
• Create views that prevent DML operations on the base table.
• Display information on views by using the data dictionary.

Unit 6: Implementing Synonyms and Indexes

• Create a public synonym by using the CREATE PUBLIC SYNONYM statement.
• Remove a synonym by using the DROP PUBLIC SYNONYM statement.
• Identify the characteristics of indexes.
• Match the types of indexes with their use.
• Create new indexes by using the CREATE INDEX statement.
• Create an index on the primary key by using the CREATE INDEX clause in the CREATE TABLE statement.
• Create a function-based index by using the UPPER function.
• Display data dictionary information about indexes created by you.
• Remove an existing index by using the DROP INDEX statement.
• Drop a primary key constraint while retaining the index by using the KEEP INDEX clause in the ALTER TABLE statement.

Unit 7: Controlling User Access

• Identify the features of Oracle database security.
• Create users in an Oracle database.
• Change the user password by using the ALTER USER statement.
• Grant a system privilege to a user.
• Create roles by using the CREATE ROLE statement.
• Grant object privileges by using the GRANT statement.
• Grant the WITH GRANT OPTION privilege to users.
• Match the data dictionary views with their description.
• Revoke privileges from users.
• Identify the properties of a database link.
• Create a public database link by using the CREATE PUBLIC DATABASE LINK statement.

Unit 8: Advanced DDL and DML Statements

• Match the types of multitable INSERT statements with their uses.
• Insert rows into multiple tables by using the INSERT INTO statement unconditionally.
• Insert rows conditionally in two tables by using the WHEN clause in the INSERT statement.
• Insert data into two tables by using the FIRST clause in the INSERT statement.
• Insert a single row as multiple rows in a table by using the INSERT INTO statement.
• Match the clauses used for creating an external table with their uses.
• Create an external table by using the ORGANIZATION EXTERNAL clause of the CREATE TABLE statement.
• Add rows to an existing table by using an external table in the INSERT INTO statement.
• Query an external table by using the SELECT statement.

Duration

8

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media

CDROM

• Oracle9i SQL: Basic SELECT Statements
• Oracle9i SQL: Data Retrieval Techniques