SGBD – SQL - TP: SELECT MONO-TABLES -- -------------------------------------------------------------------- Méthodo : Mettez les réponses directement dans ce fichier, à la suite des questions. => ça permet de garder une une copie de votre travail. => faites des copier coller de votre travail dans le client mysql pour tester Le fichier répond déjà aux 2 premières questions du TP : => vous devez utiliser le même formalisme pour les autres questions. Identifier la clé primaire de la table de résultats pour chaque requêtes => mettez en commentaire (par exemple) : --CP : NE Pour les réquêtes, appliquez le principes suivant pour choisir les colonnes à projeter : Clé Primaire, Clé d''Usage, attributs demandés, attributs de restriction, attributs de tri -- -------------------------------------------------------------------- Outils Utilisez un éditeur efficace qui fasse de la coloration syntaxique : sublimtext ou VS Code (Visual). Mettez une extension .sql à vos fichiers pour avoir la coloration syntaxique. -- -------------------------------------------------------------------- On travaille sur la BD correspond à la table suivante : CREATE TABLE employes ( id INT AUTO_INCREMENT, nom VARCHAR(50) NOT NULL, fonction VARCHAR(50) NOT NULL, date_embauche DATE NOT NULL, salaire DECIMAL(10, 2) NOT NULL, -- DECIMAL plutôt que FLOAT pour une précision monétaire commission DECIMAL(10, 2), id_departement INT NOT NULL, id_employe_chef INT, PRIMARY KEY(id) # bon usage : la primary key à la fin ); Le code complet de la BD est accessible ici : http://bliaudet.free.fr/IMG/txt/BD_EmployesTP01.sql -- -------------------------------------------------------------------- Les requêtes -- -------------------------------------------------------------------- 1. Tous les employés avec tous leurs attributs SELECT * FROM employes ; -- CP : NE 2. Tous les employés SELECT id, nom FROM employes ; -- CP : NE -- -------------------------------------------------------------------- 3. Tous les employés avec tous leurs attributs triés par ordre alphabétique 3bis. Tous les employés triés par ordre alphabétique 4. Tous les employés triés par n° de département décroissant 4bis. Tous les employés triés par n° de département décroissant et par ordre alphabétique 5. Tous les employés avec leurs salaires triés par salaire décroissant -- -------------------------------------------------------------------- 6. Tous les employés du département 30 avec tous leurs attributs 7. Tous les employés du département 30 triés par ordre alphabétique 8. Tous les managers (c''est une fonction) du département 20 8bis. Tous les managers (c''est une fonction) du département 20 et du département 30. 9. Tous les employés qui ne sont pas managers et qui sont embauchés en 1981 -- -------------------------------------------------------------------- 10. Toutes les fonctions de la société 11. Tous les employés ne travaillant pas dans le département 30 et qui soit ont un salaire > à 2800, soit sont manager. 12. Tous les employés dont le salaire est compris entre 1000 et 2000 13. Tous les numéros de département 14. Toutes les fonctions par département (10 : président, 10 : manager, etc.) -- -------------------------------------------------------------------- 15. Tous les employés ayant ou pouvant avoir une commission (ceux dont la commission n''est pas null) 16. Tous les salaires, commissions et totaux (salaire + commission) des vendeurs 17. Tous les salaires, commissions et totaux (salaire + commission) de tous les employés 18. Tous les employés embauchés en 1981 19. Tous les employés avec leur date d’embauche et leur nombre d’années d’ancienneté triés par ancienneté (on utilisera datediff(), current_date(), from_days() et year() ). -- -------------------------------------------------------------------- -- % et _ 20. Tous les employés ayant un A en troisième lettre de leurs noms. 21. Tous les employés ayant au moins deux A dans leurs noms. 22. Pour chaque employé, donnez les quatre dernières lettres de son nom, 2 fois, une fois avec substring(), une fois avec right() -- -------------------------------------------------------------------- -- limit, order by rand() 23. Lister premiers 3 employés par ordre alphabétique 23bis. Lister premiers 3 employés du département 10 par ordre alphabétique 23ter. Lister 3 employés au hasard 24. Quel est le plus gros salaire de l’entreprise ? Sans fonction max() avec un tri et un limit. 25. Quel est le plus gros salaire total des vendeurs (SALESMAN) ? Sans fonction max() avec un tri et un limit. -- -------------------------------------------------------------------- -- case, div (division entière) 26. Afficher tous les employés en affichant : « anciens » pour ceux embauchés avant le 1 janvier 1982, rien pour ceux embauchés en 1982 et « nouveaux » pour ceux embauchés après le 1 janvier 1983. On utilisera un case. On trie par date d’embauche et par ordre alphabétique. 27. Afficher les employés avec leur salaire et le numéro de leur tranche de salaire. Le numéro va de 1 à N. La première tranche va de 0 à 999, la deuxième de 1000 à 1999, la troisième de 2000 à 2999, etc. On considère qu’on ne sait pas à l’avance combien il y aura de tranche. On affichera les résultats par numéro de tranche croissante et par ordre alphabétique par tranche. -- -------------------------------------------------------------------- -- create view as select 8ter. Créer une vue apppelée managers qui contiennent les managers avec tous leurs attributs sauf leur fonction. Vérifier que cette vue a bien été créée. Ecrire une requêtes qui affiche tous les éléments de cette vue avec tous leurs attributs Ecrire une requête qui affiche tous les managers des départements 20 et 30 en partant de cette vue. 27bis. Créer une vue apppelée tranches qui contiennent la requête précédente. Vérifier que cette vue a bien été créée Ecrire une requêtes qui affiche tous les éléments de cette vue avec tous leurs attributs. Ecrire une requête qui affiche les salariés dont la tranche est >3 avec leur salaire. -- -------------------------------------------------------------------- -- variable @nom_var 8bis. Tous les employés appartement à un département dont la valeur est placée dans une variable @nd. Donner 10 à @nd et lancer la requête. Donner 20 à @nd et lancer la requête. 9bis. Tous les employés embauché une certaine année. La valeur de l''année est placée dans une variable @annee. Donner 1981 à @nd et lancer la requête. Donner 1980 à @nd et lancer la requête. **************************************************************************** **************************************************************************** EXERCICE 2 : interrogation de la BD Travail à faire La même chose que pour l’exercice 1, à la suite dans le même fichier. **************************************************************************** Les requêtes 1. Combien d''employés dans la société SELECT count(*) FROM employes; -- CP : NULL 2. Combien d''employés embauchés en 1981 3. Combien de vendeurs (« Salesman ») dans la société 4. Combien de départements dans la société 5. Combien de fonctions différentes dans la société 6. Combien y a-t-il d’employés qui n’ont pas et ne peuvent pas avoir de commission (comm non null) ? 7. Salaires minimum, maximum et moyen de la société. 8. Salaires moyens des vendeurs 9. Salaires moyens de tous les employés en tenant compte des commissions 10. Quel est le salaire moyen, les salaires min et max et le nombre d’employé par profession ? ************************************************************************ ************************************************************************ Remarque concernant la gestion des accents avec MySQL : Si on a des problèmes d’accents, faire une show variables like '%char%'; Sous Windows : mysql> show variables like '%char%'; +--------------------------+-------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------+ | character_set_client | cp850 | | character_set_connection | cp850 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | cp850 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | c:\wamp64\bin\mysql\mysql5.7.14\share\charsets\ | +--------------------------+-------------------------------------------------+ 8 rows in set, 1 warning (0.00 sec) Sous Mac mysql> show variables like '%char%'; +--------------------------+-------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /Applications/MAMP/Library/share/charsets/ | +--------------------------+-------------------------------------+ 8 rows in set (0,00 sec) Remplacement du character_set : Il se peut qu’il faille remplacer les character_set_client cp850 par de l’utf8. Cela se fait par exemple avec la commande : SET character_set_client = utf8; http://dev.mysql.com/doc/refman/5.7/en/set-character-set.html http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html Utilisation des commandes MySQL dans la calculette : https://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html