Skip to main content

Excercice SQL

 

Création des tables

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));

 

Création des données

 INSERT INTO Segment VALUES ('130.120.80','Brin RDC',NULL); 
INSERT INTO Segment VALUES ('130.120.81','Brin 1er étage',NULL); 
INSERT INTO Segment VALUES ('130.120.82','Brin 2ème étage',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-12','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); 
INSERT INTO installer (nPoste,nLog,dateIns,delai) VALUES ('p7', 'log7', '2002-04-01',NULL);

Ajout de colonnes

  • Rajouter à la table Segment une colonne nombre de salle et nombre de poste
  • Rajouter une colonne nombre d'install dans la table Logiciel
  • Rajouter une colonne nombre de type logiciel sur la table poste
ALTER TABLE Segment ADD (nbSalle TINYINT(2) DEFAULT 0, nbPoste TINYINT(2) DEFAULT 0);
ALTER TABLE Logiciel ADD nbInstall TINYINT(2) DEFAULT 0; 
ALTER TABLE Poste ADD nbLog TINYINT(2) DEFAULT 0;

Modification des colonnes

  • Modifier la colonne nomSalle de la table Salle afin que cela soit un VARCHAR(30)
  • Modifier la colonne nomSergment de la table Segment afin que cela soit un VARCHAR(15)
 ALTER TABLE Salle MODIFY nomSalle VARCHAR(30); 
 DESC Salle; 
 ALTER TABLE Segment MODIFY nomSegment VARCHAR(15); 
 DESC Segment;

Modification de donnée

Mettre a jout les étages avec les segments IP tel que :

  • 130.120.80 est assignée à l'étage 0
  • 130.120.81 est assignée à l'étage 1
  • 130.120.82 est assignée à l'étage 2.

Le prix des PC sous NT (PCNT) à baisser de 10%.

UPDATE Segment SET etage=0 WHERE indIP = '130.120.80'; UPDATE Segment SET etage=1 WHERE indIP = '130.120.81';
 UPDATE Segment SET etage=2 WHERE indIP = '130.120.82';
 SELECT * FROM Segment;
 UPDATE Logiciel

SET prix = prix*0.9 WHERE typeLog = 'PCNT'; 
 SELECT nLog, typeLog, prix FROM Logiciel;

Selection de données

Trouvé les type de poste tel que le la colonne nPoste de la table Poste soit p8

SELECT nPoste, typePoste FROM Poste WHERE nPoste = 'p8';

Trouver les noms des logiciels de type UNIX

 SELECT nomLog FROM Logiciel WHERE typeLog = 'UNIX';

Trouver les Nom,Adresse, numéro de salle des poste de type UNIX ou PCWS

SELECT nomPoste, indIP, ad, nSalle FROM poste WHERE typePoste = 'UNIX' OR typePoste = 'PCWS';

Faites de même pour les postes du segment 130.120.80 en les triant par numéro de salle décroissant

 SELECT nomPoste, indIP, ad, nSalle FROM poste WHERE (typePoste = 'UNIX' OR typePoste = 'PCWS') AND indIP = '130.120.80' ORDER BY nSalle DESC;

Trouvé les numéo de logiciel installés sur le poste p6

SELECT nLog FROM Installer WHERE nPoste = 'p6';

Trouver les Nom et adresse IP complète (ex : 130.120.80.01) des postes de type TX

SELECT nomPoste, CONCAT(indIP,'.',ad) FROM Poste WHERE typePoste = 'TX';

Fonction et Groupements

Trouver les prix moyen des logiciel de type Unix

 SELECT AVG(prix) FROM Logiciel WHERE typeLog = 'UNIX';

Afficher la liste des postes avec le nombre de logiciel.

SELECT nPoste, COUNT(nLog) FROM installer GROUP BY (nPoste);

Afficher la liste des logiciels avec le nombre de poste installé

 SELECT nLog, COUNT(nPoste) FROM Installer GROUP BY (nLog);

Trouver la valeur maximal des date d'achats de logiciel

SELECT MAX(dateAch) FROM Logiciel;

Afficher les poste ayant 2 logiciel installé

SELECT nPoste FROM Installer GROUP BY nPoste HAVING COUNT(nLog)=2;

Requetes multitable

Afficher les systèmes d'exploitation jamais installé sur les postes

SELECT DISTINCT typeLP FROM Types WHERE typeLP NOT IN (SELECT DISTINCT typePoste FROM Poste);

Afficher les logiciels installé sur les postes

SELECT DISTINCT typeLog FROM Logiciel WHERE typeLog IN (SELECT typePoste From Poste)

Renvoyer les adresses IP des postes ou Oracle 8 a été installé

SELECT CONCAT(indIP,'.',ad) FROM Poste WHERE nPoste IN

(SELECT nPoste FROM Installer WHERE nLog =  (SELECT nLog 
 FROM Logiciel    WHERE nomLog = 'Oracle 8'));

Jointures

Adresse IP des postes qui hébergent le logiciel de nom ‘Oracle 8’

 SELECT CONCAT(indIP,'.',ad) FROM Poste p, Installer i, Logiciel l WHERE p.nPoste = i.nPoste AND l.nLog = i.nLog AND l.nomLog = 'Oracle 8';
SELECT CONCAT(indIP,'.',ad) FROM Poste NATURAL JOIN Installer

NATURAL JOIN Logiciel WHERE nomLog = 'Oracle 8';

 

Noms des salles ou l’on peut trouver au moins un poste hébergeant ‘Oracle 6’

 SELECT s.nomSalle FROM Salle s, Poste p, Installer i, Logiciel l WHERE s.nSalle = p.nSalle AND p.nPoste = i.nPoste AND i.nLog = l.nLog AND l.nomLog = 'Oracle 6';
Back to top