5zTURnYs

1/*------------ Exercice 3 ---------------*/
2
3/* Traitenement des Erreur */
4SELECT * FROM `SALLE` WHERE `indIP` NOT IN (SELECT `indIP` FROM `SEGMENT`);
5
6/* Suppression des enregistrement */
7DELETE FROM `SALLE` WHERE `indIP` NOT IN (SELECT `indIP` FROM `SEGMENT`);
8ALTER TABLE `SALLE` ADD CONSTRAINT FOREIGN KEY(`indIP`) REFERENCES  `SEGMENT` (`indIP`);
9
10INSERT INTO `TYPES` (`typeLP`,`nomType`) VALUES ('Beos', 'System BE');
11ALTER TABLE `LOGICIEL` ADD CONSTRAINT FOREIGN KEY(`typeLog`) REFERENCES `TYPES` (`typeLp`);
12
13
14
15/* CREATION DYNAMIQUE DES TABLES */
16
17
18
19/* Méthode 1
20
21CREATE TABLE `Softs` AS
22	SELECT `LOGICIEL.`  `.`  `nomLogm` AS `nomSofts`,
23				 `LOGICIEL.`  `.`  `version` AS `version`,
24				 `LOGICIEL`   `.`  `prix` AS `prix`
25	FROM
26			 `LOGICIEL`; */
27
28
29
30create table if not exists Softs
31(
32		nomLog varchar(20),
33    version varchar(7),
34		prix DECIMAL(6,2)
35) as select version, prix , nomLog as nomSoft from LOGICIEL;
36
37
38create table if not exists PCSeuls
39(
40  nPoste VARCHAR(7),
41	nomPoste VARCHAR(20),
42	typePoste VARCHAR(10),
43  ad VARCHAR(3) CHECK (0> VALUE >255),
44	indIP VARCHAR(7),
45	nSalle VARCHAR(7)
46)
47	as select nPoste,nomPoste,typePoste,ad,indIP,nSalle from POSTE
48			where indIP = 'PCWS' or indIP ='UNIX';