Oracle Introduction to SQL


Request for price


Length: 5 day (40 hours)

 

Course objectives

After completing this course, students will be able to:

    • Understand the basic concepts of relational databases ensure refined code by developers.
    • Create reports of sorted and restricted data.
    • Run data manipulation statements (DML).
    • Control database access to specific objects.
    • Manage schema objects.
    • Manage objects with data dictionary views.
    • Retrieve row and column data from tables.
    • Control privileges at the object and system level.
    • Create indexes and constraints; alter existing schema objects.
    • Create and query external tables.

Course outlines

    Introduction

    • 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