Course Outline
Lesson 1 - Foundational SQL Concepts:
- Select statements
- Types of Joins
- Database indexes
- Views
- Subqueries
- Union operations
- Table creation
- Data loading procedures
- Data dumping techniques
- Introduction to NoSQL
Lesson 2 - Data Modeling Strategies:
- Transaction-based Entity-Relationship (ER) systems
- Data warehousing concepts
- Data warehouse architectures
- Star schemas
- Snowflake schemas
- Slowly Changing Dimensions (SCD)
- Handling structured and unstructured data
- Diverse table storage engines:
- Column-based storage
- Document-based storage
- In-memory storage
Lesson 3 - Indexing in NoSQL and Data Science
- Constraints (including Primary Keys)
- Index-based scanning mechanisms
- Performance optimization and tuning
Lesson 4 - NoSQL and Unstructured Data
- Scenarios for implementing NoSQL
- Eventual consistency models
- Comparing Schema on Read vs. Schema on Write
Lesson 5 - SQL for Data Analytics
- Window functions
- Lateral Joins
- Lead and Lag functions
Lesson 6 - HiveQL Fundamentals
- SQL support capabilities
- Managing External and Internal Tables
- Executing Joins
- Data partitioning
- Correlated subqueries
- Nested queries
- Identifying use cases for Hive
Lesson 7 - Amazon Redshift
- System design and structure
- Managing locks and shared resources
- Key differences from Postgres
- Determining optimal use cases for Redshift
Requirements
- A foundational understanding of databases is required.
- Prior experience with SQL is highly beneficial.
Target Audience
- Business analysts
- Software developers
- Database developers
Testimonials (3)
Gunnar adjusted the content for the second day based on our feedback from day one. He checked in with us to find out what we liked, disliked, found hard and how we wanted to approach day 2. I liked Gunnar's style of teaching: Lecture, share examples, allowed us time to practice and answer questions before moving to the next subject. It meant we could fully understand a topic before moving onto the next subject. This reduced overload of information and gave us a chance to spend more time on the topics we struggled with and less time on the stuff we found easy.
Ffion - Complete Coherence
Course - SQL For Data Science and Data Analysis
Gunnar’s training technique is dynamic, thorough, and perfectly tailored to individual needs. In our group of five, he ensured everyone stayed on track and fully understood the material throughout the course. The knowledge and resources we gained will undoubtedly be valuable for years to come. Thank you, Gunnar!
Marcia - Complete Coherence
Course - SQL For Data Science and Data Analysis
Gunnar created a great rapport with the audience and was quick to identify our needs. He was engaging and highly knowledgeable throughout and we enjoyed his humour.
