Payment by bank transfer · Moroccoتعلَّم · مارِس · أتقِن

Catalogue / Development

SQL: Querying, Modelling and Running a Database

Query, model and protect a relational database with SQL, from SELECT to window functions, up to PostgreSQL in production (indexes, transactions, permissions, backups), based on a real Moroccan online store.

  • Beginner to advanced
  • 14 lessons
  • 13 h 45 min

A MasterLinux course · Original text

What you will learn

  • Understand the relational model (tables, rows, keys) and where PostgreSQL, MySQL/MariaDB, SQLite and SQL Server fit
  • Write precise SELECT queries with filters, sorting, limits and correct handling of NULL
  • Create solid tables with the right types and constraints (primary key, NOT NULL, UNIQUE, CHECK, DEFAULT)
  • Modify data safely with INSERT, UPDATE and DELETE, including transactions
  • Produce dashboards with GROUP BY, HAVING, joins, subqueries, CTEs and UNION
  • Model a normalized online store (1NF, 2NF, 3NF) with foreign keys
  • Calculate rankings, running totals and monthly revenue with window functions and dates
  • Run PostgreSQL in production — indexes and EXPLAIN, isolation, roles and GRANT, JSONB, parameterized queries, pg_dump and pg_restore

Course content

14 lessons · 13 h 45 min
Section 1 — First steps in SQL4 lessons · 3 h
  1. Relational databases: tables, rows, keys and DBMS Preview35 min
  2. SELECT : choisir les colonnes, filtrer, trier, limiter 45 min
  3. Créer des tables : types et contraintes 50 min
  4. INSERT, UPDATE, DELETE : modifier les données en sécurité 50 min
Section 2 — Querying and modelling5 lessons · 4 h 40 min
  1. Agrégations : COUNT, SUM, AVG, GROUP BY et HAVING 50 min
  2. Jointures : INNER, LEFT, RIGHT, FULL et auto-jointure 55 min
  3. Sous-requêtes, CTE (WITH) et opérations ensemblistes (UNION, INTERSECT, EXCEPT) 55 min
  4. Modéliser : clés étrangères, normalisation (1FN, 2FN, 3FN) et schéma d'une boutique 1 h
  5. Fonctions de fenêtre (ROW_NUMBER, RANK, SUM OVER) et dates 1 h
Section 3 — PostgreSQL and performance3 lessons · 3 h
  1. Index et performance : EXPLAIN, B-tree et quand indexer 1 h
  2. Transactions et concurrence : ACID, BEGIN/COMMIT/ROLLBACK, niveaux d'isolation 1 h
  3. PostgreSQL en pratique : psql, rôles et droits (GRANT), schémas, JSONB 1 h
Section 4 — Security and backups1 lesson · 1 h 5 min
  1. Sécurité : injection SQL, requêtes paramétrées, moindre privilège, sauvegardes 1 h 5 min
Section 5 — Project1 lesson · 2 h
  1. Projet : la base complète d'une boutique en ligne et 10 requêtes métier 2 h

Requirements

  • Able to use a computer and a text editor
  • Terminal basics help for the labs (the “Fundamentals” course is recommended)
  • No prior database knowledge required

Who is this course for?

  • Web and mobile developers who need to write reliable queries for their applications
  • System administrators who host and back up PostgreSQL or MySQL databases
  • Students (BTS, DUT, bachelor’s degree, engineering schools) who want a practical, solid foundation in SQL
  • Business owners, accountants and analysts who want to pull their own figures from their store’s database