SQL Basics - databases and language training

Goals

This "discovery" course will enable you to understand relational databases and how they operate. You'll work with the SQL language to query the data in a base. You'll also become familiar with more advanced queries to analyze information.

Target audience

People in charge of reporting or analysis, assistants, anyone who needs to carry out simple queries or updates on a database with SQL language.

Prerequisite

No particular knowledge. Education common to all relational databases (Oracle, SQL Server, DB2, PostGreSQL, MySQL, Access, SQL Lite, etc.).

Contents

Learning objectives

• Understand the principle and contents of a relational database.

• Create queries to extract data based on different criteria.

• Produce queries with joins in order to get information from multiple tables.

• Use simple calculations and data aggregation.

• Combine results from multiple queries.

• Instructional methods.

Hands-on work

Many sequential exercises for extracting data from an example database.

Program

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.

• Links between tables and referential integrity.

• Metadata of tables, columns, and keys.

• Tool for querying a database.

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.).

Querying data from multiple tables

• Concept of joins: Returning information from multiple tables.

• Internal join. External join.

• The “natural” join... and its difficulties.

• Assembly operators (UNION, INTERSECT...).

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 and NTILE.

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.

Using subqueries

• What is a subquery?

• Different types of results.

• Subqueries of lists and IN, ANY/SOME and ALL operators.

• Correlated subqueries.

• Using CTE (Common Table Expressions) to factor subqueries.