Relational databases - SQL and postgresql

A course in intermediate databases and postgresql taught at Epita

Day 1: why databases, why postgresql, install

• welcome
• install
• wrap up

Day 2: postgresql configuration files, RDBMS Architecture, SQL overview, load a dataset, basic queries recap,

• postgres server
• RDBMS & SQL
• loading dataset
• wrap up

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

• SQL Queries - Explore IMDB

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

• OLAP vs OLTP + normalization + anomalies

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

• CTEs
• CTEs Lab
• CTEs Lab - solution

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

• Database Indexes Introduction

• Review with the Database Scan Algorithms Quiz

• Database Indexes Implementation

• Indexing Practice Lab

Day 11 [10/23]: 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

Day 12 [10/24]: function design, PL/pgSQL, BAN API, HTTP extension

• PL/pgSQL Functions
• SHA256 Functions Worksheet
• Writing Scalable Functions

Day 13 [10/31]: transactions, ACID, triggers, authentication, roles

• Installing HTTP Extension Windows
• BAN API Integration Workshop

Day 14 [11/07]: Big Query

• BigQuery
• SQL anti patterns
• New york trees

see also:

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

Day 15 [11/10]: graded exam on the New York trees dataset

• Exam New york trees