SGBDR Oracle - SQL (Partie I)
Le Modèle Logique de Données Relationnelle "MLDR" suivant sera utilisé comme exemple :
faculte( facId, nomFac, observation)
departement(depId, nomDep, observation, #facId_faculte
)
etudiant(etudId, nomEtud, prenEtud, dateNaiss, lieuNaiss,
sexe, adresse, serieBac, etabFreq, dipEntree, lieuObt,
dateEntree, observation, #depId_departement)
enseignant (enseignId, nomEnseign, prenEnseign, statut,
diplome, adresse, dateNaiss, sexe, observation,
#depId_departement)
module (codeMod, nomMod, credit, coefficient, semestre,
observation, #depId_departement)
utilisateur (nomUtil, motdepasse, nomPrenUtil, occupation,
statut, etat, # depId_departement)
inscription(inscripId, niveau, anneeAcad, specialite,
#etudId_etudiant)
dispenser (disId, semestre, anneeAcad, observation,
nbreHeure, dateDis, #enseignId_enseignant, #codeMod_module
)
note(noteObt, session, anneeAcad, observation,
#etudId_etudiant, #codeMod_module)
1
Type de données de base
Chaque colonne d'une table comme chaque argument d'une procédure doit être définie
avec un type. Ce type détermine le domaine des valeurs que la colonne pourra stocker.
Le tableau suivant liste les types de base d’Oracle
type Description
Char (taille) Chaîne de caractères de longueur fixe déterminée par le
paramètre taille et dont le minimum est de 1 octet et le
maximum de 2000 octets. Cette chaîne de caractères est
complétée par des espaces
Varchar2 (taille) Chaîne de caractères de longueur variable dont la longueur
maximum est exprimée par le paramètre taille en octets ou
en caractères. La taille maximum est de 4000 octets et la
taille minimum est de 1 octet ou 1 caractère. Le paramètre
taille est obligatoire.
date Donnée de type date située dans une plage comprise entre
le 1er janvier 4712 av JC et le 31 décembre 9999 ap JC
stockant l'année, mois, jour, heures, minutes et secondes
Number(p,s) Numérique de longueur totale p dont une partie décimale
de s chiffres significatifs
Donnée de type caractère pouvant stocker jusqu'à 2
LONG
gigabytes
Chaîne de caractères au format binaire dont la longueur est
RAW(size) indiquée par le paramètre obligatoire size, qui ne peut
excéder 2000 octets
Chaîne de caractères au format binaire pouvant stocker
LONG RAW
jusqu'à 2 gigaoctets
Chaîne de caractères codée en base 64 représentant
ROWID
l'adresse physique unique d'une ligne dans sa table
2
Création d’une table
La commande CREATE TABLE définit de façon unique chaque colonne d’une table. Chaque
colonne a un minimum de 3 attributs : le nom, le type et la taille.
Les règles de création d’une table
1. Le nom peut avoir un maximum de 30 caractères
2. les caractères alphabétiques (A-Z, a-z), numérique (0-9), $,#, _ sont permis
3. le nom doit commencer par les alphabets
4. les mots réservés d’oracle ne sont pas permis
Syntaxe :
CREATE TABLE nom_table
( nom_colonne1 type(taille),
nom_colonne2 type(taille),
nom_colonne3 type(taille),
…) ;
Exemple :
CREATE TABLE faculte(
facId number (2),
nomFac Varchar2 (80),
observation Varchar2 (200)
);
Insertion des données
Syntaxe :
INSERT INTO nom_table(nom_colonne1, nom_colonne2)
VALUES (expression1, expression2) ;
3
Exemple :
INSERT INTO faculte(facId, nomFac, observation)
VALUES (1, 'FST', 'creee en 2010') ;
INSERT INTO faculte(facId, nomFac, observation)
VALUES (2, 'FESA', 'creee en 2010') ;
Visualiser les données d’une table
Toutes les lignes et toutes les colonnes
Syntaxe :
SELECT * FROM nom_table ;
Exemple :
SELECT * FROM etudiant ;
Colonnes sélectionnées et toutes les lignes
Syntaxe :
SELECT nom_col1, nom_col2 FROM nom_table ;
Exemple :
SELECT etudId, nomEtud, prenEtud FROM etudiant;
Lignes sélectionnées et toutes les colonnes
Syntaxe :
SELECT * FROM nom_table WHERE condition;
4
Exemple :
SELECT * FROM etudiant WHERE serieBac='C';
Colonnes sélectionnées et lignes sélectionnées
Syntaxe :
SELECT nom_col1, nom_col2 FROM nom_table WHERE condition;
Exemple :
SELECT etudId, nomEtud, prenEtud FROM etudiant WHERE
serieBac='C';
Eliminer la redondance dans l’affichage des données avec SELECT
Syntaxe :
SELECT DISTINCT * FROM nom_table ;
Syntaxe :
SELECT DISTINCT nom_col1, nom_col2 FROM nom_table ;
Exemple :
SELECT DISTINCT lieuNaiss FROM etudiant;
Classement des données d’une table
Syntaxe :
SELECT * FROM nom_table ORDER BY nom_col1, nom_col2 [ordre de
tri];
Exemple 1 :
5
SELECT * FROM etudiant WHERE serieBac='C' ORDER BY nomEtud;
Exemple 2:
SELECT * FROM etudiant WHERE serieBac='C' ORDER BY nomEtud
DESC;
Création d’une table à partir d’une autre table
Syntaxe :
CREATE TABLE nom_table( nom_col, nom_col)
AS SELECT nom_col, nom_col FROM nom_table;
Exemple :
CREATE TABLE student (numEtud, nom, prenom)
AS SELECT etudId, nomEtud, prenETud FROM etudiant;
Insertion de données dans une table à partir d’une autre table
Syntaxe :
INSERT INTO nom_table
SELECT nom_col1, nom_colN FROM nom_table;
Operation de suppression
Suppression de toutes les lignes
Syntaxe :
DELETE FROM nom_table;
Exemple :
6
DELETE FROM student;
Suppression des lignes spécifiques
Syntaxe :
DELETE FROM nom_table WHERE condition;
Exemple :
DELETE FROM inscription WHERE etudId_etudiant = '64312';
Suppression des lignes spécifiques en fonction des données d’une autre table
On désire de fois supprimer les lignes d’une table en fonction des donnees d’une autre table.
Puisqu’il n’est pas possible de lister plus d’une table dans la clause FROM en faisant
DELETE, la clause EXISTS peut être utilisée.
Exemple :
Supprimer les inscriptions de l’étudiant dont le prénom est «Moubarak»
DELETE FROM inscription WHERE EXISTS(SELECT prenEtud FROM
etudiant WHERE etudId = etudId_etudiant AND prenEtud =
'Moubarak');
Mise à jour du contenu d’une table
Mise à jour de toutes les lignes
Syntaxe :
UPDATE nom_table
SET nom_col1 = expression1, nom_col2 = expression2 ;
Exemple :
7
UPDATE enseignant SET statut = 'Permanent';
Mise à jour conditionnelle
Syntaxe :
UPDATE nom_table
SET nom_col1 = expression1, nom_col2 = expression2
WHERE condition;
Exemple :
UPDATE enseignant SET statut = 'Permanent'
WHERE enseignId = '1209';
Modification de la structure d’une table
Ajout des nouvelles colonnes
Syntaxe :
ALTER TABLE nom_table
ADD(nom_Col type(taille),
nom_Col type(taille)…);
Exemple :
ALTER TABLE departement
ADD(dateCreation date);
8
Suppression d’une colonne
Syntaxe :
ALTER TABLE nom_table DROP COLUMN nom_Col;
Exemple :
ALTER TABLE departement DROP COLUMN dateCreation;
Modification d’une table existante
Syntaxe :
ALTER TABLE nom_table
MODIFY(nom_col nveau_type(nvelle_taille));
Exemple :
ALTER TABLE faculte MODIFY(nomFac varchar2(100));
NB : La clause ALTER TABLE ne peut pas :
1. changer le nom d’une table
2. changer le nom d’une colonne
3. diminuer la taille d’une colonne si la table contient déjà des données
Renommer une table
Syntaxe :
RENAME nom_table TO nveau_nom_table ;
Exemple :
RENAME faculte TO facultes ;
9
Supprimer et recréer une table
Syntaxe :
TRUNCATE TABLE nom_table;
Exemple :
TRUNCATE TABLE student;
Détruire une table
Syntaxe :
DROP TABLE nom_table;
Exemple :
DROP TABLE student;
Afficher les tables créées par l’utilisateur
SELECT * FROM TAB ;
Afficher la structure d’une table
Syntaxe :
DESCRIBE nom_table;
Exemple :
DESCRIBE faculte;
10
Travaux Pratiques
1. Créer les tables décrites ci-dessous :
Nom de la table : client
Description : utilisée pour stocker l’information du client
Nom de la colonne Type de données Taille
NoClient Varchar2 6
nom Varchar2 20
Adresse1 Varchar2 30
Adresse2 Varchar2 30
ville Varchar2 15
codePin number 8
etat Varchar2 15
balDue number 10, 2
Nom de la table : produit
Description : utilisée pour stocker l’information du produit
Nom de la colonne Type de données Taille
NoProduit Varchar2 6
Description Varchar2 15
pourcentProfit number 4, 2
mesureUnite Varchar2 10
quantite number 8
nivRecomm number 8
prixVente number 8, 2
prixAchat number 8, 2
11
Nom de la table : empVente
Description : utilisée pour stocker l’information des employés de vente
Nom de la colonne Type de données Taille
NoEmp Varchar2 6
nomEmp Varchar2 20
Adresse1 Varchar2 30
Adresse2 Varchar2 30
ville Varchar2 20
codePin number 8
etat Varchar2 20
montantSal number 8, 2
TGTtoget number 6, 2
venteYTD number 6, 2
remarque Varchar2 60
2. Insérer les données suivantes dans leurs tables respectives
a) Table client
Noclient Nom Ville codePin etat baldue
C00001 Ivan Bayross Mumbai 400054 Maharashtra 15000
C00002 Mamta Muzumdar Madras 780001 Tamil Nadu 0
C00003 Chhaya Bankar Mumbai 400057 Maharashtra 5000
C00004 Ashwini Joshi Bangalore 560001 karnataka 0
C00005 Hansel Colaco Mumbai 400060 Maharashtra 2000
C00006 Deepak Sharma Mangalore 560050 karnataka 0
b) Table Produit
noProduit description pourcent mesure quantite nivRecomm PrixVente prixAchat
profit Unite
P00001 T-shirts 5 pièce 200 50 350 250
P0345 Shirts 6 pièce 150 50 500 350
12
P06734 Cotton jeans 5 pièce 100 20 600 450
P07865 Jeans 5 pièce 100 20 750 500
P07868 Trousers 2 pièce 150 50 850 550
P07885 Pull overs 2.5 pièce 80 30 700 450
P07965 Denim shirts 4 pièce 100 40 350 250
P07975 Lycra tops 5 pièce 70 30 300 175
P08865 skirts 5 pièce 75 30 450 300
c) Table Empvente
noEmp nomEmp Adresse1 Adresse2 ville codePin etat
E00001 Aman A/14 Worli Mumbai 400002 Maharashtra
E00002 Omkar 65 Nariman Mumbai 400001 Maharashtra
E00003 Raj P-7 Bandra Mumbai 400032 Maharashtra
E00004 Ashish A/5 Juhu Mumbai 400044 Maharashtra
noEmp montantSal TGTtoget venteYTD Remarque
E00001 3000 100 50 bonne
E00002 3000 200 100 bonne
E00003 3000 200 100 bonne
E00004 3500 200 150 bonne
3. Recherche des informations
a. Trouver les noms de tous les clients
b. Afficher le contenu de la table ‘client’
c. lister les noms, villes et Etats de tous les clients
d. lister les produits disponibles
e. afficher tous les clients qui sont à ‘Mumbai’
f. trouver les employés de vente dont le salaire est égal à 3000
4. Mise à jour des informations
a. Changer la ville du client ‘C00005’ par ‘Bangalore’
13
b. Changer la balDue du client ‘C00001’ par 1000
c. Changer le prix d’achat du ‘trousers’ par 950
d. Changer la ville des employés de vente par ‘Pune’
5. Suppression des données
a. Supprimer tous les employés de vente dont le salaire égal à 3500
b. Supprimer tous les produits où la quantité disponible est égale à 100
c. Supprimer les clients dont la colonne ‘Etat’ porte la valeur ‘Tamil Nadu’
6. Modification de la structure d’une table
a. Ajouter la colonne ‘Telephone’ (type=number, taille=10) à la table client
b. Changer la taille de la colonne ‘prixVente’ de la table ‘produit’ par 10, 2
7. Suppression de la table
a. Détruire la table ‘client’
8. Changer le nom d’une table
b. Changer le nom de la table ‘empVente’ par ‘employes’
14
