\thumb

\begin{qcm}{Vérification de connaissances}{5} % titre , temps
On dispose d'une base de données comportant les tables suivantes:

\Par

\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
clients\\
\qquad (idClient,designation,adresse,cp,ville,email,tel)\\
produits\\
\qquad (idProduit,designation,prix,idEntrepot)\\
commandes\\
\qquad (idCommande,date,idProduit,idClient,quantite)\\
entrepots\\
\qquad (idEntrepot,designation,adresse,cp,ville,superficie)
\end{tabularx}

\Par

Le nom des champs (entre les parenthèses) est choisi de façon implicite pour qu'il n'y ait pas d'ambiguïté. 

\begin{questions}
% Q1
\item La requête :
\begin{listing}[language=SQL]{}{07-ex01.txt}\end{listing}

\Par

\begin{tabularx}{\linewidth}{@{}l@{\;}X@{}}
\choix & affiche le nom de tous les clients par ordre alphabétique de leur ville;\\[8pt]
\choix & affiche le nom de tous les clients selon un ordre alphabétique inverse de leur ville;\\[16pt]
\choix & affiche tous les champs de la table \og clients \fg{}  par ordre alphabétique inverse de leur nom;\\[16pt]
\choix & affiche tous les champs de la table \og clients \fg{}  par ordre alphabétique inverse du nom de leur ville.
\end{tabularx}


% Q2

\item \resetchoix La requête:
\begin{listing}[language=SQL]{}{07-ex02.txt}\end{listing}


\Par

\begin{tabularx}{\linewidth}{@{}l@{\;}X@{}}
\choix & affiche tous les champs des tables \og clients \fg{} et \og entrepots \fg{} qui ont la même ville;\\[16pt]
\choix & affiche tous les champs de la table \og clients \fg{} où le contenu du champ \og ville \fg{} est identique à celui d'au moins un champ de la table\linebreak \og entrepots \fg;\\[24pt]
\choix & affiche tous les champs de la table \og entrepots \fg{} où le contenu du champ \og ville \fg{} est identique à celui d'au moins un champ de la table \og clients \fg;\\[24pt]
\choix & affiche les contenus du champ \og ville \fg{} communs aux tables\linebreak \og entrepots \fg{}  et \og clients \fg.
\end{tabularx}

\newpage

%% Q3

\item\resetchoix La requête:
\begin{listing}[language=SQL]{}{07-ex03.txt}\end{listing}
\Par
est équivalente à:
\Par
\begin{tabularx}{\linewidth}{@{}l@{\;}>{\ttfamily}X@{}}
\choix & SELECT * FROM clients\\
       & WHERE clients.ville = entrepots.ville\\
\choix & SELECT * FROM entrepots\\
       & WHERE clients.ville = entrepots.ville\\
\choix & SELECT * FROM clients,entrepots\\
       & WHERE clients.ville = entrepots.ville\\
\choix & SELECT ville FROM clients,entrepots\\
       & WHERE clients.ville = entrepots.ville
\end{tabularx}

% Q4
\item\resetchoix La requête:
\begin{listing}[language=SQL]{}{07-ex04.txt}\end{listing}
\Par
\begin{tabularx}{\linewidth}{@{}l@{\;}X@{}}
\choix & affiche uniquement les clients dont la ville coïncide avec celle d'un entrepôt;\\
\choix & affiche tous les clients;\\
\choix & affiche tous les entrepôts;\\
\choix & n'affiche rien.
\end{tabularx}

% Q5
\item\resetchoix La requête:
\begin{listing}[language=SQL]{}{07-ex05.txt}\end{listing}
\Par
\begin{tabularx}{\linewidth}{@{}l@{\;}X@{}}
\choix &affiche le total des commandes effectuées après le 2019-09-02;\\
\choix &affiche le total de chaque commande effectuée après le 2019-09-02;\\
\choix & affiche toutes les entrées de la table \og commandes \fg{} où la date est supérieure à 2019-09-02 en effectuant la somme des totaux petit à petit;\\
\choix & n'affiche rien.
\end{tabularx}
\end{questions}
\end{qcm}

\begin{corrige}
\begin{questions}
\item Réponse \choix[d]. En effet, \og SELECT * FROM clients \fg{} signifie que l'on veut voir tous les champs de la table \og clients \fg{} et \og ORDER BY ville DESC \fg{} signifie que l'on souhaite effectuer un tri sur le contenu de \og ville \fg{} mais dans un ordre alphabétique inverse (DESC).

\item Réponse \choix[a]. \og clients INNER JOIN entrepots \fg{} désigne l'intersection des deux tables et \og ON clients.ville = entrepots.ville \fg{} désigne le critère à prendre en compte pour trouver l'intersection (ici, le nom des villes). \og SELECT * \fg{} signifie que l'on souhaite afficher tout donc la requête affiche tous les champs des deux tables où le nom des villes coïncide.

\item Réponse \choix[c]. La requête \texttt{SELECT * FROM clients,entrepots WHERE clients.ville = entrepots.ville} est explicite : on sélectionne tous les champs des deux tables \og clients \fg{} et \og entrepots \fg{}  où le contenu des champs \og ville \fg{} est commun. Elle fait donc exactement la même chose que la requête de la question précédente.

\item Réponse \choix[b]. La requête \texttt{SELECT * FROM clients LEFT OUTER JOIN entrepots ON (clients.ville = entrepots.ville)} prend en\linebreak compte la table de gauche (LEFT), donc \og clients \fg, et affiche toutes ses entrées en commençant par celles où les noms de villes coïncident. Elle affiche ensuite les entrées où le contenu du champ \og ville \fg ne coïncide avec aucun contenu du champ \og ville \fg{} de la table \og entrepots \fg.

\item Réponse \choix[a]. La requête \texttt{SELECT SUM(commandes.total)} renvoie un tableau d'une ligne et d'une colonne dont la valeur est la somme des entrées de la colonne \og total \fg. Il suffit ensuite de regarder la condition (ici, \og WHERE date > '2019-09-02' \fg{} ainsi que l'alias (ici, \og AS somme \fg, qui signifie que le nom de la somme sera accessible via l'appellation de \texttt{somme}).
\end{questions}
\end{corrige}

%%%%%%%%%%%%%%%%%%%%%

\begin{vf}{Vérification de connaissances}{10} % titre , temps
Les affirmations suivantes sont-elles vraies ou fausses ? Justifier la réponse.

\Par

On se place dans une base de données nommée \og bddPerso \fg.

\begin{questions}
\item Une ligne d'une table de \texttt{bddPerso} est appelée une entrée.

\item Une table de \texttt{bddPerso} est définie par la structure relationnelle\linebreak suivante :

\Par

\qquad\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
clients (idClient , nom , prenom)
\end{tabularx}

\Par

Alors \og nom \fg{} est un attribut de la table \og clients \fg.

\item Dans la table \og clients \fg{} de la question précédente, on peut prendre \og nom \fg{} comme clé primaire.

\item On définit une autre table de la manière suivante :

\Par

\qquad\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
connexions (idCommande , idClient , date)
\end{tabularx}


\Par

où \og idClient \fg{} fait référence à la clé du même nom de la table \og clients \fg.

\Par

\og idClient \fg{} est alors une clé étrangère de la table \og connexions \fg.
\end{questions}
\end{vf}

\begin{corrige}
\begin{questions}
\item \textit{Faux}. Dans une base de données, une ligne d'une table est appelée un \textit{enregistrement}.

\item \textit{Vrai}. Les étiquettes des colonnes d'une table sont appelées les attributs de la table.

\item \textit{Faux}. Une clé primaire a pour but de repérer de manière unique un enregistrement. On ne peut donc pas prendre un attribut (ici \og nom \fg) qui peut prendre plusieurs fois la même valeur. Il serait ici préférable de prendre \og idClient \fg{} comme clé primaire, si cet attribut a été créé comme étant un entier auto-incrémenté (par exemple).

\item \textit{Vrai}. En effet, toute clé primaire d'une table, recopiée en tant qu'attribut d'une autre table, est appelé \textit{clé étrangère} de cette dernière.
\end{questions}
\end{corrige}


%%%%%%%%%%%%%%%%%%%%

\begin{exo}{Festival de musique}{\null}{1}{15} 
On dispose d'une base de données d'un festival de musique, où il y a\linebreak plusieurs représentations. Un ou plusieurs musiciens peuvent participer à une représentation, mais un musicien ne peut participer qu'à une seule\linebreak représentation.

La structure de la base de données est la suivante:

\Par

\qquad
\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
Representation (idRep , titreRep , lieu)\\
Musicien (idMus , nom , idRep) \\
Programmer (Date , idRep, tarif)
\end{tabularx}

\Par

Écrire la requête SQL permettant d'afficher:

\begin{questions}
\item La liste des titres des représentations. 
\item La liste des titres des représentations ayant lieu sur le lieu nommé \linebreak \og Dionysos \fg. 
\item La liste des noms des musiciens et les titres des représentations\linebreak auxquelles ils participent. 
\item La liste des titres des représentations, les lieux et les tarifs d'une date précisée. 
\item Le nombre des musiciens qui participent à la représentation numéro 7.
\item Les représentations et leurs dates dont le tarif ne dépasse pas 30 \euro. 
\end{questions}
\end{exo}

\begin{corrige}
\begin{questions}
\item La liste des titres des représentations est donnée par la requête :
\begin{listing}[language=SQL]{}{07-ex06.txt}\end{listing}

\item La liste des titres des représentations ayant lieu sur le lieu nommé\linebreak \og Dionysos \fg est donnée par la requête:
\begin{listing}[language=SQL]{}{07-ex07.txt}\end{listing}


\item La liste des noms des musiciens et les titres des représentations\linebreak auxquelles ils participent est donnée par la requête :
\begin{listing}[language=SQL]{}{07-ex08.txt}\end{listing}

\item La liste des titres des représentations, les lieux et les tarifs d'une date précisée (par exemple le 24/12/2020) est donnée par la requête:\enlargethispage*{3em}

\begin{listing}[language=SQL]{}{07-ex09.txt}\end{listing}

\item Le nombre des musiciens qui participent à la représentation numéro 7 est donné par la requête:
\begin{listing}[language=SQL]{}{07-ex10.txt}\end{listing}

\item Les représentations et leurs dates dont le tarif ne dépasse pas 30 \euro{} sont données par la requête:
\begin{listing}[language=SQL]{}{07-ex11.txt}\end{listing}
\end{questions}
\end{corrige}


%%%%%%%%%%%%%%%%%%%

\begin{exo}{Les employés du département}{\null}{1}{15} 
On dispose d'une base de données dont la structure est la suivante :

\Par

\qquad
\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
Departements (DNO, DNOM, DIR, VILLE) 

Employes (ENO, ENOM, PROF, DATEEMB, SAL, COMM, DNO) 
\end{tabularx}

\Par

Donner les requêtes SQL qui permettent d'obtenir:

\begin{questions}
\item la liste des employés ayant une commission (attribut \og COMM \fg déclaré comme booléen);

\item les noms, emplois et salaires des employés obtenus en classant les emplois dans l'ordre alphabétique, et pour chaque emploi, en classant les salaires du plus grands au plus petit;

\item le salaire moyen des employés.

\item le salaire moyen dans le département nommé \og Production \fg\\(\texttt{DNOM = 'Production'}).
%\Par
%\textit{Pour les questions suivantes, on pourra faire appel à la fonction\linebreak d'agrégation GROUP BY (voir remarque du cours à la page \pageref{crs:groupby}).}
%\item Les numéros de département et leur salaire maximum.
%\item Les différentes professions et leur salaire moyen.
%\item Le salaire moyen par profession le plus bas.
\end{questions}
\end{exo}

\begin{corrige}
\begin{questions}
\item La liste des employés ayant une commission :
\begin{listing}[language=SQL]{}{07-ex12.txt}\end{listing}

\item

\begin{methode}
Pour obtenir une liste de résultats selon un ordre \textit{croissant} ou \textit{décroissant}, on utilise une requête avec le mot-clé \texttt{ORDER BY ... ASC} (pour un ordre croissant) et \texttt{ORDER BY ... DESC} (pour un ordre\linebreak décroissant).
\end{methode}

\newpage

Les noms, emplois et salaires des employés par emploi croissant, et pour chaque emploi, par salaire décroissant :
\begin{listing}[language=SQL]{}{07-ex13.txt}\end{listing}

\item Le salaire moyen des employés:
\begin{listing}[language=SQL]{}{07-ex14.txt}\end{listing}


\item Le salaire moyen du département \og Production \fg:
\begin{listing}[language=SQL]{}{07-ex15.txt}\end{listing}
%\item Les numéros de département et leur salaire maximum:
%
%\quad\begin{tabularx}{\linewidth}{|>{\ttfamily}X@{}}
%SELECT DNO , MAX ( SAL ) FROM Employes GROUP BY DNO
%\end{tabularx}

%\item Les différentes professions et leur salaire moyen:
%
%\quad\begin{tabularx}{\linewidth}{|>{\ttfamily}X@{}}
%SELECT PROF , MAX ( SAL ) FROM Employes GROUP BY PROF
%\end{tabularx}

%\item  Le salaire moyen par profession le plus bas:
%\quad\begin{tabularx}{\linewidth}{|>{\ttfamily}X@{}}
%SELECT AVG ( SAL ) as moy FROM Employes GROUP BY PROF\\
%ORDER BY moy ASC LIMIT 1
%\end{tabularx}
\end{questions}
\end{corrige}


%%%%%%%%%%%%%%%%%%

\begin{exo}{Obtenir la ligne d'un maximum}{\null}{2}{10} 
On dispose de la table :

\Par

\qquad
\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
commandes (idCommande,date,idClient,montant)
\end{tabularx}

\Par

Quelle requête permet d'afficher la (les) enregistrements où le prix est le plus grand ?
\end{exo}

\begin{corrige}
Au prime abord, on pourrait penser que la requête :
\begin{listing}[language=SQL]{}{07-ex16.txt}\end{listing}
\Par
suffirait. 

Mais ce n'est pas le cas car elle affiche uniquement la première ligne de la table, en ajoutant une colonne où est inscrite la valeur maximale du prix, ce qui n'est pas ce que l'on souhaite.

On peut alors penser à la requête :
\begin{listing}[language=SQL]{}{07-ex17.txt}\end{listing}
\Par
mais cela ne fonctionne pas : un message d'erreur apparaît car le \texttt{m} n'est qu'un alias et ne se substitue pas ici à la valeur du maximum.

L'idée consiste donc à insérer une requête après le WHERE dans la requête. La requête demandée est alors:
\begin{listing}[language=SQL]{}{07-ex18.txt}\end{listing}
\Par
Il est important que le nombre après \og 
WHERE prix = \fg{} soit retourné par une requête SQL (ou soit spécifié concrètement, mais nous ne le connaissons pas dans ce cas de figure).
\end{corrige}

%%%%%%%%%%%%%%%%%%

\begin{exo}{Notes annuelles des étudiants}{\null}{2}{20} 
On considère le modèle relationnel suivant concernant la gestion des notes annuelles d'une promotion d'étudiants :

\Par

\qquad
\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
ETUDIANT\\
\qquad (\underline{NumEtudiant}, Nom, Prenom) \\
MATIERE\\
\qquad (\underline{CodeMat}, LibelleMat, CoeffMat) \\
EVALUER\\
\qquad (\underline{NumEval}, \#NumEtudiant, \#CodeMat, Date, Note)
\end{tabularx}


\Par

Les clés étrangères sont précédées d'un \og \# \fg et les clés primaires sont\linebreak soulignées.

Exprimez en SQL les requêtes suivantes.

\begin{questions}
\item Quel est le nombre total d'étudiants ? 
\item Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la plus basse ? 
\item Quelles sont les moyennes de chaque étudiant dans chacune des\linebreak matières ? 
\item Quelles sont les moyennes de la classe par matière ? 
\item Quelle est la moyenne générale de chaque étudiant ? 
\item Quelle est la moyenne générale de la promotion ? 
\item Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne générale de la promotion ?
\end{questions}

\textit{Aide :} on pourra utiliser \og GROUP BY \fg{} pour plusieurs questions, qui\linebreak regroupe les entrées. Par exemple, si l'on considère la table suivante :
\Par
\begin{center}
\begin{tabular}{|*3{Zc|}}
\tabulartitle\hline
\multicolumn{3}{|Zc|}{clients}\\
\tabulartitle\hline
id & nom & montant\\
\hline
1 & Marisol Pleureur & 125.65\\
\hline
2 & Jacques Umul & 45.7\\
\hline
3 & Marisol Pleureur & 100.5\\
\hline
4 & Jacques Umul & 78.6\\
\hline
\end{tabular}
\end{center}
\Par
alors, la requête:
\begin{listing}[language=SQL]{}{07-ex19.txt}\end{listing}
\Par
calculera le total des valeurs des attributs \og montant \fg{} en les regroupant par nom, et donnera la table:
\Par
\begin{center}
\begin{tabular}{|*2{Zc|}}
\tabulartitle\hline
nom & total\\
\hline
Jacques Umul & 124.3\\
\hline
Marisol Pleureur & 226.15\\
\hline
\end{tabular}
\end{center}
\end{exo}

\begin{corrige}
\enlargethispage*{2em}
\begin{questions}
\item Le nombre total d'étudiants est donné par la requête:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex20.txt}\end{listing}

\item Parmi l'ensemble des notes, la note la plus haute et la note la plus basse sont données par la requête:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex21.txt}\end{listing}

\item Les moyennes de chaque étudiant dans chacune des matières sont données par la requête:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex22.txt}\end{listing}

\item Les moyennes de la classe par matière peuvent être données par la requête :\enlargethispage*{3em}
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex23.txt}\end{listing}
\Par
\begin{center}
\textit{(suite du script page suivante)}
\end{center}

\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex24.txt}\end{listing}


\item Pour obtenir la moyenne générale de chaque étudiant, on va se servir d'une \textit{vue}, celle obtenue avec la requête de la question 3:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex25.txt}\end{listing}
\Par
Une vue est une façon de construire une table qui peut servir dans une autre requête.

À l'aide de cette vue, nous pouvons maintenant afficher les moyennes générales par élève:

\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex26.txt}\end{listing}


\item Pour déterminer la moyenne générale de la promotion, on peut utiliser la vue basée sur la réponse à la question précédente:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex27.txt}\end{listing}
\Par
Il ne reste plus qu'à écrire la requête suivante:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex28.txt}\end{listing}




\item Pour obtenir les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne générale de la promotion, on peut utiliser une fois de plus la vue \texttt{SecondeVue}:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex29.txt}\end{listing}

\end{questions}
\enlargethispage*{5em}
\end{corrige}


%%%%%%%%%%%%%%%%%%

\enlargethispage*{5em}

\begin{exo}{Dans un lycée}{\null}{2}{15} 
On considère une base de données dont le modèle relationnel est le suivant :

\Par

\qquad
\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
eleves (\underline{idEleve},nom,prenom,age)\\
controles (\underline{idControle},\#idEleve,\#idMatiere,date,note)\\
matieres (\underline{idMatiere},nomMatiere,coef)
\end{tabularx}

\newpage


Les champs soulignés sont des clés primaires et les champ marqués d'un \og\#\fg{} désignent des clés étrangères.

\begin{questions}
\item Indiquer une requête SQL permettant d'afficher le nombre de contrôles passés par chaque élève pour une discipline donnée (par exemple, NSI).

\textit{Aide :} pour afficher des résultats par élève, on peut utiliser \texttt{GROUP BY}. Par exemple:

\Par

\begin{tabularx}{\linewidth}{|>{\ttfamily}X@{}}
SELECT * FROM table GROUP BY colonne
\end{tabularx}

\Par

On souhaite avoir comme résultat une table ayant pour attributs les nom, prénom et nombre de contrôles.

\item Créer une requête qui permet d'afficher la moyenne de chaque élève par matière.

On souhaite obtenir une table ayant pour attributs les nom et prénom des élèves, les noms des matières, leurs coefficients et la moyenne de la matière pour chaque élève.

\item Créer une requête qui permet d'afficher la moyenne générale de chaque élève en s'aidant de la vue obtenue à l'aide de la requête de la question précédente.
\end{questions}
\end{exo}

\begin{corrige}
\begin{questions}
\item 
\begin{methode}
Une jointure interne permet de lier plusieurs tables (ici, \texttt{eleves}, \texttt{controle} et \texttt{matiere}). On l'utilise pour ce genre de question.
\end{methode}
\newpage
%Une requête SQL permettant d'afficher le nombre de contrôles passés par chaque élève pour la discipline \og NSI \fg peut être la suivante :
\begin{listing}[language=SQL]{}{07-ex30.txt}\end{listing}

\enlargethispage*{5em}
\item Une requête qui permet d'afficher la moyenne de chaque élève par\linebreak matière (NomMat, Coef) peut être la suivante:
\begin{listing}[language=SQL]{}{07-ex31.txt}\end{listing}


\item 
\begin{methode}
Lorsqu'une requête semble être compliquée à construire, il est\linebreak souvent utile de la décomposer en créant une ou plusieurs vues. C'est ici le cas : nous avons créé une première vue dans la question précédente, et nous allons nous en servir pour en créer une autre, qui nous servira dans la requête principale répondant à notre question.
\end{methode}
\newpage
On crée une vue \og mavue \fg{} à partir de la requête précédente:
\begin{listing}[language=SQL]{}{07-ex32.txt}\end{listing}
\Par
Ensuite, on écrit une requête permettant de calculer comme demandé la moyenne générale de chaque élève:
\begin{listing}[language=SQL]{}{07-ex33.txt}\end{listing}
\end{questions}
\end{corrige}


%%%%%%%%%%%%%%%%%

\begin{exo}{Un cas pratique : site web marchand}{\null}{3}{30} 
Un webmaster souhaite créer une base de données nommée \og venteAdonf \fg{} pour un site internet marchand \texttt{vente-a-donf.com} dont la clientèle est\linebreak uniquement française (pour simplifier les formats des enregistrements).

Dans cette base de données, il devra y avoir les quatre tables suivantes :

\begin{itemize}
\item \texttt{users (idUser,nom,prenom,email,rue,cp,ville,tel)}

où \og cp \fg{} désigne le code postal de sa ville de résidence, les autres attributs étant explicites;

\item \texttt{modeles (idModele,nomModele)}

\item \texttt{articles (idArticle,nom,descriptif,idModele,prix)}

où \og idModele \fg{} est une clé étrangère relative à la colonne \texttt{idModele} de la table \texttt{modeles}.


\item \texttt{panier (idPanier,idUser,idArticle,idModele,quantite,}\linebreak\texttt{total)}

Ici, \og total \fg{} représente le résultat de \og quantite * prix de l'article \fg.
\end{itemize}

On décide de mettre en clés primaires les premières colonnes de chaque table.

\begin{questions}
\item \'Ecrire un fichier SQL nommé \og venteAdonf-create.sql \fg{}  permettant de créer ces quatre tables.

\textit{On rappelle qu'un tel fichier est composé de quatre requêtes séparées par un point-virgule.}

\item Voici les premiers articles mis en vente :

\begin{center}
\begin{tabular}{|Z{m{2cm}}|Z{m{3cm}}|Z{m{2cm}}|Zc|}
\tabulartitle\hline
\bfseries Nom & \bfseries Descriptif & \bfseries Modèle & \bfseries Tarif\\
\hline
Mug & 
Magnifique mug\newline personnalisable avec votre photo. &
Unique &
7,99 \euro\\
\hline
T-shirt\newline \og I Kiffe\newline Beef \fg{} &
T-shirt unique en son genre. &
S / M / L /\newline XL / XXL &
24,99 \euro\\
\hline
\end{tabular}
\end{center}

\Par

\'Ecrire un fichier SQL nommé \og venteAdonf-insertArt.sql \fg{}  permettant d'insérer ces informations dans la base de données.

\item Le jour de la mise en ligne du site, deux personnes ont passé commande :

\begin{center}
\begin{tabular}{|L|*2{Zc|}}
\hline
\bfseries Nom & Dupont & Aimaun \\\hline
\bfseries Prénom & Jean & Anne \\\hline
\bfseries Email & jdupont@free.fr & amz@free.fr \\\hline
\bfseries Rue & 3 rue des tulipes & 1 rue du glas \\\hline
\bfseries Cp & 75000 & 33000 \\\hline
\bfseries Ville & Paris & Bordeaux \\\hline
\bfseries Tel & +33601020304 & +33799989796 \\\hline
\end{tabular}
\end{center}

\Par

\'Ecrire un fichier SQL nommé \og venteAdonf-insertUsers.sql \fg{}  permettant d'insérer ces informations dans la base de données.


\item Jean Dupont a commandé 2 mugs et 3 tee-shirts (tailles S, M et L). Anne Aimaun a, quant à elle, commandé 1 mug et 1 tee-shirt (taille M).

\begin{questions}
\item \'Ecrire un fichier SQL nommé \og venteAdonf-insertPanier.sql \fg{}\linebreak  permettant d'insérer ces informations dans la base de données.

\item \'Ecrire une requête permettant d'afficher le montant total du panier de Jean Dupont.

\item Si la table \texttt{panier} n'avait pas de colonne \og total \fg, quelle requête permettrait de renvoyer le résultat de la question précédente ?
\end{questions}
\end{questions}
\end{exo}

\begin{corrige}
\enlargethispage*{3\baselineskip}
\begin{questions}
\item Un fichier SQL permettant de créer les quatre tables est le suivant :
\nathanlive{progmargin}{}%07-ex36.sql
\begin{listing}[language=SQL]{}{07-ex36-1.txt}\end{listing}
\begin{center}
\textit{(Suite page suivante)}
\end{center}
\newpage
\begin{listing}[language=SQL]{}{07-ex36-2.txt}\end{listing}
\Par

La taille des colonnes est ici arbitraire : nous les avons choisi en essayant d'être cohérent. 

Pour ce qui est de la colonne \og tel \fg, nous avons fait le choix de la\linebreak déclarer comme chaîne de caractères (pour que le format des numéros soient de la forme \texttt{+336XXXXXXXX} ou \texttt{+337XXXXXXXX}).

Pour les clés primaires, nous avons fait le choix du format \og BIGINT \fg{} car nous sommes optimistes... et souhaitons beaucoup d'entrées pour cette base de données, qui signifierait un grand succès du site.
\newpage
\begin{aretenir}
Concernant le format numérique, nous avons le choix entre :
\begin{center}
\begin{tabular}{|*3{Zc|}}
\tabulartitle\hline
\bfseries Type de données	& \bfseries Plage	& \bfseries Stockage\\
\hline
BIGINT & $-2^{63}$ à $2^{63}-1$ & Huit octets\\
\hline
INT & $-2^{31}$ à $2^{31}-1$ & Quatre octets\\
\hline
SMALLINT & $-2^{15}$ à $2^{15}-1$ & Deux octets\\
\hline
TINYINT & 0 à 255 &	Un octet\\
\hline
\end{tabular}
\end{center}
\end{aretenir}

\item Pour cette question, il faut anticiper : il faut avant tout enregistrer les\linebreak différents modèles cités (Unique, S, M, L, XL et XXL). Ce n'est\linebreak qu'ensuite que l'on pourra enregistrer les articles. On a alors:
\Par\enlargethispage*{3\baselineskip}
\nathanlive{progmargin}{}%07-ex39.sql
\begin{listing}[language=SQL]{}{07-ex37.txt}\end{listing}
\Par
\begin{center}
\textit{(suite du fichier page suivante)}
\end{center}
\newpage
\begin{listing}[language=SQL]{}{07-ex38.txt}\end{listing}


\nathanlive[13mm]{progmargin}{}
\vspace*{-1mm}
\item 
\begin{listing}[language=SQL]{Insertion des utilisateurs}{07-ex40.sql}
\end{listing}

\nathanlive[13mm]{progmargin}{}%07-ex41.sql
\vspace*{-1mm}
\item 
\begin{questions}
\item 
\begin{listing}[language=SQL]{Insertion des commandes dans le panier}{07-ex41-1.txt}\end{listing}
\begin{center}
\textit{(suite page suivante)}
\end{center}
\newpage
\begin{listing}[language=SQL]{}{07-ex41-2.txt}\end{listing}

\Par

\item La requête permettant d'afficher le total du panier de Jean Dupont est la suivante:
\begin{listing}[language=SQL]{}{07-ex42.txt}\end{listing}
\Par

\item Pour cette question, nous allons avant tout créer une vue, basée notamment sur la table \og panier \fg,  donnant une table avec deux\linebreak attributs : le \og idUser \fg{} d'un enregistrement de la table \og panier \fg{} ainsi que le total de la commande correspondant à l'enregistrement. Pour cela, nous allons prendre la jointure interne de la table\linebreak \og panier \fg{} avec la table \og articles \fg{} sur l'attribut \og idArticle \fg, en regroupant selon l'attribut \og idUser \fg.

\nathanlive{progmargin}{}
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex43.sql}\end{listing}

\Par

Une fois la vue créée, il suffit de sélectionner l'attribut \og TOTAL \fg{} qui correspond à notre client, à savoir celui pour lequel idUser = 1.

\Par

\textit{Autre solution (sans \og GROUP BY \fg):}
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex44.sql}\end{listing}
\end{questions}
\end{questions}
\end{corrige}

\thumb
\AfficheCorrige
