Oracle 9i Certification Training
Introduction to Oracle 9i SQL Curriculum
Includes:
Oracle 9i Certification SQL Programming Training Curriculum for DBA Database Administrators on CD-ROMs
|
Intro. to Oracle Training 9i - SQL Curriculum CD
|
n61130d
|
$387.00
|
|
|
Database Admin. Fundamentals I CD
|
n64311d
|
$645.00
|
|
|
Database Admin. Fundamentals II CD
|
n64321d
|
$387.00
|
|
|
Oracle 9i Performance Tuning CD
|
n64331d
|
$387.00
|
|
|
Complete Oracle Training 9i CBT CD
includes SQL Curriculum, Database Admin. Fundamentals I & II
Curriculum and Oracle 9i Performance Tuning Curriculum
|
n61131d
|
$1100.00
|
|
Oracle Training 9i SQL: Basic SELECT Statements
 This course covers relational database principles and
Oracle concepts; writing basic SQL statements; restricting and sorting data; and using single-row functions. Additionally, this course provides an introduction to relational database concepts and the use of
SQL for storing, retrieving, and manipulating data in a relational database.
Learn To
• Identify the components of the relational model.
• Match the structural elements of a relational database table with their descriptions.
• Sequence the phases of the system development life cycle.
• Match the types of SQL statements with their descriptions.
• Match the capabilities of SQL SELECT statements with their functions.
• Identify the guidelines for constructing basic SQL SELECT statement.
• Write a query that restricts rows returned by using the WHERE clause.
• Write a WHERE clause to accommodate different datatypes.
• Use comparison operators with the WHERE clause.
• Identify the different categories of datatypes.
• Match the character datatypes with their descriptions.
• Identify the features of the NUMBER datatype.
Objectives
Unit 1: Relational Database Concepts 1 - 2 hours
• Identify the components of the relational model.
• Match the structural elements of a relational database table with their descriptions.
• Sequence the phases of the system development life cycle.
• Identify the three components of an entity relationship model.
• Match the entity relationship model components with their conventions.
• Match the components of Oracle9i with their functions.
• Identify the tools and languages used to communicate with the Oracle server.
• Match the services provided by Oracle9iAS with their functions.
• Match the tools in Oracle9iDS with their functions.
• Identify the features of Oracle9i.
• Identify the features of Oracle9i Database.
• Match the components of Oracle Internet Platform with their features.
• Identify the benefits of the Entity Relationship (ER) model.
Unit 2: Writing Basic SQL Statements 1 - 2 hours
• Match the capabilities of SQL SELECT statements with their functions.
• Identify the guidelines for constructing basic SQL SELECT statement.
• Use the SELECT statement to display rows and columns from a table.
• Write a SQL statement that includes an arithmetic expression.
• Assign an alias to a column heading when retrieving data from a table.
• Concatenate multiple columns that are selected from a table.
• Identify the uses of iSQL*Plus.
• Embed a literal character string in a SELECT clause to customize the output.
• Eliminate duplicate rows from the output of a query by using the DISTINCT keyword.
• Log on to SQL*Plus through a Windows environment.
• Display the structure of a table using the DESCRIBE command.
• Match the SQL*Plus edit commands with their functions.
• Match the SQL*Plus file commands with their functions.
• Match the types of SQL statements with their descriptions.
• Log on to the iSQL*Plus environment by using a browser.
• Match the options in the iSQL*Plus window with their functions.
• Display the structure of a table by using the DESCRIBE command.
• Save iSQL*Plus commands to Script files by using the Save Script button of the iSQL*Plus window.
• Load a previously saved iSQL*Plus command from a script file by using the Load Script button
of iSQL *Plus window.
• Save the output of iSQL*Plus commands to a script file by using the iSQL *Plus window.
• Identify the differences between iSQL*Plus commands and SQL statements.
Unit 3: Restricting and Sorting Data 2 hours
• Write a query that restricts rows returned by using the WHERE clause.
• Write a WHERE clause to accommodate different datatypes.
• Use comparison operators with the WHERE clause.
• Display rows based on 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 that contain NULL values by using the 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 the precedence rules when evaluating conditions that use comparison and logical operators.
• Sort rows retrieved in ascending order by using the ORDER BY clause.
• Sort rows retrieved in descending order by 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 2 hours
• Match the types of character functions with their uses.
• Display data in lowercase by using the LOWER function.
• Display the number of characters in a value by using the LENGTH function.
• Match the character datatypes with their descriptions.
• Display information about a character value by using SUBSTR and INSTR functions.
• Display values padded with characters using LPAD and RPAD functions.
• Display rounded values to a specified decimal place by using the ROUND function.
• Display truncated values to a specified decimal place by using the TRUNC function.
• Return the remainder of one value divided by another by using the MOD function.
• Identify the uses of the SYSDATE function.
• Perform arithmetic operations on dates.
• Display date information by using common SQL date functions.
• Match the explicit datatype conversion functions with their uses.
• Convert a date value to a character string for display by using the TO_CHAR function.
• Convert a number to a character string for display by using the TO_CHAR function.
• Identify functions used to convert characters to other datatypes.
• Convert a NULL value to an actual value for display by using the NVL function.
• Identify the features of the NUMBER datatype.
• Decode an expression by using the DECODE function.
• Evaluate single-row functions that are nested.
• Match the number functions with their uses.
• Identify the concepts related to time zones.
• Concatenate character values by using the CONCAT function.
• Remove characters or extra spaces from character strings by using the TRIM function.
• Search for and replace a text expression by using the REPLACE function.
• Return the current date and time in a session's time zone by using the CURRENT_DATE function.
• Return the current date, time, and time zone of a session by using the CURRENT_TIMESTAMP function.
• Return the current date and time in a session time zone by using the LOCALTIMESTAMP function.
• Return the database time zone by using the DBTIMEZONE function.
• Return the current session's time zone by using the SESSIONTIMEZONE function.
• Return a datetime field value from a datetime or an interval value expression by using the EXTRACT function.
• Convert a TIMESTAMP value to a TIMESTAMP WITH TIME ZONE value by using the FROM_TZ function.
• Convert a character value to a TIMESTAMP datatype by using the TO_TIMESTAMP function.
• Convert a character value to a TIMESTAMP WITH TIME ZONE value by using the TO_TIMESTAMP_TZ function.
• Convert a character value to an INTERVAL YEAR TO MONTH datatype by using the TO_YMINTERVAL function.
• Identify the datatypes used in implicit datatype conversion.
• Return an expression based on the evaluation of a NULL value by using the NVL2 function.
• Compare two expressions by using the NULLIF function.
• Compare multiple expressions by using the COALESCE function.
• Implement conditional processing by using the CASE expression.
• Identify the different categories of datatypes.
• Match the LOB datatypes with their functions.
• Match the datetime datatypes with their functions.
• Define the RR date format.
• Return the time zone offset by using the TZ_OFFSET function.
• Identify the types of SQL functions.
• Match the single-row function types with their features.
top
Oracle Training 9i SQL: Data Retrieval Techniques
This course covers joins, group functions, subqueries, and the iSQL*Plus report variables. Additionally, this course shows students how to access data from multiple table using joins,
aggregate data using group functions, create subqueries, and use iSQL*Plus commands to create customized queries.
Learn To
• Identify the four Oracle join types used to display data from more than one table.
• Identify a Cartesian product.
• Retrieve rows from two tables by using an equijoin.
• 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.
• Identify the requirements for using subqueries.
• Identify the characteristics of each subquery type.
• Write a single-row subquery to return one row in the WHERE clause of a SELECT statement.
• Identify the characteristics of substitution variables.
• Prompt the user for a number value at run time by using a substitution variable.
• Prompt the user for a character value at run time by using a substitution variable.
Objectives
Unit 1: Displaying Data from Multiple Tables 1 - 2 hours
• Identify guidelines for using the SET operators.
• Create a compound query that uses the UNION operator to combine data from two results sets.
• Create a compound query that uses the INTERSECT operator to combine data from two results sets.
• Create a compound query that uses the MINUS operator to combine data from two results sets.
• Identify the four Oracle 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 nonequijoin.
• Retrieve rows from two tables by using an outer join.
• Retrieve rows from within the same table by using a self join.
• Match the SQL: 1999 join types with their correct descriptions.
• Retrieve rows from two tables by using a natural join.
• Join data from two specific columns by using the USING clause.
• Retrieve rows within the same table by using the ON clause.
• Retrieve rows from two tables by using a left or right outer join.
• Match the SET operators used to create compound queries with their correct descriptions.
Unit 2: Group Functions 1 - 2 hours
• 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.
• Create a query that produces a subtotal by using the ROLLUP operator.
• Create a query that produces cross-tabulation values by using the CUBE operator.
• Identify the group or groups on which a subtotal is based by using the GROUPING function.
• Create multiple groups of data by using the GROUPING SETS function in the GROUP BY clause.
• Create a composite column in the GROUP BY clause of a SELECT statement.
• Create a concatenated grouping using the ROLLUP and CUBE operators
Unit 3: Subqueries 2 hours
• • Identify the requirements for using subqueries.
• Identify the characteristics of each subquery type.
• 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 requirements for using multiple-row subqueries.
• Write a multiple-row subquery using the IN operator.
• Identify how a query handles null values returned from a multiple-row subquery.
• Write a multiple-column subquery using the IN operator.
• Identify the different output that results from pairwise and nonpairwise subqueries.
• Write a multiple-column subquery in the FROM clause of a SELECT statement.
• Write a scalar subquery in the ORDER BY clause of a SELECT statement.
• Write a correlated subquery in the WHERE clause of a SELECT statement.
• Write a correlated subquery using the EXISTS operator.
• Write a correlated subquery using the WITH clause
Unit 4: Reporting with iSQL*Plus 2 hours
• Identify the characteristics of substitution variables.
• Prompt the user for a number value at run time by using a substitution variable.
• Prompt the user for a character value at run time by using a substitution variable.
• Prompt the user for a date value at run time by using a substitution variable.
• Prompt the user for column names and expressions at run time using substitution variables.
• Prompt the user at run time for a value by using a substitution variable prefixed with a double ampersand.
• Maintain a variable at run time by using the DEFINE and UNDEFINE commands.
• Match the SET command variables with their functions.
• Match the iSQL*Plus format commands with their functions.
• Control the display of a column by using the COLUMN command.
• Group related rows by using the BREAK command to suppress duplicate values.
• Format page headers and footers by using the TTITLE and BTITLE format commands.
• Run a formatted report by using an iSQL*Plus script file
top
Oracle Training 9i 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.
Learn To
• 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.
Objectives
Unit 1: Creating Tables and Constraints 0.5 - 1 hour
• 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 0.5 - 1 hour
• 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 0.5 - 1 hour
• 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 0.5 - 1 hour
• 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 1 hour
• 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 1 hour
• 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 1 hour
• 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 1 hour
• 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.
top
|
