Cette formation est proposée sous forme de formation présentielle et à distance. En savoir plus
Catalogue

Discovery of SQL - Language and databases

Informatique

En collaboration avec:

Voir plus

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


Modalités

Support de cours

Le support de cours sera délivré en début du cours et peut également être téléchargé gratuitement via votre portail la veille du début du cours (téléchargez le Guide d'utilisation du portail client ici).

Certificate

By the end of the training, the trainee will receive a certificate of participation issued by the House of Training.  

Lieu
Key Job S.A.
65, Avenue de la Gare
L-1611 Luxembourg
Luxembourg
Calculer l'itinéraire