Oracle SQL: DML and DDL

This course is the third in a three-part Oracle SQL series that is based on the Oracle ILT course Introduction to Oracle: SQL and PL/SQL. Using the Oracle SQL*Plus environment this computer-based training course uses Structured Query Language (SQL) to create and populate Oracle database tables. Learners create tables and other database objects and learn to maintain and modify these data objects. Learners also learn how to insert update and delete data using SQL's Data Manipulation Language and learn to control database transactions. Finally the course teaches how to control both user and object level security in an Oracle database.

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

Application Developers Database Administrators Designer/Developers System Analysts and Technical Support Professionals. Prior to taking this course participants should be able to use a graphical user interface (GUI) and should be familiar with data processing concepts and techniques. Some prior knowledge of programming languages would be an advantage. In addition participants should have taken the first two courses in this series Oracle SQL: Basic SELECT Statements (course 61110) and Oracle SQL: Data Retrieval Techniques (61111). This course will help candidates prepare for test 1Z0-001 which applies towards the following Oracle Certified Professional (OCP) Program certification tracks: Oracle7.3 Certified Database Administrator Oracle8 Certified Database Administrator and Oracle Certified Application Developer Release 2.

Objective

• Create tables and integrity constraints.
• Manipulate data using DML.
• Alter tables and constraints.
• Create sequences.
• Create views.
• Create indexes.
• Manage system privileges.

Topics Include

Unit 1: Creating Tables and Constraints

• Identify the main database objects and their structures.
• Reference the tables of another user when appropriate permission has been granted.
• Name database objects according the Oracle naming conventions and guidelines.
• Identify the syntax of the CREATE TABLE statement.
• Identify the use of constraints as a means of enforcing data integrity.
• Prohibit null values using the NOT NULL constraint.
• Define a unique constraint for a column to ensure that two rows do not contain the same value.
• Define a PRIMARY KEY constraint using the CREATE TABLE statement.
• Create a referential integrity constraint for a foreign key column.
• Define a check constraint to ensure that appropriate values are stored in the table.
• Create a table within the SQL*Plus environment.
• Create a table using the rows from an existing table.
• Display the structure of a table using the DESCRIBE command.

Unit 2: Manipulating Data

• Identify the DML functions and the transaction control commands.
• Insert new rows in a table.
• Insert specific date time and user values in a table.
• Insert rows from one table into another table.
• Update a row without violating the integrity constraints for that table.
• Update an entire column in a database table.
• Delete rows from a table.
• Identify the actions that start and end a transaction.
• Execute the COMMIT SAVEPOINT and ROLLBACK statements.
• Roll back pending data changes.
• Rollback pending changes to a specified savepoint.

Unit 3: Altering Tables and Constraints

• Add a new column to an Oracle database table by using the ALTER TABLE statement.
• Modify the columns of an Oracle8 table by using the ALTER TABLE statement.
• Add a constraint to an Oracle table by using the ALTER TABLE statement.
• Remove a constraint from a table by using ALTER TABLE statement.
• Manage constraints by using the DISABLE and ENABLE clauses.
• Remove a table from an Oracle database.
• Rename a table in an Oracle database.
• Remove all rows from a table without generating rollback information.
• Manage comments on a table or column in an Oracle database by using the COMMENT clause.

Unit 4: Creating Sequences

• Identify the features of a sequence.
• Create a sequence by using an SQL statement.
• Display sequence values from a data dictionary table.
• Identify the rules of using NEXTVAL and CURRVAL.
• Modify a sequence using the ALTER SEQUENCE statement.
• Remove a sequence using the DROP SEQUENCE statement.

Unit 5: Creating Views

• Identify the advantages of creating views.
• Identify CREATE VIEW statement syntax used to create a view in an Oracle database.
• Create a simple view.
• Create a complex view to display values from two tables.
• Remove a view from an Oracle database.
• Identify the rules for performing DML operations on views in an Oracle database.
• Restrict the use of DML operations on a base table by using the WITH CHECK OPTION clause.
• Prevent the use of DML operations on a view by using the WITH READ ONLY clause.
• Display details about a user's views by selecting data from the data dictionary.

Unit 6: Creating Indexes

• Identify the characteristics of an index.
• Identify the different index types.
• Create an index.
• Display the indexes in the data dictionary view.
• Drop an index.

Unit 7: Controlling User Access

• Identify the features of database security.
• Create a user with an identifying password.
• Change the password of a user by using the ALTER USER statement.
• Grant privileges to a user by using the GRANT statement.
• Grant privileges to a role.
• Grant object privileges to a user by using the GRANT statement.
• Grant object privileges to a user by using the option WITH GRANT OPTION.
• Identify the seven privilege-related views in the data dictionary.
• Revoke the privileges granted to a user by using the REVOKE statement.
• Create a synonym for a table using the CREATE SYNONYM statement.
• Remove a synonym by using the DROP SYNONYM 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

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