Relational databases - SQL and postgresql

Day 1: why databases, why postgresql, install

• welcome
• install
• wrap up

Day 2: postgresql configuration files, load a dataset, basic queries recap,

• postgres server
• loading dataset
• wrap up

Day 3: SQL, Query basics, ACID, practice in IMDB and trees

• SQL and query basics
• Explore IMDB
• Improve trees

Day 4: ERD, OLAP vs OLTP, Normalization, Anomalies, normal forms,

• normalization
• normalization WorldHits dataset
• normalization Trees dataset

Day 5: windows functions and CTEs

• Window Functions and CTEs Introduction
• Windows functions
• CTEs
• Window Functions CTEs Lab

Day 6: Query performance, planner, relation set theory

• Query performance intro
• Query performance - Explain - Scans

Day 7: indexes, query optimizer, EXPLAIN, scan algorithms, energy database

• Database Indexes Introduction
• Bitmap Heap and Index Scans
• Energy Production Database
• Database Indexes Implementation
• Database Scan Algorithms Quiz
• Indexing Practice Lab

Day 8: views, PL/pgSQL, functions, triggers, stored procedures

• Views and Functions Introduction
• PostgreSQL Views
• Views Exercise
• Views Exercise Solutions
• SQL Functions COALESCE and Strings
• SQL Functions Solutions
• PL/pgSQL Functions
• SHA256 Functions Worksheet

Day 9: function design, PL/pgSQL, BAN API, HTTP extension

• Writing Scalable Functions
• BAN API Integration Workshop
• Installing HTTP Extension Windows

Day 10: transactions, ACID, triggers, authentication, roles

• Outstanding Database Topics
• Database Triggers and Procedures
• PostgreSQL Authentication
• PostgreSQL Roles Management

Day 11: catch up

• cloud & bigquery ?

Day 12: course review, ADEME database, comprehensive practice

• Final Lab Session
• ADEME Database Worksheet