In collaboration with:
Description
Introduction
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.
Objectives
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
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.
Programme
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.
Hands-on work
Many sequential exercises for extracting data from an example database.
Prerequisites
No particular knowledge. Education common to all relational databases (Oracle, SQL Server, DB2, PostGreSQL, MySQL, Access, SQL Lite, etc.).
Conditions
Course Material
The training material will be handed out at the beginning of the course and can also be downloaded free of charge via your portal the day before the start of the course (download the Client Portal User’s Guide here).
Certificate
By the end of the training, the trainee will receive a certificate of participation issued by the House of Training.
Location
L-1611 Luxembourg
Luxembourg
Sessions and schedules
Would you like to stay up to date on our training offer?
Subscribe to our newsletter