Oracle Databased for RM 1800 only

PROGRAM OUTLINE

Course : ORACLE DATABASED INTRODUCTION TO SQL

Duration : 3 days

Time : 8.30 am – 5.00 pm

Course Synopsis

Introduction (Database Architecture)

Describe course objectives Explore the Oracle 10g database architecture

Installing the Oracle Database Software

Explain core DBA tasks and tools Plan an Oracle installation Use optimal flexible architecture Install software with the Oracle Universal Installer (OUI)

Creating an Oracle Database

Create a database with the Database Configuration Assistant (DBCA) Create a database design template with the DBCA Generate database creation scripts with the DBCA

Managing the Oracle Instance

Start and stop the Oracle database and components Use Enterprise Manager (EM) Access a database with SQL*Plus and iSQL*Plus Modify database initialization parameters Understand the stages of database startup View the Alert log Use the Data Dictionary

Administering User Security

Create and manage database user accounts Authenticate users Assign default storage areas (tablespaces) Grant and revoke privileges Create and manage roles Create and manage profiles Implement standard password security features Control resource usage by users

Managing Schema Objects

Define schema objects and data types Create and modify tables Define constraints View the columns and contents of a table Create indexes, views and sequences Explain the use of temporary tables Use the Data Dictionary

Administering User Security

Create and manage database user accounts Authenticate users Assign default storage areas (tablespaces) Grant and revoke privileges Create and manage roles Create and manage profiles Implement standard password security features Control resource usage by users

Managing Schema Objects

Define schema objects and data types Create and modify tables Define constraints View the columns and contents of a table Create indexes, views and sequences Explain the use of temporary tables Use the Data Dictionary

Backup and Recovery Concepts

Identify the types of failure that may occur in an Oracle Database Describe ways to tune instance recovery Identify the importance of checkpoints, redo log files, and archived log files Configure ARCHIVELOG mode

Performing Database Backups

Create consistent database backups Back your database up without shutting it down Create incremental backups Automate database backups Monitor the flash recovery area

Performing Database Recovery

Recover from loss of a control file Recover from loss of a redo log file Perform complete recovery following the loss of a data file

Performing Flashback

Describe Flashback database Restore the table content to a specific point in the past with Flashback Table Recover from a dropped table View the contents of the database as of any single point in time with Flashback Query See versions of a row over time with Flashback Versions Query View the transaction history of a row with Flashback Transaction Query

Writing SQL SELECT Statements

Define projection, selection, and join terminology Review the basic SQL SELECT statement syntax Select all columns using a wildcard notation from a table State simple rules and guidelines for writing SQL statements Write a query containing the arithmetic operators Create a character expression with the concatenation operator Using the iSQL*Plus Environment SQL statements versus iSQL*Plus commands

Restricting and Sorting Data

Limit rows using a selection Using the WHERE clause to retrieve specific rows Using the comparison conditions in the WHERE clause Use the LIKE condition to compare literal values List the logical conditions AND, OR, NOT Describe the rules of precedence for the conditions Sort rows with the ORDER BY clause Use ampersand substitution in iSQL*Plus to restrict and sort output at run time

Using Single-Row Functions to Customize Output

Show the differences between single row and multiple row SQL functions Categorize the character functions into case manipulation and character manipulation types Use the character manipulation functions in the SELECT and WHERE clauses Explain and use the DATE and numeric functions Use the SYSDATE function to retrieve the current date in the default format Introduce the DUAL table as a means to view function results List the rules for applying the arithmetic operators on dates Use the arithmetic operators with dates in the SELECT clause

Reporting Aggregated Data Using the Group Functions

Describe and categorize the group functions Use the group functions Utilize the DISTINCT keyword with the group functions Describe how nulls are handled with the group functions Create groups of data with the GROUP BY clause Group data by more than one column Avoid illegal queries with the group functions Exclude groups of data with the HAVING clause

Displaying Data from Multiple Tables

Identify Types of Joins Retrieve Records with Natural Joins Use Table Aliases to write shorter code and explicitly identify columns from multiple tables Create a Join with the USING clause to identify specific columns between tables Use the ON clause to specify arbitrary conditions or specify columns to Join Create a Three-way join with the ON clause to retrieve information from 3 tables List the Types of Outer Joins LEFT, RIGHT, and FULL Generating a Cartesian Product

Using Sub queries to Solve Queries

List the syntax for sub queries in a SELECT statements WHERE clause List the guidelines for using sub queries

Describe the types of sub queries Execute single row sub queries and use the group functions in a sub query Identify illegal statements with sub queries Execute multiple row sub queries Analyze how the ANY and ALL operators work in multiple row sub queries

Using the SET Operators

Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows Use the UNION ALL operator to return all rows from multiple tables Describe the INTERSECT operator Use the INTERSECT operator Explain the MINUS operator Use the MINUS operator List the SET operator guidelines Order results when using the UNION operator

Manipulating Data

Write INSERT statements to add rows to a table Copy rows from another table Create UPDATE statements to change data in a table Generate DELETE statements to remove rows from a table Use a script to manipulate data Save and discard changes to a table through transaction processing Show how read consistency works Describe the TRUNCATE statement