CREATE DATABASE PUFPAF
USE PUFPAF
CREATE TABLE U
(
NU int PRIMARY KEY,
NomU varchar(20) ,
Ville varchar(20)
)
CREATE TABLE P
(
NP int primary key,
NomP varchar(20),
Couleur varchar(20),
Poids int
)
CREATE TABLE F
(
NF int PRIMARY KEY,
NomF varchar(20),
Statut varchar(20),
Ville varchar(20)
)
CREATE TABLE PUF
(
NP INT FOREIGN KEY REFERENCES P(NP),
NU INT FOREIGN KEY REFERENCES U(NU),
NF INT FOREIGN KEY REFERENCES F(NF),
Quantité int
)

/* 1 - */

SELECT *
FROM U

/* 2 - */

SELECT *
FROM U
WHERE Ville='Londres'

/* 3 - */

SELECT DISTINCT NF
FROM PUF
WHERE NU=1 and NP=1

/* 4 - */

SELECT Couleur,NomP
from PUF
Join p ON PUF.NP=P.NP
WHERE PUF.NF=1

/* 5 - */

SELECT NF
FROM PUF
JOIN P ON PUF.NP=P.NP
WHERE P.Couleur='Rouge'
AND PUF.NU = 1

/* 6 - */

SELECT DISTINCT NomF
FROM PUF
JOIN F ON PUF.NF=F.NF
WHERE PUF.NU IN ( SELECT NU
                 FROM U
                 WHERE Ville = 'Casablanca' or Ville = 'Tanger' )

/* 7 - */

SELECT NP
FROM PUF
JOIN U ON PUF.NU=U.NU
JOIN F ON PUF.NF=F.NF
WHERE U.Ville = F.Ville

/* 8 - */

SELECT NP
FROM PUF
JOIN U ON PUF.NU=U.NU
JOIN F ON PUF.NF=F.NF
WHERE U.Ville = 'Tanger' and F.Ville = 'Casablanca'

/* 9 - */

SELECT DISTINCT PUF.NU
FROM PUF
JOIN U ON PUF.NU=U.NU
JOIN F ON PUF.NF=F.NF
WHERE U.Ville<>F.Ville

/* 10 - */

SELECT NF
FROM F
WHERE NF IN ( SELECT DISTINCT NF FROM PUF WHERE NU=1 )
AND NF IN ( SELECT DISTINCT NF FROM PUF WHERE NU=2 )

/* 11 - */

SELECT DISTINCT NU
FROM PUF
WHERE NP IN ( SELECT DISTINCT NP FROM PUF WHERE NF=3 )

/* 12 - */

SELECT NP
FROM P
WHERE Poids = (SElECT MIN(Poids) FROM P)

/* 13 - */

SELECT DISTINCT NU
FROM U
WHERE NU NOT IN ( SELECT PUF.NU FROM PUF JOIN P on PUF.NP = P.NP And P.Couleur = 'Rouge' )
AND NU NOT IN ( SELECT U.NU FROM PUF JOIN U on PUF.NU = U.NU And U.Ville = 'Londres' )

/* 14 - */

SELECT DISTINCT F.Ville, NP, U.Ville
FROM PUF
JOIN U ON PUF.NU=U.NU
JOIN F ON PUF.NF=F.NF

/* 15 - */

SELECT DISTINCT F.Ville, NP, U.Ville
FROM PUF
JOIN U ON PUF.NU=U.NU
JOIN F ON PUF.NF=F.NF
WHERE F.Ville<>U.Ville

/* 16 - */

SELECT NP
FROM PUF
WHERE NU in ( SELECT NU FROM U WHERE Ville='Londres')
and SELECT COUNT(DISTINCT NU) = ( SELECT NU FROM U WHERE Ville='Londres')