History, versions and standardization of SQL language
Notion of instance
Structure of an instance: database, schemas and
main objects DBMS (Database Management Systems)

Conceptual data model: tables and relations
Creation of the database
Creation of tables
Types of columns
Addition of constraints
Addition of index, utility

Workshop: design and creation of an example database

Inserting data
Editing data
Deleting data

Workshop: creation and manipulation of data in the test base

Data selection: general syntax
Attributes
– Operator *
– Attribute aliases
– Operator DISTINCT
FROM clause and table aliases
Combination of filters: AND and OR
Notion of predicate
LIKE predicate
IN and NOT INT predicates
Taking into account NULLs: IS NULL predicate
ORDER BY
OFFSET / FETCH / LIMIT clause

Workshop: querying tables – handling the different predicates addressed – sorting previous extractions

Scalar functions

Functions for managing character strings
 Arithmetic functions

Date functions

Aggregate function: definition, use
Main aggregate functions

Workshop: handling of the various functions for managing character strings and dates – use of the SUM, MIN and MAX functions

Creation of groupings: GROUP BY
Filter on groupings: HAVING

Workshop: use of the SUM, MIN and MAX functions on groupings

Principle of
subqueries Simple
subqueries Correlated subqueries
Predicates EXISTS / NOT EXISTS

Lab: handling sub-queries

Common Table Expressions (CTE): Problem, use
Syntax

Notion of games
UNION
operator INTESECT
operator EXCEPT or MINUS operator (Oracle)

Notion of Cartesian product
Notion of join
Inner
join Self-join
Outer joins

Workshop: handling the different types of joins

Presentation
Window configuration: OVER clause
– PARTITION BY predicate –
ORDER BY
predicate
– ROW BETWEEN predicate – RANGE BETWEEN predicate
– GROUP BETWEEN predicate
– Exclusion option EXCLUDE

Workshop: interests of windowing functions

Views: simplify and predict access
Optimization: how to study a structure and act on performance
Standardization and abstractions: a system ready to evolve
Definition of stored procedures
Definition of functions

Workshops: Writing and querying views – Implementing stored procedures and functions then writing calls.

Passing of the certification (if foreseen in the financing)