Exercice cours 4
L’objectif est de mettre en pratique la création d’index dans une base de données et d’appliquer correctement les fonctions d’agrégation.
Exercice 1
Créez une base de données “exercice_cours_4”.
Exercice 2
Créez une table “Employe”. Les champs sont les suivants: id_employé (clé primaire avec un index clustered), nom, prenom, salaire, departement
Exercice 3
Ajouter les employés suivant:
-Lack, Jayton, 30000, Marketing
-Mauline, Parois, 120000, Finance
-Arrack, Bobama, 60000, Marketing
-Tonald, Drump, 150000, Production
-Tustin Judeau, 34000, Production
-Lancois, Fegault, 45000, Marketing
-Patimir, Vloutine, 90000, Finance
-Chugo, Havez, 110000, Finance
-Sicolas, Narkozi, 70000, Finance
Exercice 4
Créez un index de type nonclustered sur le champs “salaire”
Exercice 5
Pour chaque département, afficher les informations suivantes:
-La moyenne des salaires
-Le salaire le plus élevé
-Le salaire le moins élevé
-Le nombre d’employé par département
-Le salaire moyen en ne comptant que les personnes possédant un “a” dans leur prénom
-Le salaire de l’employé qui gagne le plus en affichant uniquement les departement ou ce salaire est d’au moins 100 000$
-Le nombre d’employé, mais uniquement pour les département dont le salaire de l’employé qui gagne le plus est inférieur à 100 000$.
Exercice 6
Exercice 6.1
Créez une table “repertoire”. Celle-ci a une clé primaire id_repertoire (valeur numérique, auto-incrémenté), un nom (varchar[50]) et une clé étrangère “repertoire_parent” (Qui fait référence à la clé primaire de la même table.
Exercice 6.2
Ajouter les entrées suivantes:
‘C:’, null
‘Devoir’, 1
‘Musique’, 1
‘SQL’, 2
‘CCNA’, 2
Exercice 6.3
Créez une table “fichier”. Celle-ci a une clé primaire id_fichier (Numérique, auto-incrémentée), un nom (varchar[50]) et une clé étrangère “repertoire_parent” qui fait référence à la clé primaire de la table “repertoire”
Exercice 6.4
Ajouter les entrées suivante:
-‘sys32.dll’,1
-‘note_de_cours.pdf’, 2
-‘Linkin_park.mp3’, 3
-‘Billy_Talent.mp3’, 3
-‘examen1.sql’, 4
Exercice 6.5
Faire une requête qui affiche le nom de tous les répertoire dans une colonne, et le nom de leur répertoire parent dans une 2eme colonne (Null s’il n’y en a pas).
Exercice 6.6
En utilisant une jointure, afficher le nom de tous les fichiers dans le répertoire qui porte le nom ‘c:’
Exercice 6.7
En utilisant une jointure, afficher le nombre de fichier dans chacun des répertoire.
Exercice 6.8
Créez des index du type approprié sur les champs approprié pour maximiser la performance de la base de données.
Exercice 6.9
Créez une vue à partir des requêtes des exercices 6.6 et 6.7. Utiliser des alias si nécessaire.
Exercice 7
Exercice 7.1
Créez une table “ville”. Celle-ci à une clé primaire id_ville (numérique, auto-incrémentée), un nom (varchar[50]) et une population (int)
Exercice 7.2
Insérez les valeurs suivantes:
-‘Gatineau’, 284557
-‘Montréal’, 1780000
-‘Québec’, 542298
Exercice 7.3
Créez une table “citoyen”. Celle-ci a une clé primaire “id_citoyen” (numérique, auto-incrémentée). un nom (varchar), un prenom (varchar), un age (int), un salaire (int), un id_ville (clé étrangère de la table “ville”).
Exercice 7.4
Insérez les valeurs suivantes:
-‘Tremblay’, ‘Maurice’, 18, 95000, 1
-‘Rodriguez’, ‘Alexandre’, 22, 65000, 1
-‘Plouffe’, ‘Antonio’, 36, 80000, 1
-‘Dagenais’, ‘Marie’, 26, 180000, 2
-‘Pétrin’, ‘Robert’, 62, 80000, 2
-‘Cordonier’, ‘Jocelyn’, 43, 25000, 3
-‘Marchildon’, ‘Ginette’, 52, 30500, 3
Exercice 7.5
Afficher le nom et prénom de chaque citoyen et de sa ville.
Exercice 7.6
Afficher le nombre de citoyen par ville
Exercice 7.7
Afficher la moyenne des salaires par ville
Exercice 7.8
Afficher le salaire le plus élevé par ville, mais uniquement des villes sont la moyenne des salaires est supérieure à 100 000$.
Exercice 7.9
Afficher la moyenne d’age par ville en incluant uniquement les citoyen agés de 25 à 60 ans inclusivement.
Exercice 7.10
En utilisant une requête imbriquée, afficher les noms et prénoms des citoyen faisant parti d’une ville ayant une population supérieure à 1 million.
Exercice 7.11
Créer des index du type approprié sur les champs approprié pour maximiser la performance de la base de données.
Exercice 7.12
Créer une vue à partir de la requête de l’exercice 7.9 et 7.10. Utiliser des alias si necessaire.
Exercice 8
Exercice 8.1
Créer une table “conducteur”. Celle-ci a un id_conducteur (clé primaire numérique auto-incrémentée), un nom et un prenom (varchar de longueur 50), et une nombre d’annee_experience_de_conduite (int).
Exercice 8.2
Ajouter les valeurs suivantes dans la table:
-Mageau, Alexandre, 12
-Tremblay, Martin, 20
-Rodriguez, Antonio, 7
Exercice 8.3
Créez la table “vehicule”. Celle-ci a un id_vehicule (clé primaire numérique auto-incrémenté), une marque (varchar), un modèle (varchar), une année (int), un kilométrage (int) et un id_conducteur (clé étrangère sur la table “conducteur”).
Exercice 8.4
Ajoutez les valeurs suivantes:
-Toyota, Sienna, 2005, 200304, 1
-Toyota, Corolla, 1997, 425000, 2
-Honda, Civic, 2005, 20125, 2
-Honda, Accord, 2013, 80365, 3
Exercice 8.5
Afficher le nombre moyen d’année d’expérience de conduite du conducteur pour chaque marque de vehicule.
Exercice 8.6
Afficher le nombre moyen d’année d’expérience de conduite du conducteur pour chaque marque de vehicule, en excluant du calcul les véhicules dont l’année est supérieure à 2009.
Exercice 8.7
Afficher le nombre moyen d’année d’expérience de conduite du conducteur pour chaque marque de vehicule, en excluant les résultats ou le nombre moyen d’année d’expérience de conduite est inférieure ou égal à 15.
Exercice 8.8
Afficher le nombre moyen d’année d’expérience de conduite du conducteur pour chaque marque de vehicule, en excluant les résultats ou le nombre moyen d’année d’expérience de conduite est inférieure ou égal à 15, ainsi que les vehicules le kilométrage est inférieur à 200000.
Exercice 9.9
En utilisant une requête imbriquée, afficher les noms et prénoms des conducteurs qui conduisent un véhicule de marque “Toyota”.
Exercice 8.10
Créez les index sur les champs appropriés.
Exercice 8.11
Créez une vue à partir des requêtes des exercices 8.5, 8.6, 8.7, 8.8 et 8.9. Faite une démonstration de leur utilisation.
Exercice 9
Exercice 9.1
Créez une table “cours”. Celle-ci a un champs id_cours (clé primaire, numérique, auto-incrémentée) ainsi qu’un titre (varchar).
Exercice 9.2
Insérez les cours suivant dans la table “cours”:
-SQL
-PHP
-C#
Exercice 9.3
Créez la table “eleve”. Celle-ci a un champs id_eleve (clé primaire, numérique, auto-incrémentée), ainsi qu’un nom et un prenom (varchar).
Exercice 9.4
Ajoutez les élèves suivant:
-Tremblay, Alexandre
-Rodriguez, Antonio
-Cordonier, Marie
Exercice 9.5
Créez une table “cours_eleve”. Il s’agit d’une table de jointure entre “eleve” et “cours”. Celle-ci a un champs id_cours_eleve (clé primaire, numérique, auto-incrémentée) ainsi qu’une clé étrangère id_cours, une clé étrangère id_eleve, et une note (int). On peut donc connaitre quelle note un élève X a eu dans un cours Y.
Exercice 9.6
Ajoutez les valeurs suivantes:
-1,1,60
-2,1,72
-3,1,80
-1,2,83
-3,2,90
1,2,67
-2,3,70
-3,3,76
Exercice 9.7
Afficher pour chaque élève (nom et prénom) la note qu’il a obtenu dans chaque cours.
Exercice 9.8
Afficher la moyenne que chaque élève a dans ses cours. On peut identifier un élève par son nom.
Exercice 9.9
Afficher la moyenne que chaque élève a dans ses cours. On peut identifier un élève par son nom. Enlever la moyenne des élèves dont la note la plus faible est inférieur à 65.
Exercice 9.10
Créez le ou les index nécessaires pour optimiser les performances de votre base de données.
Exercice 9.11
En utilisant une requête imbriqué, afficher les nom et prénom des élèves qui suivent le cours de PHP.
Exercice 9.12
Créez une vue à partir de la requête de l’exercice 9.11