EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

Advanced Data Analysis Using SQL

Course outline

5 days
Discuss this programme →

Duration

Advanced Data Analysis Using SQL

5 days

Overview

This advanced-level course is designed for individuals who have a solid understanding of SQL basics and wish to deepen their skills in data analysis. Participants will explore advanced SQL techniques, optimization strategies, and learn how to solve complex data challenges. The course will also cover topics such as stored procedures, triggers, and advanced data visualization using SQL.

Prerequisites

Participants should have a strong foundation in SQL fundamentals, including proficiency in writing complex queries and a good understanding of database design principles. Completion of an intermediate-level SQL course or equivalent experience is recommended.

Lesson 1: Indexing Strategies

  • Understanding index types (clustered, non-clustered)
  • Index optimization for query performance

Lesson 2: Query Execution Plans

  • Reading and interpreting execution plans
  • Optimizing queries based on execution plans

Lesson 1: Creating and Managing Stored Procedures

  • Benefits of stored procedures
  • Input parameters, output parameters, and return values

Lesson 2: User-Defined Functions

  • Scalar and table-valued functions
  • Use cases and best practices

Lesson 1: Introduction to Triggers

  • Creating and managing triggers
  • Use cases for triggers

Lesson 2: Transactions

  • ACID properties
  • Managing transactions in SQL

Lesson 1: Complex Joins

  • Self-joins
  • Non-equijoins and advanced join techniques

Lesson 2: Correlated Subqueries

  • Understanding correlated subqueries
  • Optimizing and using subqueries effectively

Lesson 1: Introduction to Window Functions

  • OVER() clause and window specification
  • Ranking and aggregate window functions

Lesson 2: Advanced Analytical Queries

  • Lag, Lead, and other advanced window functions
  • Partitioning and ordering data in analytical queries

Lesson 1: MERGE Statement

  • Combining INSERT, UPDATE, and DELETE operations
  • Use cases for the MERGE statement

Lesson 2: Advanced UPDATE and DELETE Operations

  • Using JOINs in UPDATE and DELETE statements
  • Avoiding common pitfalls in data manipulation

Lesson 1: Dynamic SQL for Reporting

  • Building dynamic SQL queries for flexible reporting
  • Parameterized queries for user interactivity

Lesson 2: Advanced Charting and Graphs

  • Using SQL for advanced charting and graphing
  • Integration with reporting tools

Lesson 1: Row-Level Security

  • Implementing row-level security in SQL
  • Managing access based on user roles

Lesson 2: Securing Stored Procedures and Triggers

  • Granting and revoking permissions on procedures and triggers
  • Best practices for securing sensitive data

Module 9: Advanced Case Studies and Project Work

  • Case studies on solving complex data analysis problems
  • Group projects on real-world data analysis challenges
  • Presenting solutions and receiving feedback

Lesson 1: NoSQL and SQL Integration

  • Using SQL with NoSQL databases
  • Polyglot persistence

Lesson 2: Big Data Integration

  • SQL in the context of big data technologies
  • Apache Hadoop, Spark, and SQL integration

Programme scope, delivery arrangements and suitability are confirmed during enquiry.