Discovery of SQL - Language and Databases

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

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 analyse information.

At the end of this training, the participant will be able to:

• 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

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

Exercise: Investigating the database by searching for tables, views, 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.
• Internal join. External join.
• The “natural” join... and its difficulties.
• Assembly operators (UNION, INTERSECT...)

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

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
• Sub-queries of lists and IN, ANY/SOME and ALL operators
• Correlated sub-queries
• Using CTE (Common Table Expressions) to factor sub-queries

Exercise: Writing queries that include sub-queries of different forms. Creating views.

At the end of the training, the participants will receive a certificate of attendance delivered by the House of Training.

Many sequential exercises for extracting data from an example database.