Oracle SQL Fundamentals

Oracle SQL Fundamentals Course Overview

Course Prerequisites

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

Course Objectives

• • Create Oracle SQL statements to query database tables
  • Use standard aggregate functions and related SELECT statement clauses
  • Implement extended SQL functions
  • Join Tables
  • Use subqueries
  • Create and alter tables and views
  • Insert, update and delete rows in database tables
  • Retrieve Data Using Correlated Subqueries, In-Line Views and Multi-Column Subqueries
  • Use the WITH Clause
  • Insert and Update Rows Using Complex Subqueries
  • Create Hierarchical Queries
  • Use ROLLUP, CUBE and GROUPING SETS Group By Enhancements
  • Implement the GROUPING and GROUPING_ID Functions
  • Manage Large Data Sets
  • Insert Data Conditionally and Unconditionally
  • Merge Rows in a Table
  • Create TIMESTAMP and INTERVAL Datatypes
  • Use TIMESTAMP, TIMEZONE and INTERVAL Functions and Literals
  • Use Regular Expressions and Implementing Related Functions
  • Manage Objects with the Data Dictionary
  • Manage Schema Objects
  • Perform Flashback Operations
  • Create and Use External Tables
  • Control User Access to Database Objects

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

Oracle SQL Fundamentals Course Content

• DAY 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
  USING SQL*PLUS
  What is SQL*Plus
  Getting Started
  Entering and Executing SQL Statements
  Editing SQL Statements
  Creating, Editing and Executing SQL Files
  USING SQL DEVELOPER
  What is Oracle SQL Developer
  Starting SQL Developer
  Configure a Connection
  Navigation Tabs
  SQL Worksheet
  Query Builder
  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
  AGGREGATE FUNCTIONS
  Overview of Built In Aggregate Functions
  The GROUP BY Clause
  The HAVING Clause

• DAY 2

  JOINING TABLES
  Overview of Table Joins
  Inner Joins
  Table Aliases
  Outer Joins
  Self Joins
  ANSI Standard Joins
  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
  CONVERSION AND MISCELLANEOUS FUNCTIONS
  Conversion Functions
  The NVL and NVL2 Functions
  The DECODE Function
  CASE Expressions
  The COALESCE and NULLIF Functions
  SQL PARAMETERS
  Command Line Substitution Parameters
  The Accept Command
  The Define and Undefine Commands
  USING SUBQUERIES
  Overview of Subqueries
  In-Line Views
  Top-N Queries
  Complex Subqueries
  Multi Column Subqueries
  Correlated Subqueries
  Subquery Rules
  Using the ANY, ALL and SOME Operators

• DAY 3

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

• DAY 4

  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
  HIERARCHICAL QUERIES
  Hierarchical data
  The START WITH and CONNECT BY clauses
  The LEVEL pseudo-column
  Sequencing the output
  Eliminating nodes and branches
  ENHANCED GROUPING FEATURES
  Review of basic grouping concepts
  The ROLLUP and CUBE extensions
  The GROUPING SETS extension
  Using the GROUPING and GROUPING_ID functions
  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
  FLASHBACK TECHNOLOGY
  Flashback Query
  The AS Clause
  Flashback Table
  Flashback Drop

• DAY 5

  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
  REGULAR EXPRESSION SUPPORT
  Regular Expression Notation
  Character matching
  Repetition operators
  Sub expression grouping
  Regular expression functions
  MANAGE OBJECTS WITH THE DATA DICTIONARY
  The Data Dictionary
  Useful Data Dictionary Tables
  Using the Data Dictionary
  MANAGE SCHEMA OBJECTS
  Column Operations
  Constraint Operations
  Creating and Dropping Indexes
  B-Tree Indexes
  Function Based Indexes
  Descending Indexes
  Creating and Using External Tables
  ACCESS CONTROL
  System Privileges and Roles
  Create Users
  Alter User Details
  Grant and Revoke Object Privileges