Exercices ORACLE - SQL Et SQL Plus [PDF]
SQL ORACLE EXERCICES SOMMAIRE CHAPITRE 1 MODÈLE PHYSIQUE DE DONNÉES (MPD) DE LA BASE UTILISÉE..............3 CHAPITRE
105 1 337KB
![Exercices ORACLE - SQL Et SQL Plus [PDF]](https://vdoc.tips/img/200x200/exercices-oracle-sql-et-sql-plus.jpg)
- Author / Uploaded
- geeekette
Datei wird geladen, bitte warten...
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 QUICKTOSEE 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 > '20JAN92' / NOM SALAIRE DT_ENTREE NGUYEN 1525.00 22JAN92 MADURO 1400.00 07FEB92 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 QUICKTOSEE 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 /