Course Catalog  > Data & Databases  > Oracle
TP2641

Oracle Database: SQL II - Intermediate Training

This course builds upon the prerequisite introductory volume from the Oracle Database 12c: SQL Expert Series and considers intermediate-level SQL topics such as writing database queries using the SQL-99 syntax and exploiting the power of built-in functions that extend the capabilities of SQL.
Request Information
Request On-Site or Customized Course Info
Course Details

Duration

3 days

Target Audience

  • Oracle Professionals
  • Business and non-IT Professionals
  • Application Designers and Database Developers
  • Business Intelligence (BI) Analysts and Consumers
  • Database Administrators
  • Web Server Administrators

Skills Gained

  • How to build intermediate-level and even advanced queries using the SQL-­‐99 join syntax, along with other advanced query topics.
  • ANSI/ISO and native Oracle SQL built-in functions.
  • Complete an application schema by creating database objects to compliment table definitions.
Course Outline
  • Understanding The Data Models
    • The Company Data Model
    • The Electronics Data Model
  • 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
  • Enhancing Groups With ROLLUP & CUBE
    • Using ROLLUP
    • The GROUPING() Function
    • Using CUBE
  • Using The CASE Expression 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
  • Sql Functions: Numeric Handling
    • About The Numeric Data Functions
    • GREATEST() Example
    • ABS() Example
    • ROUND() Example
    • TRUNC() Example
    • SIGN() Example
    • TO_NUMBER() Example & Data Type 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
  • 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
  • Database Objects: About Database Objects
    • About Database Objects
    • About Schemas
    • Making Object References
  • Database Objects: Relational Views
    • About Relational Views
    • The Create View Statement
    • Why Use Views?
    • Accessing Views With DML
    • Maintaining View Definitions
    • Alter View
    • Drop View
    • DDL Using SQL Developer
  • Database Objects: Indexes
    • About Indexes
    • CREATE & DROP INDEX Statements
    • Indexes & Performance
    • Data Dictionary Storage
  • 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
  • Database Objects: Object Management Using DDL
    • The RENAME Statement
    • TABLESPACE Placement
    • CREATE TABLE ... TABLESPACE
    • The COMMENT Statement
    • The TRUNCATE TABLE Statement
  • Database Objects: Security
    • About Object Security
    • Grant Object Privileges
    • Revoke Object Privileges
    • Object Privileges & SQL Developer
  • 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
  • Managing Constraint Definitions
    • Renaming & Dropping Constraints
    • Enabling & Disabling Constraints
    • Deferred Constraint Enforcement
    • Set Constraints
    • Handling Constraint Exceptions
    • Constraints With Views
    • Data Dictionary Storage
  • The Data Dictionary Structure
    • More About The Data Dictionary
    • Object - Specific Dictionary Views
    • USER_UPDATABLE_COLUMNS
    • The Dictionary Structure
    • Metadata & SQL Developer