The Introduction to Oracle9i: SQL Curriculum includes the following courses:

Oracle9i SQL: Basic SELECT Statements

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.

Audience

The target audience for this course includes database administrators and application developers. This course will help students prepare for the Oracle9i DBA certification, and is mapped to Exam 1Z0-007.

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

Oracle9i SQL: Data Retrieval Techniques

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.

Audience

The audience includes database administrators, designers, developers, technical support professionals, and application developers. This course will help students prepare for the Oracle9i DBA certification, and is mapped to Exam 1Z0-007.

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

Oracle9i SQL: DML and DDL

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.

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.

Total Learning Time

6 to 8 hours

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.

Regular price: $699.95Sale price: $599.95

Click here for Secure Yahoo Order: Introduction to Oracle9i: SQL Curriculum

Get Train in Oracle
Oracle Training CD

Best Regards,
Oracle Database, SQL, Application, Programming Tips