Oracle 12c SQL Fundamentals

Agenda

1 - RELATIONAL DATABASES & DATA MODELS

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

2 - SELECTION & SETUP OF THE DATABASE INTERFACE

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

3 - USING THE DATABASE INTERFACE

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

4 - INTRODUCTION TO THE SQL LANGUAGE

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

5 - THE SELECT STATEMENT

• The Select Statement
• Distinct/Unique Keyword
• Using Alias Names

6 - RESTRICTING RESULTS WITH THE WHERE CLAUSE

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

7 - SORTING DATA WITH THE ORDER BY CLAUSE

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

8 - PSEUDO COLUMNS, FUNCTIONS & TOP-N QUERIES

• 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

9 - JOINING TABLES

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

10 - USING THE SET OPERATORS

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

11 - SUMMARY FUNCTIONS USING SUB-QUERIES

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

12 - AGGREGATING DATA WITHIN GROUPS

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

13 - USE DDL TO CREATE & MANAGE TABLES

• 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

14 - USE DML TO MANIPULATE DATA

• 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

15 - UNDERSTANDING THE DATA MODELS

• The Company Data Model
• The Electronics Data Model

16 - ABOUT THE SQL-99 STANDARD

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

17 - ENHANCING GROUPS WITH ROLLUP & CUBE

• Using Rollup
• The Grouping() Function
• Using Cube

18 - SQL FUNCTIONS: CHARACTER HANDLING

• 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

19 - SQL FUNCTIONS: NUMERIC HANDLING

• 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() Function

20 - SQL FUNCTIONS: DATE HANDLING

• 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

21 - DATABASE OBJECTS: ABOUT DATABASE OBJECTS

• About Database Objects
• About Schemas
• Making Object References

22 - DATABASE OBJECTS: RELATIONAL VIEWS

• 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

23 - DATABASE OBJECTS: INDEXES

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

24 - DATABASE OBJECTS: CREATING OTHER OBJECTS

• 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

25 - DATABASE OBJECTS: OBJECT MANAGEMENT USING DDL

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

26 - DATABASE OBJECTS: SECURITY

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

27 - DATA INTEGRITY USING CONSTRAINTS

• 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

28 - MANAGING CONSTRAINT DEFINITIONS

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

29 - THE DATA DICTIONARY STRUCTURE

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