Oracle 8i Exam 1
Introduction to Oracle: SQL and PL/SQL - Exam 1, 1Z0-001
Back to Oracle 8i Certification Training Admin.
|
Oracle® 8 Certified Database Administrator CBT Exams 1Z0-001, 013, 014, 015, 016.
|
n61314D
|
$1375.00
|
|
|
Oracle8i Certified Database Administrator Curriculum CBT Exams 1zo-001, 023, 024, 025, 026.
|
n63312D
|
$1375.00
|
|
|
Oracle Certified Application Developer CBT
|
n61222D
|
$1750.00
|
|
Oracle 8i Certification Training Database Administrator Curriculum Details
 Oracle SQL and PL/SQL: Basic SELECT
This course introduces the learner to Oracle relational database concepts and the use of SQL for storing, retrieving, and manipulating data in a relational database. This course
covers relational database principles and Oracle concepts; writing basic SQL statements; restricting and sorting data; and using single-row functions.
Learn To
Identify relational database concepts and properties. Describe database design processes by explaining the system development life cycle and entity relationship modeling. Describe the
concept of object relational database management systems. Write SQL SELECT queries. Customize SQL queries when selecting data from a table. Identify SQL*Plus environment,
features, and commands. Restrict rows retrieved by a query. Identify comparison operators that can be used in defining row retrieval restrictions. Identify logical operators that can be
used to define row retrieval restrictions. Sort rows retrieved by a query. Describe the types of SQL funcitons, in order to define single row functions. Demonstrate the use of four character
functions; LOWER, UPPER, INITCAP, and LENGH. Demonstrate the use of three number functions; ROUND, TRUNC, and MOD. Demonstrate the use of data functions. Demonstrate the use of data type conversion functions.
61110 - Oracle SQL and PL/SQL: Basic SELECT
Objectives
Unit 1: Relational Database Concepts
• Define components of the relational model.
• Identify the structural elements of a relational database.
• List the properties of a relational database.
• Identify the phases of the system development life cycle.
• Identify the three components of an entity relationship model.
• Indicate the diagramming conventions used in entity relationship modeling.
• List characteristics of object relational database management systems.
• Identify the benefits of Oracle's implementation of object relational technology.
• Define the tools used to interact with the Oracle server.
• Identify the types of SQL statements.
Unit 2: Writing Basic SQL 1
• List capabilities of SQL SELECT statements.
• Identify the mandatory elements of a SELECT statement.
• Identify methods for executing SELECT statements.
• Write a SELECT statement that displays all columns and rows of a table.
• Write a SELECT statement that displays specific columns from a table.
• Identify precedence in evaluating an arithmetic expression within a SQL statement.
• Specify an alias for a column heading when extracting data from a table.
• Concatenate multiple columns that are selected from a table.
• Embed a literal character string in a SELECT clause to customize the output displayed.
• Use the DISTINCT keyword to eliminate duplicate rows from the output of a query.
• Manage null values using the NVL function in a SELECT statement.
• Identify the differences between SQL*Plus commands and SQL statements.
• Log in to SQL*Plus through a Window environment.
• Display the structure of a table using the DESCRIBE command.
• Identify SQL*Plus commands used to edit SQL statements.
• Identify SQL*Plus commands used to manage the contents of the SQL buffer.
Unit 3: Restricting and Sorting Data: Write a query that restricts rows returned by using the WHERE clause
• Identify guidelines for specifying literal strings in a WHERE clause.
• Compare one expression to another within a WHERE clause.
• Display rows within a range of values by using the BETWEEN operator.
• Display rows that match a list of values by using the IN operator.
• Perform wildcard searches by using the LIKE operator.
• Display rows containing null values by using IS NULL operator.
• Write a combined expression by using the AND operator in a WHERE clause.
• Write a combined expression by using the OR operator in a WHERE clause.
• Restrict rows by using the NOT operator in a WHERE clause.
• Identify precedence rules when evaluating conditions that use comparison and logical operators.
• Sort rows retrieved in ascending order using the ORDER BY clause.
• Sort rows retrieved in descending order using the DESC keyword in the ORDER BY clause.
• Sort rows retrieved by using a column alias in the ORDER BY clause.
• Sort rows retrieved by more than one column.
Unit 4: Single-Row Functions
• Identify features of a SQL function.
• Identify the two types of SQL functions.
• Identify the features of a single-row function.
• Convert alpha characters to lowercase by using the LOWER function.
• Convert alpha characters to uppercase by using the UPPER function.
• Capitalize the first letter of each work in a character value by using the INITCAP function.
• Display the number of characters in a value by using the LENGTH function.
• Round a value to a specified decimal place by using the Round function.
• Truncate a value to a specified decimal place by using the TRUNIC function.
• Return the remainder of one value divided by another by using the MOD function.
• Identify the common Oracle date formats.
• Select the current date by using the SYSDATE function.
• Perform arithmetic operations on dates.
• Identify common SQL date functions.
• Identify common data type conversion functions.
• Convert a date value to a character string by using the TO_CHAR function.
• Convert a number to a character string by using the TO_CHAR function.
• Evaluate single row functions that are nested.
Back to Oracle 8i Certification Training Admin
Oracle SQL and PL/SQL: Data Retrieval Techniques
This course describes how to access data from more than one table using joins and aggregating data using group functions. This course also describes how to embed a SQL
statement in a clause of another SQL statement to enhance the power of SQL queries. In addition, how to use SQL*Plus commands to build on basic SQL statements and create
customized queries that retrieve the information and display it in a format specified by the user will be covered.
Learn To
Retrieve rows from multiple tables by using an equijoin, a non equijoin, an outer join, and a self join. Write SQL statements that contain common group functions. Restrict groups of rows
retrieved by using the HAVING clause and to write SQL statements that contain common group functions. Group rows retrieved by using the GROUP BY clause. Write single row,
multiple row, multiple column subqueries. Obtain values at run time by creating and maintaining substitution variables. Use SQL*Plus format commands to format report output.
Run a formatted report using a SQL*Plus script file.
61111 - Oracle SQL and PL/SQL: Data Retrieval Techniques
Objectives
Unit 1: Displaying Data From MultipleTables
• Identify the four join types used to display data from more than one table.
• Identify a Cartesian product.
• Retrieve rows from two tables by using an equijoin.
• Specify additional search conditions in an equijoin by using the AND operator in the WHERE clause.
• Simplify a query by using a table alias in an equijoin.
• Retrieve rows from more than two tables by using an equijoin.
• Retrieve rows from two tables by using a non-equijoin.
• Retrieve rows from two tables by using an outer join.
• Retrieve rows within the same table by using a self join.
Unit 2: Group Functions
• Identify the features of a group function.
• Write SQL statements that contain common group functions.
• Manage null values when using group functions in a SQL statement.
• Group rows retrieved by using the GROUP BY clause.
• Retrieve a subgroup of rows from within a larger group retrieved by using the GROUP BY clause.
• Restrict groups of rows retrieved by using the HAVING clause.
• Identify illegal queries involving group functions.
• Write a SQL statement that contains nested group functions.
Unit 3: Subqueries
• Identify the requirements for using subqueries.
• Identify the characteristics of three subquery types.
• Write a single-row subquery to return one row in the WHERE clause of a SELECT statement.
• Retrieve data that is dependent upon the value of a group function in a single-row subquery.
• Write a single-row subquery in the HAVING clause of a SELECT statement.
• Identify two common errors that occur when using single-row subqueries.
• Identify the guidelines for using multiple-row subqueries.
• Write a multiple-row subquery.
• Identify how a query handles null values returned from a multiple-row subquery.
• Write a multiple-column subquery.
• Identify the different output that results from pairwise and nonpairwise subqueries.
• Write a multiple-column subquery in the FROM clause of a SELECT statement.
Unit 4: Reporting with SQL*Plus 2 hours
• Identify the characteristics of substitution variables.
• Prompt the user for a number value using a variable prefixed with a single ampersand.
• Prompt the user for character and date values using a variable prefixed with a single ampersand and surrounded by single quotation marks.
• Specify column names and expressions at run time using substitution variables.
• Prompt the user for a value using a variable prefixed with a double ampersand.
• Accept user input at run time using the ACCEPT command.
• Maintain a variable at run time using the DEFINE and UNDEFINE commands.
• Match the SET command variables with their functions.
• Identify the guidelines for using the SQL*Plus format commands.
• Control the display of a column using the COLUMN command options.
• Group related rows using the BREAK command.
• Format page headers and footers using the TTITLE and BTITLE format commands.
• Run a formatted report using a SQL*Plus script file.
Back to Oracle 8i Certification Training Admin
Oracle SQL and PL/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.
Learn To
Create tables and integrity constraints. Manipulate data using DML. Alter tables and constraints. Create sequences. Create views. Create indexes. Manage system privileges.
61112 - Oracle SQL and PL/SQL: DML and DDL
Objectives
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.
Back to Oracle 8i Certification Training Admin
Oracle PL/SQL Basics
This course introduces the learner to the basics of PL/SQL. The course builds the necessary skills for using Oracle PL/SQL. It will help the learner acquire the expertise to design,
develop, and implement Oracle Server applications by using Oracle Structured Query Language (SQL) and PL/SQL. The learner is guided step-by-step through the various aspects
of PL/SQL programming. Numerous easy-to-understand examples ensure that the concepts are amply illustrated.
Learn To
Identify the features and benefits of PL/SQL and understand the PL/SQL program constructs. Identify the PL/SQL datatypes Identify how to declare PL/SQL variables and identify the
PL/SQL programming conventions. Retrieve and manipulate data from the database by using PL/SQL commands Identify features and attributes of SQL cursors, and manage
transactions. Use the IF THEN ELSE and IF THEN ELSIF conditional constructs and the Basic loop, FOR LOOP, and WHILE LOOP looping constructs to control flow in PL/SQL
code. Declare and reference PL/SQL records and PL/SQL tables Identify the types and features of LOB datatypes. Understand cursors, how to operate explicit cursors, work with
explicit cursor attributes Use the advanced functionality of explicit cursors. Understand the basic concepts of exceptions, trap predefined, nonpredefined, and user defined exceptions
Understand how exceptions are propagated.
61113 - Oracle PL/SQL: Basics
Objectives
Unit 1: PL/SQL: An Introduction
• Identify the features of PL/SQL.
• Identify the benefits of PL/SQL.
• Select the actions by which a PL/SQL engine processes a block of code.
• Identify the features of the sections of a basic PL/SQL block.
• Match the PL/SQL program constructs with their features.
• Identify the syntax rules to be applied in creating a PL/SQL block.
Unit 2: Developing a Simple PL/SQL Block
• Identify the functions of PL/SQL variables.
• Identify the guidelines for declaring PL/SQL variables.
• Match the PL/SQL datatypes with their descriptions.
• Match the scalar datatypes with their descriptions.
• Declare a scalar variable.
• Declare a scalar variable with the %TYPE attribute.
• Write the code to assign a value a to a variable by using the assignment operator.
• Use operators with the PL/SQL variables.
• Manipulate data in PL/SQL variables by using SQL functions.
• Convert the datatype of a variable by using a data conversion function.
• Identify the features of variable scoping in nested PL/SQL blocks.
• Create a bind variable in the SQL*Plus environment.
• Identify the case conventions for writing PL/SQL code.
• Match the identifiers with their naming conventions.
Unit 3: Accessing the Database Using PL/SQL
• Retrieve data from a table by using the SELECT statement..INTO statement.
• Identify the conditions in which various SELECT exceptions are raised.
• Write the code to add rows to a table by using the INSERT statement.
• Write the code to modify the existing data in a table by using the UPDATE statement.
• Write the code to delete data from a table by using the DELETE statement.
• Identify the features of a SQL cursor.
• Match the SQL cursor attributes with their descriptions.
• Write the code to confirm the current transaction by using the COMMIT command.
• Write the code to discard the changes made to the table by using the ROLLBACK command.
• Write the code to control the transaction at the intermediate point by using the SAVEPOINT command.
Unit 4: Controlling Flow in PL/SQL Blocks
• Write PL/SQL code using the IF-THEN-ELSE statement.
• Write PL/SQL code using the IF-THEN-ELSIF statement.
• Match a condition that uses logical operators with its result.
• Write the code for a basic loop to insert records into a table by using the LOOP keyword.
• Write the code to execute a set of statements repeatedly by using the FOR LOOP keyword.
• Write the code to execute a set of statements repeatedly by using the WHILE LOOP.
• Identify the features of a nested loop.
Unit 5: Composite Datatypes
• Declare a PL/SQL record by using the RECORD datatype.
• Reference a PL/SQL record.
• Declare PL/SQL records with %ROWTYPE.
• Declare a PL/SQL table by using the TABLE datatype.
• Reference a PL/SQL table.
• Match the methods used to operate on PL/SQL tables with their descriptions.
• Identify the syntax to reference a table of records.
• Match the LOB datatypes with their descriptions.
• Identify the features of LOB variables in PL/SQL.
Unit 6: Explicit Cursors
• Identify the declaration methods of cursors, both implicit and explicit.
• Sequence the steps for controlling explicit cursors.
• Write the code to declare a cursor by using the CURSOR statement.
• Write the code to open a cursor by using the OPEN statement.
• Retrieve rows from a cursor by using the FETCH statement.
• Close a cursor by using the CLOSE statement.
• Check the status of a cursor by using the %ISOPEN attribute.
• Check the status of a cursor by using the %FOUND attribute.
• Check the status of a cursor by using the %NOTFOUND attribute.
• Write the code to fetch a specified number of rows from a cursor by using the %ROWCOUNT attribute.
• Write the code to process the rows of the active set conventionally by fetching values into a PL/SQL record.
• Write the code to process rows in an explicit cursor using cursor FOR loops.
• Pass parameters to a cursor when a cursor is opened by using the cursor_name parameter.
• Lock the records by using the FOR UPDATE clause.
• Write the code to update the latest fetched row by using the WHERE CURRENT OF clause.
Unit 7: Handling Exceptions
• Match the types of exceptions with their properties.
• Match some common predefined Oracle server exceptions with their descriptions.
• Identify the guidelines to trap exceptions.
• Complete the code to trap predefined exceptions.
• Complete the code to trap nonpredefined exceptions.
• Complete the code to trap user-defined exceptions.
• Match the functions for identifying the associated error message or error code with their descriptions.
• Match each calling environment with its error-handling method.
• Use the RAISE_APPLICATION_ERROR procedure to raise user-defined error codes and messages.
Back to Oracle 8i Certification Training Admin
|
