Advanced Oracle SQL Queries
This Oracle SQL training class helps students master SQL using an Oracle database and prepares students for a PL/SQL course. Note that this course covers SQL, not PL/SQL.
Public Classes: Delivered live online via WebEx and guaranteed to run . Join from anywhere!
Private Classes: Delivered at your offices , or any other location of your choice.
- Learn to create Users and Schemas.
- Learn to use pseudo-columns and functions.
- Learn to write simple and correlated subqueries.
- Learn to write simple and complex joins.
- Learn to use ROLLUP and CUBE.
- Learn to use SET operators.
- Learn to use character and non-character SQL functions.
- Learn to write INSERT, UPDATE, and DELETE statements.
- Learn to create tables.
- Learn to use keys and constraints to protect data integrity.
- Learn to use views, indexes, sequences, and synonyms.
- Pseudocolumns & Functions
- About the HR Schema
- Sample Schemas
- HR Schema Entities
- FunctionsHow to Use Functions in Oracle
- Exercise: Use the SYSCONTEXT function
- Using Rowid
- Using Rownum
- Exercise: Use ROWNUM
- Using Subqueries
- Simple Subqueries
- Nested Subquery
- Inline Views
- Subquery in the HAVING clause
- Correlated Subqueries
- Scalar Subquery
- Exercise: Subqueries
- Joining Tables
- Review of Joins
- Inner Joins
- Outer Joins
- Cross Joins
- Reflexive Join
- Non-Key Join
- Natural Joins
- Semijoins and Antijoins
- Using Named Subqueries
- Exercise: Join Exercises
- Rollup & Cube
- About Group Processing
- Simple GROUP BY
- ROLLUP Function
- GROUPING Function
- Using Cube
- Exercise: Practice GROUP BY, ROLLUP and CUBE
- Using Set Operators
- Set Operators Defined
- Relationship to Mathematical Set Theory
- Restrictions on Set Operators
- Exercise: Set Operators Exercise
- Conditional Processing
- The DECODE Function
- The Case Expression
- Exercise: Conditional Processing
- SQL Functions (Character)
- What Are SQL Functions?
- Character Functions
- Regular Expressions
- Exercise: Character Functions
- SQL Functions (Non-Character)
- Numeric Functions
- The TO_CHAR Function with numbers
- Date Format Functions
- Date Arithmetic Functions
- Null Value Functions
- Exercise: SQL Non-Character Functions
- SQL Data Manipulation Language
- The INSERT Statement
- INSERT Statement Categories
- The UPDATE Statement
- The DELETE Statement
- Complex Table References
- The MERGE statement
- Exercise: Data Manipulation Language (DDL)
Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.
Experience in the following is required for this Oracle class:
Courses that can help you meet these prerequisites: