4
novembre
2009
Cahier d’exercices SQL N°1
novembre
2009
Un article de SQLpro
Premier cahier d’exercices de requêtes SQL… 7 requêtes à former pour une base de données de boîte mail.
Les modèles de données (MCD Merise, MPD) ci dessous présente un système de boîte aux lettres pour courrier électronique.
La table des mails contient tous les mails de tous les comptes relatifs à l’application cliente. La table des comptes liste les différents comptes mails de la boîte aux lettres. La table des pièces jointes contient les différentes PJ relative à un mail.
La table des adresses mails contient à la fois les adresses mails des comptes de la boîte aux lettres ainsi que toutes les adresses mail du carnet d’adresse.
Veuillez noter que pour les adresses mail, on stocke la partie DNS dans la table DNS et la partie spécifique (nom ou pseudonyme de la personne) dans la table des adresses mail.
Modèle Conceptuel de Données : boîte mail
Modèle Physique de Données : boîte mail
Voici le dessin SQL (DDL) des tables de ce modèle :
/*==============================================================*/
/* Table : T_ADRESSE_EMAIL_ADM */
/*==============================================================*/
create table T_ADRESSE_EMAIL_ADM (
ADM_ID int not null,
DNS_ID int not null,
CML_ID int null,
ADM_COMPTE char(128) not null,
ADM_NOM varchar(256) null,
constraint PK_ADM primary key (ADM_ID));
/*==============================================================*/
/* Table : T_COMPTE_EMAIL_CML */
/*==============================================================*/
create table T_COMPTE_EMAIL_CML (
CML_ID int not null,
ADM_ID int not null,
CML_USER_NAME char(64) not null,
CML_PASSWORD char(32) not null,
CML_SERVER char(64) not null,
constraint PK_CML primary key (CML_ID));
/*==============================================================*/
/* Table : T_DNS */
/*==============================================================*/
create table T_DNS (
DNS_ID int not null,
DNS_NAME char(128) not null,
constraint PK_DNS primary key (DNS_ID));
/*==============================================================*/
/* Table : T_EMAIL_EML */
/*==============================================================*/
create table T_EMAIL_EML (
MEL_GUID UNIQUEIDENTIFIER not null, --> GUID
MEL_GUID_ORIGINE UNIQUEIDENTIFIER null, --> GUID
ADM_ID int null,
CML_ID_RECUT int null,
CML_ID_ENVOI int null,
MEL_DATEHEURE timestamp null,
MEL_TITRE varchar(256) null,
MEL_CORPS varchar(max) null,
MEL_PRIORITE smallint null,
MEL_ACCUSE boolean null,
constraint PK_EML primary key (MEL_GUID));
/*==============================================================*/
/* Table : T_PIECE_JOINTE_PCJ */
/*==============================================================*/
create table T_PIECE_JOINTE_PCJ (
PCJ_ID int not null,
MEL_GUID UNIQUEIDENTIFIER not null, --> GUID
PCJ_NOM_FICHIER varchar(256) not null,
PCJ_TYPE_CONTENU char(32) null,
PCJ_CONTENU nvarchar(255) not null,
constraint PK_PCJ primary key (PCJ_ID));
/*==============================================================*/
/* Table :T_J_MAIL_ENVOYE_ADRESSE_MEA */
/*==============================================================*/
create table T_J_MAIL_ENVOYE_ADRESSE_MEA (
ADM_ID int not null,
MEL_GUID UNIQUEIDENTIFIER not null, --> GUID
constraint PK_MEA primary key (ADM_ID, MEL_GUID));
NOTA - vous pouvez remplacer les types suivants :
UNIQUEIDENTIFIER par CHAR(36)
boolean par SMALLINT (ou BIT)
Veuillez traduire en requête SQL, les demandes ci dessous :
1.1 – Combien de courriels ont été reçut pour le compte « chris.date@ibm.com » au cours du mois de juillet 2008 ?
1.2 – Comptez le nombre d’adresse mail du carnet d’adresse par rapport aux différents serveurs DNS
1.3 – Créez la vue permettant de reconstituer l’ensemble des adresses mails avec le compte mail complet (y compris l’arobase) en ajoutant une colonne booléenne indiquant si l’adresse est un compte mail.
1.4 – Créez la contrainte CHECK de la table T_EMAIL_EML afin de faire en sorte de respecter la règle mentionnée dans le modèle :
Si le mail est un mail envoyé alors les associations « envoyé par » et « envoyées à » sont renseignées et les associations « reçut pour » et « reçut de » ne sont pas renseigné. Dans le cas d’un mail reçut, c’est l’inverse.
1.5 – Compte tenu que les réponses à un mail peuvent s’enchaîner de manière arborescente, quelle est la profondeur maximale attente par un même fil de discussion ?
1.6 – de quel(s) expéditeur(s) provien(nen)t le (ou les) courriel(s) contenant le plus de pièces jointes ?
1.7 – À combien de personnes, en moyenne, ont été envoyé les mails expédiés à plus d’un destinataire ?
NOTA : la difficulté essentielle de ces requêtes vient de la compréhension du modèle et non de la complexité des requêtes…
Pour vous aider, voici le contenu d’un jeu d’essais :
SELECT * FROM dbo.T_DNS
DNS_ID DNS_NAME
----------- ---------------
0 sqlpro.fr
1 ibm.com
2 wanadoo.fr
3 aol.com
4 free.fr
5 microsoft.com
6 yahoo.fr
SELECT * FROM dbo.T_ADRESSE_EMAIL_ADM
ADM_ID DNS_ID CML_ID ADM_COMPTE ADM_NOM
----------- ----------- ----------- ---------------------- ------------------------
1 1 NULL chris.date Chris Date (IBM)
2 1 NULL edgar.codd Edgar Codd (IBM)
3 0 1 r.dupond moi
4 0 2 remy.dupond remoi
5 0 3 rdp encore moi
6 2 NULL thierry.narguy Thierry N (copain d'avant)
7 2 NULL michele.lamarque Michèle, ma belle
8 3 NULL james.kelly JKL à New York
9 5 NULL bill.gates bilou mon pote
SELECT * FROM dbo.T_COMPTE_EMAIL_CML
CML_ID ADM_ID CML_USER_NAME CML_PASSWORD CML_SERVER
----------- ----------- ---------------- --------------------------------- ---------------
1 3 rdp 012FC685DA1420584C45FED12581C526 pop.sqlpro.fr
2 4 rdp2 45FED1258012FC680584C1C5265DA142 pop.sqlpro.fr
3 5 rd C1C545FE12FC6D1258080142584265DA pop.sqlpro.fr
SELECT * FROM dbo.T_EMAIL_EML
MEL_GUID MEL_GUID_ORIGINE ADM_ID CML_ID_RECUT CML_ID_ENVOI MEL_DATEHEURE ...
------------------------------------ ------------------------------------ ----------- ------------ ------------ ----------------------- ...
C3C16F2B-F7A2-497C-A57B-5E807AA8FD8D NULL NULL NULL 2 2008-07-25 10:44:27.000 ...
5478EC6B-B214-47A8-BBB5-8B9519EFD916 NULL 1 3 NULL 2008-12-30 22:44:16.000 ...
EC101A27-B1F6-4454-A171-9D3738D2D930 NULL NULL NULL 1 2009-01-01 12:31:28.000 ...
BEE3F817-637D-40C7-801B-AA4A2E5CBDEB C3C16F2B-F7A2-497C-A57B-5E807AA8FD8D 2 5 NULL 2008-07-25 17:28:33.000 ...
FEE0DE44-C7AB-43EF-BE13-D319844BF003 BEE3F817-637D-40C7-801B-AA4A2E5CBDEB NULL NULL 2 2008-07-28 10:19:11.000 ...
48D688D9-50FE-48F5-8AE8-D5B063F9BF9B NULL NULL NULL 3 2009-01-01 12:31:28.000 ...
24452E97-5DB8-41FB-9A32-D6131F93B38A C3C16F2B-F7A2-497C-A57B-5E807AA8FD8D 2 5 NULL 2008-07-25 17:12:44.000 ...
... MEL_TITRE MEL_CORPS MEL_PRIORITE MEL_ACCUSE
... -------------------------- --------------------------------------- ------------ ----------
... What is SQL ? Is SQL a real relationnal language ? 5 1
... Be happy with SQL And d'ont forget to read my books ! NULL NULL
... RV OK pour RV jeudi 3 1
... Re : What is SQL ? No, because of ... 5 0
... SQL, more explanation You say that SQL.... 5 0
... Voeux Salut et bonne année... 1 0
... Lu : What is SQL ? Your mail was read ... 5 0
SELECT * FROM dbo.T_PIECE_JOINTE_PCJ
PCJ_ID MEL_GUID PCJ_NOM_FICHIER PCJ_TYPE_CONTENU PCJ_CONTENU
----------- ------------------------------------ ---------------------------------------------- -------------------------------- ----------------
2 5478EC6B-B214-47A8-BBB5-8B9519EFD916 Introduction to Relationnal Databases pdf xxx
4 5478EC6B-B214-47A8-BBB5-8B9519EFD916 The Third Manifesto pdf xxx
5 5478EC6B-B214-47A8-BBB5-8B9519EFD916 Database in Depth ebook xxx
6 48D688D9-50FE-48F5-8AE8-D5B063F9BF9B carte de voeux gif xxx
SELECT * FROM dbo.T_J_MAIL_ENVOYE_ADRESSE_MEA
ADM_ID MEL_GUID
----------- ------------------------------------
1 C3C16F2B-F7A2-497C-A57B-5E807AA8FD8D
1 FEE0DE44-C7AB-43EF-BE13-D319844BF003
6 48D688D9-50FE-48F5-8AE8-D5B063F9BF9B
7 EC101A27-B1F6-4454-A171-9D3738D2D930
7 48D688D9-50FE-48F5-8AE8-D5B063F9BF9B
8 48D688D9-50FE-48F5-8AE8-D5B063F9BF9B
Voici maintenant les ordre DML d’insertion des données du jeu d’essais :
INSERT INTO dbo.T_DNS (DNS_ID, DNS_NAME) VALUES (0, 'sqlpro.fr');
INSERT INTO dbo.T_DNS (DNS_ID, DNS_NAME) VALUES (1, 'ibm.com');
INSERT INTO dbo.T_DNS (DNS_ID, DNS_NAME) VALUES (2, 'wanadoo.fr');
INSERT INTO dbo.T_DNS (DNS_ID, DNS_NAME) VALUES (3, 'aol.com');
INSERT INTO dbo.T_DNS (DNS_ID, DNS_NAME) VALUES (4, 'free.fr');
INSERT INTO dbo.T_DNS (DNS_ID, DNS_NAME) VALUES (5, 'microsoft.com');
INSERT INTO dbo.T_DNS (DNS_ID, DNS_NAME) VALUES (6, 'yahoo.fr');
INSERT INTO dbo.T_ADRESSE_EMAIL_ADM (ADM_ID, DNS_ID, CML_ID, ADM_COMPTE, ADM_NOM) VALUES (1, 1, NULL, 'chris.date', 'Chris Date (IBM)');
INSERT INTO dbo.T_ADRESSE_EMAIL_ADM (ADM_ID, DNS_ID, CML_ID, ADM_COMPTE, ADM_NOM) VALUES (2, 1, NULL, 'edgar.codd', 'Edgar Codd (IBM)');
INSERT INTO dbo.T_ADRESSE_EMAIL_ADM (ADM_ID, DNS_ID, CML_ID, ADM_COMPTE, ADM_NOM) VALUES (3, 0, NULL, 'r.dupond', 'moi');
INSERT INTO dbo.T_ADRESSE_EMAIL_ADM (ADM_ID, DNS_ID, CML_ID, ADM_COMPTE, ADM_NOM) VALUES (4, 0, NULL, 'remy.dupond', 'remoi');
INSERT INTO dbo.T_ADRESSE_EMAIL_ADM (ADM_ID, DNS_ID, CML_ID, ADM_COMPTE, ADM_NOM) VALUES (5, 0, NULL, 'rdp', 'encore moi');
INSERT INTO dbo.T_ADRESSE_EMAIL_ADM (ADM_ID, DNS_ID, CML_ID, ADM_COMPTE, ADM_NOM) VALUES (6, 2, NULL, 'thierry.narguy', 'Thierry N (copain d''avant)');
INSERT INTO dbo.T_ADRESSE_EMAIL_ADM (ADM_ID, DNS_ID, CML_ID, ADM_COMPTE, ADM_NOM) VALUES (7, 2, NULL, 'michele.lamarque', 'Michèle, ma belle');
INSERT INTO dbo.T_ADRESSE_EMAIL_ADM (ADM_ID, DNS_ID, CML_ID, ADM_COMPTE, ADM_NOM) VALUES (8, 3, NULL, 'james.kelly', 'JKL à New York');
INSERT INTO dbo.T_ADRESSE_EMAIL_ADM (ADM_ID, DNS_ID, CML_ID, ADM_COMPTE, ADM_NOM) VALUES (9, 5, NULL, 'bill.gates', 'bilou mon pote');
INSERT INTO dbo.T_COMPTE_EMAIL_CML (CML_ID, ADM_ID, CML_USER_NAME, CML_PASSWORD, CML_SERVER) VALUES (1, 3, 'rdp', '012FC685DA1420584C45FED12581C526', 'pop.sqlpro.fr');
INSERT INTO dbo.T_COMPTE_EMAIL_CML (CML_ID, ADM_ID, CML_USER_NAME, CML_PASSWORD, CML_SERVER) VALUES (2, 4, 'rdp2', '45FED1258012FC680584C1C5265DA142', 'pop.sqlpro.fr');
INSERT INTO dbo.T_COMPTE_EMAIL_CML (CML_ID, ADM_ID, CML_USER_NAME, CML_PASSWORD, CML_SERVER) VALUES (3, 5, 'rd', 'C1C545FE12FC6D1258080142584265DA', 'pop.sqlpro.fr');
UPDATE dbo.T_ADRESSE_EMAIL_ADM SET CML_ID = 1 WHERE ADM_ID = 3;
UPDATE dbo.T_ADRESSE_EMAIL_ADM SET CML_ID = 2 WHERE ADM_ID = 4;
UPDATE dbo.T_ADRESSE_EMAIL_ADM SET CML_ID = 3 WHERE ADM_ID = 5;
INSERT INTO dbo.T_EMAIL_EML (MEL_GUID, MEL_GUID_ORIGINE, ADM_ID, CML_ID_RECUT, CML_ID_ENVOI, MEL_DATEHEURE, MEL_TITRE, MEL_CORPS, MEL_PRIORITE, MEL_ACCUSE)
VALUES ('C3C16F2B-F7A2-497C-A57B-5E807AA8FD8D', NULL, NULL, NULL, 2, 2008-07-25 10:44:27.000, 'What is SQL ?', 'Is SQL a real relationnal language ?', 5, 1);
INSERT INTO dbo.T_EMAIL_EML (MEL_GUID, MEL_GUID_ORIGINE, ADM_ID, CML_ID_RECUT, CML_ID_ENVOI, MEL_DATEHEURE, MEL_TITRE, MEL_CORPS, MEL_PRIORITE, MEL_ACCUSE)
VALUES ('5478EC6B-B214-47A8-BBB5-8B9519EFD916', NULL, 1, 3, NULL, 2008-12-30 22:44:16.000, 'Be happy with SQL', 'And d''ont forget to read my books !', NULL, NULL);
INSERT INTO dbo.T_EMAIL_EML (MEL_GUID, MEL_GUID_ORIGINE, ADM_ID, CML_ID_RECUT, CML_ID_ENVOI, MEL_DATEHEURE, MEL_TITRE, MEL_CORPS, MEL_PRIORITE, MEL_ACCUSE)
VALUES ('EC101A27-B1F6-4454-A171-9D3738D2D930', NULL, NULL, NULL, 1, 2009-01-01 12:31:28.000, 'RV', 'OK pour RV jeudi', 3, 1);
INSERT INTO dbo.T_EMAIL_EML (MEL_GUID, MEL_GUID_ORIGINE, ADM_ID, CML_ID_RECUT, CML_ID_ENVOI, MEL_DATEHEURE, MEL_TITRE, MEL_CORPS, MEL_PRIORITE, MEL_ACCUSE)
VALUES ('BEE3F817-637D-40C7-801B-AA4A2E5CBDEB', 'C3C16F2B-F7A2-497C-A57B-5E807AA8FD8D', 2, 2, NULL, 2008-07-25 17:28:33.000, 'Re : What is SQL ?', 'No, because of ...', 5, 0);
INSERT INTO dbo.T_EMAIL_EML (MEL_GUID, MEL_GUID_ORIGINE, ADM_ID, CML_ID_RECUT, CML_ID_ENVOI, MEL_DATEHEURE, MEL_TITRE, MEL_CORPS, MEL_PRIORITE, MEL_ACCUSE)
VALUES ('FEE0DE44-C7AB-43EF-BE13-D319844BF003', 'BEE3F817-637D-40C7-801B-AA4A2E5CBDEB', NULL, NULL, 2, 2008-07-28 10:19:11.000, 'SQL, more explanation', 'You say that SQL....', 5, 0);
INSERT INTO dbo.T_EMAIL_EML (MEL_GUID, MEL_GUID_ORIGINE, ADM_ID, CML_ID_RECUT, CML_ID_ENVOI, MEL_DATEHEURE, MEL_TITRE, MEL_CORPS, MEL_PRIORITE, MEL_ACCUSE)
VALUES ('48D688D9-50FE-48F5-8AE8-D5B063F9BF9B', NULL, NULL, NULL, 3, 2009-01-01 12:31:28.000, 'Voeux', 'Salut et bonne année...', 1, 0);
INSERT INTO dbo.T_EMAIL_EML (MEL_GUID, MEL_GUID_ORIGINE, ADM_ID, CML_ID_RECUT, CML_ID_ENVOI, MEL_DATEHEURE, MEL_TITRE, MEL_CORPS, MEL_PRIORITE, MEL_ACCUSE)
VALUES ('24452E97-5DB8-41FB-9A32-D6131F93B38A', 'C3C16F2B-F7A2-497C-A57B-5E807AA8FD8D', 2, 2, NULL, 2008-07-25 17:12:44.000, 'Lu : What is SQL ?', 'Your mail was read ...', 5, 0);
INSERT INTO dbo.T_PIECE_JOINTE_PCJ (PCJ_ID, MEL_GUID, PCJ_NOM_FICHIER, PCJ_TYPE_CONTENU, PCJ_CONTENU)
VALUES (2, '5478EC6B-B214-47A8-BBB5-8B9519EFD916', 'Introduction to Relationnal Databases', 'pdf', 'xxx');
INSERT INTO dbo.T_PIECE_JOINTE_PCJ (PCJ_ID, MEL_GUID, PCJ_NOM_FICHIER, PCJ_TYPE_CONTENU, PCJ_CONTENU)
VALUES (4, '5478EC6B-B214-47A8-BBB5-8B9519EFD916', 'The Third Manifesto', 'pdf', 'xxx');
INSERT INTO dbo.T_PIECE_JOINTE_PCJ (PCJ_ID, MEL_GUID, PCJ_NOM_FICHIER, PCJ_TYPE_CONTENU, PCJ_CONTENU)
VALUES (5, '5478EC6B-B214-47A8-BBB5-8B9519EFD916', 'Database in Depth', 'ebook', 'xxx');
INSERT INTO dbo.T_PIECE_JOINTE_PCJ (PCJ_ID, MEL_GUID, PCJ_NOM_FICHIER, PCJ_TYPE_CONTENU, PCJ_CONTENU)
VALUES (6, '48D688D9-50FE-48F5-8AE8-D5B063F9BF9B', 'carte de voeux', 'gif', 'xxx');
INSERT INTO dbo.T_J_MAIL_ENVOYE_ADRESSE_MEA (ADM_ID, MEL_GUID) VALUES (1, 'C3C16F2B-F7A2-497C-A57B-5E807AA8FD8D');
INSERT INTO dbo.T_J_MAIL_ENVOYE_ADRESSE_MEA (ADM_ID, MEL_GUID) VALUES (1, 'FEE0DE44-C7AB-43EF-BE13-D319844BF003');
INSERT INTO dbo.T_J_MAIL_ENVOYE_ADRESSE_MEA (ADM_ID, MEL_GUID) VALUES (6, '48D688D9-50FE-48F5-8AE8-D5B063F9BF9B');
INSERT INTO dbo.T_J_MAIL_ENVOYE_ADRESSE_MEA (ADM_ID, MEL_GUID) VALUES (7, 'EC101A27-B1F6-4454-A171-9D3738D2D930');
INSERT INTO dbo.T_J_MAIL_ENVOYE_ADRESSE_MEA (ADM_ID, MEL_GUID) VALUES (7, '48D688D9-50FE-48F5-8AE8-D5B063F9BF9B');
INSERT INTO dbo.T_J_MAIL_ENVOYE_ADRESSE_MEA(ADM_ID, MEL_GUID) VALUES (8, '48D688D9-50FE-48F5-8AE8-D5B063F9BF9B');
A vous de jouer !
—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *
Bonjour M. Brouard,
Vous ne rendez pas la vie facile à ceux qui n’utilisent pas Microsoft SQL Server
Quelques indications pour ceux qui seraient sous PostGreSQL:
– UNIQUEIDENTIFIER s’appelle UUID sous PostGreSQL
– Le type NVARCHAR n’existe pas, il suffit de le remplacer par VARCHAR
– VARCHAR(max) n’existe pas, j’ai mis le type TEXT à la place
– Pour toutes les insertions, enlevez le nom du schéma (« dbo. »)
– Tout comme MikeTr, j’ai dû ajouter des apostrophes autour des datetime pour les insertions dans la table T_EMAIL_EML
– Toujours pour T_EMAIL_EML, remplacez les 0 par false et les 1 par true dans la dernière colonne
En tout cas, un énorme merci pour ce blog ! J’ai toujours trouvé que vos exercices étaient une vraie mine d’or !
À bientôt,
Fabian Pijcke
Bonjour Frédéric,
Merci pour tout cet excellent boulot sur le site. Les cours sont intéressants et surtout très pédagogiques.
Je n’ai pas encore commencé les exercices mais j’ai eu un souci pour la création, en fait deux :
. la donnée timestamp n’est acceptée qu’avec des quotes
. les dernières insertions doivent se faire sur la table T_J_MAIL_ENVOYE_ADRESSE_MEA et non T_PIECE_JOINTE_PCJ
Si d’autres ont essayé ils ont pu avoir le même souci.
Cordialement,
Mike
SOLUTIONS
1.1 – Combien de courriels ont été envoyés pas l’adresse mail « chris.date@ibm.com » au cours du mois de juillet 2008 ?
1.2 – Comptez le nombre d’adresse mail du carnet d’adresse en regard de tous les serveurs DNS
1.3 – Créez la vue permettant de reconstituer l’ensemble des adresses mails avec le compte mail complet (y compris l’arobase) en ajoutant une colonne booléenne indiquant si l’adresse est un compte mail.
1.4 – Créez la contrainte CHECK de la table T_EMAIL_EML afin de faire en sorte de respecter la règle mentionnée dans le modèle : « Si le mail est un mail envoyé alors les associations ‘envoyé par’ et ‘envoyées à’ sont renseignées et les associations ‘reçut pour’ et ‘reçut de’ ne sont pas renseigné. Dans le cas d’un mail reçut, c’est l’inverse. »
1.5 – Compte tenu que les réponses à un mail peuvent s’enchaîner de manière arborescente, quelle est la profondeur maximale attente par un même fil de discussion ?
1.6 de quel(s) expéditeur(s) provien(nen)t le (ou les) courriel(s) contenant le plus de pièces jointes ?
— ou encore :
1.7 A combien de personnes ont été envoyé en moyenne les mails expédiés à plus d’un destinataire ?
Vos comentaires et autres solutions sont les bienvennues !