Oracle Introduction to SQL


Request for price


Length: 5 day (40 hours)

 

  • This Oracle Database: Introduction to SQL training helps you write subqueries, combine multiple queries into a single query using SET operators and report aggregated data using group functions. Learn this and more through hands-on exercises.
  • This course will help you understand the advanced features of SQL.
  • Learning these features will help you query and manipulate data within the database, use the dictionary views to retrieve metadata and create reports about their schema objects.
  • Administrator
  • Analyst
  • Developer
  • Systems Administrator

Familiarity with data processing concepts and techniques

Start with the customer – find out what they want and give it to them.

Course objectivesCourse outlines

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