Oracle SQL Fundamentals Training Course

Course Contents - DAY 1

Course Introduction

• • Administration and Course Materials
• • Course Structure and Agenda
• • Delegate and Trainer Introductions

Session 1: RELATIONAL DATABASE CONCEPTS

• • What is an Oracle Database
• • Relational Database Structures
• • Tables,Rows and Columns
• • Indexes,Primary Keys and Foreign Keys
• • Data Types
• • 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

Oracle SQL Fundamentals Training Course

Course Contents - 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

Oracle SQL Fundamentals Training Course

Course Contents - 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

Oracle SQL Fundamentals Training Course

Course Contents - DAY 4

Session 16: RETRIEVE DATA USING SUBQUERIES

• • Correlated Subqueries
• • In-Line Views
• • The Exists Operator
• • The WITH Clause
• • Multi-Column Subqueries
• • Insert and Update Using a Query
• • Correlated Update and Delete

Session 17: HIERARCHICAL QUERIES

• • Hierarchical data
• • The START WITH and CONNECT BY clauses
• • The LEVEL pseudo-column
• • Sequencing the output
• • Eliminating nodes and branches

Session 18: ENHANCED GROUPING FEATURES

• • Review of basic grouping concepts
• • The ROLLUP and CUBE extensions
• • The GROUPING SETS extension
• • Using the GROUPING and GROUPING_ID functions

Session 19: ANALYTIC QUERIES

• • Ranking functions
• • The NTILE and WIDTH_BUCKET functions
• • Analytic Aggregate functions
• • The FIRST and LAST Value functions
• • The LEAG and LAG Functions
• • The LISTAGG Function

Session 20: MANAGE LARGE DATA SETS

• • Multi-table Inserts
• • Conditional and Unconditional Inserts
• • Merging Data into a Table
• • Table and View Based Merge
• • A Sub Query Based Merge

Oracle SQL Fundamentals Training Course

Course Contents - DAY 5

Session 21: FLASHBACK TECHNOLOGY

• • Flashback Query
• • The AS Clause
• • Flashback Table
• • Flashback Drop

Session 22: REGULAR EXPRESSION SUPPORT

• • Regular Expression Notation
• • Character matching
• • Repetition operators
• • Sub expression grouping
• • Regular expression functions

Session 23: VIRTUAL COLUMNS AND EXTERNAL TABLES

• • Virtual columns
• • Creating and Using External Tables

Session 24: DATE,TIME AND TIMEZONE SUPPORT

• • Date,Timestamp and Interval datatypes
• • Handling dates and times
• • Handling intervals
• • Date,timestamp and interval functions and literals
• • Related NLS parameters

Session 25: ACCESS CONTROL

• • System Privileges and Roles
• • Create Users
• • Alter User Details
• • Grant and Revoke Object Privileges

Session 26:MANAGE OBJECTS WITH THE DATA DICTIONARY

• • The Data Dictionary
• • Useful Data Dictionary Tables
• • Using the Data Dictionary