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
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
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