SQL Data Wrangling in Oracle: Table Data

Description

In this course, you will :

• Covers topics such as ordering your data with NULLs appearing first or last, using FETCH to subset your data, using SAMPLE to randomly sample from a table, using CROSS APPLY and OUTER APPLY, how to use the MODEL feature to access the rows and columns of a database table like a spreadsheet, generating random numbers, computing occurrences, using nested tables, and much more.

Syllabus :

1. Ordering, Sampling, and Random Numbers: Part I

•  ORDER BY and NULLS 
•  DEMO #1 
•  FETCH and OFFSET Introduction 
•  Limiting Rows With FETCH 
•  FETCH and the WITH TIES Keyword 
•  Skipping Rows With OFFSET 
•  Useful SQL*Plus Commands 

 

2. Ordering, Sampling, and Random Numbers: Part II

•   Sampling With SAMPLE 4m
•   Sampling With ORA_HASH() 
•   DBMS_RANDOM Package and the VALUE() Function 
•   NORMAL() Function 

 

3. Extraction and Insertion: Part I

•   Extension to IN Condition 
•   Multi-Table INSERT Feature 
•   Unconditional Multi-Table INSERT Feature 
•   Conditional Multi-Table INSERT Feature 
•   Conditional Multi-Table INSERT Feature and the FIRST Keyword

 

4. Extraction and Insertion: Part II

•   Reminder of Joins and Correlated Subqueries 
•   What Are CROSS APPLY and OUTER APPLY? 
•   CROSS APPLY Using Table-Valued Functions 
•   Introduction to Scalar-Valued Functions 
•   Introduction to Collection Functions 
•   SAS and Oracle 
•   SAS LIBNAME and Oracle 
•   PROC SQL and Oracle 
•   SAS and SQL*Loader 
•   More Useful SQL*Plus Commands 

 

5. Extraction and Insertion: Part III

•    DEMO #1: Quick Start Guide 
•   SQL*Loader in Detail 
•   SQL*Loader Command Line Options 
•   DEMO #2: Using a Parameters File 
•   SQL*Loader Control File 
•   Specifying Comments in the Control File 
•   Options Section 
•   Input File Section 
•   Field-List Section 
•   Output Section 

 

6. Extraction and Insertion: Part IV

•   Input File Section - INFILE 
•   Input File Section - BADFILE/DISCARDFILE 
•   Input File Section - Types of Input Files 
•   Field-List Section - Introduction 
•   Field-List Section - Data Types 
•   Field-List Section - Fixed Format Files 
•   DEMO #1 - Read in Data Using POSITION 
•   Field-List Section - Delimited Files 
•   DEMO #2 - Read in Data Using TERMINATED BY and ENCLOSED BY 
•   Field-List Section - Functions and Expressions 
•   DEMO #3 - Compute the Body Mass Index From HEIGHT and WEIGHT 
•   Field-List Section - Generating Data With Parameters 
•   Field-List Section - Using FILLER Fields 
•   Field-List Section - Table Level Options 
•   Output Section - Conditional Load and WHEN Clause 
•   Output Section - Loading Multiple Files 

 

7. Nested Tables

•   What Are Nested Tables? 
•   CREATE TYPE With Nested Tables 
•   The COLLECT() Function 
•   The SET() Function 
•   The CARDINALITY() Function 
•   CREATE TABLE and the Nested Table Storage Table 
•   Collection Unnesting 
•   Creating a Delimited String From a Nested Table 
•   INSERT INTO With Nested Tables
•   Complex Nested Tables 
•   Multiset Operators and the Powermultiset Functions 
•   DEMO #1 - Multiset Operators 
•   DEMO #2 - The POWERMULTISET() Function 

 

8. Computing Intersections and Occurrences: Part I

•   Preliminaries 
•   A Flashback to Grade School 
•   Method #1 - Data in a Single Table 
•   Method #2 - Data in Separate Tables 

 

9. Computing Intersections and Occurrences: Part II

•   Motivational Example 
•   Introduction to the DBMS_FREQUENT_ITEMSET Package 
•   FI_TRANSACTIONAL Procedure Syntax 
•   Example #1 - How Many Candybars Can One Person Eat? 
•   Example #2 - Just the Top X%, Please! 
•   Example #3 - Subsetting for Desired Combinations 
•   Example #4 - Including the Desired Items 
•   Example #5 - Excluding the Undesirable Items 
•   Example #6 - Using FI_HORIZONTAL on Columnar Data 

 

10. The MODEL Feature - Part I

•   Preliminaries 
•   What Is the MODEL Feature? 
•   What Are Multi-Dimensional Arrays? 
•   Motivational Example 
•   MODEL Syntax 
•   Working With Cells 
•   Updating and Inserting Cells 

 

11. The MODEL Feature - Part II

•   ANY Wildcard and CV() Function 
•   DEMO #1 - Using ANY and CV() 
•   Using IN, BETWEEN and Expressions 
•   Using FOR Loops 
•   NULL and Absent Values 

 

12. The MODEL Feature - Part III

•   Initializing New Measures 
•   Iteration With ITERATE 
•   Using UNTIL() and PREVIOUS() 
•   PRESENTV() and PRESENTNNV() 
•   Aggregate Functions 
•   Reference Model Clause 

Show more