\documentclass[pdftex,twoside,openright,11pt,a4paper]{book}

\usepackage{latexsym}
\usepackage{amssymb,amsmath}

\usepackage{listings}

\newcommand{\src}[1]{\lstinline!#1!}

\lstset{language=sql, extendedchars=false, basicstyle=\ttfamily,
  commentstyle=\itshape, showstringspaces=false}

\usepackage[francais]{babel}

\newtheorem{theo}{Theoreme}
\newtheorem{defi}{Definition}
\newtheorem{prop}{Proposition}
\newtheorem{nota}{Notation}
\newtheorem{lemme}{Lemme}
\newtheorem{hyp}{Hypothese}
\newtheorem{cts}{Contrainte}
\newtheorem{ex}{Exemple}
\newtheorem{exo}{Exercice}
\newtheorem{cex}{Contre-exemple}
\newtheorem{syn}{Syntaxe}
\newtheorem{nb}{Remarque}
\newtheorem{ax}{Schemas d'axiomes}
\newtheorem{ri}{Regles d'inference}
\newtheorem{principe}{Principe}
\newtheorem{dem}{Preuve}
\newtheorem{algo}{Algorithme}

\begin{document}

\thispagestyle{empty}
\title{Bases de donnees}
\author{}

\maketitle

\newpage

\pagenumbering{roman}

\tableofcontents
\newpage

\pagenumbering{arabic}

\chapter[\textsc{SQL} (\textit{Structured Query Language})]{Etude d'un langage assertionnel~: \textsc{SQL}
  (\textit{Structured Query Language})}
\label{cha:sql}

L'algebre relationnelle presentee dans le chapitre
\ref{cha:algbre-relationnelle} est un moyen puissant de calcul sur les
relations. Elle permet de construire une relation contenant les
elements sur lesquels porte une requete sur une base de donnees.
L'algebre relationnelle peut etre assimilee a un langage imperatif~:
on doit decrire \emph{comment} est obtenue la relation <<~reponse~>>.

L'objectif d'un langage assertionnel est d'offrir a l'utilisateur un
moyen d'exprimer une requete en ne decrivant que le resultat recherche
a l'aide d'une assertion (expression logique) sans devoir expliquer la
façon de le trouver. En d'autres termes, l'assertion exprime le
<<~quoi~>> et non le <<~comment~>>. Dans ce cas, l'utilisateur qualifie
les resultat et le systeme compose la procedure de recherche.

Les objets manipules par \textsc{SQL} sont les suivants~:

\begin{itemize}
\item des tables~: relations ou vues externes (cf. chapitre
~\ref{cha:views});
\item des colonnes (\emph{columns}) attributs de <<~base~>> des
  relations ou des vues externes ou des attributs calcules a partir
  des attributs de base (via des agregations par exemple);
\item des lignes (\emph{rows}) qui representent les tuples.
\end{itemize}

\begin{nb}
  l'algebre relationnelle que nous avons presentee precedemment
  manipulait les relations comme des ensembles de tuples. Dans
  \textsc{SQL}, l'algebre relationnelle manipule en fait des
  multi-ensembles (\emph{bags} en anglais) qui sont des ensembles dans
  lesquels les elements peuvent avoir plusieurs occurrences.
  L'utilisation de multi-ensembles permet d'<<~optimiser~>> les
  requetes. Par exemple, si l'on considere l'union de deux ensembles
  $R$ et $S$, on est oblige de comparer tout tuple de $S$ avec ceux de
  $R$ pour verifier qu'il n'apparaît pas deja dans $R$. L'utilisation
  des multi-ensembles dans les operations intermediaires permet donc
  d'accelerer les calculs.
\end{nb}

Des complements sur ce chapitre peuvent etre trouves dans
\cite{celkos99:_sql_smart,celkos97:_sql_puzzl_answer,garcia-molina02:_datab,celkos:_column_DBMS}.

\section{Historique et presentation}
\label{sec:historique}

Il existe plusieurs dialectes de \textsc{SQL}. Tout d'abord, il existe
trois versions standards de \textsc{SQL}~:
\begin{itemize}
\item une version ANSI (\emph{American National Standard Institute});
\item un standard mis a jour en 1992 appele \textsc{SQL-92} ou
  \textsc{SQL2};
\item une derniere version, notee \textsc{SQL:1999}, qui etend
  \textsc{SQL2} avec des notions <<~objets~>> et d'autres
  caracteristiques.
\end{itemize}

La plupart des versions commerciales de \textsc{SQL} fournies par les
SGBD commerciaux respectent le standard ANSI et se conforment a la
plupart des extensions de \textsc{SQL2}. Une petite partie de
\textsc{SQL:1999} est integree et, la plupart du temps, ces versions
de \textsc{SQL} possedent leurs propres extensions. Dans ce chapitre,
nous etudierons principalement la version ANSI de \textsc{SQL} et
\textsc{SQL2}. Si nous presentons des instructions presentes dans
\textsc{SQL99}, nous le preciserons.

Le langage \textsc{SQL} fonctionne selon deux modes~:

\begin{itemize}
\item un mode interprete, i.e. dans lequel \textsc{SQL} est vu comme
  un langage a part entiere. Nous presenterons \textsc{SQL}
  principalement selon ce mode;
\item un mode integre, dans lequel \textsc{SQL} est integre a un autre
  langage de programmation (\textsc{C}, \textsc{Java}, \textsc{Cobol}
  etc.). Nous presenterons dans le chapitre~\ref{cha:int-sql}
  l'interface entre \textsc{SQL} et \textsc{Java}.
\end{itemize}

On peut distinguer dans \textsc{SQL} trois sous-langages
particuliers~:
\begin{itemize}
\item le langage de manipulation de donnees (DML pour \emph{Data
    Manipulation Language}), qui permet d'obtenir des informations et
  de les mettre a jour;
\item le langage de description de donnees (DDL pour \emph{Data
    Description Language}), qui permet de creer des relations, de
  modifier leur schema etc.
\item le langage de contrôle des donnees (DCL pour \emph{Data Control
    Language}) qui permet de restreindre l'acces des donnees.
\end{itemize}

Les mots-cles de ces trois langages sont les suivants~:

\vspace{0.25cm}

\begin{tabular}[h]{*{3}{| l} |}
  \hline
  DDL & DML & DCL\\
  \hline
  \src{ALTER} & \src{DELETE} & \src{GRANT}\\
  \src{CREATE} & \src{INSERT} & \src{REVOKE}\\
  \src{COMMENT} & \src{SELECT}& \\
  \src{DESCRIBE} & \src{UPDATE}& \\
  \src{DROP} & & \\
  \src{RENAME} & & \\
  \hline
\end{tabular}

\vspace{0.25cm}

Nous allons presenter ces trois langages dans ce qui suit. Nous
considererons une relation $R$ d'attributs $A_1, \ldots, A_n$.

\section{Le langage de manipulation de donnees}
\label{sec:langage-manipulation}

Le langage de manipulation de donnees permet soit d'obtenir une
information a partir d'une ou plusieurs relations, soit de mettre a
jour des tuples d'une relation. Dans le cas de l'obtention
d'information, le resultat est edite sous la forme d'un tableau.

\subsection{Bloc de qualification}
\label{sec:bloc-de-qual}

La structure de base est le bloc de qualification\footnote{\textsc{SQL}
  est un langage insensible a la casse des caracteres, que ce soit
  pour ses mots cles ou pour les noms de relations ou d'attributs.}.

\begin{syn}~

  \begin{lstlisting}
  SELECT Ai,..., An -- colonnes et agregations
  FROM R            -- relation
  WHERE F           -- assertion
  GROUP BY A        -- regroupement
  HAVING H          -- assertion
  ORDER BY T        -- tri
  ;
  \end{lstlisting}

~
\end{syn}

Les clauses \src{GROUP}, \src{ORDER} et \src{HAVING} sont
optionnelles. Nous pouvons detailler informellement la semantique des
clauses \src{FROM}, \src{WHERE} et \src{SELECT}~:
\begin{itemize}
\item \src{FROM} permet de preciser quelles sont la ou les relations
  sur lesquelles on pose la requete;
\item \src{WHERE} permet de poser une assertion qui doit etre verifiee
  par les lignes de la table solution;
\item \src{SELECT} designe les attributs des tuples qui doivent
  apparaître dans la solution.
\end{itemize}

Le bloc de qualification <<~simple~>> (i.e. ne contenant que les
clauses \src{SELECT}, \src{FROM} et \src{WHERE}) est donc une
combinaison d'une projection et d'une selection.

\begin{nb}
  le bloc de qualification se termine toujours par un <<~\src{\;}~>>.
\end{nb}

\subsection{Projection}
\label{sec:projection-1}

La syntaxe d'une projection simple est presentee dans ce qui suit.

\begin{syn}
~

\begin{lstlisting}
  SELECT Ai,..., Ap
  FROM R;
\end{lstlisting}

~ 
\end{syn}

\begin{ex}
  La requete <<~\emph{quelle est la liste des numeros de reference et
  des types de tous les ordinateurs?}~>> s'exprime de la façon
  suivante~:

  \begin{lstlisting}
    SELECT ref, type
    FROM Ordinateur;
  \end{lstlisting}
\end{ex}

Remarquons que l'on peut obtenir des tuples identiques, en particulier
si on ne selectionne pas la cle primaire parmi les colonnes de la
projection. Pour eviter cela, on peut utiliser le mot cle
\src{DISTINCT} (on voit ici apparaître le fait qu'on travaille avec
des multi-ensembles dans \textsc{SQL}).

\begin{syn}
~

\begin{lstlisting}
  SELECT DISTINCT Ai,..., Ap
  FROM R;
\end{lstlisting}

~
\end{syn}

\subsection{Selection}
\label{sec:slection-1}

\begin{syn}
~

  \begin{lstlisting}
    SELECT *
    FROM R
    WHERE F;
  \end{lstlisting}

~
\end{syn}
  
Ici, <<~\src{*}~>> designe l'ensemble des colonnes de la relation $R$.
$F$ est une expression logique permettant de selectionner les
colonnes.

\subsubsection{Expression logique de selection}
\label{sec:expr-logiq-de}

L'expression logique peut contenir des constantes et des noms de
colonnes de la relation $R$. On la construit avec les operateurs
suivants~:

\begin{itemize}
\item \src{<}, \src{<=}, \src{=}, \src{>=}, \src{>}, \src{<>};
\item \src{AND}, \src{OR}, \src{NOT};
\item \src{IN}, par exemple \src{IN ('MS\_DOS', 'UNIX')};
\item \src{BETWEEN}, par exemple \src{BETWEEN 15 AND 30};
\item \src{LIKE} en utilisant un pattern et des jokers (cf. ce qui
  suit).
\end{itemize}

Les chaînes de caracteres peuvent etre comparees en utilisant
l'operateur \src{LIKE} qui permet de comparer une chaîne de caracteres
avec un pattern utilisant les symboles~:

\begin{itemize}
\item \src{\_} qui remplace n'importe quel caractere;
\item \src{\%} qui remplace n'importe quelle sequence de caracteres;
\end{itemize}

Par exemple \src{'DUPONT' LIKE 'D\%ONT} renverra \src{TRUE},
\src{'DUPONT' LIKE 'D\_\_T'} renverra \src{FALSE}, et \src{'DOIT' LIKE
  'D\_\_T} renverra \src{TRUE}. Si l'on veut utiliser les caracteres
\src{\_} ou \src{\%} dans une chaîne de caracteres, on peut utiliser
le mot cle \src{ESCAPE} suivi d'un caractere qui representera un
caractere d'echappement (par exemple \src{pourcentage LIKE '100x\%'
  ESCAPE 'x'}).

\subsubsection{La valeur \src{NULL}}
\label{sec:la-valeur-null}

La valeur \src{NULL} est une valeur qui peut etre affectee a certains
attributs lorsque~:

\begin{itemize}
\item on ne sait pas quelle est la valeur de l'attribut;
\item aucune valeur ne peut convenir pour l'attribut;
\item on ne peut pas connaître la valeur de l'attribut (par exemple un
  numero de telephone).
\end{itemize}

Il faut noter que \src{NULL} n'est pas une constante. On ne peut pas
l'utiliser explicitement comme operande dans la clause \src{WHERE}. Si
\src{NULL} apparaît dans une comparaison, on peut obtenir une valeur
de verite \src{UNKNOWN} pour l'expression consideree (qui est
consideree pour le retour de la requete comme s'il s'agissait d'une
valeur de verite \src{FALSE}).

\subsection{Entetes de colonnes}
\label{sec:entetes-de-colonnes}

L'entete des colonnes de la table resultat d'une requete est soit le
nom de l'attribut correspondant, soit une expression de <<~calcul~>>
contenant le nom d'un attribut. Pour une meilleure presentation, on
peut changer le nom des colonnes de la table resultat.

\begin{syn}
~

    \begin{lstlisting}
    SELECT col1 AS 'Nouveau nom 1', col2 AS 'Nouveau nom 2'
    FROM R
    ...
    ;
  \end{lstlisting}
\end{syn}

L'entete du resultat affiche portera les noms \texttt{Nouveau nom 1}
et \texttt{Nouveau nom 2} au lieu de \texttt{col1} et
\texttt{col2}. 

\begin{ex}
  On peut demander quels sont les prix en euros des ordinateurs~:

  \begin{lstlisting}
    SELECT prix / 6.55957 AS prix_euro
    FROM Ordinateur ;
  \end{lstlisting}
\end{ex}

Une application importante de cette fonctionnalite est la
personnalisation par rapport a une langue donnee
(internationalisation). Un exemple classique est celui des catalogues
multilingues qui sont issues d'une unique base de donnees.

\subsection{Tris des colonnes}
\label{sec:tris-des-colonnes}

Pour fournir un resultat trie suivant certaines colonnes, il existe
une clause particuliere, la clause \src{ORDER BY}.

\begin{syn}
 ~

  \begin{lstlisting}
    ORDER BY <listes attributs>
  \end{lstlisting}
  
  où chaque element de la liste est un attribut suivi de \src{ASC}
  (ordre ascendant) ou \src{DESC} (ordre descendant).
\end{syn}

\begin{ex}
  La requete permettant d'editer le catalogue des ordinateurs dans
  l'ordre des prix decroissants et des capacites de memoire
  croissantes est~:

  \begin{lstlisting}
    SELECT *
    FROM Ordinateur
    ORDER BY prix DESC, capa_mem ASC ;
  \end{lstlisting}
\end{ex}

\subsection{Operation de produit}
\label{sec:opration-de-produit}

Nous allons maintenant nous interesser aux operation impliquant plus
d'une relation. La premiere operation a laquelle nous allons nous
interesser est le produit de relation.

\begin{syn}
 ~

  \begin{lstlisting}
    SELECT A1,..., Ap
    FROM R1, R2,..., Rk
    WHERE F;
  \end{lstlisting}

 ~
\end{syn}

Si l'on considere $t_1, \ldots , t_k$ des tuples respectifs de $R_1,
\ldots , R_k$, le tuple $(t_1, \ldots , t_k)$ sera qualifie s'il
satisfait la formule logique $F$. La table resultat sera obtenue en
effectuant une projection de $(t_1, \ldots , t_k)$ sur $A_1, \ldots,
A_p$. La requete est donc semblable a $\pi _{A_1, \ldots, A_p}
~(\sigma _{F}~(R_1 \times \ldots \times R_k))$.

Dans le cas d'attributs provenant de tables differentes et portant le
meme nom, on les distingue en les prefixant par le nom de la relation.

\begin{ex}
  La requete <<~\emph{quels sont les types, prix et constructeurs de
  la machine numero 10?}~>> s'ecrit~:

  \begin{lstlisting}
    SELECT Ordinateur.type, Ordinateur.prix,
           TypeOrdinateur.const
    FROM Ordinateur, TypeOrdinateur
    WHERE (Ordinateur.ref = 10) AND
          (Ordinateur.type = TypeOrdinateur.type);
  \end{lstlisting}
\end{ex}

\subsection{Noms d'alias}
\label{sec:noms-dalias}

Les noms d'alias sont des noms symboliques donnes a une table. Ils
sont declares dans la clause \src{FROM}. Ces noms constituent des
variables de parcours d'une table. Le type d'une variable nom d'alias
associe a une table est celui d'une ligne de cette meme table.

\begin{syn}
 ~

  \begin{lstlisting}
    SELECT N1.A1,..., Nn.An
    FROM R1 (AS) N1,..., Rn (AS) Nn
    WHERE (N1.A1 =...);
  \end{lstlisting}

~
\end{syn}

On travaille toujours avec des noms de variable dans une requete. Par
defaut, si l'on ne precise pas un nom d'alias, le nom utilise est
celui de la relation. Par exemple, \src{FROM R} est en fait \src{FROM
  R AS R}.

\begin{ex}
Considerons la requete <<~\emph{quels sont les types, prix et
  constructeurs des ordinateurs de reference 10?}~>> est exprimee de
  la façon suivante~:

    \begin{lstlisting}
    SELECT O.type, O.prix, T.const
    FROM Ordinateur O, TypeOrdinateur T
    WHERE (O.ref = 10) AND (O.type = T.type);
  \end{lstlisting}
  
  Les variables \src{O} et \src{T} representent respectivement un
  tuple de la relation \texttt{Ordinateur} et un tuple de la relation
  \texttt{TypeOrdinateur}.
\end{ex}

\subsection{Jointures}
\label{sec:jointures-1}

On peut exprimer differents tous les types de jointures avec
\textsc{SQL} (jointure naturelle, $\theta$-jointure, jointure
externe). Nous allons les detailler rapidement dans ce qui suit.


\subsubsection{Produit cartesien}
\label{sec:produit-cartsien-1}

La forme la plus simple de jointure en \textsc{SQL} est ce que nous
avons appele le produit cartesien en algebre relationnelle.

\begin{syn}
 ~

  \begin{lstlisting}
    R CROSS JOIN S
  \end{lstlisting}

 ~
\end{syn}

Cette expression renvoie une table equivalent au produit cartesien de
$R$ et de $S$. En cas d'attributs portant le meme nom, ils seront
prefixes par le nom de la relation.

\subsubsection{$\theta$-jointure}
\label{sec:theta-jointure-1}

La $\theta$-jointure est la forme la plus generale de
jointure. Rappelons que l'on peut la voir comme la composition d'un
produit cartesien et d'une selection.

\begin{syn}
 ~

  \begin{lstlisting}
    R JOIN S ON F
  \end{lstlisting}

~
\end{syn}

\src{R JOIN S ON F} etablit la $\theta$-jointure de $R$ et $S$ sur le
critere $F$ (qui est une expression logique).

Le resultat de cette expression est evidemment une relation. On peut
donc l'utiliser dans une selection. Par exemple, la requete
correspondant a l'exemple~\ref{ex:algebre-jointure} est~:

\begin{lstlisting}
  SELECT provenance
  FROM Systeme JOIN LogicielBase ON
       type = 'Micral 75' AND Systeme.nom_sys = LogicielBase.nom_sys ;
\end{lstlisting}

\subsubsection{Autres types de jointures}
\label{sec:autres-jointures}

Les autres jointures possibles sont~:

\begin{itemize}
\item les jointures naturelles en prefixant \src{JOIN} par
  \src{NATURAL}. Il faut bien entendu que les deux relations aient un
  attribut commun et il n'y a pas de condition \src{ON};
\item les jointures externes qui sont de trois types~:
  \begin{itemize}
  \item les jointures externes completes par \src{FULL OUTER JOIN};
  \item les jointures externes a gauche par \src{LEFT OUTER JOIN};
  \item les jointures externes a droite par \src{RIGHT OUTER JOIN}.
  \end{itemize}
\end{itemize}

\subsection{Operateurs ensemblistes}
\label{sec:oprat-ensembl-1}

Les operateurs ensemblistes s'appliquent a des ensembles dont les
donnees sont compatibles (meme type). Contrairement aux autres
operateurs, les operateurs ensemblistes conservent la structure des
relations.

Intersection, union et difference peuvent s'appliquer aux resultats de
deux \src{SELECT} qui retournent les memes colonnes ou les attributs
de meme domaine. Ces trois operateurs sont des operateurs
binaires. Ils prennent en parametre deux relations compatibles ayant
la meme structure.

\begin{syn}
 ~

  \begin{lstlisting}
    R UNION S
    R INTERSECT S
    R EXCEPT S
  \end{lstlisting}

~
\end{syn}

\begin{ex}
  Considerons la requete <<~\emph{quels sont les types d'ordinateurs
    compatibles a la fois avec un Micral 75 et un Mac II?}~>>. Cette
  requete s'exrprime de la façon suivante~:

  \begin{lstlisting}
    (SELECT type_comp FROM Compatibilite
     WHERE type_ref = 'Micral 75')
    INTERSECT
    (SELECT type_comp FROM Compatibilite
     WHERE type_ref = 'Mac II');
  \end{lstlisting}
\end{ex}

\begin{nb}
  les operateurs ensemblistes suivi du mot-cle \src{ALL} renvoient un
  multi-ensemble et non un ensemble.
\end{nb}

\subsection{Operateurs d'agregation}
\label{sec:oprateurs-dagrgation-1}

Nous avons presente les operateurs d'agregation dans la section
\ref{sec:oprateurs-dagrgation}. Ils permettent d'effectuer des
operations sur certaines colonnes d'une relation. On retrouve dans
\textsc{SQL} les memes operateurs qu'en algebre relationnelle
etendue~:

\begin{itemize}
\item \src{SUM} qui permet de sommer une colonne contenant des valeurs
  numeriques;
\item \src{AVG} qui permet de moyenner une colonne contenant des
  valeurs numeriques;
\item \src{MIN} et \src{MAX} qui retourne respectivement la plus
  petite et la plus grande valeur d'une colonne;
\item \src{COUNT} retourne le nombre de valeurs d'une colonne.
\end{itemize}

Si l'on note \src{OP1}, \src{OP2}, \ldots, \src{OPk} des operateurs
d'agregations, la syntaxe de leur utilisation est la suivante~:

\begin{syn}
 ~

  \begin{lstlisting}
    SELECT A1,..., OP1(Ai),...,OPk(Aj),...,Ap
    FROM R
    WHERE F;
  \end{lstlisting}

~
\end{syn}

Seul l'operateur \src{COUNT} peut s'utiliser avec \src{*}~:
\src{COUNT(*)} permet de compter tous les tuples resultats d'une
requete.

\begin{ex}
  La requete suivante permet de recuperer le prix moyen des logiciels:

  \begin{lstlisting}
    SELECT AVG(prix)
    FROM Logiciel ;
  \end{lstlisting}

~
\end{ex}

\subsection{Regroupements}
\label{sec:regroupements-1}

Nous avons egalement vu les regroupements dans la section
\ref{sec:regroupements}. Les regroupements permettent de creer des
classes distinctes dans les tuples d'une relation suivant un critere
et d'appliquer un operateur d'agregation a chacune de ces classes.

\begin{syn}
 ~

  \begin{lstlisting}
    SELECT A1, OP1(A2)
    FROM R
    WHERE F
    GROUP BY P ;
  \end{lstlisting}

 ~
\end{syn}

\src{GROUP BY} doit se trouver apres la clause \src{WHERE}. \src{P}
est une liste d'attributs de regroupement, definie comme suit~:

\begin{defi}
  Soit 

  \begin{lstlisting}
    SELECT A1,...,Ap
    FROM R
    WHERE F
    GROUP BY P ;
  \end{lstlisting}
  
  une requete \textsc{SQL} où chaque \src{Ai} est soit un attribut,
  soit un operateur d'agregation applique a un attribut. Alors seuls
  les attributs apparaissant dans \src{P} peuvent apparaître
  \emph{sans} operateur d'agregation dans la clause \src{SELECT}.
\end{defi}

Ceci correspond bien a la definition donnee en
section~\ref{sec:regroupements} qui definissait la relation renvoyee
par un operateur de regroupement.

\begin{ex}
  Considerons la requete suivante~: <<~\emph{quelle est la liste des
    types de machines installees, avec pour chaque type le nombre
    d'exemplaire de machines?}~>>. Elle s'exprime en \textsc{SQL} par
~:

  \begin{lstlisting}
    SELECT type, COUNT(ref)
    FROM Ordinateur
    GROUP BY type;
  \end{lstlisting}

~
\end{ex}

Les agregations sont bien entendu calculees en tenant compte des
regroupements.

\subsection{Clause \src{HAVING}}
\label{sec:clause-having}

La clause \src{HAVING} permet d'appliquer un critere de selection aux
lignes d'une partition creee par \src{GROUP BY}. On peut la voir comme
le pendant de \src{WHERE} qui travaille sur la relation entiere.
Similairement a la clause \src{GROUP BY}, seuls les attributs
apparaissant dans \src{GROUP BY} peuvent apparaître non agreges dans
\src{HAVING}.

\begin{syn}
 ~

  \begin{lstlisting}
    SELECT A1,...,Ap
    FROM R
    WHERE F
    GROUP BY P
    HAVING L;
  \end{lstlisting}

~
\end{syn}

\begin{ex}
  Considerons la requete suivante~: <<~quelle est la liste des types de
  machines installees, avec le nombre d'exemplaires, dont la capacite
  memoire de chaque exemplaire atteint ou depasse 512 Ko et dont le
  nombre d'exemplaires installe atteint ou depasse 2?~>>. En ajoutant
  un tri par nombre croissant d'exemplaires, la requete s'ecrit~:

  \begin{lstlisting}
    SELECT O.type AS 'Type', COUNT(O.ref) AS 'Nombre exemplaires'
    FROM Ordinateur O
    WHERE capa_mem >= 512
    GROUP BY O.type
    HAVING count(O.ref) >= 2
    ORDER BY 2 ASC;
  \end{lstlisting}

\src{ORDER BY 2} permet de preciser d'ordonner les tuples resultats
suivant la deuxieme colonne.
\end{ex}

\subsection{Sous-requetes}
\label{sec:sous-requetes}

Dans \textsc{SQL}, une requete peut elle-meme etre composee avec une
autre requete. On parle alors de sous-requetes. Il existe plusieurs
types de sous-requetes~:

\begin{itemize}
\item les sous-requetes qui retournent une relation et qui sont
  utilisees dans une clause \src{FROM}.  Dans ce cas, il faut leur
  donner un nom d'alias pour pouvoir les utiliser;
\item les sous-requetes qui retournent une constante. Dans ce cas, on
  peut les utiliser dans la clause \src{WHERE} d'une requete. Par
  exemple~:

  \begin{lstlisting}
    SELECT prix
    FROM Ordinateur
    WHERE ref =
       (SELECT ref
        FROM Installation
        WHERE nom_sys = 'UNIX');
  \end{lstlisting}
\item les sous-requetes qui retournent une relation et qui sont
  utilisees dans une clause \src{WHERE}.
\end{itemize}

Dans ce dernier cas, on peut utiliser different operateurs, qui
s'appliquent a une relation $R$ et renvoient un booleen~:

\begin{itemize}
\item \src{EXISTS R} qui est vraie si et seulement si $R$ n'est pas
  vide;
\item \src{s IN R} est vraie si et seulement si \src{s} apparaît dans
  \src{R} (\src{s} peut etre un tuple sous la forme \src{(A1, A2)} par
  exemple). Le contraire de \src{s IN R} est \src{s NOT IN R};
\item \src{s OP ALL R} où \src{OP} est un operateur de comparaison est
  vraie si et seulement si \src{s OP t} est vraie pour tout tuple
  \src{t} de $R$;
\item \src{s OP ANY R} où \src{OP} est un operateur de comparaison est
  vraie si et seulement si \src{s OP t} est vraie pour au moins un
  tuple \src{t} de $R$.
\end{itemize}

\begin{ex}
  Considerons la requete <<~\emph{quelles sont les provenances des
  systemes installables sur un Micral 75?}~>>. Elle s'ecrit~:

\begin{lstlisting}
  SELECT provenance
  FROM Systeme
  WHERE nom_sys IN
      (SELECT nom_sys
       FROM LogicielBase
       WHERE type = 'Micral 75')
\end{lstlisting}

~
\end{ex}

\begin{ex}

  Considerons la requete <<~\emph{quels sont les noms de logiciels qui
    ne sont installes sur aucune machine?}~>>. Elle s'ecrit~:

\begin{lstlisting}
  SELECT nom, classe
  FROM Logiciel
  WHERE NOT EXISTS
      (SELECT nom_log
       FROM Installation
       WHERE nom = nom_log);
\end{lstlisting}
~
\end{ex}

\subsection{Insertion}
\label{sec:insertion}

\textsc{SQL} propose deux formes d'insertion de donnees~:

\begin{itemize}
\item une insertion de valeurs fournies en entree (par
  <<~extension~>>)~:

  \begin{syn}
~

    \begin{lstlisting}
      INSERT
      INTO R(Ai, Aj,..., Ap)
      VALUES (vi, vj,..., vp);      
    \end{lstlisting}

~
  \end{syn}
  
  \src{vk} est une constante ou une expression calculee. La valeur
  \src{vk} est celle de l'attribut \src{Ak} de la ligne en cours de
  remplissage. Si les \src{Ak} sont omis, les valeurs sont affectees
  dans l'ordre de declaration des colonnes.
  
  Si un attribut n'apparaît pas dans la liste, sa valeur est la valeur
  par defaut de son type (\src{NULL} la plupart du temps).
\item une insertion de valeurs issues d'une autre table (par
  <<~intension~>>)~:

  \begin{syn}
   ~

    \begin{lstlisting}
      INSERT
      INTO R(Ai, Aj,..., Ap)
      SELECT Ci, Cj,..., Cp
      FROM ...;
    \end{lstlisting}
    
~
  \end{syn}
\end{itemize}

\subsection{Modification de tuples}
\label{sec:modif-de-tupl}

La modification de tuples d'une table se fait par la construction
\src{UPDATE}.

\begin{syn}
 ~

  \begin{lstlisting}
    UPDATE R
    SET Ai = vi,..., Ap = vp
    WHERE F;
  \end{lstlisting}

~
\end{syn}

$F$ est l'assertion qualifiant les tuples a modifier.

\begin{ex}
L'instruction 

\begin{lstlisting}
  UPDATE Ordinateur
  SET capa_mem = capa_mem *2
  WHERE type = 'Micral 75';
\end{lstlisting}

permet de doubler la capacite memoire de tous les ordinateurs Micral 75.
\end{ex}

\subsection{Suppression}
\label{sec:suppression}

La suppression de tuples d'une table se fait par la construction
\src{DELETE}.

\begin{syn}
~

\begin{lstlisting}
  DELETE
  FROM R
  WHERE F;
\end{lstlisting}

~
\end{syn}

Cette requete supprime tous les n-uplets de $R$ satisfaisant $F$.

\begin{ex}
 La requete 

  \begin{lstlisting}
    DELETE
    FROM Ordinateur
    WHERE type = 'Goupil G4';
  \end{lstlisting}

supprime tous les ordinateurs Goupil G4.
\end{ex}

\section{Le langage de description de donnees}
\label{sec:langage-description}

Le langage de description de donnees de \textsc{SQL} permet de creer
et d'administrer~:
\begin{itemize}
\item les relations d'un schema conceptuel;
\item les vues externes construites sur ce schema;
\item une partie du niveau interne (c'est-a-dire le niveau physique)
  en definissant les index.
\end{itemize}

\subsection{Types de donnees}
\label{sec:types-de-donnes}

Dans un premier temps, nous allons introduire les types de donnees
manipulables par \textsc{SQL}. En effet, tous les attributs d'un
schema doivent avoir un type.

\begin{enumerate}
\item les chaînes de caracteres peuvent etre de longueur fixe ou de
  longueur variable. \src{CHAR(n)} est une chaîne de longueur \src{n},
  alors que \src{VARCHAR(n)} est une chaine de longueur maximale
  \src{n}. Par exemple, si \src{'bla'} est affectee a un attribut de
  type \src{CHAR(5)}, la valeur de l'attribut sera \src{'bla  '}. Si la
  meme chaîne est affectee a un attribut de type \src{VARCHAR(5)}, la
  valeur de l'attribut sera \src{'bla'};
\item des chaînes d'octets representees par les types \src{BIT(n)} et
  \src{BIT VARYING(n)};
\item le type \src{BOOLEAN};
\item le type \src{INT} ou \src{INTEGER};
\item les types \src{FLOAT} et \src{REAL} (ils sont synonymes). Un
  type reel plus precis est \src{DOUBLE PRECISION}. On peut fixer le
  format d'un reel avec \src{DECIMAL(n,d)} où \src{n} est le nombre de
  chiffres du reel et \src{d} le nombre de chiffres apres la virgule
  de ce reel;
\item enfin, il existe des types \src{DATE}, \src{TIME} et
  \src{TIMESTAMP} qui permet de combiner les deux premiers types. Le
  21 avril 1976 peut etre represente par \src{'1976-04-21'},
  \src{'04/21/1976'} \footnote{La representation peut egalement etre
    de la forme \src{'21/04/1976'}. Cela depend du SGBD installe}.
\end{enumerate}

Suivant le SGBD installe, de nombreux autres types sont disponibles~:
types geometriques, tableaux, etc.

\subsection{Creation de table}
\label{sec:cration-de-table}

La creation de tables se fait grâce a l'operateur \src{CREATE TABLE}.

\begin{syn}
~

  \begin{lstlisting}
    CREATE TABLE Nom (
      ATT1  Type1,
      ATT2  Type2,
      ...
    );
  \end{lstlisting}

~
\end{syn}

\begin{ex}
\label{ex:creation-configuration}
  La creation de la table \src{Configuration} se fait de la façon
  suivante~:

  \begin{lstlisting}
    CREATE TABLE Configuration (
      nom      VARCHAR(20),
      nom_sys  VARCHAR(20),
      type     VARCHAR(20),
      mem_min  INTEGER DEFAULT 4,
      disk_min INTEGER DEFAULT 1
    );
  \end{lstlisting}

~
\end{ex}

Le mot-cle \src{DEFAULT} permet de donner une valeur par defaut a
l'attribut. Si aucune valeur par defaut n'est precisee a la creation
de la table, \src{NULL} est utilise.

Nous verrons dans le chapitre~\ref{cha:triggers} comment ecrire des
contraintes sur les tables a la creation.

\subsection{Destruction d'une relation}
\label{sec:destr-dune-relat}

\begin{syn}
 ~

  \begin{lstlisting}
    DROP TABLE R;
  \end{lstlisting}

~
\end{syn}

Cette requete supprime non seulement le contenu de la relation, mais
egalement son schema.

\subsection{Modification du schema d'une relation}
\label{sec:modif-du-schma}

On peut modifier le schema d'une relation de deux façons~:

\begin{itemize}
\item ajouter un attribut a la relation en utilisant le mot cle
  \src{ADD}. Dans ce cas, l'attribut est egalement ajoute aux tuples
  existants et la valeur de l'attribut pour ces tuples est la valeur
  par defaut;
\item supprimer un attribut avec le mot-cle \src{DROP}.
\end{itemize}

\begin{syn}
 ~

  \begin{lstlisting}
    ALTER TABLE R
    ADD ATT1 Type1;

    ALTER TABLE R
    DROP ATT2;
  \end{lstlisting}

~
\end{syn}

\subsection{Index}
\label{sec:index}

Nous presentons rapidement les index dans cette section. Leur
utilisation permet souvent d'ameliorer les performances lors de
requetes sur une base de donnees.

\begin{defi}
  Un index sur un attribut $A$ d'une relation $R$ est une structure de
  donnees efficace pour trouver les tuples qui ont une valeur fixee
  pour $A$.
\end{defi}

Lorsque les relations ont un nombre tres important de tuples, il
devient tres coûteux lors d'une requete de verifier que tous les
tuples de la relation verifient une propriete. Par exemple, supposons
que l'on veuille les types des ordinateurs ayant un prix de 10 000 F,
et que la relation \src{Ordinateur} possede 10 000 tuples. Dans ce
cas, on va devoir faire 10 000 verifications alors qu'il ne risque
d'avoir que quelques tuples verifiant la condition. L'utilisation d'un
index sur l'attribut \src{prix} permet de trouver tout de suite les
tuples cherches\footnote{Cette operation d'optimisation est effectuee
  par processeur de requetes \textsc{SQL} qui ne va selectionner que
  les tuples cherches grâce a l'index, sans parcourir l'ensemble des
  tuples de \src{Ordinateur}.}.

\begin{syn}
~

  \begin{lstlisting}
    CREATE INDEX NomIndex ON (ATT1,...,ATTn);
  \end{lstlisting}

~
\end{syn}

Remarquons que l'on peut creer un index sur plusieurs attributs.
L'ordre des attributs est alors important pour l'efficacite de
l'optimisation. Il faut mettre en premier les attributs susceptibles
d'etre les plus utilises.

\begin{nb}
  les index doivent etre eux-memes stockes sur le disque, donc
  consomment de la place. De plus, ils rendent plus difficiles les
  operations de mise a jour. Il faut donc reflechir a l'usage
  <<~courant~>> de la base qui va etre fait (beaucoup de modifications,
  beaucoup de requetes) avant de creer un index sur certains attributs
  d'une relation.
\end{nb}

\subsection{Vues externes}
\label{sec:vues-externes}

Une vue constitue une restriction d'une base de donnees a un
sous-ensemble de cette base de donnees. Elle permet de ne rendre
visible qu'une partie de cette base.

\begin{defi}
  On appelle vue externe une relation virtuelle qui~:
  \begin{itemize}
  \item est le resultat d'une requete;
  \item ne contient aucun tuple;
  \item est utilisable en consultation comme n'importe quelle autre
    relation de la base de donnees.
  \end{itemize}
\end{defi}

Les vues ne sont donc pas stockees physiquement.

\subsubsection{Creation d'une vue}
\label{sec:cration-dune-vue}

\begin{syn}
 ~

  \begin{lstlisting}
    CREATE VIEW Nom (ATT1, ATT2,..., ATTn)
    AS
      (SELECT C1,...,Cn
       FROM R
       ...
       );
  \end{lstlisting}

~
\end{syn}

L'attribut \src{ATTi} de la vue represente la colonne \src{Ci} de meme
rang de la requete qui \emph{calcule} la vue.

\begin{ex}
  Considerons la requete qui permet de construire la vue
  \src{Catalogue}. Elle contient le nom, la classe, le nombre
  d'installations et le prix des logiciels disponibles. Elle s'ecrit~:

  \begin{lstlisting}
    CREATE VIEW Catalogue (nom, nombre prix)
    AS
      (SELECT L.nom, count(I.reference), L.prix
       FROM Logiciel L, Installation I
       WHERE L.nom = I.nom_log
       GROUP BY L.nom);
  \end{lstlisting}

~
\end{ex}

\begin{nb}
  on a jusqu'a present utilise trois termes differents~: <<~tables~>>,
  <<~relations~>> et <<~vues~>>. Les programmeurs \textsc{SQL} utilisent
  le terme <<~table~>> au lieu de <<~relation~>> pour parler des relations
  qui sont effectivement stockees. Une vue est donc une relation qui
  elle n'est pas stockee physiquement.
\end{nb}

\subsubsection{Destruction d'une vue}
\label{sec:destruction-dune-vue}

\begin{syn}
 ~

  \begin{lstlisting}
    DROP VIEW Nom;
  \end{lstlisting}

~
\end{syn}

La destruction d'une vue n'affecte en rien les tables a partir
desquelles elle etait construite.

\subsubsection{Mise a jour}
\label{sec:mise--jour}

On peut executer des mises a jour sur une table au travers d'une vue
en respectant certaines conditions.

\begin{hyp}
  Une vue peut etre mise a jour si et seulement si~:
  \begin{itemize}
  \item la requete \src{SELECT} ne porte que sur une seule relation
    $R$ et ne possede pas d'agregats ni de regroupements;
\item la clause \src{WHERE} ne contient pas $R$ dans une sous-requete;
\item la liste dans la clause \src{SELECT} (sans \src{DISTINCT}) doit
  contenir suffisamment d'attributs pour pouvoir remplir les autres
  attributs de la relation avec leur valeur par defaut.
  \end{itemize}
\end{hyp}

Un exemple tres simple permet de comprendre pourquoi on ne peut pas
mettre a jour une vue impliquant plusieurs relations. Prenons par
exemple deux relations $R$ et $S$. Supposons que le critere de la
clause \src{WHERE} porte sur une egalite entre l'attribut $a_1$ de $R$

%======================================================================
% Bibliographie
%======================================================================
\bibliographystyle{plain}
\bibliography{bibtof}

\end{document}

%%% Local Variables: 
%%% mode: latex
%%% TeX-master: t
%%% TeX-PDF-mode: t
%%% End: 
