ORACLE 10g Advanced SQL

Duration: 3 days

Develop the skills to effectively interact with an Oracle 10g Database using Oracle SQL.

Course Description:

You will receive a hands-on introduction to advanced SQL concepts, especially as they relate to Oracle’s implementation of the SQL Language. Topics necessary to the development and administration of an Oracle 10g database are covered. This course is specifically designed for those who know generic SQL but are not familiar with SQL*Plus and more importantly, Oracle’s implementation of SQL.

You will be able to…

• Converse in relational database terminology

• Understand Structured Query Language

• Format reports using SQL*Plus commands

• Extract and organize information from the database

• Insert, update & delete information in database tables

• Create and drop tables, views, synonyms and indexes

Who Needs to Attend:

Application Designers, Developers, & Database Administrators

Prerequisites:

An understanding of relational database design concepts and general knowledge of SQL.

Course Content:

SQL Basics and SQL*Plus

• Understanding the Difference Between SQL, SQL*Plus and PL/SQL

• Invoking and Leaving SQL*Plus

• Entering and Executing Commands

• The SQL Buffer, SQL*Plus Default Output

• LOGIN.SQL

• The SELECT Command & Mandatory Clauses

Using iSQLPlus

• Understanding the iSQL*Plus Environment and Commands

Single Row Functions

• Functions, Date and Numeric Format Models/Data Conversion

• Control Statements

Joining Data from Multiple Tables in Queries

• ANSI SQL99 Joins

Group Functions and the GROUP BY Clause

• Group Functions

• Distinct Operator in Group Functions

• GROUP BY, WITH Clause, ROLLUP, CUBE

• GROUPING SETS

Using Subqueries and CASE Clauses

• Single-Row, Multi-Row, Multi-Column Subqueries

• Subqueries in a WHERE Clause

• Subqueries in the FROM Clause – In-Line Views

• Subqueries In the SELECT Clause – Scalar Subqueries

• Correlated Subqueries

• CASE Clause

SQL*Plus Reporting

• SQL*Plus Report Writing Commands

• COLUMN Command / Titles, System Variables

• Master-Detail Report with TITLE and COLUMN Commands

• Control Breaks, Computing Aggregate Amounts

• Using SET Variables in SQL*Plus, SQL to Generate SQL

Data Manipulation Language (DML) Commands

• INSERT, UPDATE, DELETE, MERGE Command

• Use of Subqueries

• Transaction Control Commands / Read Consistency / Locking

• The MERGE Command

• Flashback Queries

Data Definition Language (DDL) Commands

• DDL Commands, Object Naming, Dropping Objects

• TRUNCATE, COMMENT, RENAME Command

• Creating Tables / Integrity Constraints

• CREATE TABLE, ALTER TABLE Command

• Datatypes, Types of Declarative Constraints

• Default Values

• Creating, Deferrable, Dropping, Disabling/Enabling Constraints / Displaying Constraint Information

• Handling Exceptions

• Views, Synonyms, Sequences, Indexes

• Temporary Tables, External Tables

Security

• Users

• Privileges

• Roles

• Authentication Methods

Getting Data In And Out Of Oracle

• SQL*Loader Basics

• Importing Oracle Data Into Excel

• Direct Database Queries Using ODBC

Course Labs:

• Creating Tables And Constraints

• Creating Views

• Data Manipulation

• Single Row Functions

• Advanced Joins

• Subqueries

• Group Functions

• SQL*Plus Reporting

• Advanced DDL

• Advanced DML

• Privileges And Roles