[sql] examTP
Viewer
- USE examTP
- DROP TABLE IF EXISTS Types,pk_Installer,Installer,Logiciel,Poste,Salle,Segment;
- CREATE TABLE Segment
- (indIP VARCHAR(11),
- nomSegment VARCHAR(20) NOT NULL,
- etage TINYINT(1),
- CONSTRAINT pk_Segment PRIMARY KEY (indIP));
- CREATE TABLE Salle
- (nSalle VARCHAR(7),
- nomSalle VARCHAR(20) NOT NULL,
- nbPoste TINYINT(2),
- indIP VARCHAR(11),
- CONSTRAINT pk_salle PRIMARY KEY (nSalle));
- CREATE TABLE Poste
- (nPoste VARCHAR(7),
- nomPoste VARCHAR(20) NOT NULL,
- indIP VARCHAR(11),
- ad VARCHAR(3),
- typePoste VARCHAR(9),
- nSalle VARCHAR(7),
- CONSTRAINT pk_Poste PRIMARY KEY (nPoste),
- CONSTRAINT ck_ad CHECK (ad BETWEEN '000' AND '255'));
- CREATE TABLE Logiciel
- (nLog VARCHAR(5),
- nomLog VARCHAR(20) NOT NULL,
- dateAch DATETIME,
- version VARCHAR(7),
- typeLog VARCHAR(9),
- prix DECIMAL(6,2),
- CONSTRAINT pk_Logiciel PRIMARY KEY (nLog),
- CONSTRAINT ck_prix CHECK (prix >= 0));
- CREATE TABLE Installer
- (nPoste VARCHAR(7),
- nLog VARCHAR(5),
- numIns INTEGER(5) AUTO_INCREMENT,
- dateIns TIMESTAMP DEFAULT NOW(),
- delai DECIMAL(8,2),
- CONSTRAINT pk_Installer PRIMARY KEY(numIns));
- CREATE TABLE Types
- (typeLP VARCHAR(9), nomType VARCHAR(20),
- CONSTRAINT pk_types PRIMARY KEY(typeLP));
- INSERT INTO Segment VALUES ('130.120.80','Brin RDC',NULL);
- INSERT INTO Segment VALUES ('130.120.81','Brin 1er etage',NULL);
- INSERT INTO Segment VALUES ('130.120.82','Brin 2eme etage',NULL);
- INSERT INTO Salle VALUES ('s01','Salle 1',3,'130.120.80');
- INSERT INTO Salle VALUES ('s02','Salle 2',2,'130.120.80');
- INSERT INTO Salle VALUES ('s03','Salle 3',2,'130.120.80');
- INSERT INTO Salle VALUES ('s11','Salle 11',2,'130.120.81');
- INSERT INTO Salle VALUES ('s12','Salle 12',1,'130.120.81');
- INSERT INTO Salle VALUES ('s21','Salle 21',2,'130.120.82');
- INSERT INTO Salle VALUES ('s22','Salle 22',0,'130.120.83');
- INSERT INTO Salle VALUES ('s23','Salle 23',0,'130.120.83');
- INSERT INTO Poste VALUES ('p1','Poste 1','130.120.80','01','TX','s01');
- INSERT INTO Poste VALUES ('p2','Poste 2','130.120.80','02','UNIX','s01');
- INSERT INTO Poste VALUES ('p3','Poste 3','130.120.80','03','TX','s01');
- INSERT INTO Poste VALUES ('p4','Poste 4','130.120.80','04','PCWS','s02');
- INSERT INTO Poste VALUES ('p5','Poste 5','130.120.80','05','PCWS','s02');
- INSERT INTO Poste VALUES ('p6','Poste 6','130.120.80','06','UNIX','s03');
- INSERT INTO Poste VALUES ('p7','Poste 7','130.120.80','07','TX','s03');
- INSERT INTO Poste VALUES ('p8','Poste 8','130.120.81','01','UNIX','s11');
- INSERT INTO Poste VALUES ('p9','Poste 9','130.120.81','02','TX','s11');
- INSERT INTO Poste VALUES ('p10','Poste 10','130.120.81','03','UNIX','s12');
- INSERT INTO Poste VALUES ('p11','Poste 11','130.120.82','01','PCNT','s21');
- INSERT INTO Poste VALUES ('p12','Poste 12','130.120.82','02','PCWS','s21');
- INSERT INTO Logiciel VALUES ('log1','Oracle 6', '1995-05-13', '6.2', 'UNIX', 3000);
- INSERT INTO Logiciel VALUES ('log2','Oracle 8', '1999-09-15', '8i', 'UNIX', 5600);
- INSERT INTO Logiciel VALUES ('log3','SQL Server', '1998-04-12', '7', 'PCNT', 3000);
- INSERT INTO Logiciel VALUES ('log4','Front Page', '1997-06-03', '5', 'PCWS', 500);
- INSERT INTO Logiciel VALUES ('log5','WinDev', '1997-05-13', '5', 'PCWS', 750);
- INSERT INTO Logiciel VALUES ('log6','SQL*Net', NULL, '2.0', 'UNIX', 500);
- INSERT INTO Logiciel VALUES ('log7','I. I. S.', '2002-04-12', '2', 'PCNT', 900);
- INSERT INTO Logiciel VALUES ('log8','DreamWeaver','2003-09-21','2.0','BeOS',1400);
- INSERT INTO Types VALUES ('TX','Terminal X-Window');
- INSERT INTO Types VALUES ('UNIX','Système Unix');
- INSERT INTO Types VALUES ('PCNT','PC Windows NT');
- INSERT INTO Types VALUES ('PCWS','PC Windows');
- INSERT INTO Types VALUES ('NC','Network Computer');
- INSERT INTO Installer (nPoste,nLog,dateIns,delai) VALUES ('p2', 'log1', '2003-05-15',NULL);
- INSERT INTO Installer (nPoste,nLog,dateIns,delai) VALUES ('p2', 'log2', '2003-09-17',NULL);
- INSERT INTO Installer (nPoste,nLog,dateIns,delai) VALUES ('p4', 'log5', NULL,NULL);
- INSERT INTO Installer (nPoste,nLog,dateIns,delai) VALUES ('p6', 'log6', '2003-05-20',NULL);
- INSERT INTO Installer (nPoste,nLog,dateIns,delai) VALUES ('p6', 'log1', '2003-05-20',NULL);
- INSERT INTO Installer (nPoste,nLog,dateIns,delai) VALUES ('p8', 'log2', '2003-05-19',NULL);
- INSERT INTO Installer (nPoste,nLog,dateIns,delai) VALUES ('p8', 'log6', '2003-05-20',NULL);
- INSERT INTO Installer (nPoste,nLog,dateIns,delai) VALUES ('p11','log3', '2003-04-20',NULL);
- INSERT INTO Installer (nPoste,nLog,dateIns,delai) VALUES ('p12','log4', '2003-04-20',NULL);
- INSERT INTO Installer (nPoste,nLog,dateIns,delai) VALUES ('p11','log7', '2003-04-20',NULL);
Editor
You can edit this paste and save as new:
File Description
- examTP
- Paste Code
- 05 May-2021
- 4.93 Kb
You can Share it: