Oracle 12c Release 1 SQL

Print Friendly, PDF & Email
Course No. N/A

This course provides a complete, hands-on introduction to SQL, including the use of both SQL Developer and SQL*Plus. This coverage is appropriate for both users of Oracle12c and Oracle11g. A full presentation of the basics of relational databases and their use are also provided.

Audience: This course is appropriate for anyone needing to interface with an Oracle database or needing a general understanding of Oracle database functionality, such as end users, business analysts, application developers and database administrators.
Course Duration: 5 days
Prerequisites:

Basic computer skills are a must. Knowledge of databases is desired but not required.

Course Objectives:
  • Create, retrieve and manipulate database objects through PL/SQL
  • Utilize PL/SQL to control transactions
  • Understand exception processing capabilities
  • Identify when to use cursors versus singleton selects
  • Utilize BASIC IF-THEN-ELSE logic
  • Use PL/SQL to retrieve data from OS files
  • Use triggers to implement business rules and referential integrity
  • Evaluate PL/SQL statements for efficiency
  • Utilizing collections and arrays
Course Outline:
  • Basic RDBMS Principles
    • Relational Design Principles
    • Accessing Data Through a Structured Query Language
    • Entity Relationship Diagrams
    • Data Domains
    • Null Values
    • Indexes
    • Views
    • Denormalization
    • Data Model Review
    • Lab One – Basic RDBMS Principles
    • Lab 1 Solutions – Basic RDBMS Principles
  • The SQL Language and Tools
    • Using SQL*PLUS
      • Why Use SQL*Plus When Other Tools Are Available?
      • Starting SQL*Plus
      • EZConnect
      • SQL Commands
      • PL/SQL Commands
      • SQL*Plus Commands
    • The COLUMN Command
      • The HEADING Clause
      • The FORMAT Clause
      • The NOPRINT Clause
      • The NULL Clause
      • The CLEAR Clause
    • Predefined Define Variables
    • SQL
    • Command History
    • Copy and Paste In SQL*PLUS
    • Entering SQL Commands
    • Entering PL/SQL Commands
    • Entering SQL*PLUS Commands
    • Default Output From SQL*PLUS
    • Entering Queries
    • What About PL/SQL?
    • Lab Two – SQL Language and Tools
    • Lab Two Solutions – SQL Language and Tools
  • Using SQL Developer
    • Choosing an SQL Developer Version
    • Configuring Connections
      • Creating a Basic Connection
      • Creating a TNS Connection
      • Connecting
    • Configuring Preferences
    • Using SQL Developer
      • The Columns Tab
      • The Data Tab
      • The Constraints Tab
      • The Grants Tab
      • The Statistics Tab
      • Other Tabs
      • Queries In SQL Developer
      • Query Builder
      • Accessing Objects Owned By Other Users
      • The Actions Pulldown Menu
    • DIFFERENCES Between SQL Developer and SQL*PLUS
      • Reporting Commands Missing in SQL Developer
      • General Commands Missing in SQL Developer
    • Data Dictionary Reports
    • User Defined Reports
    • Using Scripts in SQL Developer
    • Lab Three – Using SQL Developer
    • Lab Three Solutions – Using SQL Developer
  • SQL Query Basics
    • Understanding the Data Dictionary
      • Exporting Key Data Dictionary Information
    • The Dictionary View
    • Components of A Select Statement
      • The SELECT Clause
      • The FROM Clause
      • The WHERE Clause
      • The GROUP BY Clause
      • The HAVING Clause
      • The ORDER BY Clause
      • The START WITH and CONNECT BY Clauses
      • The FOR UPDATE Clause
      • Set Operators
    • Column Aliases
    • Fully Qualifying Tables and Columns
    • Table Aliases
    • Using Distinct and All in Select Statements
    • Lab Four – SQL Query Basics
    • Lab Four Solutions – SQL Query Basics
  • Data Manipulation
    • The Data Manipulation Language
      • The INSERT Command
      • The UPDATE Command
      • The DELETE Command
      • Using the DEFAULT Keyword With Updates And Inserts
    • Using SQL Developer for DML
    • The Transaction Control Language (TCL)
    • Implicit TCL
    • Lab Five – Data Manipulation
    • Lab Five Solutions – Data Manipulation
  • WHERE and ORDER BY
    • WHERE Clause Basics
    • Comparison Operators
    • Literals and Constants In SQL
    • Simple Pattern Matching
    • Logical Operators
    • The Dual Table
    • Arithmetic Operators
    • Expressions In SQL
    • Character Operators
    • Pseudo Columns
    • ORDER BY Clause Basics
    • Ordering Nulls
    • Accent and Case Insensitive Sorts
    • Sampling Data
    • Where and Order by In SQL Developer
    • ALL, ANY, SOME
    • Lab Six – WHERE and ORDER BY
    • Lab Six Solutions – WHERE and ORDER BY
  • Functions
    • The Basics of Oracle Functions
    • NUMBER Functions
    • CHARACTER Functions
    • DATE Functions
    • CONVERSION Functions
    • OTHER Functions
    • LARGE OBJECT Functions
    • ERROR Functions
    • The RR Format Model
    • Leveraging Your Knowledge
    • Lab Seven – Functions
    • Lab Seven Solution – Functions
  • ANSI 92 Joins
    • Basics of ANSI 92 Joins
    • Using Query Builder with Multiple Tables
    • Table Aliases
    • Outer Joins
      • Outer Joins in Query Builder
    • Set Operators
    • Self-Referential Joins
    • Non-Equijoins
    • Lab Eight – ANSI 92 Joins
    • Lab Eight Solutions – ANSI 92 Joins
  • ANSI 99 Joins
    • Changes with ANSI 9
    • Cross Join
    • Natural Join
    • Join Using
    • Join On
    • Left / Right Outer Join
    • Full Outer Join
    • Lab Nine – ANSI 9 Joins
    • Lab Nine Solutions – ANSI 99 Joins
  • GROUP BY and HAVING
    • Introduction to Group Functions
      • Limiting Rows
      • Including NULL
      • Using DISTINCT with Group Functions
    • Group Function Requirements
    • The Having Clause
    • Other Group Function Rules
    • Using Query Builder with Group Clauses
    • Rollup and Cube
    • The Grouping Function
    • Grouping Sets
    • Lab 10 – GROUP BY and HAVING
    • Lab 10 Solutions – GROUP BY and HAVING
  • Subqueries
    • Why Use Subqueries?
    • WHERE Clause Subqueries
    • FROM Clause Subqueries
    • HAVING Clause Subqueries
    • Correlated Subqueries
    • Scalar Subqueries
    • DML and Subqueries
    • Exists Subqueries
    • Hierarchical Queries
    • Top N and Bottom N Queries
    • Creating Subqueries Using Query Builder
    • Lab 11 – Subqueries
    • Lab 11 Solutions – Subqueries
  • Basic Reporting
    • Basic Reporting
      • The COLUMN Command
      • Setting Column Width
    • PRINT | NOPRINT
    • TTITLE | BTITLE
    • REPHEADER / REPFOOTER
    • NEW_VALUE / OLD_VALUE
    • The COMPUTE Command
    • Comments in Script Files
    • Substitution Variables
      • Named Substitution Variables
      • Numbered Substitution Variables
      • Dealing with Multiple References
      • Using the DEFINE Command
      • The ACCEPT and PROMPT Commands
    • Running Scripts Unattended
    • Lab 12 – Basic Reporting
    • Lab 12 Solutions – Basic Reporting
  • Data Import and Export
    • Using SQL*LOADER with Field Delimited Data
    • Using SQL*LOADER with Comma Delimited Data
    • Data Loading Using SQL Developer
    • Exporting Oracle Data into Excel
    • Doing an ODBC Query
    • A Word About Data Pump
    • Lab 13: Data Import and Export
    • Lab 13 Solutions: Data Import and Export
  • Security
    • Basic Security
      • SYSTEM Privileges
      • OBJECT Privileges
    • The Data Dictionary and Security
    • Using Roles for Privilege Management
    • Using Profiles
      • Kernel Limits
      • Password Limits
      • Creating and Using Profiles
    • Lab 14 – Security
    • Lab 14 Solutions – Security
  • Advanced Data Manipulation
    • The Merge Command
    • Multiple Column Subquery Updates and Deletes
    • DML Against Views
    • Transactions and Read Consistency
    • DML Locks
    • Flashback Technologies
    • Inserting Large Objects
    • Changed Data Tracking
      • Flashback Versions Query
      • Log Miner
      • Change Data Capture
      • Flashback Data Archive
    • Lab 15 – Advanced Data Manipulation
    • Lab 15 Solutions – Advanced Data Manipulation
  • Introduction to Data Definition
    • Introduction to DDL Commands
    • Key Objects
    • Object Naming Rules
    • The Data Dictionary
    • Available Datatypes
      • Using Extended Datatypes
    • The Create Table Statement
    • Naming Constraints
    • Integrity Constraints
      • Primary Keys
      • Foreign Keys
      • NOT NULL Constraints
      • UNIQUE Constraints
      • CHECK Constraints
      • DEFAULT Values
    • IDENTITY Columns
    • Constraints and Create Table… As Select
    • Constraint Limitations
    • Creating Tables in SQL Developer
    • Other DDL Actions in SQL Developer
    • The Alter Table Command
    • Dropping Objects
    • Renaming Objects
    • The Truncate Command
    • The Comment Command
    • Creating Simple Views
    • Lab 16 – Introduction to Data Definition
    • Lab 16 Solutions – Introduction to Data Definition
  • Advanced Data Definition
    • DDL and The Data Dictionary
    • Disabling Constraints
    • Enabling Constraints
    • Handling Constraint Exceptions
    • Using Deferrable Constraints
    • Sequences
    • External Tables for Data Storage
      • Why Are External Tables Useful?
      • Privileges Needed
      • Syntax for Creating External Tables
    • External Tables and the ORACLE_DATAPUMP Driver
    • Indexes
      • Guidelines
      • Index Creation Syntax
      • Rebuilding Indexes
      • Function Based Indexes
    • Comments
    • Synonyms
    • Create Synonym Syntax
    • Complex Views
      • Syntax for Views
    • Virtual Columns
    • Compressed Tables
    • Invisible Indexes
    • Online DDL Enhancements
    • Invisible Columns
    • Creating Multiple Indexes on Columns
    • Lab 17 – Advanced Data Definition
    • Lab 17 Solutions – Advanced Data Definition
  • Regular Expressions
    • Available Regular Expression Functions
    • Regular Expression Operators
    • Character Classes
    • Pattern Matching Options
    • REGEX_LIKE
    • REGEXP_SUBSTR
    • REGEXP_INSTR
    • REGEXP_REPLACE
    • REGEXP_COUNT
    • Lab 18 – Regular Expressions
    • Lab 18 Solutions – Regular Expressions
  • Analytics
    • The WITH Clause
    • Reporting Aggregate Functions
    • Analytical Functions
    • User-Defined Bucket Histograms
    • The MODEL Clause
    • Pivot and Unpivot
    • Temporal Validity
    • Lab 19 – Analytics
    • Lab 19 Solutions – Analytics
  • Analytics II
    • Ranking Functions
    • Rank
    • DENSE_RANK
    • CUME_DIST
    • PERCENT_RANK
    • ROW_NUMBER
    • Windowing Aggregate Functions
    • RATIO_TO_REPORT
    • LAG / LEAD
    • Linear Regression Functions
    • Inverse Percentile Functions
    • Hypothetical Ranking Functions
    • Pattern Matching