====== Cours bases de données / PHP ======





Demandes: <del>[[#trucs|vue clé étrangères,élimination des doublons]]</del> 
===== Documents =====

{{enseignement:fc:fc-01-md-ar.pdf|Modèle relationnel/algèbre}}

{{enseignement:fc:fc-02-sql.pdf|SQL}}

{{enseignement:fc:fc-03-php.pdf|PHP/HTML}}

{{enseignement:fc:fc-04-css-js.pdf|CSS/JavaScript}}

{{enseignement:fc:fc-05-pl-sql.pdf|PL/SQL}}

{{enseignement:fc:fc-06-rh.pdf|Requêtes Hierarchiques}}

===== Accès à la base Oracle =====

Depuis les ordinateurs de la salle TD 5: [[http://b710ntb.univ-lyon1.fr:5560/isqlplus/|Interface web ISQLPlus]]

Hors des salles de TP: [[enseignement:aide:oracle|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.

===== Installation XAMPP =====

[[http://www.apachefriends.org/fr/xampp-windows.html|Page de XAMPP]]

===== Pratique SQL =====

==== Documentation en ligne ====

[[http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm|Documentation SQL Oracle]]

[[http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm|Documentation PL/SQL Oracle]]

==== Mise en place ====

Pour créer une table ''EMPLOYE'' ayant le même contenu que la table ''EMPLOYE'' du compte FC00:
<code sql>
CREATE TABLE EMPLOYE AS
(SELECT * FROM FC00.EMPLOYE)
</code>

Copier dans votre compte toutes tables du compte ''FC00''. La requête suivante permet de les lister:
<code sql>
SELECT TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'FC00'
</code>

==== Requêtes ====

=== Requêtes simples ===


  * 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é.<code sql>select nom, next_day(embauche,'monday')
from employe
</code>
==Correction Requètes simple==
<code SQL>
--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
</code>
=== Avec des jointures et/ou aggrégats ===

  * 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 <code sql>
-- 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
</code>


==== Transactions ====

=== Transaction de base ===

  - Insérer deux ou trois lignes dans la table ''BATIMENT'', puis lister le contenu de la table.
  - Utiliser l'instruction ''ROLLBACK'', puis lister à nouveau le contenu de la table. Que s'est-il passé?
  - 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''?
  - 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 ?
  - 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 ?

=== Transactions concurrentes ===



  - Vider le contenu de la table ''BATIMENT'' et valider l'opération.
  - Se connecter au même compte Oracle à partir d'un autre navigateur et constater aussi que la table est bien vide.
  - 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 ?
  - 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.
  - 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.
  - 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 ?
  - 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).







==== Trucs ====

[[enseignement:lif4:aide-sql|Quelques fonctions utiles]]

=== Formats de dates en Oracle ===

Pour récupérer le format courant:
<code sql>
select *
from nls_session_parameters
where parameter like 'NLS_DATE%';
</code>

Pour changer le format vers un format comme '2008-07-14':
<code sql>
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';
</code>


=== Trouver la vue système adéquate en Oracle ===

<code sql>
--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
</code>

=== Lister les contraintes en Oracle ===
<code sql>
SELECT * FROM USER_CONSTRAINTS;
</code>
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 [[http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1037.htm#i1576022|Documentation de ALL_CONSTRAINTS]]

=== Élimination de doublons en Oracle ===
Avec une table intermédiaire, sur la table ''Truc'' dont la future clé primaire sera (''toto'',''titi''):
<code sql>
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 );

</code>
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.

===Typage dynamique en PL/SQL===
<code sql>
client.mail%TYPE
</code>
Est remplacer dynamiquement par le type de la colonne mail de la table client.
s'utilise comme un type, par exemple varchar