Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

RAPPORT TP SQL ORACLE ANNEE ACADEMIQUE 2018-2019 TROISIEME ANNEE OPTION SYSTEME

RAPPORT TP SQL ORACLE ANNEE ACADEMIQUE 2018-2019 TROISIEME ANNEE OPTION SYSTEME D’INFORMATION KONE Abdel Kader Razack et COMPAORE Fatoumata Yasmina | Administration de Base de Donnée | 20/03/2019 Chargé du cours : Dr Borli Michel Jonas SOME Chargé des Travaux Pratiques : Abdoul Aziz KINDO PAGE 1 SQL>conn sys/sysdba as sysdba TP1 Connectez-vous en tant que sysdba 1 Créer l'utilisateur username: esisi3 password: e51513 2 SQL>conn sys/sysdba as sysdba PAGE 2 Select * From All_Users; 3 SQL>Select * from All_Users ; Donner un nouveau mot de passe a HR/HR 4 SQL> alter user hr identified by rhu; PAGE 3 Ouvrir la session HR. Que remarquez-vous ? 5 SQL> conn hr/rhu; Remarque : Le compte hr est verouillé Déverrouillez le compte HR 6 SQL> alter user hr account unlock; SQL>conn sys/sysdba as sysdba PAGE 4 Lister les tables de HR 7 SQL> conn hr/rhu ; SQL>select table_name from user_tables; PAGE 5 SQL> select count(*) from REGIONS; SQL> select count(*) from LOCATIONS; SQL> select count(*) from DEPARTMENTS; select count(*) from DEPARTMENTS; Donner le nombre de n uplet de chaque table de HR 8 SQL> conn hr/rhu ; SQL> select count(*) from JOBS; select count(*) from DEPARTMENTS; SQL> select count(*) from EMPLOYEES; select count(*) from DEPARTMENTS; SQL> select count(*) from JOB_HISTORY; select count(*) from DEPARTMENTS; SQL> select count(*) from COUNTRIES; select count(*) from DEPARTMENTS; PAGE 6 Que constatez-vous? 10 On ne peut pas se connecter Créer Un Rôle Manager Avec Les Privilèges CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SESSION, RESSOURCES 11 SQL>create role manager; Créer la table Etudiant (mat: chaine(6), nom: chaine(10), prenom chaine(20)) dans la session de esisi3. 9 SQL> Conn esisi3/e51513 ; PAGE 7 SQL>grant create table,create view,create procedure,create session,resource to manager; Attribuer le rôle manager à esisi3 et enfin créer enfin la table étudiant 12 SQL> Grant manager to esisi3; SQL> conn esisi3/e51513 SQL> set role manager ; SQL> create table Etudiant( mat varchar2(6), nom varchar2(10), prenom varchar2(20), constraint pk_Etudiant primary key(mat)); PAGE 8 Quels sont les privilèges de HR? (Aidez-vous avec la vue DBA_SYS_PRIVS) 13 SQL> conn sys/sysdba as sysdba SQL> select privilege from dba_sys_privs where grantee = 'HR'; PAGE 9 Créer le profile app_user– 14 SQL> CREATE PROFILE app_user LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL 3000 CONNECT_TIME 45 LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL 1000 PRIVATE_SGA 15K; SQL>alter profile app_user limit failed_login_attempts 5; CPU_PER_CALL 3000 CONNECT_TIME 45 LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL 1000 PRIVATE_SGA 15K; PAGE 10 Cette ligne indique que seules cinq (5) tentatives de connexion sont autorisées Attribuez ce profile à HR 15 SQL> alter user hr profile app_user; PAGE 11 TP2 Donnez le nombre d’employés de chaque département sans jointure 1 SQL> conn hr/rhu SQL> select department_id,count(*) from employees group by department_id; Donnez le nombre d’employés de chaque département (department_name, nombre) 2 SQL> select department_name,count(employee_id) from departments,employees where departments.department_id = employees.department_id (+) group by department_name ; PAGE 12 Nom de l'employé qui a le plus gros salaire. 3 SQL>select first_name,last_name, salary from employees where salary= (select max(salary) from employees); Et si l'on veut les noms des employés qui ont les trois plus gros salaires (avec leur salaire) ? 4 SQL>select last_name,first_name,salary from employees where rownum< 4 order by salary desc; PAGE 13 Noms des employés qui ont le plus gros salaire de leur département. 5 SQL>select first_name,last_name,salary,department_id from employees, (select max(salary) sal,department_id d from employees group by employees.department_id) where salary=sal and department_id=d ; La liste des trois plus gros salaires de chaque département ? 6 SQL> select First_Name,Last_Name,salary,department_id from employees e where 3 > (select count(*) from employees where salary > e.salary and department_id = e.department_id); PAGE 14 Liste des numéros des départements qui ont plus d'employés que le département 10. 7 SQL> select department_id from employees group by department_id having count(*) > (select count(*) from employees where department_id=10); PAGE 15 Trouver le nom du (ou des) département(s) qui a le plus d'employés 8 SQL> select Department_name from departments where Department_id= (select department_id from employees group by department_id having count(*)=(select max(count(*)) from employees group by department_id)); Créez une vue V_EMP contenant : le matricule, le nom, le numéro de département, la somme de la commission et du salaire baptisée GAINS, le lieu du département. 9 SQL> create view v_emp (matr, nom, dept, GAINS) as select employee_id,Department_name, employees.department_id, salary + nvl(commission_pct,0) from employees,departments where employees.department_id = departments.department_id; PAGE 16 Sélectionnez les lignes de V_EMP dont le salaire total est supérieur à 10.000 10 SQL>select * from v_emp where gains > 10000; Essayez de mettre à jour le nom de l'employé Ki Gee à travers la vue V_EMP. 11 SQL>update v_emp set first_name='kado' where first_name like '%ki gee'; Créez une vue VEMP10 qui ne contienne que les employés du département 10 de la table EMP (n'utilisez pas l'option CHECK pour cette création). 12 SQL>create or replace view v_emp10 as select * from employees where department_id=10; PAGE 17 Insérez dans cette vue un employé SOULIER qui appartient au département 20. Essayez ensuite de retrouver cet employé au moyen de la vue VEMP10 puis au moyen de la table EMPLOYEES. SQL>insert into V_EMP10(employee_id,last_name,department_id,e mail,phone_number,hire_date,job_id) values (300,'SOULIER',20,'soul@','12.77.99','12/03/2000','AC _MGR'); La nouvelle insertion est visible avec la table mais invisible avec la vue Détruisez cette vue V_EMP10 et recréez-la avec l'option CHECK. 13 SQL>drop view V_emp10 ; SQL>create or replace view VEMP10 as select * from employees where department_id=10 with check option; PAGE 18 Essayez d'insérer un employé Martin Mouton pour le département 30. Que se passe-t-il ? 14 SQL>insert into v_EMP10(employee_id,last_name,department_id,em ail,phone_number,hire_date,job_id) values (700,'MARTIN',30,'martin@','12.7.99','11/03/2000','AC _MGR'); Essayez de modifier le département d'un employé visualisé à l'aide de cette vue. SQL> update V_EMP10 set department_id=20 where employee_id=200; Impossible de modifier le departement Liste des salaires des employés avec le pourcentage par rapport au total des salaires de leur département (utilisez une vue qui fournira le total des salaires). 15 SQL>create or replace view total as select department_id,sum(salary) sumsal from employees group by department_id ; Avec with check option il est impossible d'inserer un employer d'un autre departement PAGE 19 SQL>select first_name,last_name,salary,salary*100/sumsal poucentage from employees e,total t where e.department_id=t.department_id; Créer une vue VIMPOT qui permet de voir l’impôt payé par chaque employé. l’impot est calculé comme indiquer dans le tableau ci-dessous 16 SQL>create or replace view VIMPOT as select first_name,last_name,case when min_salary>=0 and max_salary<=5000 then salary*0 when min_salary>=5000 and max_salary<=9000 then salary*0.1 when min_salary>=9000 and max_salary<=12000 then salary*0.2 else salary*0.3 end Impot from employees e,jobs j where e.job_id=j.job_id; PAGE 20 TP3 Écrivez un programme pour échanger les salaires des employés 120 et 122. 1 SQL>declare x employees.salary%type; y employees.salary%type; begin select salary into x from employees where employee_id =120; select salary into y from employees where employee_id =122; update employees set salary =y where employee_id=120; update employees set salary =x where employee_id=122; end; / PAGE 21 Augmenter le salaire de l'employé 115 selon les conditions suivantes : Si l'expérience est supérieure à 10 ans, augmenter le salaire de 20% Si l'expérience est supérieure à 5 ans, augmenter le salaire de 10% Autrement 5% . 2 SQL>declare dat employees.hire_date%type; begin select hire_date into dat from employees where employee_id=115; update employees set salary=( case when (select (sysdate-dat)/365 from employees where employee_id=115) > 10 then salary+20*salary/100 when (select (sysdate-dat)/365 from employees where employee_id=115) between 5 and 10 then salary+10*salary/100 else salary+5*salary/100 end ) where employee_id=115; end; / PAGE 22 Modifier le pourcentage de commission comme suit pour un employé avec ID = 150. Si le salaire est supérieur à 10000, alors la commission est de 0,4%, si le salaire est inférieur à 10000 mais l'expérience est supérieure à 10 ans, 0,35% si le salaire est inférieur à 3000 alors la commission est de 0,25%. Dans les autres cas, la commission est de 0,15%. 3 SQL>declare dat employees.hire_date%type; sal employees.salary%type; begin select hire_date into dat from employees where employee_id=150; select salary into sal from employees where employee_id=150; update employees set commission_pct=(case when sal>10000 then 0.4 when (sal between 3000 and 10000) and (select (sysdate-dat)/365 from employees where employee_id=150) > 10 then 0.35 when sal<3000 then 0.25 else 0.15 end) where employee_id=150; end; / PAGE 23 Créez une fonction qui prend l'ID de département et renvoie le nom du gestionnaire du département. 4 SQL>create or replace function manager(id in departments.department_id%type) return employees.first_name%type is m_id departments.department_id%type; m_name employees.first_name%type; begin select manager_id into m_id from departments where department_id=id; select first_name into m_name from employees where employee_id=m_id; return m_name; end manager; / PAGE 24 Créez une fonction qui prend l'ID de l'employé et renvoie le nombre des emplois occupé par l’employé dans le passé. 5 SQL>create or replace function historique(id in employees.employee_id%type) return number is n Number; begin select count(*) into n from job_history where employee_id=id; return n; end historique; / PAGE 25 Créez une procédure qui prend l'ID du département et change l'ID du responsable du département pour l'employé du département le plus rémunéré. 6 SQL>create or replace procedure management(id in departments.department_id%type) is e_id employees.employee_id%type; sal employees.salary%type; begin select max(salary) into sal from employees where department_id=id; select employee_id into e_id from employees where salary=sal and department_id=id; update departments set manager_id=e_id where department_id=id; end management; / PAGE 26 Assurez-vous qu'aucune modification ne peut être apportée à uploads/Marketing/ rapport-tp-sql-oracle.pdf