Oracle9i SQL Server

Total Items: 11 Total Time: 24.00 hour(s)  

Summary:

This training is designed to help those who have some experience with relational databases to prepare for the Oracle9i 1Z0-007 exam.  

This series is for anyone who is interested in learning more about      

Learning Segment 1 : Introduction to SQL    

Summary:

This learning segment provides an introduction to the Oracle9i relational database management system and basic database concepts.  

Objectives:

• Understand basic database concepts
• List new SQL features in Oracle9i
• Use the four available SQL Plus tools
• Enter and execute basic queries using the SELECT command  

Topics:

• Basic database concepts
• SQL Plus command lineSQL Plus window
• SQL Plus worksheeti
• SQL Plus
• Using the SELECT statement
• Writing SQL commands
• Writing queries    

 Learning Segment 2 : Intermediate Queries    

Summary:

This learning segment provides information about using the SELECT statement and using the WHERE and ORDER BY clauses in Oracle9i SQL.  

Objectives:

• Use the WHERE clause to filter out rows from query results
• Format query results
• Use multiple filters in a query
• Query the DUAL table
• Sort query results using the ORDER BY clause
• Handle null values in query results  

Topics:

• Using the WHERE clause
• Formatting query results
• Using multiple filters
• Dual and pseudocolumns
• Using the ORDER BY claus
• eSorting rules for null values    

 Learning Segment 3 : Queries with Single-Row Functions    

Summary:

This learning segment examines the types and uses of functions available for various data types in Oracle9i SQL.  

Objectives:

• Describe the most commonly-used functions available in SQL
• Use character, number, and date functions in SELECT statements
• Use conversion functions
• Find online documentation for more information about functions as needed  

Topics:

• String functions
• Number functions
• Date functions
• Miscellaneous functions
• Conversion functions
• Combining functions    

Learning Segment 4 : Combining Tables    

Summary:

This learning segment examines joining tables in Oracle9i SQL using the ANSI format JOIN clause and the Oracle proprietary format.  

Objectives:

• Write SELECT statements to access data from more than one table using equality and nonequality joins
• View data that generally does not meet a join condition by using outer joins
• Join a table to itself using a self join
• Create various types of join queries
• Identify the differences between Oracle proprietary format and ANSI format for joining tables  

Topics:

• Joining tables
• Cross joins
• Natural joins
• The USING and the ON clause
• Outer joins
• Left outer joins
• Right outer joinsSelf joins
• Other types of joins    

Learning Segment 5 : Grouping and Summarizing Data    

Summary:

This learning segment examines the summarizing of query results into groups using the GROUP BY clause in Oracle9i SQL.  

Objectives:

• Explain how group functions work
• Use aggregate functions
• Use DISTINCT and ALL clauses
• Group and sort rows using the GROUP BY clause
• Use the HAVING clause to restrict dataUse the ROLLUP, CUBE, and GROUPING SETS clauses  

Topics:

• The GROUP BY clause and group functions
• Aggregate functions
• Grouping rows and sorting with GROUP BY
• Restricting DataExtending the GROUP BY clause    

Learning Segment 6 : Advanced Queries and Views    

Summary:

This learning segment provides an in-depth look at using subqueries and views in Oracle9i SQL.  

Objectives:

• List the types of subqueries
• Use subqueries in Oracle SQL commands
• Add and drop views
• Change views
• Modify data using views  

Topics:

• Subqueries
• Types and uses of views
• Simple, constraint and complex views
• Changing and dropping views
• Working with views
• Inserting, updating and deleting in views    

Learning Segment 7 : Working with Data    

Summary:

This learning segment examines the four main commands used in SQL to add, modify, remove, and merge rows of data in database tables.  

Objectives:

• Distinguish between Oracle9i and user-defined datatypes
• Use the four Oracle9i DML commands INSERT, UPDATE, DELETE, and MERGE
• Understand transactions and the commands used to manipulate their behavior  

Topics:

• Datatypes
• Data Manipulation Language
• Using transactions
• Adding and changing data
• Deleting and merging data    

Learning Segment 8 : Creating Tables and Constraints    

Summary:

This learning segment examines creating and changing tables and defining constraints on tables in Oracle9i SQL.  

Objectives:

• Create, modify, rebuild, and remove tables
• Use the TRUNCATE command
• Create, modify and drop constraints
• Use primary key constraints
• Use foreign key contraints
• Add comments to SQL scripts and schema objects  

Topics:

• Creating tables
• Relational and temporary tables
• Adding comments
• Changing table structures
• Renaming, truncating and dropping tables
• Constraints
• Primary key, unique and foreign key constraints
• Table level and check constraints
• Adding, modifying and dropping constraints    

Learning Segment 9 : Database Objects and User Security    

Summary:

This learning segment examines how to create database objects including sequences, synonyms, and indexes in Oracle9i SQL. It also covers sharing table data with other users through privileges and roles.  

Objectives:

• Create, modify, and delete sequences
• Create, modify, and delete synonyms
• Create, modify, and delete indexes
• Create, modify, and delete users
• Grant and revoke system and object privileges
• Create, grant, revoke, enable, disable, and delete roles  

Topics:

• Working with sequences
• Working with synonyms
• Understanding indexes
• Working with indexes
• Understanding users
• Working with users
• Granting privileges
• Revoking privileges
• Creating, granting, and revoking roles
• Enabling, disabling, and dropping roles    

Learning Segment 10 : SQL*Plus And iSQL*Plus Reporting and PL/SQL    

Summary:

This learning segment examines how to use environment settings, variables, and special SQL Plus commands to generate professional looking reports in Oracle9i SQL.  

Objectives:

• Create a well formatted report with SQL Plus
• Know when and how to use variables
• Use iSQL Plus
• Use Top-N Analysis
• Identify the basic concepts of PL/SQL  

Topics:

• SQL Plus reports
• Environment settings
• Using environment settings
• Variables and scripts
• Generating reports
• Reporting features
• Running reports
• Top-N queriesPL/SQLRetrieving data in PL/SQL
• Dynamic SQL
• Control structures
• Iteration and repetition    

Learning Segment 11 : Practice Exam    

Summary:

This learning segment provides questions to prepare users who plan to take exam 1Z0-007: Introduction to Oracle9i: SQL.  

Objectives:

• Answer practice questions in the format of the 1Z0-007 Introduction to Oracle 9i SQL exam
• Assess readiness to take the exam and identify topic areas requiring further practice  

Topics:

• SQL Plus reportsWriting basic SQL select statements
• Restricting and sorting data
• Single-row functions
• Displaying data from multiple tables
• Aggregating data using group functions
• Subqueries
• Producing readable output with iSQL Plus
• Manipulating data
• Creating and managing tables
• Including constraints
• Creating views
• Creating other database objectsControlling user access

Features:

• Flash and audio provide animated, interactive Lesson Introductions, Activities, and Simulations.
• Simulations teach learners to perform specific tasks in applications through guided, multi-step exercises.
• Activities allow learners to apply course concepts in an interactive questioning environment.
• Exercises allow learners to practice in the actual application being studied.
• Supplied sample files include sample documents, application files, programs, and programming code that enable learners to practice with these files, enhancing the learning experience.
• A Course Topics list contains active hyperlinks, permitting quick access to specific topics.
• Find-A-Word allows learners to look up an unfamiliar term in the Glossary, on the Web, or in a dictionary. In addition, it lets them find other occurrences of the term in the same course.
• Search text enables learners to rapidly search all text within a course to easily retrieve information required.
• Courses challenge the learner with a variety of question formats, including multi-step simulations, true/false, multiple choice, and fill-in-the-blank.
• A skill assessment generates a customized learning path based on the results of a pre-test.
• A glossary provides a reference for definitions of unfamiliar terms.
•  Bookmarking tracks the learner's progress in a course.  

Technical Requirements:

P500+ Processor, 128MB of RAM; Windows 2000, 2003, XP, Minimum screen resolution 800x600, Internet Explorer 5.5 or higher; Windows Media Player 9.0 or higher; Flash 8.0 or higher; 56K minimum connection; broadband (256 kpbs or higher) connection recommended; Cookies enabled; Sound card with speakers or headphones strongly recommended.