SQL Data Wrangling in Oracle: Table Data
This course describes Oracle functionality useful to the SQL-savvy data analyst, and features topics such as CROSS and OUTER APPLY, using the MODEL feature, using Nested Tables, computing occurrences, generating random numbers, and much, much more.
What you'll learn
This course describes Oracle functionality useful to the SQL-savvy data analyst, and features 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.
Table of contents
- Introduction 0m
- Module Contents 0m
- Reminder of Joins and Correlated Subqueries 3m
- What Are CROSS APPLY and OUTER APPLY? 2m
- DEMO #1 2m
- CROSS APPLY Using Table-Valued Functions 1m
- Introduction to Scalar-Valued Functions 2m
- Introduction to Collection Functions 3m
- DEMO #2 1m
- SAS and Oracle 2m
- SAS LIBNAME and Oracle 2m
- PROC SQL and Oracle 1m
- SAS and SQL*Loader 2m
- More Useful SQL*Plus Commands 1m
- Summary 2m
- Introduction 0m
- Module Contents 1m
- DEMO #1: Quick Start Guide 6m
- SQL*Loader in Detail 2m
- SQL*Loader Command Line Options 6m
- DEMO #2: Using a Parameters File 1m
- SQL*Loader Control File 2m
- Specifying Comments in the Control File 1m
- Options Section 1m
- Input File Section 1m
- Field-List Section 2m
- Output Section 1m
- Summary 1m
- Introduction 0m
- Module Contents 2m
- Input File Section - INFILE 3m
- Input File Section - BADFILE/DISCARDFILE 2m
- Input File Section - Types of Input Files 1m
- Field-List Section - Introduction 1m
- Field-List Section - Data Types 3m
- Field-List Section - Fixed Format Files 1m
- DEMO #1 - Read in Data Using POSITION 2m
- Field-List Section - Delimited Files 1m
- DEMO #2 - Read in Data Using TERMINATED BY and ENCLOSED BY 2m
- Field-List Section - Functions and Expressions 2m
- DEMO #3 - Compute the Body Mass Index From HEIGHT and WEIGHT 1m
- Field-List Section - Generating Data With Parameters 1m
- Field-List Section - Using FILLER Fields 2m
- Field-List Section - Table Level Options 2m
- Output Section - Conditional Load and WHEN Clause 3m
- Output Section - Loading Multiple Files 1m
- Summary 1m
- Introduction 0m
- Module Contents 1m
- Preliminaries 0m
- What Are Nested Tables? 2m
- CREATE TYPE With Nested Tables 3m
- The COLLECT() Function 1m
- The SET() Function 1m
- The CARDINALITY() Function 1m
- CREATE TABLE and the Nested Table Storage Table 3m
- Collection Unnesting 4m
- Creating a Delimited String From a Nested Table 2m
- INSERT INTO With Nested Tables 3m
- Complex Nested Tables 6m
- Multiset Operators and the Powermultiset Functions 2m
- DEMO #1 - Multiset Operators 2m
- DEMO #2 - The POWERMULTISET() Function 3m
- Summary 2m
- Introduction 0m
- Module Contents 2m
- Preliminaries 2m
- Motivational Example 3m
- Introduction to the DBMS_FREQUENT_ITEMSET Package 2m
- FI_TRANSACTIONAL Procedure Syntax 4m
- Example #1 - How Many Candybars Can One Person Eat? 2m
- Example #2 - Just the Top X%, Please! 1m
- Example #3 - Subsetting for Desired Combinations 1m
- Example #4 - Including the Desired Items 1m
- Example #5 - Excluding the Undesirable Items 1m
- Example #6 - Using FI_HORIZONTAL on Columnar Data 2m
- DEMO #1 - Unwinding the ITEMSET Nested Table Column 1m
- Summary 1m
About the author
Scott is a statistical computer programmer specializing in SAS and SQL, but has learned many other languages such as Java, Android, C#, C, PL/SQL, R, and SPSS to name a few. His personal website, sheepsqueezers.com, features documents and presentations on a variety of topics he has learned over the years. Scott is also the author of "Android from A to D," which is his take on how a newbie should be taught Android programming, as well as "Poor Man's Data Dictionary," which describes how to quickl... more