SQL-courses

SQL introduction course, first part of the UE "Programming for data science", 5 sessions <-> 5 notebooks.

S1 - SQL basics (part 1)

notebook #1

• definitions and principles of relational models
• entities relationships diagram (ERD)
• database management systems (DBMS)
• introduction to SQLite
• SELECT … FROM, DISTINCT, LIMIT, etc.
• COUNT(), SUM(), AVG(), etc.
• WHERE, BETWEEN, LIKE, IS NOT, etc.
• GROUP BY, ORDER BY, HAVING, etc.

S2 - SQL basics (part 2)

notebook #2

• PRAGMAS
• CREATE TABLE, INSERT INTO, VALUES, etc.
• import .csv into DB, pandas.DataFrame.to_sql(), pandas.read_sql(), etc.
• UPDATE, DELETE, DROP
• UNION
• string functions
• datetime functions
• joins
• CASE WHEN, WITH,
• windows functions, OVER

S3 - corrections

notebook #3

Correction of the exercises from the first two sessions.

S4 - exercices

notebook #4 : statements notebook #4b : answers

• ALTER TABLE
• foreign key constraints
• Exercices with mock-up data : create a database, store data, list tables and columns, check data quality (missing values…), simple statistics, build metrics…

S5 - SQLAlchemy - NoSQL

notebook #5

Two parts :

• a short introduction to the SQLAlchemy library : connect to a database, models
• NoSQL basics : definition, overview and some examples with unqlite (basic operations, cursors, transactions)

Graded homework

• notebook (statements)
• data