1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160
|
CREATE TABLE T_CLIENT
(CLI_ID int NOT NULL ,
TIT_CODE char (8) ,
CLI_NOM char (32) ,
CLI_PRENOM varchar (25) ,
CLI_ENSEIGNE varchar (100))
INSERT INTO T_CLIENT VALUES (1, 'M.', 'DUPONT', 'Alain' , NULL)
INSERT INTO T_CLIENT VALUES (2, 'M.', 'MARTIN', 'Marc' , 'Transports MARTIN & fils')
INSERT INTO T_CLIENT VALUES (3, 'M.', 'BOUVIER', 'Alain' , NULL)
INSERT INTO T_CLIENT VALUES (4, 'M.', 'DUBOIS', 'Paul' , NULL)
INSERT INTO T_CLIENT VALUES (5, 'M.', 'DREYFUS', 'Jean' , NULL)
INSERT INTO T_CLIENT VALUES (6, 'M.', 'FAURE', 'Alain' , 'Boulangerie du marché')
INSERT INTO T_CLIENT VALUES (7, 'M.', 'LACOMBE', 'Paul' , NULL)
INSERT INTO T_CLIENT VALUES (8, 'Melle.', 'DUHAMEL', 'Evelyne' , NULL)
INSERT INTO T_CLIENT VALUES (9, 'Mme.', 'BOYER', 'Martine' , NULL)
INSERT INTO T_CLIENT VALUES (10, 'M.', 'MARTIN', 'Martin' , 'HERMAREX IMPORT_EXPORT')
INSERT INTO T_CLIENT VALUES (11, 'M.', 'PAUL', 'Marcel' , 'Cie Internationale des Machines Electromécaniques')
INSERT INTO T_CLIENT VALUES (12, 'M.', 'DUVAL', 'Arsène' , NULL)
INSERT INTO T_CLIENT VALUES (13, 'M.', 'PHILIPPE', 'André' , NULL)
INSERT INTO T_CLIENT VALUES (14, 'M.', 'PIERRELAYE', 'Paul' , NULL)
INSERT INTO T_CLIENT VALUES (15, 'Melle.', 'DAUMIER', 'Amélie' , NULL)
INSERT INTO T_CLIENT VALUES (16, 'M.', 'CHABAUD', 'Daniel' , NULL)
INSERT INTO T_CLIENT VALUES (17, 'M.', 'BAILLY', 'Jean-François' , 'Entreprise DUPONT CHAUFFAGE')
INSERT INTO T_CLIENT VALUES (18, 'M.', 'FAYOLLE', 'Olivier' , NULL)
INSERT INTO T_CLIENT VALUES (19, 'M.', 'COCINO', 'Gérard' , NULL)
INSERT INTO T_CLIENT VALUES (20, 'M.', 'FRANQUINET', 'Florent' , NULL)
INSERT INTO T_CLIENT VALUES (21, 'M.', 'MALATERRE', 'Arnaud' , NULL)
INSERT INTO T_CLIENT VALUES (22, 'M.', 'MEDARD', 'Jacques' , NULL)
INSERT INTO T_CLIENT VALUES (23, 'M.', 'AUZENAT', 'Michel' , NULL)
INSERT INTO T_CLIENT VALUES (24, 'M.', 'CHTCHEPINE', 'Dominique' , 'HOTEL *** DE LA GARE')
INSERT INTO T_CLIENT VALUES (25, 'M.', 'LE GUILLARD', 'Alain' , NULL)
INSERT INTO T_CLIENT VALUES (26, 'M.', 'GARREAU', 'Paul' , 'IBM Corp.')
INSERT INTO T_CLIENT VALUES (27, 'M.', 'LECUYER', 'Lionel' , NULL)
INSERT INTO T_CLIENT VALUES (28, 'M.', 'PRA-LETTRY', 'Emmanuel' , NULL)
INSERT INTO T_CLIENT VALUES (29, 'M.', 'SILLET', 'Jacques' , NULL)
INSERT INTO T_CLIENT VALUES (30, 'M.', 'TROLLAT', 'Hervé' , NULL)
INSERT INTO T_CLIENT VALUES (31, 'M.', 'BOUCHET', 'Michel' , NULL)
INSERT INTO T_CLIENT VALUES (32, 'M.', 'LEBAILLIF', 'Christian' , NULL)
INSERT INTO T_CLIENT VALUES (33, 'M.', 'DU HAUT CILLY', 'Guy' , NULL)
INSERT INTO T_CLIENT VALUES (34, 'Mme.', 'GALLACIER', 'Noëlle' , 'Transports GALLACIER')
INSERT INTO T_CLIENT VALUES (35, 'M.', 'PICOT', 'Dominique' , NULL)
INSERT INTO T_CLIENT VALUES (36, 'M.', 'BEAUNEE', 'Pierre' , NULL)
INSERT INTO T_CLIENT VALUES (37, 'M.', 'VERNET', 'Daniel' , NULL)
INSERT INTO T_CLIENT VALUES (38, 'M.', 'ALBERT', 'Christian' , NULL)
INSERT INTO T_CLIENT VALUES (39, 'Mme.', 'HESS', 'Lucette' , NULL)
INSERT INTO T_CLIENT VALUES (40, 'M.', 'CHATON', 'Gérard' , NULL)
INSERT INTO T_CLIENT VALUES (41, 'M.', 'PLATONOFF', 'Philippe' , NULL)
INSERT INTO T_CLIENT VALUES (42, 'Mme.', 'LETERRIER', 'Monique' , 'SA AROMAX ENTREVONT')
INSERT INTO T_CLIENT VALUES (43, 'M.', 'MONTEIL', 'Jean' , NULL)
INSERT INTO T_CLIENT VALUES (44, 'M.', 'SPITHAKIS', 'Jean-Paul' , NULL)
INSERT INTO T_CLIENT VALUES (45, 'M.', 'ORELL', 'Olivier' , NULL)
INSERT INTO T_CLIENT VALUES (46, 'Mme.', 'MARTINET', 'Carmen' , NULL)
INSERT INTO T_CLIENT VALUES (47, 'M.', 'RAY', 'Yannick' , NULL)
INSERT INTO T_CLIENT VALUES (48, 'M.', 'TARSAC', 'René' , NULL)
INSERT INTO T_CLIENT VALUES (49, 'M.', 'COULOMB', 'Renaud' , 'Cabinet COULOMN et CALEMANT')
INSERT INTO T_CLIENT VALUES (50, 'M.', 'SAVY', 'Jean-Claude' , 'Etude et conseil SAVY frères')
INSERT INTO T_CLIENT VALUES (51, 'Mme.', 'DAVID', 'Jacqueline' , NULL)
INSERT INTO T_CLIENT VALUES (52, 'M.', 'FORGEOT', 'Jean-Bernard' , NULL)
INSERT INTO T_CLIENT VALUES (53, 'M.', 'BERGER', 'Jean-Pierre' , NULL)
INSERT INTO T_CLIENT VALUES (54, 'M.', 'DOUBLET', 'Thierry' , NULL)
INSERT INTO T_CLIENT VALUES (55, 'M.', 'MATHIEU', 'Gérard' , NULL)
INSERT INTO T_CLIENT VALUES (56, 'Mme.', 'MOURGUES', 'Jacqueline' , NULL)
INSERT INTO T_CLIENT VALUES (57, 'M.', 'PIERROT', 'Robert' , NULL)
INSERT INTO T_CLIENT VALUES (58, 'M.', 'FRANQUEBALME', 'Daniel' , 'SA DUBOIS-MORISSE')
INSERT INTO T_CLIENT VALUES (59, 'Mme.', 'ZAMPIERO', 'Annick' , NULL)
INSERT INTO T_CLIENT VALUES (60, 'M.', 'PASCOT', 'Vincent' , NULL)
INSERT INTO T_CLIENT VALUES (61, 'M.', 'MECHRI', 'Pierre' , NULL)
INSERT INTO T_CLIENT VALUES (62, 'M.', 'THIERY', 'Fathy' , NULL)
INSERT INTO T_CLIENT VALUES (63, 'Mme.', 'ROURE', 'Marie-Louise' , NULL)
INSERT INTO T_CLIENT VALUES (64, 'M.', 'VILLE', 'Jean-Paul' , NULL)
INSERT INTO T_CLIENT VALUES (65, 'M.', 'NOCENTINI', 'Alain' , NULL)
INSERT INTO T_CLIENT VALUES (66, 'M.', 'LAYANI', 'Lionel' , 'Restaurant Les Près d''Amélie')
INSERT INTO T_CLIENT VALUES (67, 'M.', 'RECHUL', 'Jacques' , NULL)
INSERT INTO T_CLIENT VALUES (68, 'Mme.', 'DE CONINCK', 'Patricia' , NULL)
INSERT INTO T_CLIENT VALUES (69, 'M.', 'LEI', 'Alain' , NULL)
INSERT INTO T_CLIENT VALUES (70, 'M.', 'MICHEL', 'Fernand' , 'HOTEL DE LA POSTE')
INSERT INTO T_CLIENT VALUES (71, 'M.', 'BOURA', 'André' , 'CAFE DES SPORTS')
INSERT INTO T_CLIENT VALUES (72, 'M.', 'CARDONA', 'Philippe' , NULL)
INSERT INTO T_CLIENT VALUES (73, 'M.', 'THOMASSE', 'Jean-Claude' , NULL)
INSERT INTO T_CLIENT VALUES (74, 'Mme.', 'MOURIES', 'Nathalie' , NULL)
INSERT INTO T_CLIENT VALUES (75, 'M.', 'MARTIN', 'Jean-Pierre' , NULL)
INSERT INTO T_CLIENT VALUES (76, 'M.', 'BENZAQUI', 'Joël' , NULL)
INSERT INTO T_CLIENT VALUES (77, 'M.', 'ROUSSILLON', 'Alain' , NULL)
INSERT INTO T_CLIENT VALUES (78, 'M.', 'FARGETTON', 'Denis' , NULL)
INSERT INTO T_CLIENT VALUES (79, 'M.', 'LEPERCQ', 'Jean-Claude' , NULL)
INSERT INTO T_CLIENT VALUES (80, 'M.', 'OLIVIA', 'Hubert' , 'La Table de Méditérranée (restaurant)')
INSERT INTO T_CLIENT VALUES (81, 'M.', 'CASTAREDE', 'Jean-Jacques' , NULL)
INSERT INTO T_CLIENT VALUES (82, 'M.', 'LEOTARD', 'Jean-Paul' , NULL)
INSERT INTO T_CLIENT VALUES (83, 'Mme.', 'LALANDE', 'Colette' , NULL)
INSERT INTO T_CLIENT VALUES (84, 'M.', 'BAVEREL', 'Frédéric' , NULL)
INSERT INTO T_CLIENT VALUES (85, 'M.', 'NOEL', 'Régis' , NULL)
INSERT INTO T_CLIENT VALUES (86, 'M.', 'THIRIOT', 'Jacky' , NULL)
INSERT INTO T_CLIENT VALUES (87, 'M.', 'BERTRAND', 'Christophe' , NULL)
INSERT INTO T_CLIENT VALUES (88, 'M.', 'BACQUE', 'Michel' , 'GARAGE DU CENTRE')
INSERT INTO T_CLIENT VALUES (89, 'M.', 'COUASSE', 'François' , 'SA IMPEX internatonal')
INSERT INTO T_CLIENT VALUES (90, 'M.', 'JOLY', 'Christophe' , NULL)
INSERT INTO T_CLIENT VALUES (91, 'M.', 'BENATTAR', 'Pierre' , NULL)
INSERT INTO T_CLIENT VALUES (92, 'Mme.', 'PARIS', 'Michèle' , NULL)
INSERT INTO T_CLIENT VALUES (93, 'Mme.', 'LEAL', 'Jany' , NULL)
INSERT INTO T_CLIENT VALUES (94, 'M.', 'BENATTAR', 'Bernard' , NULL)
INSERT INTO T_CLIENT VALUES (95, 'M.', 'AIACH', 'Alexandre' , NULL)
INSERT INTO T_CLIENT VALUES (96, 'M.', 'GAL', 'Fabrice' , NULL)
INSERT INTO T_CLIENT VALUES (97, 'Mme.', 'CHAMBON', 'Edith' , NULL)
INSERT INTO T_CLIENT VALUES (98, 'M.', 'DUQUESNAY', 'Jacques' , NULL)
INSERT INTO T_CLIENT VALUES (99, 'M.', 'CHEVALLIER-CHANTEPIE', 'Yanis' , NULL)
INSERT INTO T_CLIENT VALUES (100, 'M.', 'JEAN', 'Henri' , 'Ambulances Tour Eiffel')
INSERT INTO T_CLIENT VALUES (101, 'M.', 'DARBOIS', 'Patrick' , NULL)
SELECT COUNT(*) N FROM T_CLIENT
N
-----------
101
SELECT TOP 1 CLI_ID, CLI_NOM
FROM T_CLIENT
ORDER BY LEN(CLI_NOM)
CLI_ID CLI_NOM
----------- --------------------------------
47 RAY
SELECT TOP 1 PERCENT CLI_ID, CLI_NOM
FROM T_CLIENT
ORDER BY LEN(CLI_NOM)
CLI_ID CLI_NOM
----------- --------------------------------
47 RAY
69 LEI
-- 1% de 101 = 2 !!! Fabuleux...
SELECT TOP 1 PERCENT WITH TIES CLI_ID, CLI_NOM
FROM T_CLIENT
ORDER BY LEN(CLI_NOM)
CLI_ID CLI_NOM
----------- --------------------------------
47 RAY
69 LEI
96 GAL
-- 1% de 101 = 3 avec la queue !
SELECT CLI_ID, CLI_NOM, LEN(CLI_NOM) AS L
FROM T_CLIENT
ORDER BY L ASC
CLI_ID CLI_NOM L
----------- -------------------------------- -----------
47 RAY 3
69 LEI 3
96 GAL 3
100 JEAN 4
85 NOEL 4
... |
Partager