Advanced Oracle SQL Queries (SQL204)
Course Length: 3 days
Delivery Methods:
Available as private class only
Course Overview
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.
Course Benefits
- 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.
Course Outline
- Pseudocolumns & Functions
- About the HR Schema
- Sample Schemas
- HR Schema Entities
- FunctionsHow to Use Functions in Oracle
- Exercise: Use the SYSCONTEXT function
- Pseudo-Columns
- Using Rowid
- Using Rownum
- Exercise: Use ROWNUM
- About the HR Schema
- Using Subqueries
- Simple Subqueries
- Definition
- Nested Subquery
- Inline Views
- Subquery in the HAVING clause
- Correlated Subqueries
- Scalar Subquery
- Exercise: Subqueries
- Simple Subqueries
- Joining Tables
- Review of Joins
- Equijoins
- 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
- EMP_DETAILS_VIEW
- 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
- CONCAT
- LENGTH
- INSTR
- REPLACE
- UPPER
- LOWER
- INITICAP
- LPAD
- RPAD
- TRIM
- TO_CHAR
- SOUNDEX
- 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
- Numeric Functions
- SQL Data Manipulation Language
- The INSERT Statement
- INSERT Statement Categories
- The UPDATE Statement
- The DELETE Statement
- Transactions
- Complex Table References
- The MERGE statement
- Exercise: Data Manipulation Language (DDL)
- The INSERT Statement
Class Materials
Each student will receive a comprehensive set of materials, including course notes and all the class examples.
Class Prerequisites
Experience in the following is required for this class:
- Basic SQL Skills