# Excercice SQL

## Création des tables

```SQL
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

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

```

## <span class="mw-headline" id="bkmrk-ajout-de-colonnes-0">Ajout de colonnes</span>

- <span class="mw-headline">Rajouter à la table Segment une colonne nombre de salle et nombre de poste  
    </span>
- <span class="mw-headline">Rajouter une colonne nombre d'install dans la table Logiciel  
    </span>
- <span class="mw-headline">Rajouter une colonne nombre de type logiciel sur la table poste</span>

```SQL
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;
```

## <span class="mw-headline" id="bkmrk-modification-des-col-0">Modification des colonnes</span>

- <span class="mw-headline">Modifier la colonne nomSalle de la table Salle afin que cela soit un VARCHAR(30)  
    </span>
- <span class="mw-headline">Modifier la colonne nomSergment de la table Segment afin que cela soit un VARCHAR(15)</span>

```SQL
 ALTER TABLE Salle MODIFY nomSalle VARCHAR(30); 
 DESC Salle; 
 ALTER TABLE Segment MODIFY nomSegment VARCHAR(15); 
 DESC Segment; 
```

## <span class="mw-headline" id="bkmrk-modification-de-donn-0">Modification de donnée</span>

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%.

```SQL
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; 
```

## <span class="mw-headline" id="bkmrk-selection-de-donn%C3%A9es-0">Selection de données</span>

<span class="mw-headline">Trouvé les type de poste tel que le la colonne nPoste de la table Poste soit p8</span>

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

Trouver les noms des logiciels de type UNIX

```SQL
 SELECT nomLog FROM Logiciel WHERE typeLog = 'UNIX'; 
```

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

```SQL
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

```SQL
 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

```SQL
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

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

## <span class="mw-headline" id="bkmrk-fonction-et-groupeme-0">Fonction et Groupements</span>

<span class="mw-headline">Trouver les prix moyen des logiciel de type Unix</span>

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

Afficher la liste des postes avec le nombre de logiciel.

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

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

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

Trouver la valeur maximal des date d'achats de logiciel

```SQL
SELECT MAX(dateAch) FROM Logiciel; 
```

Afficher les poste ayant 2 logiciel installé

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

## <span class="mw-headline" id="bkmrk-requetes-multitable-0">Requetes multitable</span>

<span class="mw-headline">Afficher les systèmes d'exploitation jamais installé sur les postes</span>

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

Afficher les logiciels installé sur les postes

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

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

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

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

## <span class="mw-headline" id="bkmrk-jointures-0">Jointures</span>

<span class="mw-headline">Adresse IP des postes qui hébergent le logiciel de nom ‘Oracle 8’</span>

```
 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';
```

```SQL
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’

```SQL
 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'; 
```