Oracle SQL

Course Overview

Course Prerequisites

• • There are no formal pre-requisites for the Oracle SQL course, although an understanding of databases and exposure to information technology in general would be useful.

Target Audience for this Course

• • Anyone who needs to use and understand Oracle SQL to query and update data in an Oracle database.

Included with this course

• • Comprehensive course materials and exercises

Course Content

• DAY 1

  Session 1: RELATIONAL DATABASE CONCEPTS
  What is an Oracle Database
  Relational Database Structures
  Tables, Rows and Columns
  Indexes, Primary Keys and Foreign Keys
  Supported Datatypes
  The Data Dictionary
  
  Session 2: USING SQL*PLUS
  What is SQL*Plus
  Getting Started
  Entering and Executing SQL Statements
  The SQL*Plus Command Line History
  Editing SQL Statements
  The login.sql File
  Creating, Editing and Executing SQL Files
  The SQLcl Utility
  
  Session 3: USING SQL DEVELOPER
  What is Oracle SQL Developer
  Starting SQL Developer
  Configure a Connection
  Navigation Tabs
  SQL Worksheet
  Query Builder
  
  Session 4: RETRIEVING DATA WITH THE SELECT STATEMENT
  The SELECT Statement
  The SELECT and FROM Clauses
  Conditions and the WHERE Clause
  Other Conditional Operators
  Logical Operators
  The ORDER BY Clause
  Column Aliases
  Arithmetic Expressions
  Precedence of Operators
  Markup CSV
  
  Session 5: AGGREGATE FUNCTIONS
  Overview of Built In Aggregate Functions
  The GROUP BY Clause
  The HAVING Clause
  
  Session 6: JOINING TABLES
  Overview of Table Joins
  Inner Joins
  Table Aliases
  Outer Joins
  Self Joins
  ANSI Standard Joins
  
  Session 7: BASIC SUBQUERIES AND SET OPERATORS
  Overview of Subqueries
  Basic Subqueries
  Set Operators
  The Union, Intersect and Minus Operators

• DAY 2

  Session 8: NUMERIC, CHARACTER AND DATE FUNCTIONS
  Function Types
  Using the Table dual to try out Functions
  Numeric Functions
  Character Functions
  String Concatenation
  Date Arithmetic and Date Functions
  
  Session 9: CONVERSION AND MISCELLANEOUS FUNCTIONS
  Conversion Functions
  The NVL and NVL2 Functions
  The DECODE Function
  CASE Expressions
  The COALESCE and NULLIF Functions
  
  Session 10: SQL PARAMETERS
  Command Line Substitution Parameters
  The Accept Command
  The Define and Undefine Commands
  
  Session 11: COMPLEX SUBQUERIES
  Subqueries Usage
  In-Line Views
  Top-N Queries
  Subqueries with Joins
  Multi Column Subqueries
  Correlated Subqueries
  Subquery Rules
  Using the ANY, ALL and SOME Operators

• DAY 3

  Session 12: MANAGING DATA
  Inserting Rows
  Updating Rows
  Deleting Rows
  Verifying Updates
  Transaction Control
  Commit and Rollback
  Savepoints
  Commits and Constraints
  Amending Data in SQL Developer
  
  Session 13: MANAGING TABLES
  Creating Tables
  Specifying Constraints
  Altering Tables, Columns and Constraints
  Dropping Tables, Columns and Constraints
  Recovering Dropped Tables
  Copying Tables
  
  Session 14: MANAGING INDEXES AND VIEWS
  Creating Indexes
  Dropping Indexes
  Listing Indexes
  Creating and Using Views
  Dropping Views
  Listing Views
  
  Session 15: MANAGING SEQUENCES AND SYNONYMS
  Create a Sequence
  View Sequence Details
  Create a Synonym
  List Synonyms