Exercices ORACLE - SQL Et SQL Plus [PDF]

Zitiervorschau

SQL ORACLE EXERCICES

SOMMAIRE CHAPITRE 1 MODÈLE PHYSIQUE DE DONNÉES (MPD) DE LA BASE UTILISÉE..............3 CHAPITRE 2 : SQL ET SQL*PLUS....................................................................................................4 EXERCICES 1 À 10 : SQL ET SQL*PLUS :..............................................................................................................................4 EXERCICES 11 À 14 : SQL ET SQL*PLUS,............................................................................................................................5

CORRIGES DES EXERCICES......................................................6 CHAPITRE 2 : SQL ET SQL*PLUS...................................................................................................7 EXERCICES 1 À 10 : SQL ET SQL*PLUS :..............................................................................................................................7 EX. 1...........................................................................................................................................................................................7 EX.2............................................................................................................................................................................................7 EX.3............................................................................................................................................................................................8 EX.4............................................................................................................................................................................................9 EX.5............................................................................................................................................................................................9 EX.6..........................................................................................................................................................................................10 EX.7..........................................................................................................................................................................................10 EX.8..........................................................................................................................................................................................11 EX.9..........................................................................................................................................................................................11 EX.10........................................................................................................................................................................................12 EXERCICES 11 À 14 : SQL ET SQL*PLUS............................................................................................................................12 EX.11........................................................................................................................................................................................12 EX.12........................................................................................................................................................................................13 EX.13........................................................................................................................................................................................13 EX.14........................................................................................................................................................................................14 EXERCICES 15 : LES VUES...................................................................................................................................................14

DEMONSTRATIONS...........................................................................................................................16 CHAPITRE 3 : LES TRAITEMENTS STOCKES........................................................................... 16

2 /

Chapitre 1 Modèle Physique de Données (MPD) de la base utilisée Modèle Physique de Données Proj et : Formation Modèle : Exos Auteur : Or@Tech Version 1.0 10.11.99

e_image no format validite nom_fichier image

NUMBER(7) VARCHAR2(25) VARCHAR2(1) VARCHAR2(255) LONG RAW

not null null null null null

NUMBER(7) VARCHAR2(1) VARCHAR2(255) VARCHAR2(2000)

not null null null null

no nom

e_continent NUMBER(7) VARCHAR2(50)

no = image_no

e_service no nom continent_no



NUMBER(7) VARCHAR2(25) NUMBER(7)

e_produit NUMBER(7) VARCHAR2(50) VARCHAR2(255) NUMBER(7) NUMBER(7) NUMBER(11,2)

no nom description texte_no image_no prix_conseille

not null not null null null null null

e_client NUMBER(7) VARCHAR2(50) VARCHAR2(25) VARCHAR2(400) VARCHAR2(30) VARCHAR2(20) VARCHAR2(30) VARCHAR2(5) VARCHAR2(9) NUMBER(7) NUMBER(7) VARCHAR2(255)

not null not null null

no nom telephone adresse ville departement pays cp_postal solvabilite emp_no continent_no commentaires

commande_no ligne_no produit_no prix quantite qte_livree

not null not null not null null null null

not null not null null null no = superieur_no null null null null null null null null

no = service_no no = emp_no

no nom prenom utilisateur dt_entree commentaires no = client_no superieur_no titre service_no salaire no = produit_no pct_commission

e_emp NUMBER(7) VARCHAR2(25) VARCHAR2(25) VARCHAR2(8) DATE VARCHAR2(255) NUMBER(7) VARCHAR2(25) NUMBER(7) NUMBER(11,2) NUMBER(4,2)

e_titre titre VARCHAR2(25)

3 /

no client_no dt_commande dt_livraison emp_no total nt_paiement validite

not null not null null null null null null null null null null

titre = titre

no = commande_no

e_commande NUMBER(7) NUMBER(7) DATE DATE NUMBER(7) NUMBER(11,2) VARCHAR2(8) VARCHAR2(1)

no = entrepot_no

no = superieur_no

no = produit_no

e_ligne NUMBER(7)

NUMBER(7)

NUMBER(7) NUMBER(11,2) NUMBER(9) NUMBER(9)

not null not null

no = continent_no

no = continent_no

no = texte_no

not null not null null null null null null null null

no = continent_no

e_texte no validite nom_fichier texte

e_entrepot NUMBER(7) NUMBER(7) LONG VARCHAR2(30) VARCHAR2(20) VARCHAR2(30) VARCHAR2(5) VARCHAR2(25) NUMBER(7)

no continent_no adresse ville departement pays cp_postal telephone superieur_no

not null

no = emp_no

not null not null null null null null null null

produit_no entrepot_no qte_stockee stock_securite max_stocke detail_sortie dt_stock

e_stock NUMBER(7) NUMBER(7) NUMBER(9) NUMBER(9) NUMBER(9) VARCHAR2(255) DATE

not null not null null null null null null

Chapitre 2 : SQL et SQL*Plus EXERCICES 1 À 10 : SQL ET SQL*PLUS : Préparation : charger la base DELUXE.SQL Enregistrez la requête que vous construirez Enregistrez également les résultats dans un fichier spool

Exercice 1 Liste des services dont le numéro est supérieur à 45.

Exercice 2 Liste des employés dont le nom commence par "M" Récrire la requête en utilisant une variable.

Exercice 3 Liste des employés n’ayant pas de commission.

Exercice 4 Liste des employés qui ont les fonctions suivantes : Magasinier, Chef d’entrepôt.

Exercice 5 Liste des employés gagnant entre 1400 et 2000 et ayant intégré la société après le 20 janvier 1992.

Exercice 6 Liste des employés dont la commission est supérieure à 200, classés dans l’ordre croissant des commissions.

Exercice 7 Liste des employés triés par titre et pour chaque titre triés par salaire décroissant.

Exercice 8 Liste des employés et des services dans lesquels ils exercent.

Exercice 9 Liste des numéros de produits qui n’ont jamais été commandés.

Exercice 10 Liste des employés travaillant en Europe et ayant le même titre que SMITH.

4 /

EXERCICES 11 À 14 : SQL ET SQL*PLUS, Exercice 11 Liste des employés (no, nom, salaire) triés par nom. Le salaire de VELASQUEZ doit être caché par des "*".

Exercice 12 Liste des employés (nom, prénom, date d’entrée, salaire et numéro de service) travaillant dans un service qui contient au moins un Représentant Commercial.

Exercice 13 Liste des employés dont le prénom comprend au moins un A ou un i et dont l’ensemble (nom, prénom) comporte au plus 6 voyelles.

Exercice 14 Afficher les informations du service ayant le plus d’employés : son numéro, son nom, le nom du continent où il se trouve et le nombre d’employés y travaillant.

Exercice 15 : Les vues Créer une vue V_EMP qui rassemblera : Le numéro, le nom, le titre, le salaire et le nom du service de l’employé Regarder le contenu de la vue EMP_T Supprimer la table E_EMP (avec l’option CASCADE CONSTRAINTS) Afficher le contenu de la vue V_EMP Que se passe-t-il ?

5 /

CORRIGES DES EXERCICES

6 /

Chapitre 1 : SQL et SQL*Plus EXERCICES 1 À 10 : SQL ET SQL*PLUS : Ex. 1. SELECT nom, no FROM e_service WHERE no > 45 / NOM                                    NO  ­­­­­­­­­­­­­­­­­­­­­­­­­ ­­­­­­­­­­­­­­­  Administration                         50  1 ligne(s) retournée(s)

Ex.2. Première requête SELECT nom FROM e_emp WHERE nom LIKE 'M%' / NOM                        ­­­­­­­­­­­­­­­­­­­­­­­­­  MENCHU                     MAGEE                      MADURO                     MARKARIAN                  4 ligne(s) retournée(s)

7 /

2ème requête SQL> spool var SQL> get var2.sql 1 SELECT nom 2 FROM e_emp 3* WHERE nom LIKE '&1%' SQL> r 1 SELECT nom 2 FROM e_emp 3* WHERE nom LIKE '&1%' Entrez une valeur pour 1 : M ancien 3 : WHERE nom LIKE '&1%' nouveau 3 : WHERE nom LIKE 'M%' NOM ------------------------MENCHU MAGEE MADURO MARKARIAN 4 ligne(s) sélectionnée(s).

Ex.3. SELECT nom, salaire, pct_commission FROM e_emp WHERE pct_commission IS NULL / NOM                               SALAIRE  PCT_COMMISSION  ­­­­­­­­­­­­­­­­­­­­­­­­­ ­­­­­­­­­­­­­­­ ­­­­­­­­­­­­­­­  VELASQUEZ                         2500.00                  NGAO                              1450.00                  NAGAYAMA                          1400.00                  QUICK­TO­SEE                      1450.00                  ROPBURN                           1550.00                  URGUHART                          1200.00                  MENCHU                            1250.00                  BIRI                              1100.00                  CATCHPOLE                         1300.00                  HAVEL                             1307.00                  MADURO                            1400.00                  SMITH                              940.00                  NOZAKI                            1200.00                  PATEL                              795.00                  NEWMAN                             750.00                  MARKARIAN                          850.00                  CHANG                              800.00                  PATEL                              795.00                  DANCS                              860.00                  SCHWARTZ                          1100.00                  20 ligne(s) retournée(s)

8 /

Ex.4. SELECT nom , titre FROM e_emp WHERE titre  IN ('Magasinier', 'Chef d''entrepôt') / NOM                       TITRE                      ­­­­­­­­­­­­­­­­­­­­­­­­­ ­­­­­­­­­­­­­­­­­­­­­­­­­  URGUHART                  Chef d'entrepôt            MENCHU                    Chef d'entrepôt            BIRI                      Chef d'entrepôt            CATCHPOLE                 Chef d'entrepôt            HAVEL                     Chef d'entrepôt            MADURO                    Magasinier                 SMITH                     Magasinier                 NOZAKI                    Magasinier                 PATEL                     Magasinier                 NEWMAN                    Magasinier                 MARKARIAN                 Magasinier                 CHANG                     Magasinier                 PATEL                     Magasinier                 DANCS                     Magasinier                 SCHWARTZ                  Magasinier                 15 ligne(s) retournée(s)

Ex.5.

SELECT nom, salaire, dt_entree FROM e_emp WHERE salaire  BETWEEN 1400 AND 2000 AND dt_entree > '20­JAN­92' / NOM                               SALAIRE DT_ENTREE    ­­­­­­­­­­­­­­­­­­­­­­­­­ ­­­­­­­­­­­­­­­ ­­­­­­­­­­­  NGUYEN                            1525.00 22­JAN­92    MADURO                            1400.00 07­FEB­92    2 ligne(s) retournée(s)

9 /

Ex.6. SELECT nom, salaire, pct_commission, pct_commission * salaire/100 "COMMISSION" FROM e_emp WHERE (pct_commission * salaire/100) >200 ORDER BY commission / NOM SALAIRE PCT_COMMISSION COMMISSION ------- --------------- --------------- --------------NGUYEN 1525.00 15.00 228 DUMAS 1450.00 17.50 253 2 ligne(s) retournée(s)

Ex.7. SELECT nom, titre, salaire FROM e_emp ORDER BY titre, salaire DESC / NOM TITRE -------------- -------------------------

SALAIRE -------

HAVEL

Chef d'entrepôt

1307.00

CATCHPOLE

Chef d'entrepôt

1300.00

MENCHU

Chef d'entrepôt

1250.00

URGUHART

Chef d'entrepôt

1200.00

BIRI

Chef d'entrepôt

1100.00

ROPBURN

DR, Administration

1550.00

QUICK-TO-SEE

DR, Finance

1450.00

NGAO

DR, Opérations

1450.00

NAGAYAMA

DR, Ventes

1400.00

MADURO

Magasinier

1400.00

NOZAKI

Magasinier

1200.00

SCHWARTZ

Magasinier

1100.00

SMITH

Magasinier

940.00

DANCS

Magasinier

860.00

MARKARIAN

Magasinier

850.00

CHANG

Magasinier

800.00

PATEL

Magasinier

795.00

PATEL

Magasinier

795.00

NEWMAN

Magasinier

750.00

VELASQUEZ

Président

2500.00

NGUYEN

Représentant Commercial

1525.00

SEDEGHI

Représentant Commercial

1515.00

GILJUM

Représentant Commercial

1490.00

DUMAS

Représentant Commercial

1450.00

MAGEE

Représentant Commercial

1400.00

10 /

Ex.8. SELECT e_emp.nom ‘‘EMPLOYE’’, e_service.nom ‘‘SERVICE’’ FROM e_emp, e_service WHERE e_emp.service_no = e_service.no / EMPLOYE                   SERVICE                        ­­­­­­­­­­­­­­­­­­­­­­­­­ ­­­­­­­­­­­­­­­­­­­­­­­­­  VELASQUEZ                 Administration             NGAO                      Opérations                 NAGAYAMA                  Ventes                     QUICK­TO­SEE              Finance                    ROPBURN                   Administration             URGUHART                  Opérations                 MENCHU                    Opérations                 BIRI                      Opérations                 CATCHPOLE                 Opérations                 HAVEL                     Opérations                 MAGEE                     Ventes                     GILJUM                    Ventes                     SEDEGHI                   Ventes                     NGUYEN                    Ventes                     DUMAS                     Ventes                     MADURO                    Opérations                 SMITH                     Opérations                 NOZAKI                    Opérations                 PATEL                     Opérations                 NEWMAN                    Opérations                 MARKARIAN                 Opérations                 CHANG                     Opérations                 PATEL                     Ventes                     DANCS                     Opérations                 SCHWARTZ                  Opérations                 25 ligne(s) retournée(s)

Ex.9. SELECT no FROM e_produit MINUS SELECT produit_no FROM e_ligne /      NO  ­­­­­­­­­­­­­­­            41020           41050 2 ligne(s) retournée(s)

11 /

Ex.10. SELECT e.nom, e.titre FROM e_emp e, e_service s WHERE e.service_no = s.no   AND s.continent_no = (SELECT no FROM e_continent                                    WHERE nom = 'Europe')   AND titre = (SELECT titre FROM e_emp WHERE nom = 'SMITH') / NOM                       TITRE                      ­­­­­­­­­­­­­­­­­­­­­­­­­ ­­­­­­­­­­­­­­­­­­­­­­­­­  DANCS                     Magasinier                 SCHWARTZ                  Magasinier                 2 ligne(s) retournée(s)

EXERCICES 11 À 14 : SQL ET SQL*PLUS Ex.11. SQL> SELECT no, nom, 2> DECODE(nom, 'VELASQUEZ', '****', salaire) SALAIRE 3> FROM e_emp 4> ORDER BY nom / NO --------8 9 22 24 15 12 10 16 11 21 7 3 20 2 14 18 19 23 4 5 25 13 17 6 1

NOM --------------BIRI CATCHPOLE CHANG DANCS DUMAS GILJUM HAVEL MADURO MAGEE MARKARIAN MENCHU NAGAYAMA NEWMAN NGAO NGUYEN NOZAKI PATEL PATEL QUICK-TO-SEE ROPBURN SCHWARTZ SEDEGHI SMITH URGUHART VELASQUEZ

SALAIRE -----------------------------1100 1300 800 860 1450 1490 1307 1400 1400 850 1250 1400 750 1450 1525 1200 795 795 1450 1550 1100 1515 940 1200 ****

25 ligne(s) retournée(s) 12 /

Ex.12. SELECT nom, prenom, dt_entree, salaire, service_no FROM e_emp  e WHERE EXISTS (SELECT * FROM e_emp     WHERE titre = 'Représentant Commercial' AND service_no = e.service_no) / NOM --------NAGAYAMA MAGEE GILJUM SEDEGHI NGUYEN DUMAS PATEL

PRENOM -----Midori Colin Henry Yasmin Mai André Radha

DT_ENTREE --------17-JUN-91 14-MAY-90 18-JAN-92 18-FEB-91 22-JAN-92 09-OCT-91 17-OCT-90

SALAIRE ------1400.00 1400.00 1490.00 1515.00 1525.00 1450.00 795.00

SERVICE_NO ---------31 31 32 33 34 35 34

7 ligne(s) retournée(s)

Ex.13

SELECT nom, prenom FROM e_emp WHERE (prenom LIKE '%A%' OR prenom LIKE '%i%')   AND LENGTH( TRANSLATE ( UPPER(nom||prenom),  '*BCDFGHJKLMNPQRSTVWXZ­','*'))  create or replace view v_emp 2 as 3 select e.no, e.nom, e.prenom, e.titre, e.salaire, d.nom "SERVICE" 4 from e_emp e, e_service d 5* where e.service_no = d.no Vue créée. SQL> select nom, service, titre, salaire from v_emp; NOM SERVICE TITRE SALAIRE ------------ ------------------ ---------------------- ---------VELASQUEZ Administration Président 2500 NGAO Opérations DR, Opérations 1450 NAGAYAMA Ventes DR, Ventes 1400 QUICK-TO-SEE Finance DR, Finance 1450 ROPBURN Administration DR, Administration 1550 URGUHART Opérations Chef d'entrepôt 1200 MENCHU Opérations Chef d'entrepôt 1250 BIRI Opérations Chef d'entrepôt 1100 CATCHPOLE Opérations Chef d'entrepôt 1300 HAVEL Opérations Chef d'entrepôt 1307 MAGEE Ventes Représentant Commercial 1400 GILJUM Ventes Représentant Commercial 1490 SEDEGHI Ventes Représentant Commercial 1515 NGUYEN Ventes Représentant Commercial 1525 DUMAS Ventes Représentant Commercial 1450 MADURO Opérations Magasinier 1400 SMITH Opérations Magasinier 940

14 /

NOZAKI NEWMAN MARKARIAN CHANG PATEL DANCS SCHWARTZ

Opérations Opérations Opérations Opérations Ventes Opérations Opérations

Magasinier Magasinier Magasinier Magasinier Magasinier Magasinier Magasinier

1200 750 850 800 795 860 1100

25 ligne(s) sélectionnée(s).

SUPPRESSION DE LA TABLE CIBLE SQL> drop table e_emp; drop table e_emp * ERREUR à la ligne 1 : ORA-02449: clés uniques/primaires de la table référencées par des clés étrangères SQL> drop table e_emp cascade; drop table e_emp cascade * ERREUR à la ligne 1 : ORA-00905: Mot-clé absent SQL> SQL> drop table e_emp cascade constraints; Table supprimée. LA VUE EXISTE MAIS ELLE EST INVALIDE ! SQL> select * from v_emp; select * from v_emp * ERREUR à la ligne 1 : ORA-04063: view "SCOTT.V_EMP" a des erreurs

15 /

DEMONSTRATIONS Chapitre 1 : LES TRAITEMENTS STOCKES DEMONSTRATION : LE PACKAGE (basée sur le schéma SCOTT)

create or replace package pack_emp as

procedure pp_emp (no

in number,

name

in varchar,

salr

in number,

dept

in number)

; procedure pp_sup (no

in number,

dept

in number)

; function f_cpt (no_dept nm_job

in number, in varchar2)

return number ; end; / create or replace package body pack_emp as procedure pp_emp (no

in number,

name

in varchar,

salr

in number,

dept

in number)

as begin insert into emp (empno, ename, sal, deptno) values (no,name, salr, dept); commit; end; procedure pp_sup (no 16 /

in number,

dept in number) as begin delete from emp where empno = no; commit; end; function f_cpt (no_dept nm_job

in number, in varchar2)

return number as cpt number :=0; begin select count(deptno) into cpt from emp where deptno

= no_dept

and upper(job)= upper(nm_job) ; return(cpt); end; end; / DEMONSTRATION : LA FONCTION REM EXECUTION D UNE FONCTION REM Exemple pour executer la procedure EXE_UIWORK : REM execute exe_uwork (10,'MANAGER') REM puis pour vérifier : select * from t_cpt REM REM CREATION D UNE TABLE DE RECEPTION DES RESULTATS DE LA FONCTION DROP TABLE t_cpt; CREATE TABLE t_cpt (dt_jr

date,

nb_salarie number) 17 /

/ REM CREATION DE LA PROCEDURE QUI EXECUTE LA FONCTION DU PACKAGE CREATE OR REPLACE PROCEDURE exe_uwork (no_dept nm_job

number, varchar2)

IS cpt_dept emp.deptno%type := 0; BEGIN cpt_dept := pack_emp.f_cpt(no_dept,nm_job); insert into t_cpt values (sysdate, cpt_dept); commit; END; /

DEMONSTRATION : LE TRIGGER REM REM Creation du trigger qui recherche la sequence sur emp2 REM REM CREATION TABLE EMP2 REM CREATE TABLE EMP2 (empno number(3) not null, ename varchar2(15) not null) TABLESPACE user_data STORAGE (INITIAL 50K) / REM CREATION DE LA SEQUENCE SUR EMP2 REM CREATE SEQUENCE seq_emp2 increment by 1 start with 1 /

18 /

REM CREATION DU TRIGGER REM create or replace trigger trg_seq before insert on emp2 for each row begin /* select de la sequence dans la variable new. */ select seq_emp2.nextval into :new.empno from dual; end; / REM REM REM INSERTION

dANS EMP2

INSERT INTO emp2 (ename) VALUES ('EXO TRIGGER') / COMMIT; REM SELECT * FROM EMP2 /

19 /