Oracle Database 12c: Introduction to SQL

This training covers the following topics:

An overview of Oracle Database

• Features of Oracle Database 12c
• Various aspects of Relational database
• Types of SQL statements
• Starting database

Fetching Data using the SQL SELECT Statement

• SQL SELECT statements
• Generate a report of data from the output of a basic SELECT statement
• Select Specific Columns
• Using Column Heading Defaults and Arithmetic Operators
• Understanding Operator Precedence
• Using Describe command

Learn to Sort and Retrieve Data

• Using Where clause
• Using comparison and logical operators
• Rules of precedence
• ORDER BY clause

Usage of Single-Row Functions to Customize Output

• Differences between single row and multiple row functions
• Operate strings with character function in the SELECT and WHERE sections
• Operate statistics with the ROUND, TRUNC, and MOD functions
• Achieve arithmetic with date data
• Operate dates with the DATE methods

Invoke Conversion Methods and Conditional Expressions

• Define implicit and explicit data type alteration
• Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion methods
• Nest multiple functions
• Applying the NVL, NULLIF, and COALESCE methods to data
• Using conditional IF THEN ELSE logic in a SELECT declaration

Aggregate Data Using the Group Functions

• Use the aggregation methods to produce meaningful reports
• Divide the retrieved data in groups by using the GROUP BY clause
• Eliminate groups of data by using the HAVING clause

Show Data from Multiple Tables Using Joins

• Write SELECT declarations to admission data from more than one table
• Display data that usually does not meet a join condition by using outer joins
• Join a table to itself by using a self-join

Use Sub-queries to Solve Queries

• Define the types of problem that sub-queries can solve
• Describe sub-queries
• List the kinds of sub-queries
• Write single-row and multiple-row sub-queries

The SET Operators

• Define the SET methods
• Using a SET operator to combine multiple queries into a single query
• Controlling the order of rows

Data Manipulation Statements (DML)

• Define DML statement
• Insertion of rows into a table
• Using UPDATE and DELETE statement
• Understanding COMMIT and ROLLBACK statements

Using DDL Statements to Create and Manage Tables

• Classify the main database objects
• Appraisal of table structure
• Creating a simple table
• Decipher how constraints can be created at table creation
• Define how schema objects work

Other Schema Objects

• Making a simple and complex view
• Retrieving data from views
• Creating, maintaining, and using sequences
• Creating and maintaining indexes
• Create private and public synonyms

Control User Access

• Distinguish system rights from object rights
• Grant System Privileges
• Create and Grant Privileges to a Role
• Changing Password and Grant Object Privileges

Management of Schema Objects

• Add, Modify, and Drop a Column
• Adding and Dropping a Constraint
• Enabling and disabling Constraint
• Creating and Removing Indexes
• Perform Flashback Operations

Manage Objects with Data Dictionary Views

• Data dictionary introduction
• Use Dictionary Views
• USER_OBJECTS and ALL_OBJECTS Views
• Information about Table and Column
• For constraint information query database views
• For view, sequence, index and synonym information query the dictionary view
• Add a comment to a table
• For comment information query the database views

Manipulate Large Data Sets

• Use subqueries to Manipulate Data
• Using a subquery as source to retrieve data
• Insert Using a Subquery as a Target
• Usage of the WITH CHECK OPTION Keyword on DML Statements
• List different types of Multi-table INSERT Statements
• Use Multi-table INSERT Statements
• Integrate rows in a table
• Track Changes in Data over a period of time

Data Management in different Time Zones

• Time Zones overview
• CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
• Compare Date and Time in a Session's Time Zone
• DBTIMEZONE and SESSIONTIMEZONE
• Alter between DATE and TIMESTAMP
• INTERVAL Data Types
• Use EXTRACT, TZ_OFFSET and FROM_TZ
• Invoke TO_TIMESTAMP, TO_YMINTERVAL and TO_DSINTERVAL

Retrieve Data Using Sub-queries

• Subqueries for multiple-column
• Non-pairwise and Pairwise Comparison
• Scalar Subquery Expressions
• With Correlated Subqueries solve problems
• Using Correlated Subqueries Update and Delete Rows
• The EXISTS and NOT EXISTS operators
• Invoke the WITH clause
• Recursive WITH clause