Course Outline for SQL Programming Training

Introduction

• Course Objectives, Course Agenda and Appendixes Used in this Course.
• Overview of Oracle Database 12c and Related Products
• Overview of relational database management concepts and terminologies
• Introduction to SQL and its development environments
• What is Oracle SQL Developer?
• Starting SQL*Plus from Oracle SQL Developer
• The Human Resource (HR) Schema
• Tables used in the Course

Retrieving Data using the SQL SELECT Statement

• Capabilities of the SELECT statement
• Arithmetic expressions and NULL values in the SELECT statement
• Column aliases
• Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
• Use of the DESCRIBE command

Restricting and Sorting Data

• Limiting the Rows
• Rules of precedence for operators in an expression
• Substitution Variables
• Using the DEFINE and VERIFY command

Using Single-Row Functions to Customize Output

• Describe the differences between single row and multiple row functions
• Manipulate strings with character function in the SELECT and WHERE clauses
• Manipulate numbers with the ROUND, TRUNC and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the date functions

Using Conversion Functions and Conditional Expressions

• Describe implicit and explicit data type conversion
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nest multiple functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Use conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group Functions

• Group Functions
• Creating Groups of Data
• Restricting Group Results

Displaying Data from Multiple Tables Using Joins

• Introduction to JOINS
• Types of Joins
• Natural join
• Self-join
• Non equijoins
• OUTER join

Using Subqueries to Solve Queries

• Introduction to Subqueries
• Single Row Subqueries
• Multiple Row Subqueries

Using the SET Operators

• Set Operators
• UNION and UNION ALL operator
• INTERSECT operator
• MINUS operator
• Matching the SELECT statements
• Using ORDER BY clause in set operations

Managing Tables using DML statements

• • • Data Manipulation Language
    • Database Transactions

Introduction to Data Definition Language

• • • Data Definition Language

Introduction to Data Dictionary Views

• • • Introduction to Data Dictionary
    • Describe the Data Dictionary Structure
    • Using the Data Dictionary views
    • Querying the Data Dictionary Views

Creating Sequences, Synonyms, Indexes

• • • Overview of sequences
    • Overview of synonyms
    • Overview of indexes

Creating Views

• • • • Overview of views

Managing Schema Objects

• • • • Managing constraints
      • Creating and using temporary tables
      • Creating and using external tables
      • Retrieving Data by Using Subqueries

Retrieving Data by Using a Subquery as Source

• • • • Working with Multiple-Column subqueries
      • Using Scalar subqueries in SQL
      • Correlated Subqueries
      • Working with the WITH clause

Manipulating Data by Using Subqueries

• • • • Using Subqueries to Manipulate Data
      • Inserting by Using a Subquery as a Target
      • Using the WITH CHECK OPTION Keyword on DML Statements
      • Using Correlated Subqueries to Update and Delete rows

Controlling User Access

• • • • System privileges
      • Creating a role
      • Object privileges
      • Revoking object privileges

Manipulating Data

• • • • Overview of the Explicit Default Feature
      • Using multitable INSERTs
      • Using the MERGE statement
      • Performing flashback operations
      • Tracking Changes in Data

Managing Data in Different Time Zones

• • • • Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP
      • Working with INTERVAL data types