Tp 5 Ldd Lmd Lid

TP 5 SQL LANGAGE LDD/LMD/LID BASE DE DONNEES PROJET USE master if exists (select * from master..sysdatabases where name = 'PROJET') drop database PROJET GO CREATE DATABASE PROJET ON ( NAME = PROJET_dat, FILENAME ='d:\MES TPs\PROJET.mdf', SIZE = 20, MAXSIZE = 30, FILEGROWTH = 1) LOG ON ( NAME = 'PROJET_log', FILENAME = 'd:\MES TPs\PROJET.ldf', SIZE = 10MB, MAXSIZE = 12MB, FILEGROWTH = 1MB ) GO use PROJET

CREATE TABLE categories ( numcat int primary key, libelle varchar(50) default NULL ) ; INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

categories categories categories categories categories categories categories categories categories categories categories

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1, 'Ordinateur de bureau'); (2, 'ordinateur portable'); (3, 'imprimante'); (4, 'scanner'); (5, 'processeur'); (6, 'carte graphique'); (7, 'carte son'); (8, 'ecran'); (9, 'péripherique'); (10, 'connectique'); (11, 'mémoire');

CREATE TABLE clients ( numcli int NOT NULL, nom varchar(30) default NULL, prenom varchar(30) default NULL, ville varchar(30) default NULL, PRIMARY KEY (numcli) ) ; INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO

clients clients clients clients clients clients clients

VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1, (2, (3, (4, (5, (6, (7,

'MOUGIN', 'PIERRE', 'DOUAI'); 'PIERRARD', 'ROGER', 'DOUAI'); 'TRENTA', 'ADELINE', 'TOURCOING'); 'MOSKOWITCH', 'MICHEL', 'MARCQ EN BAROEUL'); 'DELAPORTE', 'STEPHANE', 'LA MADELEINE'); 'AMBERT', 'SOPHIE', 'ARRAS'); 'ZATOPEK', 'ANDRE', 'LAMBERSART');

INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO BAROEUL'); INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO BAROEUL'); INSERT INTO INSERT INTO INSERT INTO INSERT INTO

clients clients clients clients clients clients clients clients clients clients clients clients

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(8, 'AYMARD', 'RENE', 'LILLE'); (9, 'BENABAR', 'JEAN-PIERRE', 'ARRAS'); (10, 'PERROCHON', 'MARC', 'ROUBAIX'); (11, 'BONIFACE', 'JEAN', 'DOUAI'); (12, 'MOUNIER', 'ANNE-MARIE', 'ARRAS'); (13, 'PERCEVAL', 'GAEL', 'ROUBAIX'); (14, 'SAUNIERE', 'CHARLES', 'DOUAI'); (15, 'CAPITANO', 'ANGELO', 'TOURCOING'); (16, 'BUGARACH', 'JEAN-MARC', 'LILLE'); (17, 'VALMIGERE', 'PIERRE', 'MARCQ EN BAROEUL'); (18, 'SOUBEYRAN', 'ANNE-SOPHIE', 'ARRAS'); (19, 'DUPUIS', 'PIERRE-ANDRE', 'MARCQ EN

clients clients clients clients clients clients clients clients clients

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(20, (21, (22, (23, (24, (25, (26, (27, (28,

'MARTIN', 'ANNE-SOPHIE', 'ROUBAIX'); 'BUFFIERE', 'PIERRE', 'LAMBERSART'); 'LECHAT', 'EMMANUEL', 'LA MADELEINE'); 'LACROIX', 'DANIEL', 'LILLE'); 'POIMULLE', 'JEAN-CLAUDE', 'HEM'); 'GOMBERT', 'MARCEL', 'ROUBAIX'); 'LABRICOLE', 'ADRIEN', 'LILLE'); 'DESSERPRIX', 'JEAN-PIERRE', 'LILLE'); 'JURANSSON', 'CATHERINE', 'MARCQ EN

clients clients clients clients

VALUES VALUES VALUES VALUES

(29, (30, (31, (32,

'KERMORAL', 'ANNE', 'LAMBERSART'); 'STUPP', 'KARL', 'LA MADELEINE'); 'GOEDEL', 'THEOPHILE', 'LILLE'); 'BRICOUT', 'JULES', 'ROUBAIX');

CREATE TABLE correspondance ( numvente int NOT NULL , numprod int NOT NULL , quantite int NOT NULL , PRIMARY KEY (numvente,numprod) ) ; INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(2, 1, 10); (2, 15, 5); (3, 5, 8); (3, 20, 4); (3, 47, 2); (3, 57, 4); (6, 5, 20); (6, 20, 15); (13, 58, 30); (18, 1, 20); (18, 59, 5); (18, 23, 2); (23, 2, 10); (23, 1, 20); (25, 5, 30); (26, 1, 50); (26, 13, 5); (26, 55, 10); (28, 55, 100); (31, 1, 20); (31, 55, 10); (33, 4, 10); (33, 55, 10); (35, 1, 15);

INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance correspondance

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(35, 22, 5); (35, 55, 10); (35, 23, 10); (35, 46, 2); (35, 54, 3); (35, 31, 2); (35, 41, 30); (35, 28, 5); (35, 51, 4); (35, 61, 4); (35, 68, 2); (1, 1, 1); (4, 2, 1); (5, 55, 1); (7, 1, 1); (7, 55, 1); (8, 46, 1); (9, 61, 1); (10, 42, 2); (11, 55, 1); (12, 3, 1); (14, 28, 1); (15, 28, 1); (16, 7, 1); (17, 51, 1); (19, 8, 1); (20, 12, 1); (21, 13, 1); (22, 9, 1); (24, 13, 1); (27, 41, 4); (29, 54, 1); (30, 55, 1); (32, 9, 1); (34, 22, 1); (37, 50, 3);

CREATE TABLE employes ( numemp int NOT NULL , nom varchar(30) default NULL, prenom varchar(30) default NULL, adresse varchar(50) default NULL, ville varchar(30) default NULL, emploi varchar(30) default NULL, chef int default NULL, agence char(3) default NULL, salairefixe float default NULL, commission float default NULL, PRIMARY KEY (numemp) ); INSERT INTO employes VALUES (1, 'DUPONT', 'ERIC', '12 rue du Bac', 'ARRAS', 'chef d_agence', 41, 'ARR', '3000', '2000'); INSERT INTO employes VALUES (3, 'CONTE', 'HONORE', '42, place Napoléon', 'DENAIN', 'agent administratif', 15, 'DOU', '1800', NULL); INSERT INTO employes VALUES (4, 'CASSANO', 'GERARD', '255, rue du Prado', 'ARRAS', 'agent administratif', 1, 'ARR', '1800', NULL); INSERT INTO employes VALUES (5, 'DUPREUX', 'ROBERT', '75, rue mauriac', 'BETHUNE', 'chef des ventes', 1, 'ARR', '2800', '2000');

INSERT INTO employes VALUES (6, 'PENET', 'LOUISE', '57, rue conti', 'DOUAI', 'chef des ventes', 15, 'DOU', '2800', '1300'); INSERT INTO employes VALUES (7, 'DELCOURT', 'FLORENCE', '88, rue du bac', 'LENS', 'vendeur', 5, 'ARR', '2200', '1500'); INSERT INTO employes VALUES (8, 'CASPER', 'ARMAND', '123, rue Trarieux', 'VALENCIENNES', 'vendeur', 6, 'DOU', '2000', '1700'); INSERT INTO employes VALUES (9, 'COURTIN', 'GERARD', '89, Bd St Germain', 'WASQUEHAL', 'chef des ventes', 31, 'LI1', '2800', '1500'); INSERT INTO employes VALUES (10, 'DECLERC', 'HENRI', '18, rue Gros', 'DENAIN', 'agent administratif', 1, 'ARR', '1800', NULL); INSERT INTO employes VALUES (12, 'WILLIAMS', 'GEORGE', '37, rue dentellière', 'CALAIS', 'vendeur', 5, 'ARR', '1900', '1300'); INSERT INTO employes VALUES (13, 'BEAUCHAMP', 'EMILIE', '38, rue valjean', 'CALAIS', 'agent administratif', 1, 'ARR', '1800', NULL); INSERT INTO employes VALUES (14, 'VANASTEN', 'ROBERT', '36, rue verte', 'ROUBAIX', 'vendeur', 9, 'LI1', '2400', '1500'); INSERT INTO employes VALUES (15, 'DELFOSSE', 'LUCIEN', '4, Bd Gayant', 'DOUAI', 'chef d_agence', 41, 'DOU', '2000', '1800'); INSERT INTO employes VALUES (16, 'SCHMIDT', 'ANDRE', '161, rue Lavoisier', 'LAMBERSART', 'vendeur', 9, 'LI1', '2200', '1200'); INSERT INTO employes VALUES (17, 'MARTIN', 'ALAIN', '119, rue Maurice Berteaux', 'HELLEMMES', 'agent administratif', 31, 'LI1', '1800', NULL); INSERT INTO employes VALUES (18, 'DUPONT', 'ERIC', '64, rue Jeanne d_Arc', 'HELLEMMES', 'chef d_agence', 41, 'LI2', '3000', '1500'); INSERT INTO employes VALUES (19, 'LUCAS', 'ANNIE', '25, rue du Château', 'CROIX', 'vendeur', 9, 'LI1', '2100', '1600'); INSERT INTO employes VALUES (20, 'THERY', 'GERARD', '63, rue Roger Salengro', 'HELLEMMES', 'agent administratif', 31, 'LI1', '1800', NULL); INSERT INTO employes VALUES (21, 'WOLNIEWICZ', 'IRENA', '14, rue Nationale', 'DOUAI', 'vendeur', 6, 'DOU', '2200', '1200'); INSERT INTO employes VALUES (25, 'DUMONT', 'VERONIQUE', '22, allée Strauss', 'LEERS', 'agent administratif', 31, 'LI1', '1800', NULL); INSERT INTO employes VALUES (26, 'DECONNINCK', 'HERVE', '196 allée Ste Cécile', 'LAMBERSART', 'vendeur', 9, 'LI1', '2150', '1300'); INSERT INTO employes VALUES (27, 'SIREUIL', 'CORINNE', '206, rue de l_Yser', 'TOURCOING', 'agent administratif', 31, 'LI1', '1800', NULL); INSERT INTO employes VALUES (28, 'DAILLY', 'CLAUDE', '17, rue Louis Braille', 'LAMBERSART', 'chef des ventes', 18, 'LI2', '2800', '1200'); INSERT INTO employes VALUES (29, 'TRELCAT', 'LEON', '3, rue du 2ème DB', 'ST ANDRE', 'vendeur', 28, 'LI2', '2230', '700'); INSERT INTO employes VALUES (30, 'LECLERCQ', 'FREDERIC', '142, rue Ogiers', 'CROIX', 'vendeur', 28, 'LI2', '2200', '1100'); INSERT INTO employes VALUES (31, 'TENZA', 'EMMANUELLE', '25, Av du Mal Delattre de Tassigny', 'ST ANDRE', 'chef d_agence', 41, 'LI1', '3000', '2000'); INSERT INTO employes VALUES (32, 'QUINTARD', 'ANDRE', '41, rue Jean Jaurès', 'HELLEMMES', 'vendeur', 28, 'LI2', '2200', '1300'); INSERT INTO employes VALUES (33, 'CASTELAIN', 'ANDRE', '2, rue Wasquehal', 'ROUBAIX', 'agent administratif', 18, 'LI2', '1800', NULL); INSERT INTO employes VALUES (34, 'POLLET', 'PHILIPPE', '152, Bd Jean Baptiste Lebas', 'ROUBAIX', 'agent administratif', 18, 'LI2', '1800', NULL); INSERT INTO employes VALUES (40, 'REGNAULT', 'YVES', '2, rue de L_orphelinat', 'LILLE', 'chef d_agence', 41, 'LI3', '3500', '2000'); INSERT INTO employes VALUES (41, 'PERROCHON', 'MARTINE', '92, rue des Stations', 'LILLE', 'PDG', NULL, NULL, '10000', '5000'); INSERT INTO employes VALUES (42, 'MARTIN', 'MARC', '39, rue d_Arras', 'LILLE', 'agent administratif', 40, 'LI3', '1800', NULL); INSERT INTO employes VALUES (43, 'RAPP', 'ALFREDE', '13, rue d_Antin', 'LILLE', 'agent administratif', 40, 'LI3', '1800', NULL);

INSERT INTO employes VALUES (44, 'MONET', 'ARTHUR', '142, rue Barthélémy Delespaul', 'LILLE', 'vendeur', 28, 'LI2', '2180', '1100'); INSERT INTO employes VALUES (45, 'MATTES', 'GERARD', '49, rue de la Louvière', 'LILLE', 'vendeur', 47, 'LI3', '2250', '1600'); INSERT INTO employes VALUES (46, 'PRADIER', 'ARMAND', '15, Pl du Maréchal Leclerc', 'LILLE', 'vendeur', 47, 'LI3', '2000', '1900'); INSERT INTO employes VALUES (47, 'FIVET', 'JOSEPH', '224, rue Nationale', 'LILLE', 'chef des ventes', 40, 'LI3', '2800', '1400'); INSERT INTO employes VALUES (50, 'LEMAIRE', 'BEATRICE', '145, rue Anatole France', 'RONCHIN', 'vendeur', 47, 'LI3', '2200', '1600'); INSERT INTO employes VALUES (52, 'MARTINEZ', 'ANNA', '38, rue des Hêtres', 'ROUBAIX', 'agent administratif', 40, 'LI3', '1800', NULL); INSERT INTO employes VALUES (53, 'MARTIN', 'PATRICK', '78, rue Horace Vernet', 'ROUBAIX', 'agent administratif', 18, 'LI2', '1800', NULL); INSERT INTO employes VALUES (55, 'SOLAGNA', 'LOUIS', '1, allée Edgard Degas', 'TOURCOING', 'vendeur', 47, 'LI3', '2400', '1500'); INSERT INTO employes VALUES (56, 'DURAND', 'SYLVIE', '4, allée Sylva', 'LAMBERSART', 'vendeur', 9, 'LI1', '2500', '1800'); INSERT INTO employes VALUES (65, 'RENOU', 'CHRISTOPHE', '9 Rue des Saules', 'ERQUINGHEM-LYS', 'vendeur', 5, 'ARR', '2100', '1600'); INSERT INTO employes VALUES (78, 'DIRICKS', 'ADELINE', '32, rue Vlamynck', 'DOUAI', 'vendeur', 6, 'DOU', '2325', '1400'); INSERT INTO employes VALUES (80, 'CRAYENCOURT', 'ADRIENNE', '95, Avue de Waterloo', 'DOUAI', 'agent administratif', 15, 'DOU', '1800', NULL); INSERT INTO employes VALUES (82, 'CROS', 'PIERRE', '135, route de palaja', 'VALENCIENNES', 'agent administratif', 15, 'DOU', '1800', NULL); INSERT INTO employes VALUES (57, 'VANASTEN', 'Emilie', '45, rue de la paix', 'ARRAS', 'vendeur', 5, 'ARR', '2000', '500'); CREATE TABLE produits ( numprod int NOT NULL , designation varchar(100) default NULL, categorie int default NULL, poids float default NULL, couleur varchar(5) default NULL, prixunit float default NULL, quantite int default NULL, seuil int default NULL, PRIMARY KEY (numprod) ) ; INSERT INTO produits VALUES (1, 'SONY VAIO VGC-VA1 - Pentium D 820 2,8 GHz 1 Go 250 Go 20" DVD±RW', 1, '5', 'GRIS', '1840', 15, 5); INSERT INTO produits VALUES (2, 'APPLE iMac - Intel Core Duo 2 GHz 512 Mo 250 Go 20" DVD±RW (MA200F/A)', 1, '4', 'BLANC', '689', 10, 4); INSERT INTO produits VALUES (3, 'APPLE Mac mini - Intel Core Duo 1,66 GHz 512 Mo 80 Go DVD±RW (MA206F/A)', 1, '4', 'BLEU', '799', 12, 3); INSERT INTO produits VALUES (4, 'SONY VAIO VGX-XL100 - Pentium D920 2,8 GHz 1 Go 250 Go DVD±RW', 1, '5', 'GRIS', '1460', 10, 2); INSERT INTO produits VALUES (5, 'ACER Aspire L250 Living Center - Athlon 64 3400+ 2,2 GHz 512 Mo 250 Go DVD±RW', 1, '4', 'NOIR', '740', 10, 2); --INSERT INTO produits VALUES (6, 'HP Pavilion w5229.fr Athlon 64 3200+ 512 Mo 160 Go DVD±RW', 1, 6.5, 'GRIS', '800', 8, 2); INSERT INTO produits VALUES (7, 'HP Compaq Business Desktop dx5150 - Athlon 64 X2 3800+ 2 GHz 1024 Mo 160 Go DVD±RW', 1, '5', 'GRIS', '755', 5, 2); INSERT INTO produits VALUES (8, 'ACER Aspire 3633WLMi_S56 Celeron M 370 1,5GHz 512 Mo 60 Go 15,4" DVD±RW', 2, '2', 'GRIS', '559', 20, 5);

INSERT INTO produits VALUES (9, 'FUJITSU SIEMENS Amilo Pro Edition V2030 Celeron M370 1,5 GHz 256 Mo 40 Go 15.1" DVD/CD-RW', 2, '3', 'NOIR', '539', 18, 5); INSERT INTO produits VALUES (10, 'TOSHIBA Satellite M70-204 - Pentium M740 1,73 GHz 1 Go 100 Go 15,4" DVD±RW + tuner TNT USB + sacoche', 2, '2', 'GRIS', '189', 12, 5); INSERT INTO produits VALUES (11, 'SONY Vaio VGN-SZ2XP/C Core Duo 2 GHz 1024 Mo 100 Go 13,3" DVD±RW', 2, '2', 'BLANC', '290', 14, 5); INSERT INTO produits VALUES (12, 'ACER Aspire 3633WLMi_S108 Celeron M 370 1,5 GHz 1024 Mo 80 Go 15,4" DVD±RW', 2, '2', 'GRIS', '769', 10, 5); INSERT INTO produits VALUES (13, 'Canon Imprimante SELPHY CP710', 3, '4', 'GRIS', '124', 20, 5); INSERT INTO produits VALUES (14, 'Canon Imprimante PIXMA iP6210D', 3, '5', 'GRIS', '65', 10, 5); INSERT INTO produits VALUES (15, 'HP Multifonction Photosmart 3310', 3, '4', 'NOIR', '312', 18, 5); INSERT INTO produits VALUES (16, 'Kodak Station d_impression EasyShare Série 3', 3, '5', 'NOIR', '109', 14, 5); INSERT INTO produits VALUES (17, 'Samsung Imprimante photo 10x15 SPP-2040', 3, '4', 'GRIS', '138', 10, 5); INSERT INTO produits VALUES (18, 'Canon Imprimante PIXMA iP2200', 3, '5', 'GRIS', '53', 12, 5); INSERT INTO produits VALUES (19, 'Brother Multifonction MFC-215C', 3, '4', 'NOIR', '111', 10, 5); INSERT INTO produits VALUES (20, 'Canon Imprimante PIXMA iP4200', 3, '5', 'GRIS', '82', 15, 5); INSERT INTO produits VALUES (21, 'Epson Multifonction Stylus Photo RX700', 3, '6', 'GRIS', '269', 8, 5); INSERT INTO produits VALUES (22, 'Brother Imprimante laser HL-2030', 3, '5', 'NOIR', '91', 10, 5); INSERT INTO produits VALUES (23, 'SAPPHIRE TECHNOLOGY Radeon X1600 PRO 512 Mo AGP', 6, '0', NULL, '145', 10, 3); INSERT INTO produits VALUES (24, 'Radeon 9600 Pro Advantage 256 Mo AGP', 6, '0', NULL, '55', 7, 3); INSERT INTO produits VALUES (25, 'POINT OF VIEW GeForce 7800GS 256 Mo AGP', 6, '0', NULL, '299', 6, 4); INSERT INTO produits VALUES (26, 'XFX GeForce 7900 GT Xtrem Edition 256 Mo HDTV/Dual DVI PCI Express', 6, '0', NULL, '365', 10, 3); INSERT INTO produits VALUES (27, 'XFX GeForce 7800 GS Extreme Edition 256 Mo sortie HDTV/DVI AGP', 6, '0', NULL, '340', 5, 3); INSERT INTO produits VALUES (28, 'CREATIVE Carte son 7.1 PCI Sound Blaster Audigy SE (version oem) - Technologie EAX 3.0 Advanced HD', 7, '0', NULL, '24', 15, 3); INSERT INTO produits VALUES (29, 'PIX Carte Son PCI Dolby Digital 5.1 8738 6-C', 7, '0', NULL, '59', 12, 3); INSERT INTO produits VALUES (30, 'CREATIVE Carte son 7.1 PCI Sound Blaster Audigy 4 (version oem) - Technologie EAX 4.0 Advanced HD', 7, '0', NULL, '52', 15, 3); INSERT INTO produits VALUES (31, 'AMD Athlon 64 3000+ - 2 GHz, Cache L2 512 Ko Socket 939 (version boîte)', 5, '0', NULL, '70', 10, 4); INSERT INTO produits VALUES (32, 'AMD Athlon 64 3200+ - 2 GHz, Cache L2 512 Ko Socket 939 (version boîte)', 5, '0', NULL, '81', 12, 4); INSERT INTO produits VALUES (33, 'INTEL Celeron D336 - 2.8 GHz, Cache L2 256 Ko Socket 775 (version boîte)', 5, '0', NULL, '52', 4, 4); INSERT INTO produits VALUES (34, 'AMD Athlon 64 X2 3800+ - 2 GHz, Cache L2 1 Mo Socket 939 (version boîte)', 5, '0', NULL, '179', 6, 4); INSERT INTO produits VALUES (35, 'AMD Sempron 2800+ 64bits - 1,6 GHz, Cache L2 256 Ko Socket 754 (version boîte)', 5, '0', NULL, '49', 10, 4);

INSERT INTO produits VALUES (36, 'AMD Sempron 3400+ 64bits - 2 GHz, Cache L2 256 Ko Socket 754 (version boîte)', 5, '0', NULL, '74', 12, 4); INSERT INTO produits VALUES (37, 'AMD Athlon 64 3500+ - 2,2 GHz, Cache L2 512 Ko Socket AM2 (version boîte)', 5, '0', NULL, '90', 11, 4); INSERT INTO produits VALUES (38, 'AMD Sempron 2800+ 64bits - 1,6 GHz, Cache L2 256 Ko Socket 754 (version bulk)', 5, '0', NULL, '69', 12, 4); INSERT INTO produits VALUES (39, 'AMD Athlon 64 3200+ - 2 GHz, Cache L2 512 Ko Socket 939 (version OEM)', 5, '0', NULL, '119', 10, 4); INSERT INTO produits VALUES (40, 'INTEL Celeron D331 - 2.66 GHz, Cache L2 256 Ko Socket 775 (version boîte)', 5, '0', NULL, '49', 8, 4); INSERT INTO produits VALUES (41, 'Mémoire PC 1 Go DDR PC3200 sous blister (garantie 10 ans)', 11, '0', NULL, '89', 17, 5); INSERT INTO produits VALUES (42, 'Mémoire PC 512 Mo DDR SDRAM PC3200 sous blister (garantie 10 ans)', 11, '0', NULL, '48', 15, 5); INSERT INTO produits VALUES (43, 'Mémoire PC 512 Mo SDRAM PC133 sous blister (garantie 10 ans)', 11, '0', NULL, '45', 5, 5); INSERT INTO produits VALUES (44, 'Mémoire PC 1 Go DDR PC2700 sous blister (garantie 10 ans)', 11, '0', NULL, '92', 12, 5); INSERT INTO produits VALUES (45, 'Mémoire PC 256 Mo DDR SDRAM PC2100 sous blister (garantie 10 ans)', 11, '0', NULL, '22', 7, 5); INSERT INTO produits VALUES (46, 'Epson Scanner Perfection 3490 Photo', 4, '2', 'GRIS', '73', 8, 3); INSERT INTO produits VALUES (47, 'Canon Scanner CanoScan 8400F', 4, '3', 'GRIS', '164', 6, 3); INSERT INTO produits VALUES (48, 'Epson Scanner Perfection 4990 Photo', 4, '3', 'NOIR', '411', 5, 3); INSERT INTO produits VALUES (49, 'Mustek Scanner ScanExpress A3 USB', 4, '3', 'GRIS', '135', 10, 3); INSERT INTO produits VALUES (50, 'Canon Scanner Canoscan 9950F', 4, '3', 'BLANC', '365', 8, 3); INSERT INTO produits VALUES (51, 'GN NETCOM Casque GN 501 SC', 9, '0', NULL, '15', 12, 5); INSERT INTO produits VALUES (52, 'Logitech Webcam Quickcam Sphere Refresh', 9, '0', NULL, '92', 20, 8); INSERT INTO produits VALUES (53, 'NGS Pavé numérique VIP Keyboard USB pour ordinateur portable', 9, '0', NULL, '20', 20, 10); INSERT INTO produits VALUES (54, 'Altec Lansing Haut-parleurs 2.1 VS 2221', 9, '1', NULL, '32', 10, 8); INSERT INTO produits VALUES (55, 'Sony Ecran TFT 17" SDM-E76D (8 ms)', 8, '1', 'GRIS', '189', 15, 10); INSERT INTO produits VALUES (56, 'Sony Ecran TFT 19" SDM-E96D (8 ms)', 8, '1', 'NOIR', '245', 12, 10); INSERT INTO produits VALUES (57, 'Acer Ecran TFT 20,1" AL2016W (8ms)', 8, '2', 'BLANC', '349', 19, 10); INSERT INTO produits VALUES (58, 'Viewsonic Ecran TFT 17" VA703m (8 ms)', 1, '0', NULL, '135', 15, 10); INSERT INTO produits VALUES (59, 'Samsung Ecran TFT 17" SAMTRON 74V (8 ms)', 8, '1', 'NOIR', '159', 11, 10); INSERT INTO produits VALUES (60, 'Câble USB 2.0 mâle/mâle 5 m', 10, NULL, NULL, '9', 30, 10); INSERT INTO produits VALUES (61, 'Hama Adaptateur clavier XT/AT vers PS/2', 10, '0', NULL, '5', 22, 10); INSERT INTO produits VALUES (62, 'Câble Ethernet RJ45 rouge (catégorie 5) 10 m', 10, '0', NULL, '15', 12, 10); INSERT INTO produits VALUES (63, 'Hama Câble Firewire 400 6 broches mâle/6 broches mâle 2 m (45034)', 10, '0', NULL, '12', 27, 10); INSERT INTO produits VALUES (64, 'Câble USB A mâle/B mâle 1,80 m', 10, '0', NULL, '10', 14, 10);

INSERT INTO produits VALUES (65, 'Hama Câble USB A mâle/B mâle 3 m', 10, NULL, NULL, '7', 10, 10); INSERT INTO produits VALUES (66, 'Belkin Adaptateur Série Pro VGA femelle DVI mâle', 10, '0', NULL, '28', 15, 10); INSERT INTO produits VALUES (67, 'Apple Câble FireWire fin 4 vers 6 broches 1,8 m (M8706)', 10, '0', NULL, '29', 12, 10); INSERT INTO produits VALUES (68, 'Case Logic Attache Câble CT6N', 10, '0', NULL, '4', 19, 10); CREATE TABLE projets ( numproj int NOT NULL , designation varchar(50) default NULL, agence char(3) default NULL, budgetprevu float default NULL, PRIMARY KEY (numproj) ) ; INSERT INTO '50000'); INSERT INTO INSERT INTO INSERT INTO '25000'); INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO INSERT INTO

projets VALUES (1, 'Eqpmt Lycée Montebello-Lille', 'LI1', projets VALUES (2, 'Auchan V2', 'LI3', '120000'); projets VALUES (3, 'Continent Wasquehal', 'LI3', '95000'); projets VALUES (4, 'Lycée Beaudelaire Roubaix', 'LI3', projets projets projets projets projets projets projets

VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(5, 'Lycée Gambetta Tourcoing', 'LI3', '12000'); (6, 'Préfecture Lille', 'LI1', '23000'); (7, 'Préfecture Arras', 'ARR', '15000'); (8, 'Conseil Regional PdC', 'LI2', '75000'); (9, 'Sollac', 'DOU', '200000'); (10, 'metal Artois', 'ARR', '85000'); (11, 'Sncf', 'DOU', '25000');

CREATE TABLE vendeurs ( numvendeur int NOT NULL , nom varchar(30) default NULL, prenom varchar(30) default NULL, adresse varchar(50) default NULL, ville varchar(30) default NULL, chef int default NULL, agence char(3) default NULL, salairefixe float default NULL, commission float default NULL, PRIMARY KEY (numvendeur) ) ; INSERT INTO vendeurs VALUES (7, 'DELCOURT', 'FLORENCE', '88, rue du bac', 'LENS', 5, 'ARR', '2200', '1500'); INSERT INTO vendeurs VALUES (8, 'CASPER', 'ARMAND', '123, rue Trarieux', 'VALENCIENNES', 6, 'DOU', '2200', '1700'); INSERT INTO vendeurs VALUES (12, 'WILLIAMS', 'GEORGE', '37, rue dentellière', 'CALAIS', 5, 'ARR', '2200', '1300'); INSERT INTO vendeurs VALUES (14, 'VANASTEN', 'ROBERT', '36, rue verte', 'ROUBAIX', 9, 'LI1', '2200', '1500'); INSERT INTO vendeurs VALUES (16, 'SCHMIDT', 'ANDRE', '161, rue Lavoisier', 'LAMBERSART', 9, 'LI1', '2200', '1200'); INSERT INTO vendeurs VALUES (19, 'LUCAS', 'ANNIE', '25, rue du Château', 'CROIX', 9, 'LI1', '2200', '1600');

INSERT INTO vendeurs VALUES (21, 'WOLNIEWICZ', 'IRENA', '14, rue Nationale', 'DOUAI', 6, 'DOU', '2200', '1200'); INSERT INTO vendeurs VALUES (26, 'DECONNINCK', 'HERVE', '196 allée Ste Cécile', 'LAMBERSART', 9, 'LI1', '2200', '1300'); INSERT INTO vendeurs VALUES (29, 'TRELCAT', 'LEON', '3, rue du 2ème DB', 'ST ANDRE', 28, 'LI2', '2200', '700'); INSERT INTO vendeurs VALUES (30, 'LECLERCQ', 'FREDERIC', '142, rue Ogiers', 'CROIX', 28, 'LI2', '2200', '1100'); INSERT INTO vendeurs VALUES (32, 'QUINTARD', 'ANDRE', '41, rue Jean Jaurès', 'HELLEMMES', 28, 'LI2', '2200', '1300'); INSERT INTO vendeurs VALUES (44, 'MONET', 'ARTHUR', '142, rue Barthélémy Delespaul', 'LILLE', 28, 'LI2', '2200', '1100'); INSERT INTO vendeurs VALUES (45, 'MATTES', 'GERARD', '49, rue de la Louvière', 'LILLE', 47, 'LI3', '2200', '1600'); INSERT INTO vendeurs VALUES (46, 'PRADIER', 'ARMAND', '15, Pl du Maréchal Leclerc', 'LILLE', 47, 'LI3', '2200', '1900'); INSERT INTO vendeurs VALUES (50, 'LEMAIRE', 'BEATRICE', '145, rue Anatole France', 'RONCHIN', 47, 'LI3', '2200', '1600'); INSERT INTO vendeurs VALUES (55, 'SOLAGNA', 'LOUIS', '1, allée Edgard Degas', 'TOURCOING', 47, 'LI3', '2200', '1500'); INSERT INTO vendeurs VALUES (56, 'DURAND', 'SYLVIE', '4, allée Sylva', 'LAMBERSART', 9, 'LI1', '2200', '1800'); INSERT INTO vendeurs VALUES (65, 'RENOU', 'CHRISTOPHE', '9 Rue des Saules', 'ERQUINGHEM-LYS', 5, 'ARR', '2200', '1600'); INSERT INTO vendeurs VALUES (78, 'DIRICKS', 'ADELINE', '32, rue Vlamynck', 'DOUAI', 6, 'DOU', '2200', '1400'); INSERT INTO vendeurs VALUES (57, 'VANASTEN', 'Emilie', '45, rue de la paix', 'ARRAS', 5, 'ARR', '1800', '500'); CREATE TABLE ventes ( numvente int NOT NULL, numvendeur int default NULL, datevente char(10) default NULL, numcli int default NULL, numproj int default NULL, PRIMARY KEY (numvente) ) ; INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1, 7, '05/09/2006', 12, NULL); (2, 12, '05/09/2006', 6, 3); (3, 21, '06/09/2006', 2, 4); (4, 32, '06/09/2006', 3, NULL); (5, 46, '06/09/2006', 10, NULL); (6, 7, '06/09/2006', 9, 2); (7, 55, '07/09/2006', 7, NULL); (8, 78, '07/09/2006', 11, NULL); (9, 50, '07/09/2006', 20, NULL); (10, 8, '07/09/2006', 1, NULL); (11, 44, '08/09/2006', 15, NULL); (12, 7, '11/09/2006', 12, NULL); (13, 46, '11/09/2006', 32, 9); (14, 65, '11/09/2006', 18, NULL); (15, 16, '11/09/2006', 19, NULL); (16, 46, '11/09/2006', 21, NULL); (17, 65, '11/09/2006', 22, NULL); (18, 14, '12/09/2006', 8, 6); (19, 19, '12/09/2006', 23, NULL); (20, 30, '12/09/2006', 24, NULL); (21, 44, '12/09/2006', 25, NULL);

INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes ventes

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(22, (23, (24, (25, (26, (27, (28, (29, (30, (31, (32, (33, (34, (35, (36, (37,

32, '12/09/2006', 26, NULL); 14, '12/09/2006', 8, 6); 56, '13/09/2006', 27, NULL); 12, '13/09/2006', 6, 3); 21, '13/09/2006', 2, 4); 55, '13/09/2006', 28, NULL); 8, '13/09/2006', 14, 10); 29, '13/09/2006', 29, NULL); 30, '13/09/2006', 30, NULL); 7, '13/09/2006', 9, 2); 45, '14/09/2006', 31, NULL); 12, '14/09/2006', 6, 3); 32, '14/09/2006', 3, NULL); 46, '14/09/2006', 5, 11); 44, '14/09/2006', 25, NULL); 14, '15/09/2006', 2, 4);

CREATE TABLE agences ( refag char(3) NOT NULL, designation varchar(50) default NULL, ville varchar(30) default NULL, capital float default NULL, PRIMARY KEY (refag) );

INSERT INTO '70000'); INSERT INTO '100000'); INSERT INTO '100000'); INSERT INTO '50000'); INSERT INTO '120000');

agences VALUES ('ARR', 'AGENCE D_ARRAS CENTRE', 'ARRAS', agences VALUES ('DOU', 'UNITE DE DOUAI ZI EST', 'DOUAI', agences VALUES ('LI1', 'BUREAU LILLE CENTRE', 'LILLE', agences VALUES ('LI2', 'UNITE DE LILLE OUEST', 'LILLE', agences VALUES ('LI3', 'UNITE DE LILLE EST', 'LILLE',

TRAVAIL A FAIRE Ecrivez en SQL les requêtes suivantes et testez-les Requêtes simples 1. Donnez la liste de toutes les informations sur tous les produits. 2. Donnez la liste des informations sur les produits de couleur grise. 3. Donnez la liste des produits dont le poids est compris entre 2 et 3 kilos. 4. Donnez la liste des différentes couleurs des produits. 5. Donnez la liste (nom, prenom, agence), triée par le nom, des vendeurs dont le nom commence par DU 6. Donnez la liste, triée par le nom, des vendeurs des agences d’Arras ou de Douai. 7. Donnez la liste (nom, prenom, agence, salairefixe) des vendeurs, triée par agence puis par salaire fixe. 8. Quels sont les vendeurs dont la commission n’EST PAS comprise entre 1000 et 1500 euros et dont le nom ne contient pas 2 fois la lettre ’L’. 9. Quels sont les vendeurs dont le salaire fictif, exprimé par : salairefixe + 10*commission, est supérieur à 20000 euros. 10. Comptez le nombre de vendeurs des agences de Lille-Centre (LI1) 11. Comptez le nombre des différentes couleurs des produits. 12. Affichez le poids total, le poids minimal et le poids moyen de tous les produits. 13. Affichez le salaire fixe moyen et la commission moyenne des vendeurs des agences de Lille. 14. Quels sont les produits dont le poids est inconnu. 15. Donnez la liste des agences, avec, pour chacune d’elle, le nombre de vendeurs, le salaire fixe moyen, la commission moyenne de ses vendeurs. Accompagnez chaque colonne de cette liste du libellé correspondant. 16. Donnez la liste des homonymes parmi les vendeurs. 17. Donnez la liste des categories de produits, avec pour chacune d’elle, le nombre de produits concernes, leur poids moyen, leur poids total, la valeur totale. 18. Répondre à la même question en ne tenant compte que des produits dont le prix unitaire est compris entre 100 et 500 euros et dont le nombre est supérieur à 3.

Requêtes avec jointures 19. Donnez la liste des pièces vendues dans le cadre du projet numéro 4 20. Donnez la liste, triée par ordre alphabétique, de vendeurs qui sont intervenus dans le cadre de ce projet. 21. En ce qui concerne les projets, donnez le numéro des produits vendus, leur désignation, la quantité vendue, le prix total, le numéro du vendeur, celui du projet et la désignation de ce dernier. Ordonnez le résultat par projet et vendeur. 22. Modifiez la requête précédente en y rajoutant la désignation de l’agence qui suit le projet. 23. Donnez la liste des produits (numprod, designation) vendus par une des agences d’Arras ou de Douai. 24. Donnez la liste des projets, avec, pour chacun d’eux, le nombre des ventes, la quantité totale vendue, la valeur des ventes. 25. Donnez la liste de toutes les équipes possibles de deux vendeurs d’une même agence (sans répétition). 26. Donnez la liste de tous les ensembles possibles de 3 produits (sans répétition) dont la somme des prix soit inférieure `a 1000 euros. 1 27. Donnez la liste, sans répétition, de tous les ensembles possibles de deux produits dont la moyenne des poids NE SOIS PAS comprise entre 3 et 4 kg. 28. Donnez la liste des vendeurs qui interviennent dans un projet qui n’est pas suivi par leur agence. 29. Donnez la liste des clients, classée par ordre décroissant en fonction du volume des achats. 30. Donnez la liste des catégories de produits, ordonnée selon le prix moyen des produits appartenant à chaque catégorie. 31. Donnez la liste des agences, ordonnée (sens décroissant) selon le nombre de leur personnel. Sous requêtes non corrélatives REM : Ces requêtes pourraient aussi bien être écrite uniquement à l’aide de jointures. 32. Donnez la liste des vendeurs qui interviennent dans le projet numéro 4. 33. Donnez la liste des produits dont le poids soit supérieur à la moyenne des poids et dont le prix soit compris entre 100 et 500 euros. 34. Donnez la liste des agences dont le chiffre d’affaire (valeur totale des ventes réalisées) soit supérieure au chiffre d’affaire moyen de toutes les agences (chiffre d’affaire total divisé par le nombre d’agence,considère comme connu et égal à 5). 35. Donnez la liste des vendeurs dont le salaire est supérieur à celui d’au moins un vendeur d’une des agences de Lille. 36. Même requête, en remplaçant : “au moins un vendeur” par “de tous les vendeurs”. 37. Donnez la liste des vendeurs qui interviennent dan un projet pour lequel des pièces grises ont été vendues. Remplacez TOUTES les jointures par des sous- requêtes imbriquées. 38. Quel est le vendeur qui a le plus vendu (en valeur financière). Sous-requetes corrélatives 43. Donnez la liste des vendeurs dont le salaire est supérieur à la moyenne des salaires des vendeurs de la même agence. 44. Donnez la liste des vendeurs qui n’ont rien vendu. 45. Donnez la liste des projets pour lesquels aucun produit de valeur supérieure à 500 euros n’a été vendu. 46. Donnez la liste des vendeurs qui travaillent dans une des agences qui suivent les projets retournes par la requête précédente.