Constituer un jeu d’essais rapidement en SQL

Voici en quelques requêtes un petit exemple pour remplier rapidement des tables de jeu d’essais à l’aide de jointures peu orthodoxes et de calculs aléatoires

Dans cet exemple, nous traitons d’un club de sport ayant des adhérents (table T_ADHERENT) des sports (T_SPORT) et une table de jointures entre les adhérents et les sports pour connaître les pratiquants de tel ou tel sport (T_J_PRATIQUE).

Les tables :


CREATE TABLE T_ADHERENT 

(ADR_ID        INT NOT NULL PRIMARY KEY, 

 ADR_NOM       CHAR(32), 

 ADR_PRENOM    VARCHAR(25)); 

 

CREATE TABLE T_SPORT 

(SPT_ID        INT NOT NULL PRIMARY KEY, 

 SPT_LIBELLE   VARCHAR(32)); 

 

CREATE TABLE T_J_PRATIQUE 

(ADR_ID   INT NOT NULL  

          FOREIGN KEY REFERENCES T_ADHERENT (ADR_ID), 

 SPT_ID   INT NOT NULL  

          FOREIGN KEY REFERENCES T_SPORT (SPT_ID), 

 CONSTRAINT PK_PRT PRIMARY KEY (ADR_ID, SPT_ID));

On rempli préalablement la tables des adhérents avec 100 valeurs distinctes :


INSERT INTO T_ADHERENT VALUES (1, 'DUVERLIE', 'FRANCOISE'); 

INSERT INTO T_ADHERENT VALUES (2, 'LEMARCHAND', 'COLETTE'); 

INSERT INTO T_ADHERENT VALUES (3, 'ROCANCOURT', 'XAVIER'); 

INSERT INTO T_ADHERENT VALUES (4, 'BUISARD', 'GEORGES'); 

INSERT INTO T_ADHERENT VALUES (5, 'LAROSE', 'SOLANGE'); 

INSERT INTO T_ADHERENT VALUES (6, 'HOLLEY', 'JANINE'); 

INSERT INTO T_ADHERENT VALUES (7, 'RIVIERE', 'GERARD'); 

INSERT INTO T_ADHERENT VALUES (8, 'LEGRAND', 'JEAN LOUIS'); 

INSERT INTO T_ADHERENT VALUES (9, 'DUDOUET-MALASSIS', 'GISELE'); 

INSERT INTO T_ADHERENT VALUES (10, 'MORIN', 'GENEVIEVE'); 

INSERT INTO T_ADHERENT VALUES (11, 'LE NOEL', 'GUY'); 

INSERT INTO T_ADHERENT VALUES (12, 'LANGRAND', 'AUGUSTE'); 

INSERT INTO T_ADHERENT VALUES (13, 'ROBERT', 'PIERRE'); 

INSERT INTO T_ADHERENT VALUES (14, 'COISPEL', 'BERNARD'); 

INSERT INTO T_ADHERENT VALUES (15, 'LEBOSQUAIN', 'JEAN'); 

INSERT INTO T_ADHERENT VALUES (16, 'LEROY', 'NADINE'); 

INSERT INTO T_ADHERENT VALUES (17, 'LENORMAND', 'MICHEL'); 

INSERT INTO T_ADHERENT VALUES (18, 'LE COMTE', 'GERARD'); 

INSERT INTO T_ADHERENT VALUES (19, 'FERREIRA', 'JACK'); 

INSERT INTO T_ADHERENT VALUES (20, 'VALENTE', 'J GEORGES'); 

INSERT INTO T_ADHERENT VALUES (21, 'GAUTIER', 'YVETTE'); 

INSERT INTO T_ADHERENT VALUES (22, 'ROYER', 'JEAN'); 

INSERT INTO T_ADHERENT VALUES (23, 'LOPEZ', 'JEAN PIERRE'); 

INSERT INTO T_ADHERENT VALUES (24, 'LETOUZEY', 'ANNETTE'); 

INSERT INTO T_ADHERENT VALUES (25, 'LEVAVASSEUR', 'SOPHIE'); 

INSERT INTO T_ADHERENT VALUES (26, 'LAIGNEL', 'ROGER'); 

INSERT INTO T_ADHERENT VALUES (27, 'MORAINE', 'HUGUETTE'); 

INSERT INTO T_ADHERENT VALUES (28, 'ROSEN', 'BERNADETTE'); 

INSERT INTO T_ADHERENT VALUES (29, 'MARIE', 'GENEVIEVE'); 

INSERT INTO T_ADHERENT VALUES (30, 'MEUDIC', 'JANINE'); 

INSERT INTO T_ADHERENT VALUES (31, 'HEBERT', 'FRANCOISE'); 

INSERT INTO T_ADHERENT VALUES (32, 'FALISE', 'MARIE THERESE'); 

INSERT INTO T_ADHERENT VALUES (33, 'HEMERY', 'JEANNE'); 

INSERT INTO T_ADHERENT VALUES (34, 'HEUDE', 'CLAUDINE'); 

INSERT INTO T_ADHERENT VALUES (35, 'DAIGREMONT', 'CLAUDE'); 

INSERT INTO T_ADHERENT VALUES (36, 'LEFRANCOIS', 'FRANCOIS'); 

INSERT INTO T_ADHERENT VALUES (37, 'THETIOT', 'JEAN'); 

INSERT INTO T_ADHERENT VALUES (38, 'FREMONT', 'REMY'); 

INSERT INTO T_ADHERENT VALUES (39, 'BARRASSIN', 'DANIEL'); 

INSERT INTO T_ADHERENT VALUES (40, 'SERAFIN', 'MAURICE'); 

INSERT INTO T_ADHERENT VALUES (41, 'DUBUS', 'JEANNE'); 

INSERT INTO T_ADHERENT VALUES (42, 'LEBLOND', 'MARIE LOUISE'); 

INSERT INTO T_ADHERENT VALUES (43, 'VANDAELE', 'BERNARD'); 

INSERT INTO T_ADHERENT VALUES (44, 'PERRIER', 'GUY'); 

INSERT INTO T_ADHERENT VALUES (45, 'MANCEL', 'JEAN CHARLES'); 

INSERT INTO T_ADHERENT VALUES (46, 'PIEDVACHE', 'MADELEINE'); 

INSERT INTO T_ADHERENT VALUES (47, 'DEGRENNE', 'CLAUDE'); 

INSERT INTO T_ADHERENT VALUES (48, 'CARABEUF', 'YVES'); 

INSERT INTO T_ADHERENT VALUES (49, 'BAROCHE', 'PAULETTE'); 

INSERT INTO T_ADHERENT VALUES (50, 'DEVIQUE', 'CHRISTIAN'); 

INSERT INTO T_ADHERENT VALUES (51, 'GOGUET', 'DANIEL'); 

INSERT INTO T_ADHERENT VALUES (52, 'DAGAIN', 'ALAIN'); 

INSERT INTO T_ADHERENT VALUES (53, 'GROSMESNIL', 'MICHEL'); 

INSERT INTO T_ADHERENT VALUES (54, 'COLIN', 'ALPHONSINE'); 

INSERT INTO T_ADHERENT VALUES (55, 'BONVICINI', 'MAURICE'); 

INSERT INTO T_ADHERENT VALUES (56, 'RYDER', 'RAYMONDE'); 

INSERT INTO T_ADHERENT VALUES (57, 'DUCHATELLE', 'ROLAND'); 

INSERT INTO T_ADHERENT VALUES (58, 'GDOWSKI', 'LOUISE'); 

INSERT INTO T_ADHERENT VALUES (59, 'MESNIL', 'PASCAL'); 

INSERT INTO T_ADHERENT VALUES (60, 'LEDUC', 'BERNARD'); 

INSERT INTO T_ADHERENT VALUES (61, 'COLLET', 'THERESE'); 

INSERT INTO T_ADHERENT VALUES (62, 'AUGER', 'FRANCOISE'); 

INSERT INTO T_ADHERENT VALUES (63, 'CANONNE', 'PATRICIA'); 

INSERT INTO T_ADHERENT VALUES (64, 'VASLIN', 'BERNARD'); 

INSERT INTO T_ADHERENT VALUES (65, 'MARTIN', 'ELIANE'); 

INSERT INTO T_ADHERENT VALUES (66, 'CATHRINE', 'HENRI'); 

INSERT INTO T_ADHERENT VALUES (67, 'MARTIN', 'CLAUDE'); 

INSERT INTO T_ADHERENT VALUES (68, 'DELARUE', 'MARIE MADELEINE'); 

INSERT INTO T_ADHERENT VALUES (69, 'PIROU', 'PAUL'); 

INSERT INTO T_ADHERENT VALUES (70, 'FARCY', 'JOSETTE'); 

INSERT INTO T_ADHERENT VALUES (71, 'LEBLATIER', 'MARCEL'); 

INSERT INTO T_ADHERENT VALUES (72, 'LEFLOCH', 'GERARD'); 

INSERT INTO T_ADHERENT VALUES (73, 'CORDELLIER', 'MARCELLINE'); 

INSERT INTO T_ADHERENT VALUES (74, 'VAN DOORNE', 'MARIE CLAUDE'); 

INSERT INTO T_ADHERENT VALUES (75, 'DAUVERGNE', 'YOLANDE'); 

INSERT INTO T_ADHERENT VALUES (76, 'LEDRU', 'MICHEL'); 

INSERT INTO T_ADHERENT VALUES (77, 'DESLANDES', 'MADELEINE'); 

INSERT INTO T_ADHERENT VALUES (78, 'LEBRET', 'NICOLE'); 

INSERT INTO T_ADHERENT VALUES (79, 'BAZILLE', 'DANIEL'); 

INSERT INTO T_ADHERENT VALUES (80, 'LHERMITE', 'SIMONE'); 

INSERT INTO T_ADHERENT VALUES (81, 'LESENECHAL', 'ARMELLE'); 

INSERT INTO T_ADHERENT VALUES (82, 'GUERIN', 'PATRICIA'); 

INSERT INTO T_ADHERENT VALUES (83, 'JOSEPHINE', 'JULIETTE'); 

INSERT INTO T_ADHERENT VALUES (84, 'HAMEL', 'MAURICETTE'); 

INSERT INTO T_ADHERENT VALUES (85, 'SANCHEZ ACUNA', 'CHARLES'); 

INSERT INTO T_ADHERENT VALUES (86, 'JACQUES', 'ODETTE'); 

INSERT INTO T_ADHERENT VALUES (87, 'ANJOU', 'FERNAND'); 

INSERT INTO T_ADHERENT VALUES (88, 'LEFIOT', 'SERGE'); 

INSERT INTO T_ADHERENT VALUES (89, 'MENANT', 'XAVIER'); 

INSERT INTO T_ADHERENT VALUES (90, 'DAVID', 'SIMONNE'); 

INSERT INTO T_ADHERENT VALUES (91, 'MARIAGE', 'IRENE'); 

INSERT INTO T_ADHERENT VALUES (92, 'CAPET', 'BERNARD'); 

INSERT INTO T_ADHERENT VALUES (93, 'GROULT', 'JEAN CLAUDE'); 

INSERT INTO T_ADHERENT VALUES (94, 'ROGUE', 'HELENE'); 

INSERT INTO T_ADHERENT VALUES (95, 'LEMAINE', 'MARTHE'); 

INSERT INTO T_ADHERENT VALUES (96, 'BOURREAU', 'IRENE'); 

INSERT INTO T_ADHERENT VALUES (97, 'ROCQUANCOURT', 'CECILE'); 

INSERT INTO T_ADHERENT VALUES (98, 'ROBINE', 'GEORGETTE'); 

INSERT INTO T_ADHERENT VALUES (99, 'LEMOIGNE', 'MARCELINE'); 

INSERT INTO T_ADHERENT VALUES (100, 'LEBRETON', 'JEAN-LUC');

On effectue une insertion avec un produit cartésien de manière a augmenter significativement le volume des données


INSERT INTO T_ADHERENT  

SELECT T1.ADR_ID + 100* T2.ADR_ID, T1.ADR_NOM, T2.ADR_PRENOM 

FROM   T_ADHERENT AS T1 

       INNER JOIN T_ADHERENT AS T2 

       ON T1.ADR_NOM <> T2.ADR_NOM 

          AND T1.ADR_PRENOM <> T2.ADR_PRENOM;

On insère des sports :

INSERT INTO T_SPORT VALUES (1, ‘Aerobic sportive’);
INSERT INTO T_SPORT VALUES (2, ‘Aérostation’);
INSERT INTO T_SPORT VALUES (3, ‘Aikido’);
INSERT INTO T_SPORT VALUES (4, ‘Alpinisme’);
INSERT INTO T_SPORT VALUES (5, ‘Apnée’);
INSERT INTO T_SPORT VALUES (6, ‘Aqua gym’);
INSERT INTO T_SPORT VALUES (7, ‘Arts martiaux artistiques’);
INSERT INTO T_SPORT VALUES (8, ‘Athlétisme’);
INSERT INTO T_SPORT VALUES (9, ‘Aviron’);
INSERT INTO T_SPORT VALUES (10, ‘Badminton’);
INSERT INTO T_SPORT VALUES (11, ‘Baseball’);
INSERT INTO T_SPORT VALUES (12, ‘Basket ball’);
INSERT INTO T_SPORT VALUES (13, ‘Beach volley’);
INSERT INTO T_SPORT VALUES (14, ‘Biathlon’);
INSERT INTO T_SPORT VALUES (15, ‘Billard’);
INSERT INTO T_SPORT VALUES (16, ‘BMX’);
INSERT INTO T_SPORT VALUES (17, ‘Boxe américaine’);
INSERT INTO T_SPORT VALUES (18, ‘Boxe anglaise’);
INSERT INTO T_SPORT VALUES (19, ‘Boxe chinoise’);
INSERT INTO T_SPORT VALUES (20, ‘Boxe française’);
INSERT INTO T_SPORT VALUES (21, ‘Boxe thaïlandaise’);
INSERT INTO T_SPORT VALUES (22, ‘Canoë kayak’);
INSERT INTO T_SPORT VALUES (23, ‘Canyonisme’);
INSERT INTO T_SPORT VALUES (24, ‘Capoeira’);
INSERT INTO T_SPORT VALUES (25, ‘Char à voile’);
INSERT INTO T_SPORT VALUES (26, ‘Course à pied’);
INSERT INTO T_SPORT VALUES (27, ‘Cyclisme sur piste’);
INSERT INTO T_SPORT VALUES (28, ‘Cyclisme sur route’);
INSERT INTO T_SPORT VALUES (29, ‘Cyclo-cross’);
INSERT INTO T_SPORT VALUES (30, ‘Deltaplane’);
INSERT INTO T_SPORT VALUES (31, ‘Equitation’);
INSERT INTO T_SPORT VALUES (32, ‘Escalade’);
INSERT INTO T_SPORT VALUES (33, ‘Escrime’);
INSERT INTO T_SPORT VALUES (34, ‘Fitness’);
INSERT INTO T_SPORT VALUES (35, ‘Football’);
INSERT INTO T_SPORT VALUES (36, ‘Football US’);
INSERT INTO T_SPORT VALUES (37, ‘Golf’);
INSERT INTO T_SPORT VALUES (38, ‘Gymnastique artistique’);
INSERT INTO T_SPORT VALUES (39, ‘Gymnastique douce’);
INSERT INTO T_SPORT VALUES (40, ‘Gymnastique rythmique’);
INSERT INTO T_SPORT VALUES (41, ‘Haltérophilie’);
INSERT INTO T_SPORT VALUES (42, ‘Handball’);
INSERT INTO T_SPORT VALUES (43, ‘Hockey sur glace’);
INSERT INTO T_SPORT VALUES (44, ‘Ju-Jitsu traditionnel’);
INSERT INTO T_SPORT VALUES (45, ‘Karaté’);
INSERT INTO T_SPORT VALUES (46, ‘Karting’);
INSERT INTO T_SPORT VALUES (47, ‘Kick boxing’);
INSERT INTO T_SPORT VALUES (48, ‘Kite surf’);
INSERT INTO T_SPORT VALUES (49, ‘Krav maga’);
INSERT INTO T_SPORT VALUES (50, ‘Kung fu’);
INSERT INTO T_SPORT VALUES (51, ‘Luge’);
INSERT INTO T_SPORT VALUES (52, ‘Lutte gréco-romaine’);
INSERT INTO T_SPORT VALUES (53, ‘Moto cross’);
INSERT INTO T_SPORT VALUES (54, ‘Moto vitesse’);
INSERT INTO T_SPORT VALUES (55, ‘Motoneige’);
INSERT INTO T_SPORT VALUES (56, ‘Musculation’);
INSERT INTO T_SPORT VALUES (57, ‘Nage avec palmes’);
INSERT INTO T_SPORT VALUES (58, ‘Natation’);
INSERT INTO T_SPORT VALUES (59, ‘Natation synchronisée’);
INSERT INTO T_SPORT VALUES (60, ‘Parachutisme’);
INSERT INTO T_SPORT VALUES (61, ‘Parapente’);
INSERT INTO T_SPORT VALUES (62, ‘Patinage artistique’);
INSERT INTO T_SPORT VALUES (63, ‘Pêche sous-marine’);
INSERT INTO T_SPORT VALUES (64, ‘Pelote basque’);
INSERT INTO T_SPORT VALUES (65, ‘Pentathlon’);
INSERT INTO T_SPORT VALUES (66, ‘Planche à voile’);
INSERT INTO T_SPORT VALUES (67, ‘Plongée’);
INSERT INTO T_SPORT VALUES (68, ‘Plongeon’);
INSERT INTO T_SPORT VALUES (69, ‘Rafting’);
INSERT INTO T_SPORT VALUES (70, ‘Randonnée équestre’);
INSERT INTO T_SPORT VALUES (71, ‘Randonnée pédestre’);
INSERT INTO T_SPORT VALUES (72, ‘Raquette à neige’);
INSERT INTO T_SPORT VALUES (73, ‘Rugby à XIII’);
INSERT INTO T_SPORT VALUES (74, ‘Rugby à XV’);
INSERT INTO T_SPORT VALUES (75, ‘Skateboard’);
INSERT INTO T_SPORT VALUES (76, ‘Ski alpin’);
INSERT INTO T_SPORT VALUES (77, ‘Ski de fond’);
INSERT INTO T_SPORT VALUES (78, ‘Ski de randonnée’);
INSERT INTO T_SPORT VALUES (79, ‘Ski de vitesse’);
INSERT INTO T_SPORT VALUES (80, ‘Ski nautique’);
INSERT INTO T_SPORT VALUES (81, ‘Snowboard’);
INSERT INTO T_SPORT VALUES (82, ‘Skate’);
INSERT INTO T_SPORT VALUES (83, ‘Spéléologie’);
INSERT INTO T_SPORT VALUES (84, ‘Squash’);
INSERT INTO T_SPORT VALUES (85, ‘Surf’);
INSERT INTO T_SPORT VALUES (86, ‘Taekwondo’);
INSERT INTO T_SPORT VALUES (87, ‘Tennis’);
INSERT INTO T_SPORT VALUES (88, ‘Tennis de table’);
INSERT INTO T_SPORT VALUES (89, ‘Tir à l »arc’);
INSERT INTO T_SPORT VALUES (90, ‘Tir sportif’);
INSERT INTO T_SPORT VALUES (91, ‘Tir subaquatique’);
INSERT INTO T_SPORT VALUES (92, ‘Trampoline’);
INSERT INTO T_SPORT VALUES (93, ‘Triathlon’);
INSERT INTO T_SPORT VALUES (94, ‘ULM’);
INSERT INTO T_SPORT VALUES (95, ‘Voile’);
INSERT INTO T_SPORT VALUES (96, ‘Vol à voile’);
INSERT INTO T_SPORT VALUES (97, ‘Volley ball’);
INSERT INTO T_SPORT VALUES (98, ‘VTT’);
INSERT INTO T_SPORT VALUES (99, ‘Water polo’);
INSERT INTO T_SPORT VALUES (100, ‘Yoga’);

On fait un nouveau pseudo produit cartésien avec un aléa pour limiter le nombre de ligne à insérer dans la table de jointure :


INSERT INTO T_J_PRATIQUE 

SELECT DISTINCT ADR_ID, SPT_ID 

FROM   T_ADHERENT AS A 

       INNER JOIN T_SPORT AS S 

          ON CAST((ADR_ID + 100 * SPT_ID) * RAND() AS VARCHAR(16)) LIKE '%33%';

Il existe d’autres moyens, mais celui-ci est simple et rapide !


-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

Identifiez-vous

Créer un compte

Le blog de SQLpro

Le SQL pour SQL Server, PostGreSQL et tous les autres SGBDR

Constituer un jeu d’essais rapidement en SQL

Laisser un commentaire Annuler la réponse.

Identifiant
Mot de passe

Mot de passe oublié ?