Oracle Database 12c: Introduction to SQL

Today Oracle is known as the most widely used Relational Database. Oracle provides its services to users around the world. There are various components of an Oracle training such as SQL, PL/SQL, Oracle Forms, and Oracle Reports. This course focuses on providing the delegates the knowledge about using Oracle's query language i.e. SQL. SQL or the Structred Query Language is a tool used by developers and users alike to extract information as well as update the tables in an Oracle database. To use SQL, Oracle provides the tool SQL * Plus which can be used for querying with SQL and also isssuing formatting commands to the database.

Course Content

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