Relational databases
A course in intermediate databases and postgresql
Relational databases - SQL and postgresql
Day 1: why databases, why postgresql, install
Day 2: postgresql configuration files, RDBMS Architecture, SQL overview, load a dataset, basic queries recap,
Day 3: SQL, Query basics, ACID, practice in IMDB and trees
Day 4: OLAP vs OLTP, Normalization, Anomalies, normal forms,
Day 5: Normalization practice
- Practice: Improve trees
- Practice: Normalization Trees dataset
- Practice: Normalization Trees dataset - solution
Day 6: windows functions
- Window Functions and CTEs Introduction
- Windows functions
- Windows functions exercises
- Windows functions exercises - solution
Day 7: CTEs
Day 8 [10/09]: Query performances
- Query performance intro
- SQL and Relational Algebra
- Query performance - Explain - Scans
- More on Bitmap Heap and Index Scans - pg_stats table
Day 9 [10/10]: Graded worksheet
- normalization
- Window functions
-
CTEs
- graded worksheet
Day 10 [10/17]: indexes, query optimizer, EXPLAIN, scan algorithms, energy database
Day 11 [10/23]: views, PL/pgSQL, functions, triggers, stored procedures
- Views and Functions Introduction
- Views Exercise
- SQL Functions COALESCE and Strings
- SQL Functions Solutions
Day 12 [10/24]: function design, PL/pgSQL, BAN API, HTTP extension
Day 13 [10/30]: transactions, ACID, triggers, authentication, roles
Day 14 [10/31]: catch up
- Database Triggers and Procedures
- PostgreSQL Authentication
- PostgreSQL Roles Management
- cloud & bigquery ?
Day 15 [11/07]: graded practice on the ADEME dataset
covers indexes and functions
everything else: