PL/SQL
- Introduction to PL/SQL
- Declaring Variables
- Writing Executable Statements
- Using SQL within PL/SQL
- Control Structures
5.1 Conditionals
5.2 Loops - Using Composite Data Types
- Using Explicit Cursors
- Exception Handling
- Procedures
- Functions
- Packages
Author: juandejuan
PowerBI – Data Analytics Sheet Design
General concepts 1a. Power BI objects.
1b. Measures and dimensions.
Data analytics objects 2a. Creating visuals.
2a1. Tables and matrices.
2a2. Charts.
2a3. Text boxes. 2b. Filters and slicers.
2b1. Levels of filter usage.
2b2. Selectors for slicers. 2c. Hierarchies.
2c1. Creating dimension hierarchies.
2c2. Using hierarchies in visuals. 2d. Interaction between elements. 2e. Creating the dashboard.
Advanced elements 3a. Calculated tables.
3b. Measures using calculated tables.
Data Visualization and Storytelling (with Power BI and Tableau)
TABLE OF CONTENTS:
Data visualization fundamentals.
1a. Analytics and visualization.
1b. Types of analytics: Report, self-consumption analysis, information discovery.
1c. Data models for analytics tools.
PowerBI.
2a. Preparing the data model.
2a1. Importing data from different sources.
2a2. Transformations with Power Query.
2a3. Integration of heterogeneous data sources (Data Mashup).
2a4. Table relationships. 2b. Creating new elements.
2b1. Derived measures at the row level.
2b2. Derived measures at the report level.
2c3. New attributes.
2c4. Filtered measures.
2c5. Level measures.
2c6. Measures with time shift. 2c. Calculated tables. 2d. Creating visual elements.
2d1. Tables.
2d2. Charts.
2d3. Interaction between elements. 2e. Filters and data segmentation.
2e1. Levels of filter use.
2e2. Selectors for data segmentation. 2f. Hierarchies.
2f1. Creating dimension hierarchies.
2f2. Using hierarchies in visual elements. 2g. Creating the dashboard.
Tableau.
3a. Preparing the data model.
3a1. Importing data from different sources.
3a2. Transformations.
3a3. Integrating heterogeneous data sources (Data Mashup).
3a4. Table relationships. 3b. Creating new elements.
3b1. Derived measures at the row level.
3b2. Derived measures at the report level.
3c3. New attributes.
3c4. Filtered measures.
3c5. Level measures.
3c6. Time-shifted measures. 3c. Creating visual elements.
3c1. Tables.
3c2. Charts.
3c3. Interaction between elements. 3d. Filters.
3d1. Levels of filter use. 3e. Hierarchies.
3e1. Creating dimension hierarchies.
3e2. Using hierarchies in visual elements. 3f. Creating the dashboard.
Creating stories
4a. Story creation functionality.
4b. Visualization elements.
4c. Presentation.
Basic SQL (MySQL)
CONTENTS:
Relational database concepts
MySQL basics
2a. Accessing the server
2b. Using MySQL Workbench
SQL language: Using SELECT
3a. Basic statements
3b. Using aliases
3c. Arithmetic expressions
3d. NULL concept
SQL language: Using single-row functions
4a. Single-row functions for characters
4b. Single-row functions for numeric values
4c. Single-row functions for dates
4d. Function nesting
4e. Type conversion functions
4f. Null handling functions
4g. Conditional functions
SQL language: Grouping data
5a. Using GROUP BY
5b. Using HAVING
SQL language: Table joins
6a. Using ANSI syntax
6b. Using “Oracle” syntax
6c. INNER / OUTER joins
6d. Using self-joins (SELF JOIN)
6e. EQUIJOIN / NONEQUIJOIN joins
6f. Joining using Cartesian product
SQL language: Subqueries
7a. Using subqueries
7b. Converting subqueries to joins
DDL language: Creating objects
8a. Creating tables
8b. Creating restrictions
8b1. Primary keys
8b2. Foreign keys
8b3. NOT NULL, CHECK and UNIQUE restrictions
8c. Creating indexes
DML language: Managing rows
9a. Inserting rows (INSERT)
9b. Updating rows (UPDATE)
9c. Deleting rows (DELETE)
SQL Optimization
Introduction
1.1 Identifying SQL to optimize
1.2 Tuning strategies
Optimizer operation
2.1 Analyzing and processing a query
2.2 Query transformer
2.3 Transformer operations
2.4 Selectivity and cardinality
2.5 Calculating the cost of a query
2.6 Adaptive query optimization
2.7 Controlling optimizer behavior
Execution plans
3.1 Displaying execution plans
3.2 Reviewing execution plans
3.3 Using EXPLAIN PLAN
3.4 EXPLAIN PLAN options
3.5 Using AUTOTRACE
3.6 Performance views
3.7 Monitoring SQL
Interpreting execution plans
4.1 How to read an execution plan
4.2 Table operations
4.3 Index operations
4.4 Join operations
4.5 Other operations
Result caching
5.1 Introduction to RESULT_CACHE
5.2 Enabling RESULT_CACHE RESULT_CACHE
5.3 Using RESULT_CACHE
Optimizer Statistics
6.1 Importance of Statistics
6.2 Calculating and Configuring Statistics Calculation
6.3 Histograms and Histogram Calculation
Using BIND Variables
7.1 Cursor Sharing Concept
7.2 Using BIND Variables for Cursor Sharing
Application Trace
8.1 Using TKPROF
8.2 Using TRCSESS
Best Practices with SQL
9.1 Examples of Good and Bad Practices
9.2 Recommended SQL
9.3 Automatic Transformations
