Realistic Software Training

Author / Training Specialist / Developer

Oracle SQL & PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts

On-site Course Syllabus

Course Description

Day 1

This outline is based upon the five day SQL and PL/SQL offering

Day 2 Morning - The Select statement (Character and Number functions)

Character Functions The INITCAP Function            The INSTR Function The LENGTH Function The LOWER Function The LPAD Function The LTRIM Function The REPLACE Function The RPAD Function The RTRIM Function The SOUNDEX Function The TO_CHAR Function The TRANSLATE Function The TRIM Function The UPPER Function The DECODE Function Case Expressions Using Functions in the Where Clause Creating New Values with Numeric and Date Functions The MOD and TO_NUMBER Functions Using the NVL and the NVL2 Functions The ROUND Function   The TRUNC Function Dates and Date Functions   Date Pictures   Changing the Date Picture Date Functions   The ADD_MONTHS Function   The LAST_DAY Function   The MONTHS_BETWEEN Function   The NEXT_DAY Function   The ROUND and TRUNC Data Functions   The TO_DATE Function

Day 2 Afternoon - The Select statement (Set Operators, Subqueries, Variables, Views, and Analytic Functions)

Using Set Operators, Subqueries, and Variables The UNION and UNION ALL operators The MINUS Operator The INTERSECT Operator Subqueries Multiple-Row Subqueries Multiple-Column Queries Correlated Subqueries Variables      Using the Single Ampersand Substitution Variable Using the Double-Ampersand Variable Defining User Variables Using Database and Materialized Views Administering Database Views DML and Views The WITH CHECK OPTION Clause How to Updatte Records by Using a View That Can't Be Updated Why use views? Materialized Views Analytic Processing with SQL   ROLLUP   CUBE   Ranking Functions   The RANK Function   The DENSE_RANK Function   Top-N and Bottom-N Queries   The PERCENT_RANK Function   The CUME_DIST Function   The NTILE Function   The ROW_NUMBER Function   Windowing     The CUMULATIVE AGGREGATE Function     Moving Averages     The RATIO-TO-REPORT Function     The LAD and LEAD Functions   Statistical Functions

Day 1          Top          Day 3          Day 4          Day 5