| Course Code: |
RT 363 |
| Course Abstract: |
This course is a common starting point in the Oracle database curriculum for administrators, developers and business users. The objective of this course is to provide an introduction to the SQL database language within the context of an Oracle database. Specific subject areas to be explored and objectives to be reached are:
> A summation of relational database principles and an introduction to the Oracle relational database server.
>Using the iSQL*Plus web-based database interface and other traditional interfaces.
>Writing simple SQL queries and moderately complex SQL queries using various join techniques.
>Summarizing, grouping and sorting data.
>Advanced query techniques such as correlated subqueries.
>Developing simple SQL*Plus reports.
>Creating and maintaining database tables.
>Taking advantage of new features such as the use of regular expressions.
>Moving beyond basic table definitions, consideration of defining the full range of database objects, such as advanced table definitions, indexes, sequences, views and others.
>Understanding basic database security and object privileges.
>Using the powerful set of built-in SQL functions.
>Protecting the database and extending the basic data model using declarative constraints.
>Performing advanced SQL queries such as grouping and cube operations and SQL99 join syntax.
>Developing complex SQL*Plus reports and using SQL*Plus scripts. |
| Audience: |
The target audience for this course is all Oracle professionals, both business and systems professionals. Among the specific groups for whom this course will be helpful are:
>Application designers and developers.
>Database administrators.
>Business users and non-technical end users. |
| Duration: |
3 days |
| Learning Outcomes: |
Upon completion of this course, the participant will be able to:
Part I Objectives:
> Explain relational database principles and the Oracle relational database server
> Use the iSQL*Plus web-based database interface and other traditional interfaces
> Write simple SQL queries and moderately complex SQL queries using various join techniques
> Summarize, group and sort data
> Use advanced query techniques such as correlated subqueries
> Develop simple SQL*Plus reports
> Create and maintain database tables
> Take advantage of new features such as the use of regular expressions
> Move beyond basic table definitions, consideration of defining the full range of database objects, such as advanced table definitions, indexes, sequences, views and others
> Identify basic database security and object privileges
> Use the powerful set of built-in SQL functions
> Protect the database and extending the basic data model using declarative constraints
> Perform advanced SQL queries such as grouping and cube operations and SQL99 join syntax
> Develop complex SQL*Plus reports and using SQL*Plus scripts |
| Course Topics: |
Relational Databases & SQL
About Relational Databases
Elements Of SQL
Using SQL*Plus
About SQL*Plus
Using The MS Windows SQL*Plus Tool
Using The ISQL*Plus Web Tool
Entering Commands & Syntax
Building A Select Statement
About the Select Statement
Selecting Columns
Using Alias Names
Building A Where Clause
About Logical Operators
Equality Operator
Boolean Operators
Null & Between Operators
Finding Text Strings
In Operator
Ordering the Result Table
About the Order by Clause
Multiple Column Sorts
Specifying the Sort Sequence
About Null Values Within Sorts
Using Column Aliases
Pseudo Columns & Functions
About Pseudo Columns & Functions
Using Rowid
Using Rownum
Using The Functions
Using The Dual Table
Sessiontimezone Function
Joining Tables
About Joins
Inner Join
Reflexive Join
Non-Key Join
Outer Join
Using Set Operators
About The Set Operators
Set Operator Examples
Summary Functions
About Summary Functions
Summary Functions With Distinct
Using Subqueries
About Subqueries
Standard Subqueries
Correlated Subqueries
Creating & Selecting Groups
About Summary Groups
Finding Groups Within The Base Tables
Selecting Data From The Base Tables
Selecting Groups From The Result Table
SQL*Plus Commands
About SQL*Plus Commands
More About Script Files
Invoking Operating System Commands
Saving Session Output
Build Simple SQL*Plus Reports
About The SQL*Plus Report Features
Session Output With Set
Format Column Output With Column
Defining Report Breaks With Break
Produce Subtotals With Compute
SQL Data Definition Language
About SQL DDL
Create Table Statement
Alter Table Statement
Drop Table Statement
Supportive Statements
SQL Data Manipulation Language
About SQL DML
About The Insert Statement
About The Delete Statement
About The Update Statement
About Transactions
Truncate Table
Complex Table References |
| Prerequisites: |
No mandatory prerequisites exist for this course. However, one or more of the following courses are suggested prerequisites:
> Advanced Technology Tour
> Oracle Designer Data Modeling |