-- Fichier texte encodé en UTF8 with BOM -- ------------------------------------------------------------------------------------------------------------ -- ------------------------------------------------------------------------------------------------------------ -- MLD : -- employes(id, nom, fonction, date_embauche, salaire, commission, id_departement, id_employe_chef(null)) -- departements (id, nom, ville) -- tache(id, nom_tache, duree_theorique, #id_projet, #id_employe(null)) -- projet(id, nom_projet, date_debut, date_fin) -- ------------------------------------------------------------------------------------------------------------ -- ------------------------------------------------------------------------------------------------------------ -- Graphe des tables : -- taches -- / \ -- / \ -- projets \ -- 0 -- r--0> employes le 0 veut dire que ça peut être null -- chef | / \ -- |____/ \ -- departements -- on a un lien circulaire : on peut multiplier les tables employés -- -- employes -- / \ -- / \ -- employes-chef departements -- / \ -- / \ -- employes-chef departements-chef -- les employes qui ne travaillent pas dans la même ville que leur chef -- -- employes -- / \ -- / \ -- employes-chef departements -- / \ -- / ville -- departements-chef -- / -- ville-chef ********************************************************************************* EXERCICE 1: Jointures naturelles ********************************************************************************* Travail à faire • Répondre à toutes les questions ci-dessous en SQL2. • Vous ne devez projeter que les attributs nécessaires: CP, CU, AD, AR – Tri devant ou derrière (Clé Primaire, Clé d''Usage, Attributs Demandés, Attributs de restriction). • Vous devez précisez la clé primaire de chaque table de résultats. ********************************************************************************* Série 1: 101. Tous les employés avec tous leurs attributs et toutes les informations de leurs départements. 102. Tous les employés de Dallas. 103. Tous les employés du département RESEARCH. 104. Tous les analystes du département ACCOUNTING. 105. Toutes les fonctions du département SALES. 106. Tous les employés qui gagnent plus que 1500 et qui travaillent à NEW YORK. Série 2 201. Tous les employés avec leurs chefs. 202. Combien y a-t-il de lignes dans la table résultat de la requête précédente ? Pourquoi? 203. Tous les employés avec leur salaire et avec leurs chefs et le salaire du chef, en montrant aussi ceux qui n''ont pas de chef. 204. Tous les employés dont le chef gagne plus que 3000; 205. Tous les employés dont le chef est à DALLAS. 206. Toutes les fonctions des employés qui ont un chef dans le département 20. 207. Toutes les fonctions des employés qui ont un chef à CHICAGO. Série 3 301. Tous les employés ne travaillant pas dans le même département que leur chef. 302. Tous les employés embauchés avant leur chef. 303. Donner la liste de tous les supérieures hiérarchiques avec leur salaire, 304. Tous les chefs avec la liste de leurs subordonnées. Série 4 401. Tous les employés avec le nom de leur département et le nom de leur chef ainsi que le nom du département de leur chef. 402. Combien y a-t-il de lignes dans la table résultat de la requête précédente ? Pourquoi? 403. Comme pour la 203, montrez aussi les employés ceux qui n''ont pas de chef. 404. Tous les employés ne travaillant pas dans la même ville que leur chef. 405. Les employés qui sont CLERK dont le chef n’’est pas de New York. On veut aussi le nom du département des Clerks. Série 5 -- MLD : -- employes(id, nom, fonction, date_embauche, salaire, commission, id_departement, id_employe_chef(0)) -- departements (id, nom, ville) -- tache(id, nom_tache, duree_theorique, #id_projet, #id_employe(0)) -- projet(id, nom_projet, date_debut, date_fin) -- taches -- / \ -- / \ -- projets \ -- 0 -- r--0> employes le 0 veut dire que ça peut être null -- chef | / \ -- |____/ \ -- departements 501. Toutes les tâches avec leur projet et leur employé. 502. Toutes les tâches avec leur projet et leur employé. On montre aussi les tâches qui n''ont pas d''employé. 503. Toutes les tâches de tous les employés classées oar nom d''employé et par id de tâche 504. Idem en ajoutant le nom du projet de la tâche et en classant par nom d''employé et par date de début de pojet. 505. Idem pour un employé en particulier (un de ceux qui a le plus de tâches) 506. Toutes les tâches et leurs projets pour le département 10, classés par dat de début de projet et par numéro de tâche. 507. Toutes les tâches et leurs projets pour de tous les subordonnés de tous les chefs, classés par nom de chef puis par nom de début de projet et id de tache. 508. Idem pour un chef en particulier (un de ceux qui a le plus de tâches réalisées par ses subordonnés) ********************************************************************************* ********************************************************************************* ********************************************************************************* EXERCICE 2: Jointures non naturelle : requêtes imbriquées, jointures externes, jointures artificielles ********************************************************************************* Les requêtes 601. Tous les employés travaillant dans un département qui contient au moins un 'ANALYST'. 602. Tous les employés ayant le même job que les employés du département 30. 603. Tous les noms et dates d’embauche des employés embauchés avant BLAKE. 604. Tous les employés ayant le même chef que ALLEN 605. Tous les employés n’ayant pas le même chef que 606. Tous les employés n’ayant pas de subordonnées. 607. Tous les départements vides (dans lesquels il n'y a pas d'employés) avec leurs noms et villes. ALLEN 608. Quels sont les employés qui ne sont pas supérieurs hiérarchiques? 609. Tous les employés ayant le même job et le même chef que MARTIN.e. 610. Tous les employés travaillant à Chicago et ayant le même job qu’ALLEN. 611. Tous les employés du département RESEARCH embauchés la même année que quelqu’un du département SALES 612. Tous les employés embauchés avant tous les employés du département 10. Pour vérifier on affichera tous les employés du 10 ordonnés par date d’embauche. **************************************************************************** **************************************************************************** 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; Utilisation des commandes MySQL dans la calculette: https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html