.jpg)
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
.jpg){kind=small}
.jpg)
.jpg)
