« Back to Class Listing

SQL Level 2: Intermediate

Description

Take your SQL skills to the next level by learning how to do more work with SQL inside the database before pushing data across the network to your applications. This class takes a time efficient ‘problem / solution/ discussion’ approach and covers the topics that are important to today’s SQL developers.

This class teaches you how to use window functions, pivoting rows into columns, reverse-pivoting columns into rows, using pivoting to facilitate inter-row calculations, and double-pivoting a result set. Also covered is the topic of ‘bucketization’, how to create histograms, summarize data into buckets, perform aggregations over a moving range of values, generate running-totals and subtotals, and other advanced, data warehousing techniques.

CLICK HERE FOR REQUIRED BOOK

Upon completion of this course, students will know about:

  • Retrieving Records
  • Conditional SQL & Sample Data
  • Conditional Sorting & Stacking Rows
  • Views and Different Join Types
  • Working with Text Data
  • Working Numerical Data
  • Working with Dates
  • Grouping and Window Functions
  • Pivoting and Pivot Reports
  • Other Ways to Pivot
  • Bucketization and Histograms
  • Analyzing and Presenting Data

Section: SQL-LVL2 (04NOV17)
Dates: Nov 04, 2017 - Nov 11, 2017 Time: 9:00 AM
Tuition: $725.00 Instructor: Ali Kazmi Register
Lesson 1: Retrieving Records
  • About the SQL Cookbook
  • Setting up the Sample Databases
  • Using Multiple Conditions
  • Referencing Aliased Columns
Lesson 2: Conditional SQL & Sample Data
  • Using CASE WHEN THEN
  • Limiting Rows Returned
  • Returning Random Rows
  • Transforming NULL Values
Lesson 3: Conditional Sorting & Stacking Rows
  • Dealing with Nulls when Sorting
  • Conditional Sorting
  • Using UNION ALL to Stack Rows
  • Using UNION to Remove Duplicates
Lesson 4: Views & Different Join Types
  • How and Why to use Views
  • Using CROSS JOINS
  • CROSS JOIN vs INNER JOIN
  • Using OUTER JOINS
Lesson 5: Working with Text Data
  • Walking a String
  • Counting Occurrences
  • Removing Unwanted Characters
Lesson 6: Working Numerical Data
  • Generate a Running Total
  • Averages without High and Low
  • Changing Values in a Running Total
Lesson 7: Working with Dates
  • Adding/Subtracting Date Intervals
  • Calculating Date Differences
  • Counting Work Days
Lesson 8: Grouping & Window Functions
  • GROUP BY Review
  • Using the OVER function
  • Using OVER (PARTITION BY)
  • NULLS and Windowing
Lesson 9: Pivoting & Pivot Reports
  • Pivoting Rows into Columns
  • Pivoting to Columns Multi Row
  • Pivoting Columns to Rows
Lesson 10: Other Ways to Pivot
  • Using the PIVOT operator in SQL
  • Using Excel Pivot Tables
  • Connecting to Databases
  • Using Excel Power Pivot
Lesson 11: Bucketization & Histograms
  • Creating Fixed Size Buckets
  • Creating Variable Size Buckets
  • Coding Horizontal Histograms
Lesson 12: Analyzing & Presenting Data
  • BI Systems Pros and Cons
  • Power BI Features and Demonstration

Class Schedule

Date Times Meeting Type Location
11/04/2017 9:00 AM - 4:30 PM Classroom Executive Park
11/11/2017 9:00 AM - 4:30 PM Classroom Executive Park

Additional Information

Final Enrollment:
 
Clock Hours: 13.0