Oracle SQL Fundamentals

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

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

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

Session 16: ACCESS CONTROL

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

Session 17: RETRIEVE DATA USING SUBQUERIES

• ANY, ALL or SOME Operators
• 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 18: HIERARCHICAL QUERIES

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

Session 19: 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 20: 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 21: 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

Session 22: FLASHBACK TECHNOLOGY

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

Session 23: REGULAR EXPRESSION SUPPORT

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

Session 24: MANAGE OBJECTS WITH THE DATA DICTIONARY

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

Session 25: ADVANCED SCHEMA MANAGEMENT

• Virtual columns
• Invisible columns
• Default values for table columns
• Function Based Indexes
• Creating and Using External Tables

Session 26: 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