Course Outline
Module 1 - Fundamental SQL Concepts:
- SELECT statement syntax and usage
- Table join methodologies
- Index creation and management
- Virtual tables and views
- Embedded subqueries
- Set operations (UNION)
- Table structure definition
- Data ingestion procedures
- Data extraction and export
- Non-relational database principles
Module 2 - Data Modeling Frameworks:
- Entity-relationship modeling for transactional systems
- Data warehousing architecture
- Data warehouse architectural models
- Star schema design
- Snowflake schema design
- Slowly Changing Dimensions (SCD) strategies
- Distinction between structured and unstructured data
- Comparison of table storage engine types:
- Column-oriented storage
- Document-based storage
- In-memory storage
Module 3 - Indexing in NoSQL and Data Science Contexts
- Primary key constraints
- Index-driven data scanning
- Performance optimization techniques
Module 4 - NoSQL Architectures and Unstructured Data
- Criteria for selecting NoSQL solutions
- Concepts of eventual consistency
- Schema-on-read versus Schema-on-write paradigms
Module 5 - Advanced SQL for Analytical Processing
- Window functions
- Lateral joins
- Lead and Lag analytical functions
Module 6 - HiveQL Implementation
- SQL compatibility features
- Management of external and internal tables
- Join operations
- Data partitioning strategies
- Correlated subquery usage
- Nested query construction
- Appropriate use cases for Hive
Module 7 - Amazon Redshift
- System design and structural considerations
- Locking mechanisms and shared resource management
- Differences from PostgreSQL
- Optimal scenarios for Redshift deployment
Requirements
- A foundational understanding of database management systems.
- Prior experience with SQL is advantageous.
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.
