Oracle SQL

Oracle PL/SQL

Oracle Forms

Oracle Reports

Residential Courses

Prices

Location

Oracle SQL: DML and DDL
Computer based training course

Course ID: 61112

Course Overview

This course is the third in a three-part Oracle SQL series that is based on 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.

Learn To:

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

Content Emphasis

Skills-Based

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: Certified Database Administrator and Oracle Certified Application Developer, Release 2.

Total Learning Time

6 - 8 Hour(s)

Course Contents

Unit 1: Creating Tables and Constraints

Duration: 0.5 - 2 Hour(s)

• 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

Duration: 0.5 - 1 Hour(s)

• 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

Duration: 1 Hour(s)

• 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

Duration: 1 Hour(s)

• 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

Duration: 1 Hour(s)

• 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

Duration: 1 Hour(s)

• 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

Duration: 1 Hour(s)

• 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.

Anglesey has over 125 miles of coastline designated "areas of Natural Outstanding Beauty".

Tyn Y Felin is a mere five minutes away from the A55 North Wales Coast Expressway and is a perfectly situated coastal retreat on the serene and beautiful Isle of Anglesey.