Entraînement Algorithmique et SQL - Modélisation Merise

Ce document constitue un entraînement complet destiné aux étudiants de BTS CGO 2A, préparant l'épreuve d'Organisation du Système d’Informations. Il s'agit d'un examen blanc conçu pour évaluer et consolider les compétences fondamentales en gestion des données et en logique de programmation, essentielles à leur parcours académique et professionnel.

Il couvre les notions suivantes :

La manipulation de bases de données relationnelles via des requêtes SQL, de la sélection simple aux opérations complexes de jointures et de sous-requêtes.
La conception et l'implémentation d'algorithmes et de fonctions pour résoudre divers problèmes, incluant la gestion de règles métier et le calcul numérique.

Modélisation Merise : Entraînement Algorithmique et SQL

Télécharger PDF

Entraînement BTS BLANC : Algorithmique et SQL

SQL : Requêtes et Gestion de Données

Schéma Relationnel

Le schéma relationnel des bases de données utilisé pour les requêtes est le suivant :

ARTICLES (NOART, LIBELLE, STOCK, PRIXINVENT) : Cette table contient les informations sur les articles, incluant leur numéro d'identification, leur libellé, la quantité en stock et leur prix d'inventaire unitaire.
FOURNISSEURS (NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR) : Cette table répertorie les fournisseurs avec leur numéro d'identification, leur nom, leur adresse et la ville où ils sont situés.
ACHETER (NOFOUR#, NOART#, PRIXACHAT, DELAI) : Il s'agit d'une table de liaison (ou table associative) qui enregistre les relations entre les articles et les fournisseurs. Elle contient le numéro du fournisseur, le numéro de l'article, le prix d'achat de cet article auprès de ce fournisseur et le délai d'approvisionnement en jours. Les champs avec un dièse (#) sont des clés étrangères.

Vous trouverez ci-dessous des exemples de requêtes SQL. Leur difficulté est progressive.

1.1. Articles avec un stock inférieur à 10

Sélectionnez les numéros et libellés des articles dont le stock est inférieur à 10.

SELECT NOART, LIBELLE
FROM ARTICLES
WHERE STOCK<10;

Cette requête utilise la clause WHERE pour filtrer les enregistrements de la table ARTICLES, ne retenant que ceux dont la valeur du champ STOCK est strictement inférieure à 10.

1.2. Articles avec un prix d'inventaire entre 100 et 300

Obtenez la liste complète des articles dont le prix d'inventaire est compris entre 100 et 300 (bornes incluses).

SELECT *
FROM ARTICLES
WHERE PRIXINVENT BETWEEN 100 AND 300;

L'opérateur BETWEEN est une manière concise de spécifier un intervalle de valeurs. Il est inclusif, c'est-à-dire qu'il inclut les valeurs des bornes.

1.3. Fournisseurs sans adresse connue

Trouvez les fournisseurs pour lesquels l'adresse n'a pas été renseignée dans la base de données.

SELECT *
FROM FOURNISSEURS
WHERE ADRFOUR IS NULL;

La condition IS NULL est utilisée pour vérifier si une colonne contient une valeur nulle (absence de donnée).

1.4. Fournisseurs dont le nom commence par "STE"

Listez tous les fournisseurs dont le nom commence par la séquence de caractères "STE".

SELECT *
FROM FOURNISSEURS
WHERE NOMFOUR LIKE 'STE%';

L'opérateur LIKE est utilisé pour la recherche de motifs dans les chaînes de caractères. Le symbole % est un caractère générique qui représente zéro, un ou plusieurs caractères.

1.5. Fournisseurs avec délai d'approvisionnement supérieur à 20 jours

Affichez les noms, adresses et villes des fournisseurs qui proposent des articles pour lesquels le délai d'approvisionnement est supérieur à 20 jours.

SELECT NOMFOUR, ADRFOUR, VILLEFOUR
FROM FOURNISSEURS AS F, ACHETER AS A
WHERE F.NOFOUR=A.NOFOUR
AND DELAI>20;

Cette requête réalise une jointure implicite entre les tables FOURNISSEURS et ACHETER sur leur clé commune NOFOUR, puis filtre les résultats en fonction du délai d'approvisionnement.

1.6. Nombre total d'articles référencés

Comptez le nombre total d'articles différents référencés dans la base de données.

SELECT COUNT(*) AS NbArticles
FROM ARTICLES;

La fonction d'agrégation COUNT(*) retourne le nombre total de lignes dans une table ou dans un groupe de lignes.

1.7. Valeur totale du stock

Calculez la valeur totale de l'ensemble du stock, en multipliant la quantité en stock de chaque article par son prix d'inventaire, puis en sommant tous ces produits.

SELECT SUM(STOCK*PRIXINVENT) AS ValeurStock
FROM ARTICLES;

La fonction d'agrégation SUM() calcule la somme des valeurs d'une colonne ou d'une expression pour un ensemble de lignes.

1.8. Articles triés par stock décroissant

Obtenez les numéros, libellés et quantités en stock des articles, classés par ordre décroissant de leur stock.

SELECT NOART, LIBELLE, STOCK
FROM ARTICLES
ORDER BY STOCK DESC;

La clause ORDER BY est utilisée pour trier les résultats d'une requête. Le mot-clé DESC (pour "descendant") indique un tri dans l'ordre décroissant.

1.9. Prix d'achat maximum, minimum et moyen par article

Pour chaque article, affichez son numéro et son libellé, ainsi que le prix d'achat maximum, minimum et moyen enregistré.

SELECT A.NOART, LIBELLE, MAX(PRIXACHAT) AS PMAX, MIN(PRIXACHAT) AS PMIN, AVG(PRIXACHAT) AS PMOY
FROM ACHETER A, ARTICLES B
WHERE A.NOART = B.NOART
GROUP BY A.NOART, LIBELLE;

Les fonctions d'agrégation MAX(), MIN() et AVG() sont utilisées ici en combinaison avec la clause GROUP BY. GROUP BY regroupe les lignes ayant les mêmes valeurs dans les colonnes spécifiées, permettant ainsi d'appliquer les fonctions d'agrégation à chaque groupe.

1.10. Délai moyen par fournisseur (au moins 2 articles)

Calculez le délai moyen d'approvisionnement pour chaque fournisseur qui propose au moins deux articles différents.

SELECT A.NOFOUR, NOMFOUR, AVG(DELAI) AS DelaiMoyen
FROM ACHETER AS A, FOURNISSEURS AS F
WHERE A.NOFOUR = F.NOFOUR
GROUP BY A.NOFOUR, NOMFOUR
HAVING COUNT(*) >=2;

La clause HAVING est similaire à WHERE, mais elle est utilisée pour filtrer les groupes de lignes après que la clause GROUP BY a été appliquée. Ici, elle assure que seuls les fournisseurs proposant au moins deux articles sont inclus.

1.11. Prix minimum de chaque article avec le fournisseur correspondant

Trouvez le prix d'achat le plus bas pour chaque article et identifiez le fournisseur qui propose ce prix minimum.

SELECT A1.NOART, A.LIBELLE, A1.PRIXACHAT, F.NOMFOUR
FROM ACHETER AS A1, ARTICLES AS A, FOURNISSEURS AS F
WHERE A1.NOART = A.NOART
AND A1.NOFOUR = F.NOFOUR
AND A1.PRIXACHAT = (
SELECT MIN(A2.PRIXACHAT)
FROM ACHETER AS A2
WHERE A2.NOART = A.NOART
);

Cette requête utilise une sous-requête corrélée dans la clause WHERE. Pour chaque ligne de la requête externe, la sous-requête est exécutée pour trouver le prix d'achat minimum spécifique à l'article en cours de traitement.

1.12. Articles disponibles chez plusieurs fournisseurs

Énumérez les articles qui peuvent être achetés auprès de plusieurs fournisseurs, en indiquant pour chacun le prix d'achat et le délai d'approvisionnement correspondant, ainsi que le nom du fournisseur.

SELECT DISTINCT A.NOART, AR.LIBELLE, ACH.PRIXACHAT, ACH.DELAI, F.NOMFOUR
FROM ACHETER ACH
JOIN ARTICLES AR ON ACH.NOART = AR.NOART
JOIN FOURNISSEURS F ON ACH.NOFOUR = F.NOFOUR
WHERE ACH.NOART IN (
SELECT NOART
FROM ACHETER
GROUP BY NOART
HAVING COUNT(DISTINCT NOFOUR) > 1
);

Cette requête utilise une sous-requête pour identifier les articles qui sont fournis par plus d'un fournisseur (COUNT(DISTINCT NOFOUR) > 1). Ensuite, la requête principale joint les tables pour récupérer tous les détails pertinents pour ces articles.

1.13. Fournisseurs offrant le plus de produits

Identifiez le ou les fournisseurs qui proposent le plus grand nombre d'articles différents.

SELECT F.NOFOUR, F.NOMFOUR, COUNT(*) AS nombre_produits
FROM FOURNISSEURS AS F, ACHETER AS A
WHERE F.NOFOUR = A.NOFOUR
GROUP BY F.NOFOUR, F.NOMFOUR
HAVING COUNT(*) = (
SELECT MAX(nb_prod) FROM (
SELECT COUNT(*) AS nb_prod
FROM ACHETER
GROUP BY NOFOUR
) AS B
);

Cette requête est composée de plusieurs niveaux de sous-requêtes. La sous-requête la plus interne calcule le nombre de produits par fournisseur. La sous-requête intermédiaire trouve le nombre maximum de produits parmi tous les fournisseurs. Enfin, la requête externe sélectionne tous les fournisseurs qui atteignent ce nombre maximum.

Algorithmique : Conception de Fonctions et Procédures

Les exercices suivants visent à rédiger des algorithmes ou des fonctions répondant à des règles de gestion spécifiques.

2.1. Algorithme de Calcul de Réduction

Rédigez un algorithme nommé CalculReduction qui satisfait aux règles de gestion ci-après décrites :

RG1 : L'algorithme demande à l'utilisateur de saisir le montant TTC initial de sa commande et vise à afficher : le montant HT définitif de la commande, le montant de TVA et le montant TTC définitifs de la commande.
RG2 : La TVA est supposée au taux de 20%.
RG3 : La réduction est calculée sur la base du montant TTC initial.
RG4 : La politique de remise est la suivante :
- De 0 à 100 euros (exclus) d'achats : aucune remise.
- De 100 à 300 euros (exclus) d'achats : 5% de remise.
- Au-delà de 300 euros d'achats : 10% de remise.

Explication : Cet algorithme nécessite une étape de saisie utilisateur, suivie d'une série de conditions (SI...ALORS...SINON SI...ALORS...SINON) pour déterminer le taux de réduction applicable. Une fois la réduction calculée, il faut en déduire le montant TTC final, puis le montant HT et la TVA.

2.2. Fonction PuissancePositive(a, n)

Rédigez une fonction qui retourne le réel a élevé à la puissance de l'exposant n, où n est un entier positif ou nul. Les nombres a et n sont fournis en paramètres d'entrée de la fonction.

Fonction PuissancePositive(a : réel, n : entier naturel) : réel

Explication : Pour implémenter cette fonction, on peut utiliser une boucle (par exemple, POUR ou TANT QUE) qui effectue n multiplications de a par lui-même. Le cas particulier où n est 0 doit retourner 1 (sauf si a est 0, où le résultat est indéfini ou 0 selon les conventions).

2.3. Fonction Puissance(a, n) (avec exposant négatif)

En vous inspirant de la question 2.2., rédigez la fonction suivante, retournant le réel a élevé à la puissance d'exposant n, avec n positif, négatif ou nul.

Fonction Puissance(a : réel, n : entier) : réel

Explication : Cette version de la fonction Puissance doit étendre la logique précédente pour gérer les exposants négatifs. Si n est négatif, le résultat est 1 / PuissancePositive(a, -n). Des précautions doivent être prises si a est nul et n négatif (division par zéro).

2.4. Algorithme de Jeu "Plus ou Moins"

Rédigez l’algorithme qui satisfait aux règles de gestion suivantes :

RG1 : L’algorithme vise à faire deviner un nombre à l’utilisateur (compris entre 0 et 9999). Ce nombre est choisi aléatoirement au moyen de la fonction Alea(n, m), laquelle retourne un entier compris entre n et m.
RG2 : À chaque tour de boucle, l’algorithme demande à l’utilisateur de saisir une solution (c'est-à-dire la valeur qu’il pense que le nombre à deviner a).
RG3 : Si l’utilisateur n’a pas deviné, on lui affiche le message « Plus ! » ou « Moins ! » selon si sa proposition est inférieure ou supérieure au résultat attendu.
RG4 : L’algorithme se termine lorsque l’utilisateur a deviné, après avoir affiché le message « Gagné ! ».

Explication : Cet algorithme est un exemple classique de jeu interactif impliquant une boucle conditionnelle (TANT QUE) qui se poursuit tant que la bonne réponse n'est pas trouvée. Des tests conditionnels (SI...ALORS...SINON) sont utilisés pour donner des indices à l'utilisateur.

Foire Aux Questions (FAQ)

Qu'est-ce qu'une jointure en SQL et quand l'utiliser ?

Une jointure en SQL est une opération qui combine des lignes de deux ou plusieurs tables basées sur une colonne de relation entre elles. Elle est utilisée lorsque vous avez besoin de récupérer des données qui sont réparties dans différentes tables liées, par exemple, pour afficher le nom d'un fournisseur avec les détails des articles qu'il propose.

Comment optimiser une requête SQL lente ?

L'optimisation d'une requête SQL implique plusieurs techniques : s'assurer que les index sont correctement définis sur les colonnes utilisées dans les clauses WHERE, JOIN, ORDER BY et GROUP BY ; éviter les sous-requêtes corrélées si des alternatives non corrélées ou des jointures peuvent être utilisées ; limiter le nombre de colonnes sélectionnées avec SELECT * ; et analyser le plan d'exécution de la requête pour identifier les goulots d'étranglement.

Quelle est l'importance des règles de gestion (RG) en algorithmique ?

Les règles de gestion sont des contraintes ou des principes définis par le métier qui régissent le comportement d'un système. En algorithmique, elles sont cruciales car elles servent de spécifications claires pour le développeur. Elles garantissent que l'algorithme ou le programme développé répondra précisément aux besoins fonctionnels et aux logiques métier attendues, assurant ainsi la pertinence et la validité de la solution.