Formation et Cours en programmation SQL

Cours en programmation SQL en Français, Anglais et Néerlandais

Formation SQL

Day 1 : Manipulation dans une Table

Introduction
- Demo SQL
- Présentation de SQL
- Installation
- Connection/Déconnexion
- Utilisation de SQL

Création d'une Table
- Syntaxe SQL
- Types et Objets SQL
- Création d'une table

Interrogation d'une base I
- Clause SELECT
- Clause FROM
- Condition WHERE
- Opérateurs AND/OR

Interrogation d'une base II
- Mot clés ORDER BY
- Clause INSERT
- Clause UPDATE
- Clause DELETE

Day 2: Manipulation dans plusieurs Tables

Création de Tables
- Clause CREATE DATABASE
- Clause CREATE TABLE
- Conditions initiales
  - Contraintes
  - Clés primaires
  - Clés étrangères

Interrogation d'une base III
- Clause JOINT
- Clause UNION
- Clauses VIEWs
- Fonctions DATE

Fonctions SQL
- Les Fonctions SQL
- Fonction COUNT()
- Fonction AVG()
- Fonction SUM()
- Fonction MIN(); MAX()

Summary

------------------
A la fin de la formation, le participant pourra:

• Créer des bases de données SQL

• Insérer, modifier et effacer des données dans une table

• Interroger une table pour récupérer les informations qui y seront contenus

• Interroger plusieurs tables afin de croiser des informations stockées dans celles-ci

• Utiliser des fonctions SQL pour un meilleur rendu des données brutes de la table

 

Formation Microsoft SQL Serveur

Module 1: Installing SQL Server 2008
This module explains how to install SQL Server 2008.
Lessons
* Installing SQL Server 2008.

Lab : Installing SQL Server 2008

* Install a SQL Server.

After completing this module, students will be able to:

* Install a SQL Server 2008.
* Troubleshoot installation issues.

Module 2: Managing SQL Server 2008
This module explains how to use the standard tools for managing SQL Server installs.
Lessons

* Managing SQL Server.
* Metadata Retrieval.

Lab : Managing SQL Server 2008

* Manage SQL Server Services.
* Use Dedicated Administrator Connection.
* Retrieve Metadata.

After completing this module, students will be able to:

* Use SQL Server Configuration Manager.
* Use SQL Server Management Studio.
* Use sqlcmd utility.
* Use Dedicated Administrator Connection.
* Use Powershell.
* Use system stored procedures.
* Use catalog views.
* Use dynamic management views.
* Use built-in functions.

Module 3: Database Management
This module explains how to create and manage databases.
Lessons

* Managing Databases.

Lab : Database Management

* Create a Database.
* Delete a Database.

After completing this module, students will be able to:

* Describe various disk types such as SATA, SSD, SCSI.
* Describe storage solutions such as SAN.
* Describe common RAID levels.
* Describe the purpose of each system database.
* Modify and delete databases.
* Compress data at the row and page level.

Module 4: Security
This module explains how to administer security in SQL Server 2008.
Lessons

* Overview of Security.
* Accessing SQL Server.
* Advanced Security.

Lab : Security

* Modify SQL Server Authentication Mode.
* Configure Permissions.

After completing this module, students will be able to:

* Describe Principals and Securables.
* Create schemas.
* Change the authentication mode of SQL Server.
* Create logins.
* Create database users.
* Create roles.
* Assign principals to roles.
* Understand impersonation and delegation.
* Create linked servers.

Module 5: Disaster Recovery
This module explains how to backup and restore data in SQL Server.
Lessons

* Disaster Recovery Overview.
* Disaster Recovery Using Full Database Backups.
* Disaster Recovery Using Differential Database Backups.
* Disaster Recovery Using Transaction Log Backups.

Lab : Full Database Backups
Lab : Differential Database Backups
Lab : Transaction Log Backups

After completing this module, students will be able to:

* Perform a full database backup and restore.
* Perform a differential database backup and restore.
* Perform a transaction log backup and restore.
* Restore system databases.
* Rebuild system databases.
* Restore a page.
* Create database snapshots.

Module 6: Automation and Maintenance
This module explains how to automate routine administrative tasks and perform maintenance tasks to keep the database performing optimally.
Lessons

* SQL Server Agent Automation.
* Advanced SQL Server Automation.
* Database Maintenance.

Lab : Automation and Maintenance

* Create a Job.
* Create an Alert.
* Export Data using the Import and Export Wizard.
* Create a Maintenance Plan.

After completing this module, students will be able to:

* Create jobs.
* Create alerts.
* Configure operators.
* Configure Database Mail.
* Configure SQL Server Agent security using predefined roles.
* Create a master and target server for multiserver administration.
* Check for corruption in a database.
* Import and export data to and from SQL Server and other sources.
* Create maintenance plans.

Module 7: Indexing
This module explains how to data is quickly retrieved using SQL Server indexes.
Lessons

* Optimizing Data Retrieval.
* Types of Indexes.
* Creating, Altering, and Deleting Indexes.
* Index Maintenance.

Lab : Indexing

* Create Indexes.
* Remove Fragmentation.
* Delete Indexes.

After completing this module, students will be able to:

* Describe statistics and how the query optimizer uses them.
* Create clustered and nonclustered indexes.
* Create unique indexes.
* Modify and delete indexes.
* Create included indexes.
* Identify and remove fragmentation in an index.

Module 8: Monitoring and Performance Tuning
This module explains how to monitor activity within SQL Server and limit resources that are consumed by applications and users.
Lessons

* Areas to Monitor.
* Monitoring Tools.
* Data Collection.
* Resource Governor.

Lab : Monitoring and Performance Tuning

* Create a SQL Server Profiler Trace.
* Create a Table from Trace Data.
* Identify Missing Indexes Using Database Engine Tuning Advisor.

After completing this module, students will be able to:

* Monitor SQL Server using SQL Server Profiler, Activity Monitor, and various logs.
* Create a server-side trace.
* Identify missing indexes using Database Engine Tuning Advisor.
* Configure data collection and upload.
* Limit resource usage with Resource Governor.

Module 9: Auditing SQL Server 2008
Lessons

* Triggers.
* Service Broker and Event Notifications.
* Policy-Based Management.
* Change Data Capture.
* Change Tracking.
* SQL Server Audit.

Lab : Auditing SQL Server 2008

* Create a DDL Trigger.

After completing this module, students will be able to:

* Create DML and DDL triggers.
* Configure Service Broker.
* Create Event Notifications.
* Create a Policy.
* Configure change data capture and change tracking.
* Configure auditing.

Module 10: Upgrading to SQL Server 2008
This module explains how to successfully upgrade to SQL Server 2008.
Lessons

* Pre-Upgrade Tasks.
* Upgrade Options.
* Post-Upgrade Tasks.

After completing this module, students will be able to:

* Understand upgrade options.
* Identify pre- and post-upgrade tasks.

MySQL Training

Relational Database Basics

• Brief History of SQL
• Relational Databases
• Popular Databases
• SQL Statements

Simple SELECTs with MySQL

• Introduction to the Northwind Database
• Some Basics
• SELECTing All Columns in All Rows
• Exploring the Tables
• SELECTing Specific Columns
• Sorting Records
• The WHERE Clause and Operator Symbols
• The WHERE Clause and Operator Words
• Checking Multiple Conditions
• Writing SELECTs with Multiple Conditions

Advanced SELECTs with MySQL

• Calculated Fields
• Aggregate Functions and Grouping
• Built-in Data Manipulation Functions

Subqueries, Joins, and Unions with MySQL

• Subqueries
• Joins
• Outer Joins
• Unions

Inserting, Updating and Deleting Records with MySQL

• INSERT
• UPDATE
• DELETE

Creating and Modifying Tables with MySQL

• Creating Tables
• Datatypes
• Altering Tables
• Renaming Tables
• Dropping Tables

Views and Stored Procedures

• Creating Views with MySQL
• Dropping Views
• Creating Stored Procedures
• Benefits of Stored Procedures

 

Oracle SQL training

1. Relational Database concepts

• Concept of database
• Database Management Systems (DBMS)
• Relational Database Management Systems (RDBMS)
• SQL Engine
• SQL vs. procedural code
• Tables
• Data types
• Normalization
• First normal form (1NF)
• Primary and candidate keys
• Limitations of 1NF
• Second normal form (2NF)
• Limitations of 2NF
• Third normal form (3NF)
• Boyce Codd normal form
• Database schemes
• Logical Data Model
• Relationship types
• Physical Data Model
• Foreign key
• Referential integrity
• Introduction to queries

2. Selection Queries

• Selecting: SELECT statement
• Choosing columns and tables with SELECT
• Filtering: WHERE statement
• DISTINCT
• Alias
• Logical operators: BETWEEN
• Logical operators: ALL
• Logical operators ANY
• Logical operators: EXISTS and IN
• Comparing EXISTS and IN
• Logical operators: NOT
• Logical operators: LIKE
• Aggregate functions and GROUP BY
• Counting: COUNT
• Restricting: HAVING
• Sorting: ORDER BY
• Sub-queries
• Set operations

3. Table creation and modification

• Table creation
• CREATE TABLE syntax
• CREATE TABLE examples
• Use of CONSTRAINT
• ALTER TABLE syntax
• ALTER TABLE examples
• Introduction to Indexes
• Indexes: advantages and disadvantages
• Indexes: examples
• When to create an index
• Index creation and destruction
• Views
• Views creation and destruction
• Remove a table: DROP
• Remove table data: TRUNCATE
• INSERT
• UPDATE
• DELETE

4. Using several tables: JOIN

• Join types
• Inner Join
• Self Join
• Inner Join limitations
• Outer Joins
• Left Join
• Right Join
• Full Join
• Crossed Join and Union Join

5. Using Transactions

• Integrity
• Concurrence and locking
• Transaction control
• COMMIT and ROLLBACK
• Transaction constraints

6. Stored Procedures and Triggers

• Stored procedures
• Advantages of using a stored procedure
• Example of a store procedure
• Triggers
• Example of a trigger
• Trigger with conditions
• BLOB fields
• Query optimization
• General optimizations
• Introduction to database security

Toutes les formations en programmation:

• Formation iPhone & iPad
• Formation Android
• Formation HTML
• Formation XHTML
• Formation XML
• Formation CSS
• Formation ActionScript
• Formation Flex
• Formation JQuery
• Formation Ajax
• Formation PHP
• Formation SQL
• Formation Visual Basic
• Formation Visual Studio
• Formation C
• Formation C#
• Formation C++
• Formation ASP.net
• Formation VB.net
• Formation Web services
• Formation Java
• Formation Linux shell programming
• Formation Delphi
• Formation Cobol
• Formation Programmation OO
• Formation UML
• Formation SAS

Quelques clients: Cefora, IBM, Holcim, CBR, Total, Bpost, Cisco, Shape, Colruyt, Telelingua, Nestlé, CPAS Molenbeek, Fabricom, Club Med, OTAN, G4Securicor, Fonds de Participation, Roularta, Georg Fischer, SGC, STIB, Police Fédérale, KBC, Fortis, ING, Tetrapak, Starwood hotels, Royal Canin, GSK, ULB, Jetair, Belgacom, VRT, Banque National de Belgique, Hansgrohe, Regie des Batiments, General Electric, Hiscox, Prace, Toyota,...

Plus de Cours en programmation à Bruxelles