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
- 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*PLUS
- 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
- Understanding the Data Dictionary
- 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
- The Data Manipulation Language
- 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
- Introduction to Group Functions
- 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
- 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
- Basic 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
- 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*PLUS
- 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
- Understanding the Data Dictionary
- 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
- The Data Manipulation Language
- 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
- Introduction to Group Functions
- 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
- 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
- Basic 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