Accès à la base de données test

Une base de données est actuellement en ligne et a pour but de vous permettre de vous entrainer à effectuer des requêtes SQL. C'est une base de données très simpliste qui contient les informations nécessaires à la gestion d'un forum sur internet.

Cette base de données peut être accédée directement depuis les machines de l'université (ms8**@montefiore.ulg.ac.be).
Vous pouvez également y accéder depuis votre domicile, mais devez impérativement vous connecter à une machine ms8** via SSH.

Dans un cas comme dans l'autre, voici la procédure à suivre:

Connectez-vous à mysql en utilisant la commande:

mysql -h ms8db.montefiore.uliege.be -u test -p

Le mot de passe est : MySQLtest
Choisissez la base de données test en utilisant la commande:

use test;
Exécutez vos requêtes SQL.
Pour vous déconnecter, la commande est :

quit

Pour les étudiants en géomatique, j'ai demandé d'installer la même base de données sur un serveur chez vous. Vous devez vous connecter à une machine, et pour se connecter à la base de données, suivez la procédure suivante:

Connectez-vous à mysql en utilisant la commande:

mysql -h www.geo.ulg.ac.be -u student -p

Le mot de passe est : 0000
Choisissez la base de données test en utilisant la commande:

use test;
Exécutez vos requêtes SQL.
Pour vous déconnecter, la commande est :

quit

NOUVEAU

Voici le lien vers le script php permettant d'exécuter des requêtes sans l'obligation de se connecter sur son compte étudiant au Montefiore. Pour visualiser les bases de données accessibles, tapez la commande

show databases;

dans le champ "Saisissez la requête SQL".

Pour exécuter une requête sur une de ces bases de données, tapez le nom de la base de données dans le champ "indiquer le nom...", et dans l'autre champs la requête désirée.

NOTE:

La base de données n'est accessible qu'en sélection (

SELECT ... FROM ... WHERE

). Vous ne pourrez ni ajouter, ni supprimer, ni modifier des tuples existants. Il vous sera également impossible de créer, modifier ou supprimer des tables.

Pour commencer...

Une des premières commandes que vous devriez essayer est la commande

desc

(ou

describe

).
Cette commande a pour effet de vous montrer la structure de la table dont vous aurez placé le nom en argument.

Ex:

DESC membre;

aura pour effet l'affichage des informations suivantes:

+------------------+--------------+------+-----+------------+-------+
| Field            | Type         | Null | Key | Default    | Extra |
+------------------+--------------+------+-----+------------+-------+
| Id_Membre        | int(11)      |      | PRI | 0          |       |
| Nom_Membre       | varchar(100) |      |     |            |       |
| Prenom_Membre    | varchar(100) |      |     |            |       |
| Email            | varchar(200) |      |     |            |       |
| Date_Inscription | date         |      |     | 0000-00-00 |       |
+------------------+--------------+------+-----+------------+-------+
5 rows in set (0.00 sec)

Les tables existantes dans la base de données sont: membre, identification, online, message, private_message, message_reference et topic
Je vous encourage vivement à essayer la commande desc sur chacune de ces tables avant de commencer à essayer vos premières requêtes SQL.

Quelques exemples de requêtes

Vous pourriez par exemple essayer de concevoir des requêtes SQL permettant de répondre aux questions suivantes:

1) Rechercher le nom et prénom de tous les membres du forum.
2) Rechercher le nom et prénom des membres actuellement en ligne.
3) Afficher, pour chaque membre, son nom, prénom, login et mot de passe, le tout trié par nom et prénom.
4) Afficher tous les messages publics.
5) Afficher tous les messages publics qui ne font référence à aucun message.
6) Afficher les messages (publics ou privés) les plus récents.
7) Afficher tous les messages (publics ou privés) dont le texte contient le mot : "Bonjour".
8) Afficher, pour chaque membre, le nombre de messages postés.
9) Afficher, pour chaque membre, le nombre de messages postés en faisant la distinction entre message public et message privé.
10) Afficher les messages privés à destination du membre dont le nom est "Fer".
11) Afficher le nom et prénom des membres ayant posté des messages privés, mais aucun message public.
12) Afficher tous les messages du topic numéro 2.
13) Afficher le numéro du topic dans lequel tous les membres ont posté au moins une fois.
14) Afficher le numéro du topic le plus populaire (dans lequel le plus de messages ont été postés).
15) Afficher, parmi les membres en ligne, le numéro de celui qui a reçu le plus de messages privés.

Cette liste de question n'est pas exhaustive, et je vous encourage à essayer de répondre à vos propres questions.

Solutions

Cette partie ne devrait théoriquement servir que pour vous aider à

VERIFIER

que votre solution est bien correcte, et

EN AUCUN CAS

vous ne devriez lire le texte ci-dessous avant d'avoir tenté de résoudre les problèmes par vous-même.

Exercice 1

	SELECT Nom_Membre, Prenom_Membre 
    	FROM membre;

Exercice 2

    	SELECT Nom_Membre, Prenom_Membre 
    	FROM membre, online 
    	WHERE membre.Id_Membre = online.Id_Membre;

Exercice 3

    	SELECT Nom_Membre, Prenom_Membre, Login, Passwd 
    	FROM membre, identification 
   	WHERE membre.Id_Membre = identification.Id_Membre 
   	ORDER BY Nom_Membre, Prenom_Membre;

Exercice 4

    	SELECT * 
    	FROM message 
    	WHERE Id_Message NOT IN (SELECT Id_Message 
                             	 FROM private_message);

Exercice 5

    	SELECT *        
    	FROM message
    	WHERE Id_Message NOT IN (SELECT Id_Message 
                                 FROM private_message)
    	AND Id_Message NOT IN (SELECT Id_Message
                               FROM message_reference);

Exercice 6

    	SELECT *
    	FROM message
   	WHERE Date_Envoi = (SELECT MAX(Date_Envoi)
                            FROM message);

Exercice 7

    	SELECT * 
    	FROM message 
    	WHERE Texte LIKE '%Bonjour%';

Exercice 8

    	(SELECT Id_Membre, COUNT(*) 
    	FROM message 
    	GROUP BY Id_Membre) 
       UNION 
    	(SELECT Id_Membre, "0" 
    	FROM message m1 
    	WHERE NOT EXISTS (SELECT * 
                      	  FROM message m2 
                          WHERE m1.Id_Membre = m2.Id_Membre));

Exercice 9

	(SELECT mess1.Id_Membre, mess1.count_public, mess2.count_private 
	 FROM (SELECT Id_Membre, COUNT(*) AS "count_public" 
	       FROM message 
	       WHERE Id_Message NOT IN (SELECT Id_Message 
	                                FROM private_message) 
	       GROUP BY Id_Membre) mess1, 
	       (SELECT Id_Membre, COUNT(*) AS "count_private" 
	       FROM message 
	       WHERE Id_Message IN (SELECT Id_Message 
	                            FROM private_message) 
	       GROUP BY Id_Membre) mess2 
	 WHERE mess1.Id_Membre = mess2.Id_Membre)
	UNION
	(SELECT mess1.Id_Membre, mess1.count_public , "0" as count_private 
	 FROM (SELECT Id_Membre, COUNT(*) AS "count_public" 
	       FROM message 
	       WHERE Id_Message NOT IN (SELECT Id_Message 
	                                FROM private_message) 
	       GROUP BY Id_Membre) mess1 
	 WHERE NOT EXISTS (SELECT mess2.Id_Membre 
	                   FROM message mess2 
	                   WHERE Id_Message IN (SELECT Id_Message 
	                                        FROM private_message) 
	                   AND mess1.Id_Membre = mess2.Id_Membre))
	UNION
	(SELECT mess2.Id_Membre, "0" as count_public , mess2.count_private 
	 FROM (SELECT Id_Membre, COUNT(*) AS "count_private" 
	       FROM message 
	       WHERE Id_Message IN (SELECT Id_Message 
	                            FROM private_message) 
	       GROUP BY Id_Membre) mess2 
	 WHERE NOT EXISTS (SELECT mess1.Id_Membre 
	                   FROM message mess1 
	                   WHERE Id_Message NOT IN (SELECT Id_Message 
	                                            FROM private_message) 
	                   AND mess1.Id_Membre = mess2.Id_Membre))
	UNION
	(SELECT mess2.Id_Membre, "0" as count_public , "0" as count_private 
	 FROM message mess2 
	 WHERE NOT EXISTS (SELECT mess1.Id_Membre 
	                   FROM message mess1 
	                   WHERE Id_Message NOT IN (SELECT Id_Message 
	                                            FROM private_message) 
	                   AND mess1.Id_Membre = mess2.Id_Membre) 
	 AND NOT EXISTS (SELECT mess3.Id_Membre 
	                 FROM message mess3 
	                 WHERE Id_Message IN (SELECT Id_Message 
	                                      FROM private_message) 
	                 AND mess3.Id_Membre = mess2.Id_Membre));

Exercice 10

	SELECT * 
	FROM message, private_message 
	WHERE message.Id_Message = private_message.Id_Message 
	AND private_message.Id_Membre_To IN (SELECT Id_Membre 
	                                     FROM membre 
	                                     WHERE Nom_Membre = 'Fer');

Exercice 11

	SELECT Nom_Membre, Prenom_Membre 
	FROM membre 
	WHERE Id_Membre IN (SELECT Id_Membre 
	                    FROM message 
	                    WHERE Id_Message IN (SELECT Id_Message 
	                                         FROM private_message)) 
	AND Id_Membre NOT IN (SELECT Id_Membre 
	                      FROM message 
	                      WHERE Id_Message NOT IN (SELECT Id_Message 
	                                               FROM private_message));

Exercice 12

	SELECT * 
	FROM message 
	WHERE Id_Topic = 2;

Exercice 13

	SELECT Id_Topic 
	FROM message m1 
	WHERE NOT EXISTS (SELECT Id_Membre 
	                  FROM membre m2 
	                  WHERE NOT EXISTS (SELECT * 
	                                    FROM message m3 
	                                    WHERE m2.Id_Membre = m3.Id_Membre 
	                                    AND m1.Id_Message = m3.Id_Message));

Exercice 14

	SELECT Id_Topic 
	FROM (SELECT m1.Id_Topic, COUNT(*) 
	      FROM message m1 
	      WHERE Id_Message NOT IN (SELECT Id_Message 
	                               FROM private_message) 
	      GROUP BY Id_Topic 
	      HAVING COUNT(*) = (SELECT MAX(count) 
	                         FROM (SELECT Id_Topic, COUNT(*) as count 
	                               FROM message 
	                               WHERE Id_Message NOT IN (SELECT Id_Message 
	                                                        FROM private_message) 
	                               GROUP BY Id_Topic) temp
	                         )
	      ) temp2;

Exercice 15

	 SELECT Id_Membre_to 
	 FROM (SELECT Id_membre_to, COUNT(*) 
	       FROM private_message 
	       WHERE Id_Membre_to IN (SELECT Id_Membre 
	                              FROM online) 
	       GROUP BY Id_membre_to 
	       HAVING COUNT(*) = (SELECT MAX(count) 
	                          FROM (SELECT Id_Membre_to, COUNT(*) as count 
	                                FROM private_message 
	                                WHERE Id_Membre_To IN (SELECT Id_Membre 
	                                                       FROM online) 
	                                GROUP BY Id_membre_to) temp 
	                          )
	       ) temp2;