Course Outline
Lesson 1 - Fundamentals of SQL:
- SELECT statements
- Types of JOIN operations
- Database indexing
- Virtual tables (Views)
- Nested queries (Subqueries)
- Set operations (UNION)
- Table creation protocols
- Data ingestion processes
- Data export procedures
- Introduction to NoSQL
Lesson 2 - Data Modeling Principles:
- Entity-Relationship (ER) systems for transactional processing
- Data warehousing concepts
- Warehouse architectural models
- Star schema architecture
- Snowflake schema architecture
- Managing Slowly Changing Dimensions (SCD)
- Differentiation between structured and unstructured data
- Storage engine variations:
- Column-oriented storage
- Document-based storage
- In-memory processing
Lesson 3 - Indexing in NoSQL and Data Science Contexts
- Constraint enforcement (Primary keys)
- Index-driven data retrieval
- System performance optimization
Lesson 4 - NoSQL Paradigms and Unstructured Data Management
- Criteria for NoSQL implementation
- Eventual consistency models
- Schema-on-read versus schema-on-write approaches
Lesson 5 - SQL Applications in Data Analytics
- Window functions
- LATERAL JOIN operations
- LEAD and LAG analytical functions
Lesson 6 - HiveQL Specification
- Standard SQL compatibility
- Differentiating external and internal tables
- JOIN mechanisms within Hive
- Data partitioning strategies
- Correlated subquery execution
- Nested query structures
- Use cases for Apache Hive
Lesson 7 - Amazon Redshift Operations
- Schema design best practices
- Resource locking and sharing protocols
- Differences from PostgreSQL architectures
- Appropriate scenarios for Redshift deployment in government systems
Requirements
- Knowledge of database management systems
- Familiarity with SQL is advantageous.
Target Participants
- Business analysts
- Software engineers
- Database administrators
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.
