Please je veux la correction de ce exercice

Bonjour,

TP Base de données relationnelle
________________________________________
Création de la base de données
Créer une base de données nommée ‘revision’ avec un fichier de données de taille initiale 10MB et de pourcentage d’incrémentation de 10% ainsi que d’un fichier journal de taille initiale 1MB et de pourcentage d’incrémentation de 10%.
________________________________________
Création des tables
EMP(empno, ename, job, hiredate, mgr#, sal, comm, deptno#)
DEPT(deptno, dname, loc)
PROJET(CodeP, NomP)
PARTICIPATION(empno#, CodeP#, Fonction)
Ecrire le code de création des 4 tables.
________________________________________
Insertion des données dans les tables Projet et Participation
INSERT INTO DEPT VALUES (10,'FINANCES','PARIS');
INSERT INTO DEPT VALUES (20,'RECHERCHES','GRENOBLE');
INSERT INTO DEPT VALUES (30,'VENTES','LYON');
INSERT INTO DEPT VALUES (40,'FABRICATION','ROUEN');
INSERT INTO EMP VALUES (7369,'LECLERC','SECRETAIRE',7902,'02/12/80',6400.00,NULL,20);
INSERT INTO EMP VALUES (7499,'BIRAUD','COMMERCIAL',7698,'20/2/81',12800.00,2400.00,30);
INSERT INTO EMP VALUES (7521,'BERGER','COMMERCIAL',7698,'22/10/81',10000.00,4000.00,30);
INSERT INTO EMP VALUES (7566,'MERCIER','DIRECTEUR',7839,'02/3/81',23000.00,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','COMMERCIAL',7698,'28/5/81',10000.00,11200.00,30);
INSERT INTO EMP VALUES (7698,'NOIRET','DIRECTEUR',7839,'01/5/81',22800.00,NULL,30);
INSERT INTO EMP VALUES (7782,'LESAGE','DIRECTEUR',7839,'09/6/81',19600.00,NULL,10);
INSERT INTO EMP VALUES (7788,'DUBOIS','INGENIEUR',7566,'14/4/85',24000.00,NULL,20);
INSERT INTO EMP VALUES (7839,'LEROY','PRESIDENT',NULL,'17/11/81',40000.00,NULL,10);
INSERT INTO EMP VALUES (7844,'BENAIN','COMMERCIAL',7698,'08/5/81',12000.00,0.00,30);
INSERT INTO EMP VALUES (7876,'CLEMENT','SECRETAIRE',7788,'10/5/85',8800.00,NULL,20);
INSERT INTO EMP VALUES (7900,'FREMONT','SECRETAIRE',7698,'03/12/81',7600.00,NULL,30);
INSERT INTO EMP VALUES (7902,'CHATEL','INGENIEUR',7566,'17/12/81',24000.00,NULL,20);
INSERT INTO EMP VALUES (7934,'VILLARD','SECRETAIRE',7782,'23/8/82',10400.00,NULL,10);
insert into projet values('0001', 'Gestion RH');
insert into projet values('0002', 'Projet Portail');
insert into projet values('0003', 'Dataware House');
insert into projet values('0004', 'Gestion Porte Feuille');
insert into projet values('0005', 'tion Courrier');
insert into participation values(7369, '0001', 'CHARGE COMMUN');
insert into participation values(7499, '0001', 'CHARGE VENTE');
insert into participation values(7521, '0001', 'CHARGE VENTE');
insert into participation values(7566, '0001', 'RESP TECH');
insert into participation values(7654, '0001', 'CHARGE VENTE');
insert into participation values(7698, '0001', 'RESP COMMERC');
insert into participation values(7782, '0001', 'RESP ADMINIS');
insert into participation values(7788, '0001', 'DEVELOPPEUR');
insert into participation values(7839, '0001', 'CHEF DE PROJET');
insert into participation values(7876, '0001', 'CHARGE COMMUN');
insert into participation values(7900, '0001', 'CHARGE COMMUN');
insert into participation values(7902, '0001', 'CONCEPTEUR');
insert into participation values(7934, '0001', 'CHARGE COMMUN');
insert into participation values(7844, '0001', 'RESP COMMERC');
insert into participation values(7788, '0002', 'DEVELOPPEUR');
insert into participation values(7654, '0002', 'CHARGE VENTE');
insert into participation values(7876, '0002', 'CHARGE COMMUN');
insert into participation values(7788, '0003', 'DEVELOPPEUR');
insert into participation values(7876, '0003', 'CHARGE COMMUN');
insert into participation values(7844, '0003', 'RESP COMMERC');
insert into participation values(7788, '0004', 'DEVELOPPEUR');
insert into participation values(7902, '0004', 'CONCEPTEUR');
insert into participation values(7566, '0004', 'RESP TECH');
insert into participation values(7788, '0005', 'DEVELOPPEUR');
insert into participation values(7902, '0005', 'CONCEPTEUR');
________________________________________
Manipulation (Insertion/modification/suppression) simples des données
1. Créer un nouveau département : 50, COMPTABILITE, TOULOUSE
2. Créer un nouveau département : 60, DIRECTION
3. Dupliquer l'enregistrement du département 20, en lui donnant le numéro de département 25 (en utilisant un select pour récupérer les informations sur le département 20).
4. Le département 60 s'est maintenant établi dans la ville de BORDEAUX. Enregistrer le fait dans la table DEPT.
5. Promouvoir Mr. MERCIER au poste de ‘PDG’, dans le département 60.
6. Éliminer de la table EMP la ligne de Mr. BIRAUD qui prend sa retraite.
7. Augmenter de 10 % le salaire de tous les employés du département 10.
8. Mr. SIMON vient d'être embauché comme commercial au salaire de 10000 Frs. L'enregistrer sous le matricule 7910 dans le département 30.
________________________________________
Manipulation (Insertion/modification/suppression) un peu plus compliquées
1. Enregistrez les participations suivantes aux projets :
o Au projet « Gestion Porte Feuille » (vous supposerez que vous connaissez les matricules et le code du projet « Gestion Porte Feuille ») : MERCIER, CHATEL
o Au projet « Dataware House » (sans connaître ni les matricules des employés, ni le code du projet « Dataware House ») : LEROY, CHATEL
2. Enregistrez les nominations de
o MERCIER comme chef du projet « Gestion Porte Feuille »,
o CHATEL comme trésorier dans tous les projets auxquels il participe.
3. Les départements de GRENOBLE sont supprimés et leurs employés sont réaffectés au département 40. Mettre à jour les tables DEPT et EMP.
________________________________________
Transactions et contraintes d'intégrité
Commencez une nouvelle transaction (n'oubliez pas de le faire !!). Exceptionnellement, pendant cet exercice, ne faites aucun commit (tout sera annulé à la fin de l'exercice).
1. Supprimez tous les employés sauf ceux du département 10. Si vous n'y arrivez pas (pourquoi ?), commencez par supprimer les participations aux projets des employés des départements de numéros différents de 10.
2. Affichez nom, poste, département, salaire, commission pour tout le personnel.
3. Annulez la transaction en cours et relancez la requête de la question 2.
________________________________________
Contraintes d'intégrité
1. Faites afficher les informations sur les employés du département 10.
2. Essayez de supprimer le département 10 (pas les employés du département 10, le département lui-même). Expliquez ce qui se passe.
3. Modifiez la définition des tables pour que la suppression du département 10 soit autorisée et entraîne automatiquement la suppression des employés de ce département.
4. Créer une transaction puis supprimez le département 10 et vérifiez ce qui s'est passé pour les employés du département 10.
5. Annulez la transaction et changez les définitions des contraintes pour que le comportement soit le même qu'au début de l'exercice.
________________________________________
Interrogations sur une seule table
1. Liste des divers postes, en n'affichant chaque poste qu'une seule fois.
2. On considèrera qu'une commission NULL correspond à un employé qui ne touche aucune commision (donc commission = 0).
Noms des employés dont le salaire est inférieur à la commission. Noms des employés dont le salaire est supérieur à la commission.
3. Noms des employés qui ne touchent pas de commission.
4. Noms des employés dont la 2ème lettre est un E.
5. Numéros de département dont au moins un employé touche une commission (au sens où la commission de l'employé n'est pas NULL).
6. Noms des employés qui touchent entre 2.000 et 8.000 €.
________________________________________
Jointures
1. Affichez le produit cartésien des noms des départements par les lieux.
2. Noms des employés et lieu où ils travaillent.
3. Lieu des départements (mais pas les noms des départements) dans lesquels des employés touchent une commission, en utilisant une jointure.
4. Noms des supérieurs directs de chaque employé qui a un supérieur (nom des employés qui ont un supérieur, suivi du nom du supérieur).
5. Noms des employés, avec le nom de leur supérieur direct quand ils en ont un.
6. Nom des employés qui sont le supérieur d'un autre employé.
7. Noms des projets où sont inscrits des employés des départements de Grenoble.
________________________________________
Sous-interrogations
1. Lieu des départements dans lesquels des employés touchent une commission, en utilisant cette fois une sous-interrogation et non plus une jointure.
2. Noms et lieux des départements dans lesquels il y a au moins un ingénieur.
Pouvez-vous y arriver au moyen d'une jointure ?
Pouvez-vous y arriver au moyen d'une sous-interrogation ?
3. Noms et lieux des départements dans lesquels il n'y a pas d'ingénieur.
Pouvez-vous y arriver au moyen d'une jointure ?
Pouvez-vous y arriver au moyen d'une sous-interrogation ?
4. Matricules et noms des employés qui ne sont les supérieurs d'aucun employé. Étonnant ? Faites afficher le nom de chaque employé avec le matricule de son supérieur et faites appel à votre logique. Corrigez votre première requête pour avoir ce qui est demandé.
________________________________________
Sous-interrogations "avancées"
1. A l'aide d'une sous-interrogation synchronisée, dressez la liste des employés qui gagnent moins de 50 % du salaire de leur supérieur direct.
2. Noms des employés qui gagnent plus que tous les commerciaux.
3. Noms des employés qui gagnent plus que tous les commerciaux de leur département.
Proposez 2 méthodes, une affichant les noms des employés dont le département ne comprend pas de commerciaux, et une autre ne les affichant pas. Laquelle vous semble répondre le plus logiquement à la question ?
4. En utilisant une sous-interrogation, dressez la liste des employés ayant le même poste et le même supérieur que BERGER.
________________________________________
Divisions ?
Pour chacune des questions de cet exercice, vérifiez qu'au moins une donnée répond à la question. Sinon, modifiez les données de votre base que ça soit le cas.
1. Liste des noms des employés qui participent à tous les projets. Arrangez-vous pour qu'il y en ait au moins un.
2. Numéros des départements qui participent à tous les projets (chaque projet a au moins un employé de ces départements). Arrangez-vous pour qu'il y en ait au moins un qui réponde à la question, sans qu'aucun de ses employés ne participe à tous les projets.
3. Numéros des départements dont tous les employés gagnent plus de 1.500 €. Arrangez-vous pour qu'il y en ait au moins un.
________________________________________
Mettons un peu d'ordre !
En une seule requête, liste des employés, présentés selon cet ordre (vous pourrez utiliser la fonction decode) :
• d'abord les directeurs par ordre alphabétique,
• puis les autres employés par date d'embauche la plus récente.
________________________________________
Regroupements
1. Nombre d'employés de chaque département.
2. Total des salaires des employés qui ont le même supérieur direct que BIRAUD.
3. Nom de l'employé (ou des employés) qui a le plus gros salaire. Et si l'on veut les noms des employés qui ont les trois plus gros salaires (avec leur salaire) ? Si vous ne trouvez pas, attendez l'exercice "Trier des regroupements" qui vous indiquera une idée de solution (pas la plus simple).
4. Noms des employés qui ont le plus gros salaire de leur département. Et si l'on veut la liste des trois plus gros salaires de chaque département ?
5. Totaux des salaires par poste et par département, sous la forme suivante (c'est vous qui choisissez les départements qui vous intéressent, avec les en-têtes de colonnes) :
Poste Dept 10 Dept 20 Dept 30
Secrétaire 100000 20000 150000
Ingénieur 80000 130000 200000

________________________________________
Toujours plus...
1. Liste des numéros des départements qui ont plus d'employés que le département 10.
2. Trouver le nom du (ou des) département(s) qui a le plus d'employés.
________________________________________
Trier des regroupements
1. Liste des numéros de département triés par nombres d'employés.
2. Liste des employés, triés par salaire décroissant, avec, pour chaque employé, le nombre d'employés qui gagnent plus.
3. Liste des employés avec, pour chaque employé, son rang dans la hiérarchie des salaires (1 pour celui qui gagne le plus). Essayez ensuite de faire afficher en plus le salaire de l'employé.
Est-ce que vous pouvez en déduire une méthode pour résoudre les questions 3 et 4 de l'exercice "Regroupements" qui portent sur les 3 plus gros salaires ?
________________________________________
mon msn saadia11@live.com