Oracle Training Overview
This class is designed specifically for the needs of end-users, testers, technical support and other personnel supporting or working with an Oracle database. You will benefit from this class if you but need to interact with Oracle but are not faced with the day-to-day task of programming.
Through classroom lecture and demonstration and ample hands-on workshops, you will learn how to query the database with SQL, create reports with SQL*Plus, control (grant and revoke) database privileges, install and test PL/SQL programs (no programming is required!), create database tables and more!
Hands-on workshops constitute approximately 50% of the class.
Oracle Training Prerequisites
Strong basic computer concepts such as operating system and file concepts. Previous database experience is helpful but not required.
Oracle Training Course duration
3 Days
Oracle Training Objectives
After successfully completing this course, you will be able to:
- Query and manipulate an Oracle database using Structured Query Language (SQL)
- Write sophisticated SQL queries using techniques such as joins and grouping
- Create database objects such as tables and views
- Granting and revoking database privileges
- Utilize SQL*Plus query and report generation features
- Install and test a PL/SQL program
- Query the Oracle data dictionary
- Describe the fundamental concepts of the Relational Model of Data and how Orace8i adheres to the model
- Describe the Oracle support for object oriented concepts and Java
Oracle Training Course outline
- Introduction to Oracle8i
- Objectives
- Oracle8i - The Internet Database
- Oracle Editions
- 8i Releases
- And Now, Oracle9i
- SQL and PL/SQL
- Accessing the Database
- Additional Important Features and Options
- Related Products
- Oracle Architecture
- What is SQL*Plus?
- Development Environment
- Connect to SQL*Plus
- SQL*Plus Describe Command
- SQL*Plus Connect Command
- SQL*Plus Host Command
- SQL SELECT Command
- Editing in SQL*Plus
- Listing the Buffer Contents
- Editing the Buffer Contents
- Editor Commands
- SQL*Plus Edit Command
- Related SQL*Plus Commands
- Editing: A Better Way
- Running SQL*Plus Scripts
- Exit from SQL*Plus
- Relational Databases and SQL
- Relational Model of Data
- Key Concepts
- Data Structure: Two dimensional tables
- Data Integrity
- Entity Integrity
- Primary Keys
- Referential Integrity
- Domain Integrity
- A Word About Nulls
- SQL Concepts
- SQL Examples
- SQL Terminology
- Partial List of DDL Statements
- Partial List of DML Statements
- Relational Database Design
- Entities Defined
- Attributes Defined
- Relationships Defined
- Many-to-Many Relationship
- Normalizing Data
- Normal Forms
- Schema
- Data Access Basics
- Basic SQL Select Command
- Select Column List
- Select Distinct
- Calculated Columns
- Column Aliases
- Concatenated Columns
- Sorting: Order By
- Sorting by Calculated Columns
- Sorting by Column Alias
- Sorting by multiple columns
- Comparison Operator =
- Comparison Operator >
- Available Comparison Operators
- Logical Operator AND
- Available Logical Operators
- Accessing Remote Tables
- Datatypes and Functions
- Datatypes
- Datatype Conversion
- Dual: The Oracle Work Table
- Pseudo Columns
- Pseudo Columns: user, sysdate, uid, null
- Pseudo Columns: rowid and rownum
- Functions Overview
- String Manipulation Functions
- Case Conversion Functions
- Concatenation Function
- TRIM Function
- substr Function
- instr Function
- DECODE Function
- TRANSLATE Function
- Numeric Functions
- ROUND Function
- TRUNC Function
- NVL Function
- Character Conversion Function
- Date Conversion Functions
- Date Arithmetic & Functions
- Adding & Subtracting Days
- ADD_MONTHS Function
- LAST_DAY Function
- TRUNC Function
- SQL*Plus Reporting
- Creating SQL*Plus Reports
- SQL*Plus Sample Report Script
- Executing SQL*Plus Reports
- Report Output
- Spooling Output
- Controlling the SQL*Plus Environment
- Set Commands
- Customizing Your SQL*Plus Environment
- Page Header: Ttitle
- Header and Footer System Variables
- Multi-Line ttitle
- Reformatting Columns
- Displaying Current Column Settings
- Numeric Format Patterns
- SQL*Plus BREAK Command
- SQL*Plus COMPUTE Command
- Advanced SQL*Plus Scripting
- Scripts and Parameters
- Using &&
- Executing Scripts
- Naming Substitution Variables
- Define/Undefine Substitution Variables
- Creating Substitution Variables
- Displaying Substitution Variables
- Talking to Your Scripts
- Executing a Script Using Accept & Prompt
- SQL*Plus COPY Command
- COPY Command Options
- COPY Command Transaction Size
- Batch Processing
- Creating SQL*Plus Batch Scripts
- Creating a Windows Batch Program
- SQL Script for UNIX
- UNIX Shell Script
- Using SQL*Plus as a Code Generator
- 5 Steps to Generate Code Using SQL*Plus
- Data Manipulation & Transaction Control
- Inserting Rows
- Inserting Rows Rounding Data
- Inserting Multiple Rows
- Deleting Rows
- truncate Command
- Updating a Single Row
- Updating Multiple Rows
- Transaction Control
- Commit & Rollback
- Transaction Control Who Sees What?
- Transaction Control Locking Mechanisms
- Savepoints
- Commits in SQL*Plus
- Setting autocommit
- Advanced Data Access
- Conditional Operators
- Search Lists: IN
- Search Range: BETWEEN
- Search Patterns: LIKE
- LIKE Operator Using Multiple Wildcards
- LIKE Operator with Escape
- Selecting Rows with Null Values
- IS NULL Operator
- Joining Tables
- Join Diagram
- Unqualified Names in Joins
- Table Aliases in Joins
- Outer Joins
- UNION, INTERSECT and MINUS Operators
- Subquery
- Subquery Restrictions
- Subquery Rules
- Subquery IN Operator
- Subquery EXISTS Operator
- Subquery in FROM Clause
- Extended Subquery Support
- Grouping Data
- Group Functions
- MIN, MAX, SUM, COUNT, AVG
- Group Functions with Nulls
- Summary Grouping
- GROUP BY Clause
- Grouping Multiple Columns
- Golden GROUP BY Rule
- Where Clause Restrictions
- HAVING Clause
- ROLLUP
- CUBE
- GROUPING Function
- DECODE and GROUPING
- Creating Tables
- Oracle Objects
- Naming Rules
- Listing Oracle Objects using the Data Dictionary
- Creating Tables
- Creating Tables from Other Tables
- CTAS Options
- CTAS Additional Constraints
- Constraints
- Constraint Names
- Enable/Disable Constraints
- Listing Constraints using the Data Dictionary
- Altering Table Structure
- ALTER TABLE Adding Columns
- ALTER TABLE Adding Constraints
- DROP COLUMN
- Dropping Tables
- Views and Synonyms
- What is a View?
- Views Concept Diagram
- What Are Views Used For?
- Creating Views: Hiding Sensitive Columns
- Creating Views Simplify Table Access
- Creating Views: Using Column Aliases
- Updateable Views
- Read Only Views
- WITH CHECK OPTION
- Views & The Data Dictionary
- What is a Synonym?
- Private Synonyms
- Public Synonyms
- Synonym Search Sequence
- Synonyms & The Data Dictionary
- What is PL/SQL?
- PL/SQL Capabilities & Restrictions
- Types of PL/SQL Programs
- Executing PL/SQL
- PL/SQL Subprograms
- PL/SQL Storage & Execution
- PL/SQL Program Structure
- Anonymous Block Compile Errors
- PL/SQL Output to SQL*Plus
- Tools for PL/SQL Development
- Working in SQL*Plus
- PL/SQL Stored Procedures & Functions
- What is a Procedure?
- CREATE PROCEDURE Syntax
- Reviewing Compile Errors
- Calling PL/SQL from SQL*Plus
- Dropping And Re-Creating Procedures
- Procedure Signatures
- Calling Procedures
- Procedure Synonyms
- Dependencies
- Recompiling Dependent Objects
- Granting Execute Privilege
- Procedures & The Data Dictionary
- Managing Security
- Security Basics
- ANY System Privileges
- Insufficient Privileges
- Granting Of System Privileges
- The PUBLIC Keyword
- The ADMIN Option
- Revoking System Privileges
- System Privs and the Data Dictionary
- Object-level Privileges
- GRANT Object-level Privileges
- INSERT
- UPDATE
- The ALL Option
- WITH GRANT OPTION
- Which Object-Level Privileges Exist
- Roles: Concepts
- Creating a New Role
- Password Protected Roles
- Dropping A Role
- Giving System And Object Privileges To Roles
- Granting Roles
- The ADMIN OPTION
- Granting Roles to Roles
- Revoking Roles
Hardware/Software Requirements
- Hardware Requirements
- Minimal Processor: Pentium 166 or Pentium 200
- Recommended Processor: Pentium 233 or Pentium 266
- RAM: 128 MB (256 MB recommended)
- Oracle Corporation also recommends increasing virtual memory to 200 MB. (Modify it in the Performance tab of System Properties in the Control Panel.)
- Available Disk Space (FAT or HTFS): 3 GB
- Video: 256 color
- A connection to the Internet is desirable (but not absolutely necessary) to show students various Oracle-related sites.
- Software Requirements
- Windows NT 4.0, Windows 2000, Sun/OS, Solaris or Linux
- Oracle8i 8.1.6 or above
- Java JDK 1.1.5 or above*
- Microsoft Internet Explorer 5 or Netscape Navigator 4.5
- Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)*
- Student Files
- Text Editor*
|
