Programming & Development

Oracle 12c SQL Fundamentals

Introduction:

This textbook takes a unique approach to SQL training in that it incorporates data modeling theory, relational database theory, graphical depictions of theoretical concepts and numerous examples of actual SQL syntax into one learning vehicle.

Course Objectives
The first portion of this textbook considers the logical models upon which a relational database is based and the various configurations and environments in which you may work with the Oracle database.
The next segment focuses on the actual SQL syntax for writing database queries. You will begin with the simplest of queries and then proceed onto moderately complex query scenarios.
Finally, this textbook covers the DDL, DML and transaction control portions of the SQL language that allow one to create, maintain and manipulate application database objects and application data.

Course Outline:

About Data Models
About the Relational Model
The Electronics Data Model
About the Relational DBMS

Considering Available Tools
Selecting the Appropriate Tool
Oracle Net Database Connections
Oracle PAAS Database Connections
Setup SQL Developer
Setup SQL*Plus
Setup Jdeveloper

About Bind & Substitution Variables
Using SQL Developer
Using SQL*Plus

About the SQL Language
Characteristics of SQL
Introducing SQL Using Select
SQL Rules

The Select Statement
Distinct/Unique Keyword
Using Alias Names

About Logical Operators
Equality Operator
Boolean Operators
REGEXP_LIKE()
In Operator

About the Order by Clause
Multiple Column Sorts
Specify the Sort Sequence
About Null Values within Sorts
Using Column Aliases

Rowid Pseudo Column
ORA_ROWSCN PSEUDO Column
ROWNUM Pseudo Column
About the Built-In Functions
SYSDATE
User & UID
SESSIONTIMEZONE Function
Using the Dual Table
Row Limiting & Top-N Queries
FETCH First x Rows Only Clause
OFFSET x Rows Clause
FETCH…PERCENT Clause
The WITH TIES Option

About Joins
Inner Joins
Reflexive Join
Non-Key Join
Outer Join

About the Set Operators
SQL Set Operator Examples
UNION Example
INTERECT Example
MINUS Example
UNION All

Finding Data with Sub-Queries
Standard Sub-Queries
Correlated Sub-Queries
The EXISTS Operator

About Summary Groups
Find Groups within the Tables
Select Data From the Base Tables
Select Groups from the Results

Create Table Statement
Column Data Types
Not Null
Default
Describe
Alter Table Statement
Drop Table Statement
Table DDL Using SQL Developer
Alter User Statement
Alter Session Statement
NLS_Language
NLS_Date

The Insert Statement
The Delete Statement
The Update Statement
About Transactions
Transaction Rollback
Transaction Commit
Transaction Savepoint
The Set Transaction Statement
Set Transaction Read Only Statement Rules

The Company Data Model
The Electronics Data Model

SQL-92 & SQL-99
Cross Joins
Natural Joins
Inner Joins
Implicit Inner Join
Outer Joins
Anti Joins
Named Sub-Queries

Using Rollup
The Grouping() Function
Using Cube

What are the SQL Functions?
String Formatting Functions
UPPER(), LOWER() Example
INITCAP() Example
Character Codes Functions
CHR((), ASCII() Examples
PAD & TRIM Functions
RPAD() Example
RTRIM() Example
TRIM() Example
String Manipulation Functions
DECODE () Example
SUBSTR() Example
INSTR() Example
TRANSLATE() Example
REPLACE() Example
String Comparison Functions
LEAST() Example
Phonetic Search Function
SOUNDEX() Example

About the Numeric Data Functions
GREATEST() Example
ABS() Example
ROUND() Example
TRUNC() Example
SIGN() Example
TO_NUMBER() Example & Data ype Conversions
NULL VALUES FUNCTIONS
NVL() & NVL2() Function
NVL() Example (Character)
NVL() Example (Numeric Loss of Data)
NVL() Example (Numeric Output)
NVL2() Example
COALESCE() Function
NULLIF() Functio

Date Formatting Functions
TO_CHAR() & TO_DATE() Format Patterns
TO_CHAR() Examples
TO_DATE() Examples
EXTRACT() Example
Date Arithmetic Functions
MONTHS_BETWEEN() Example
ADD_MONTHS() Example
LAST_DAY() Example
NEXT_DAY() Example
TRUNC(), ROUND() Dates Example
NEW_ TIME() Example
About V$TIMEZONE_NAMES
CAST() FUNCTION & TIME ZONES

About Database Objects
About Schemas
Making Object References

About Relations Views
The Create View Statement
Why Use Views?
Accessing Views with DML
Maintaining View Definitions
Alter View
Drop View
DDL Using SQL Developer

About Indexes
Create & Drop Index Statements
Indexes & Performance
Data Dictionary Storage

About Sequences
Referencing NEXTVAL
Referencing CURRVAL
Within the DEFAULT Clause
Alter Sequence & Drop Sequence
ALTER SEQUENCE
DROP SEQUENCE
About Identity Columns
CREATE TABLE…GENERATED AS IDENTITY
ALTER TABLE…GENERATED AS IDENTITY
Start With Limit Value
ALTER TABLE...DROP IDENTITY
About Synonyms
CREATE & DROP SYNONYM Statements
CREATE SYNONYM
DROP SYNONYM
Public Vs Private Synonyms
CREATE SCHEMA AUTHORIZATION

The RENAME Statement
TABLESPACE Placement
CREATE TABLE…TABLESPACE
The Comment Statement
The TRUNCATE TABLE Statement

About Object Security
Grant Object Privileges
Revoke Object Privileges
Object Privileges & SQL Developer

About Constraints
NOT NULL Constraint
NOT NULL Example
CHECK Constraint
UNIQUE Constraint
PRIMARY KEY Constraint
REFERENCES Constraint
ON DELETE CASCADE Example
ON DELETE SET NULL Example
Constraints on Existing Tables
Constraints & SQL Developer

RENAMING & DROPPING Constraints
ENABLING & DISABLING Constraints
DEFERRED Constraint Enforcement
SET CONSTRAINTS
Handling Constraint Exceptions
Constraints with Views
DATA Dictionary Storage

More About the Data Dictionary
OBJECT-SPECIFIC Dictionary Views
USER_UPDATABLE_COLUMNS
The Dictionary Structure
METADATA & SQL Developer

Enroll in this course

$2,695.00

Need Help Finding The Right Training Solution?

Our training advisors are here for you.