Oracle 12c Release 1 SQL

Course Number:

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
Share on facebook
Share on twitter
Share on linkedin
Share on email

Related Posts

About Us
Ntier company logo

IT Training, Agile Ways of Working and High Impact Talent Development Strategies

Let Us Come to You!

Classes recently delivered in: Atlanta, Boston, Chicago, Columbus, Dallas, Detroit, Indianapolis, Jerusalem, London, Milan, New York, Palo Alto, Phoenix, Pittsburgh, Portland, Raleigh, San Antonio, San Diego, San Francisco, San Jose, Seattle, Springfield, Mass., St. Louis, Tampa and more!