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

Course Description:

You will receive a hands-on introduction to SQL Basics and SQL*Plus including a basic knowledge of relational databases and how to access them through SQL and SQL*Plus. Topics necessary to the development and administration of an Oracle 11g database are covered.

You will be able to ...

• Converse in relational database terminology

• Understand Structured Query Language basics

• 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

Course Content:

In the Beginning…Database Management System Overview

• What is a Database?

• Types of DBMSs

Introduction to Entity Relationship Modeling

• What is a Data Model?

• Why Use an Entity Relationship Model?

• Input and Components of an Entity Relationship Model

• Reading the Entity Relationship Model

• Guidelines for a Model Review

Fundamentals of Relational Database Management Systems

• What is an RDBMS?

• History of SQL

• Physical Data Storage

• Relationships between Tables

• Domains - Data Integrity Constraint

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 SQL Developer

Using iSQLPlus

• Understanding the iSQL*Plus Environment and Commands

The WHERE and ORDER BY Clause

• WHERE, ORDER BY Clause

• Using the Data Dictionary

• Hierarchical Queries

Single Row Functions

• Single Row vs. Group Functions

• Date and Numeric Format Models

• Data Conversion

• Control Statements

• Regular Expressions

Joining Data from Multiple Tables in Queries

• The JOIN Condition / The Cartesian Product

• Outer JOINs

• SET Operators

• 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

Analytic Functions (Optional)

• Single Row Analytic Functions

• Aggregating Analytic Functions

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, Changing Passwords

Getting Data In And Out Of Oracle

• SQL*Loader Basics

• Importing Oracle Data Into Excel

• Direct Database Queries Using ODBC

Course Labs:

Lab 1: Entity Identification

Lab 2: Identifying / Resolving Relationships

Lab 3: Data Normalization

Lab 4: RDBMS Principles

Lab 5: Basic Database Design

Lab 6: Basic Data Definition Language

Lab 7: Creating Tables And Constraints

Lab 8: Creating Views

Lab 9: Data Manipulation

Lab 10: SQL Queries

Lab 11: Filtering And Sorting Data

Lab 12: Single Row Functions

Lab 13: Joining Multiple Tables

Lab 14: Set Operations

Lab 15: Subqueries

Lab 16: Grouping And Group Functions

Lab 17: Advanced Data Analysis

Lab 18: SQL*Plus Reporting

Lab 19: Advanced DDL

Lab 20: Advanced DML

Lab 21: Privileges And Roles