Introduction to databases
- What are a database and a database server?
- Reading a relational model.
- Creating a table. Notions of columns and types
- Primary key and uniqueness.
- Notion of referential integrity.
- Tool for querying a database.
Exercise
Investigating the database by searching for tables, columns, and keys.
Extracting data from a table
- What is an extraction query?
- List the values to be returned.
- The WHERE clause for filtering data.
- The absence of a value (NULL marker).
- Returning unduplicated rows (DISTINCT).
- Restriction operators (BETWEEN, IN, LIKE, etc.).
Exercise
Querying multiple tables on different criteria.
Querying data from multiple tables
- Concept of joins: Returning information from multiple tables.
- Inner join and outer join.
- The “natural” join... and its difficulties.
- Assembly operators (UNION, INTERSECT...).
- Introduction to views: principle and benefits
Exercise
Creating queries with joins and assembly operators.
Ranking and statistics
- Finding aggregate values (MIN, MAX, AVG, SUM, etc.).
- Calculating relative aggregates with GROUP BY.
- Filtering aggregate values with HAVING.
- Mixing aggregates and details with OVER.
- Ranking results with RANK, ROW_NUMBER.
Exercise
Creating queries using simple and aggregate calculations. Subtotals and numbering.
Presenting and sorting data
- Presenting data from columns with aliases
- Converting from one type to another.
- Making choices using the CASE operator.
- Sorting data with ORDER BY.
- Operations on character strings and dates.
Exercise
Using functions to improve the presentation of the query result.
Using subqueries
- What is a subquery?
- Different types of results.
- Subqueries of lists and IN, ANY/SOME and ALL operators.
- Correlated subqueries.
Exercise
Writing queries that include subqueries of different forms.
3d
- 21h00 
