iNEWPM

Course Details

Duration: 24 hrs

Audience:


Ø  Data Warehouse Administrator

Ø  Forms Developer

Ø  System Analysts

Ø  Business Analysts

Ø  Developer

Ø  Application Developers

Ø  PL/SQL Developer


 

Suggested Prerequisites:

Ø  None

 


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

Are you providing Training Classes
IT Courses / Govt Exam Preparation
Higher Studies / Studies Abroad
NEWFreeCompanies HiringUpdates//nuPM