Demandes: vue clé étrangères,élimination des doublons

Modèle relationnel/algèbre

SQL

PHP/HTML

CSS/JavaScript

PL/SQL

Requêtes Hierarchiques

Depuis les ordinateurs de la salle TD 5: Interface web ISQLPlus

Hors des salles de TP: Accès via un tunnel SSH

Comptes: FCxx où xx est le numéro de votre compte UNIX (fcinfoxx). Le mot de passe Oracle est égal au login Oracle.

Page de XAMPP

Documentation SQL Oracle

Documentation PL/SQL Oracle

Pour créer une table EMPLOYE ayant le même contenu que la table EMPLOYE du compte FC00:

CREATE TABLE EMPLOYE AS
(SELECT * FROM FC00.EMPLOYE)

Copier dans votre compte toutes tables du compte FC00. La requête suivante permet de les lister:

SELECT TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'FC00'

• Donner les noms et les départements des employés gagnant entre 20000 et 25000.
• Donner les noms et les salaires des employés commerciaux ou ingénieurs.
• Donner les noms des employés dont le nom commence par M.
• Donner la liste des employés ayant un nom de plus de 4 lettres se terminant par “t”.
• Donner les noms des employés du département 30 ayant un salaire supérieur à 25000.
• Donner les noms des employés directeurs, ou commerciaux et travaillant dans le département 10.
• Donner les noms des employés directeurs ou commerciaux, et travaillant dans le département 10.
• Donner tous les employés classés par fonction, et pour chaque fonction classés par salaire décroissant.
• Donner la liste des employés ayant une commission (non nulle), classée par commission décroissante.
• Donner les noms des personnes embauchées après janvier 1995.
• Donner la liste des employés dont la commission est strictement inférieure à 10% du salaire.
• Donner les noms des ingénieurs embauchés avant le 1 janvier 2000.
• Quelles sont les fonctions ne donnant pas lieu à commission.
• Donner les noms et fonctions de chacun. Classer le résultat par fonction puis par nom.
• Donner pour chaque commercial son revenu (salaire + commission).
• Donner la date d’embauche de chaque employé arrondie à l’année.
• Donner pour chaque employé le nombre de jours depuis son embauche.
• Donner la liste de tous les employés dont le nom ressemble à ‘DUPONT’.
• Afficher tous les salaires avec un $ en tête et au moins trois chiffres (dont deux décimales).
• Donner la liste de tous les noms des employés en ayant supprimé tous les 'L' et les 'E' en tête des noms.
• Donner la liste de tous les noms des employés en ayant remplacé les A et les M par des * dans les noms.
• Donner la date du lundi suivant l'embauche de chaque employé.

  SELECT nom, next_day(embauche,'monday')
  FROM employe

--Requêtes
--Requêtes simples
 
--    *
--      Donner les noms et les départements des employés gagnant entre 20000 et 25000.
SELECT NOM,NUM_DEPT 
FROM EMPLOYE 
WHERE SALAIRE BETWEEN 20000 AND 25000;
 
 --   *
 --     Donner les noms et les salaires des employés commerciaux ou ingénieurs.
 
SELECT NOM,SALAIRE 
FROM EMPLOYE 
WHERE FONCTION IN ('ingenieur','commercial');
 --   *
 --     Donner les noms des employés dont le nom commence par M.
SELECT NOM
FROM EMPLOYE 
WHERE SUBSTR(NOM,1,1)='M';
 --   *
 --     Donner la liste des employés ayant un nom de plus de 4 lettres se terminant par “t”.
SELECT *
FROM EMPLOYE 
WHERE LENGTH(NOM)>4 AND SUBSTR(NOM,LENGTH(NOM),1)='t';
 --   *
 --     Donner les noms des employés du département 30 ayant un salaire supérieur à 25000.
SELECT NOM
FROM EMPLOYE 
WHERE NUM_DEPT=30 AND SALAIRE>25000;
 
 --   *
 --     Donner les noms des employés directeurs, ou commerciaux et travaillant dans le département 10.
SELECT NOM
FROM EMPLOYE 
WHERE NUM_DEPT=10 AND FONCTION IN ('ingenieur','directeur');
 
  --  *
   --   Donner tous les employés classés par fonction, et pour chaque fonction classés par salaire décroissant.
SELECT *
FROM EMPLOYE 
ORDER BY FONCTION,SALAIRE DESC;
 
 --   *
 --     Donner la liste des employés ayant une commission (non nulle), classée par commission décroissante.
SELECT *
FROM EMPLOYE 
WHERE COMMISSION<>0
ORDER BY COMMISSION DESC;
 --   *
 
  --    Donner les noms des personnes embauchées après janvier 1995.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';
SELECT *
FROM EMPLOYE 
WHERE EMBAUCHE>='19950101';--Rq : On ne peut pas mettre >'19950100' : Pourquoi?
 --   *
  ----    Donner la liste des employés dont la commission est strictement inférieure à 10% du salaire.
SELECT *
FROM EMPLOYE 
WHERE COMMISSION<salaire/10
  --  *
  --    Donner les noms des ingénieurs embauchés avant le 1 janvier 2000.
SELECT NOM
FROM EMPLOYE 
WHERE EMBAUCHE<='20000101' AND FONCTION ='ingenieur'
  --  *
  --    Quelles sont les fonctions ne donnant pas lieu à commission.
SELECT DISTINCT FONCTION
FROM EMPLOYE 
MINUS
SELECT FONCTION
FROM EMPLOYE 
WHERE nvl(COMMISSION,0)<>0
  --  *
 --     Donner les noms et fonctions de chacun. Classer le résultat par fonction puis par nom.
SELECT NOM
FROM EMPLOYE
ORDER BY FONCTION,NOM
  --  *
  --    Donner pour chaque commercial son revenu (salaire + commission).
SELECT nvl(COMMISSION,0)+SALAIRE AS REVENU,EMPLOYE.*
FROM EMPLOYE 
WHERE FONCTION ='commercial';
 --   *
 --     Donner la date d’embauche de chaque employé arrondie à l’année.
--Avec notre format de date
SELECT SUBSTR(EMBAUCHE,1,4) AS ANNEE,EMPLOYE.*
FROM EMPLOYE
--De maniere plus générale
SELECT TO_CHAR(EMBAUCHE,'YYYY') AS ANNEE,EMPLOYE.*
FROM EMPLOYE
--ou encore ...
 
 --   *
 --     Donner pour chaque employé le nombre de jours depuis son embauche.
SELECT round(SYSDATE-EMBAUCHE) AS Nb_Jours_Emb,EMPLOYE.*
FROM EMPLOYE
 --   *
 --     Donner la liste de tous les employés dont le nom ressemble à ‘DUPONT’.
SELECT EMPLOYE.*
FROM EMPLOYE
WHERE UPPER(NOM) LIKE '%DUPONT%';--"Ressemble"
 --   *
 --     Afficher tous les salaires avec un $ en tête et au moins trois chiffres (dont deux décimales).
SELECT '$'||TO_CHAR(SALAIRE,'99999999990.000')||'000',EMPLOYE.*
FROM EMPLOYE;
 
 --   *
  --    Donner la liste de tous les noms des employés en ayant supprimé tous les 'L' et les 'E' en tête des noms.
SELECT '$'||TO_CHAR(SALAIRE,'99999999990.000')||'000',EMPLOYE.*
FROM EMPLOYE;
  --  *
 --     Donner la liste de tous les noms des employés en ayant remplacé les A et les M par des * dans les noms.
SELECT translate(UPPER(NOM),'AM','**'),EMPLOYE.*
FROM EMPLOYE;
 --   *
  --    Donner la date du lundi suivant l'embauche de chaque employé.
SELECT next_day(EMBAUCHE,'Monday'),TO_CHAR(EMBAUCHE,'DAY DD MON YYYY')
	,EMPLOYE.*
FROM EMPLOYE;--Avec contrôle

• Afficher le nom des départements dont le bâtiment possède une entrée principale au sud.
• Afficher nombre d'employés pour le département dirigé par 'Julius'
• Donner le salaire moyen dans l'entreprise
• Donner le salaire moyen, ainsi le nombre de personnes par département
• Donner le bâtiment dans lequel travaillent le plus de personnes
• Pour chaque directeur de département, donner le rapport en pourcentage entre son salaire et le salaire moyen des autres employé du département qu'il dirige

  -- version 1
  SELECT Nom, 
  100*Salaire/
  (
  SELECT AVG(Salaire)
  FROM Employe NATURAL JOIN Departement
  WHERE Num_chef = Dir.Num
        AND Employe.Num != Dir.Num
  ) AS Rapport_de_salaire
   
  FROM Employe Dir
  WHERE Num IN (SELECT Num_chef FROM Departement);
   
  -- version 2
  SELECT Directeur.Nom, 
       Directeur.Salaire/AVG(Employe.Salaire)*100 AS Rapport_de_salaire
  FROM Employe NATURAL JOIN Departement, Employe Directeur
  WHERE Directeur.Num = Departement.Num_chef
    AND Employe.Num != Directeur.Num
  GROUP BY Departement.Num_chef, Directeur.Nom, Directeur.Salaire

1. Insérer deux ou trois lignes dans la table BATIMENT, puis lister le contenu de la table.
2. Utiliser l'instruction ROLLBACK, puis lister à nouveau le contenu de la table. Que s'est-il passé?
3. Insérer deux ou trois lignes dans la table, utiliser l'instruction COMMIT puis l'instruction ROLLBACK. Lister le contenu de la table. Quel est le rôle de l'instruction COMMIT?
4. Insérer à nouveau deux ou trois lignes dans la table et fermer brutalement votre session (par exemple en quittant le navigateur), puis rentrer à nouveau dans votre compte Oracle. Les données saisies ont-elles été préservées ?
5. Insérer deux ou trois lignes dans la table, créer une table puis insérer à nouveau deux ou trois lignes dans la table BATIMENT et enfin utiliser l'instruction ROLLBACK. Lister le contenu de la table BATIMENT. Que peut-on en conclure ?

1. Vider le contenu de la table BATIMENT et valider l'opération.
2. Se connecter au même compte Oracle à partir d'un autre navigateur et constater aussi que la table est bien vide.
3. Insérer dans chacune des fenêtres deux ou trois lignes différentes. Que voit-on des lignes insérées à partir de chacun des deux écrans ? Conclusion ?
4. Valider les modifications dans l'une des deux fenêtre puis lister le contenu de la table dans chacune des deux fenêtres. Conclure sur la portée de la validation.
5. Valider les modifications dans chacune des fenêtres. Insérer à nouveau quelques lignes dans chacune des fenêtres puis annuler les modifications dans une des deux fenêtres. Lister le contenu de la table dans les deux comptes et conclure sur la portée de l'annulation.
6. Valider les mise-à-jour dans chaque fenêtre. Ajouter une clé à la table. Ajouter dans chaque fenêtre une ligne ayant la même valeur pour la clé. Que se passe-t-il ? Annuler l'insertion dans la fenêtre encore active. Que devient le blocage ?
7. Refaire les opérations du point précédent, mais en validant l'insertion au lieu de l'annuler. Expliquer ce qui c'est passé dans les deux cas (annulation et validation).

Quelques fonctions utiles

Pour récupérer le format courant:

SELECT *
FROM nls_session_parameters
WHERE parameter LIKE 'NLS_DATE%';

Pour changer le format vers un format comme '2008-07-14':

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';

--Vue : all_catalog contient toutes les vues oracles
--Ici je m''en sert pour chercher les paramètres liés au language.
SELECT DISTINCT TABLE_NAME
 FROM all_catalog WHERE TABLE_NAME LIKE '%NLS%' 
 
--Liste des paramètres NLS pour la session en cours
SELECT *  FROM NLS_SESSION_PARAMETERS 
--Liste des paramètres NLS pour la session en cours
SELECT * FROM NLS_DATABASE_PARAMETERS
 
--Permet de connaitre la description des tables FC06
SELECT * FROM all_tab_columns WHERE owner='FC06'
--On s'en sert après :
SELECT TABLE_NAME,column_name,data_type,data_length 
FROM all_tab_columns 
WHERE owner='FC06' 
ORDER BY TABLE_NAME,column_name

SELECT * FROM USER_CONSTRAINTS;

Les clés étrangères ont le type R, les clés primaires le type P, les contraintes de valeur le type C, les contraintes UNIQUE le type U, voir Documentation de ALL_CONSTRAINTS

Avec une table intermédiaire, sur la table Truc dont la future clé primaire sera (toto,titi):

CREATE TABLE TMP AS -- Attention: COMMIT au passage
SELECT toto, titi,MAX(rowid) AS rowid_a_garder
FROM Truc
GROUP BY toto,titi
HAVING COUNT(*)>1;
 
DELETE FROM Truc
WHERE (toto,titi) IN (SELECT toto,titi FROM tmp)
  AND rowid NOT IN (SELECT rowid_a_garder FROM tmp);
 
DROP TABLE TMP; -- Attention: COMMIT au passage
 
--un peu moins orthodoxe il y a aussi :
DELETE FROM  Truc B 
WHERE col1 IN (SELECT A.col1 
               FROM Truc A 
               WHERE B.rowid<A.rowid 
               GROUP BY A.col1 );

Attention: la ligne conservée parmis les doublons sur la clé primaire est arbitraire. Si on veut utiliser un autre critère, il faut mettre dans TMP les infos pour l'évaluer.

client.mail%TYPE

Est remplacer dynamiquement par le type de la colonne mail de la table client. s'utilise comme un type, par exemple varchar