Entrainement Evaluation

Vous êtes ici : >> SQL / Evaluation
Temps d'étude : 2h30. Niveau : Moyen.
Tags : Base de données, cours sql, Evaluation sql
Fichier(s) utile(s) pour ce cours : immobilier.sql

Evaluation : Modélisation

Pour nous entrainer sur les requêtes, vous pouvez télécharger le fichier SQL complet ici : immobilier.sql

Voici les structures et données enregistrées dans les tables :

Table Agence

FieldTypeNullKeyDefaultExtra
idAgenceint(6)NOPRIauto_increment
nomvarchar(100)NO
adressevarchar(100)NO


idAgencenomadresse
257400logic-immowww.logic-immo.com
383505century21rue century
504585laforetrue laforet
544688fnaimrue fnaim
608870orpirue orpi
654178fonciarue foncia
654658guy-hoquetrue guy-hoquet
654893selogerwww.seloger.com
692702bouygues immobilierwww.bouygues-immobilier.net


SQL / BDD Immobilier / Table Agence
CREATE TABLE IF NOT EXISTS agence (
  idAgence int(6) NOT NULL AUTO_INCREMENT,
  nom varchar(100) NOT NULL,
  adresse varchar(100) NOT NULL,
  PRIMARY KEY (idAgence)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

INSERT INTO agence (idAgence, nom, adresse) VALUES
(257400, 'logic-immo', 'rue logic'),
(383505, 'century21', 'rue century'),
(504585, 'laforet', 'rue laforet'),
(544688, 'fnaim', 'rue fnaim'),
(608870, 'orpi', 'rue orpi'),
(654178, 'foncia', 'rue foncia'),
(654658, 'guy-hoquet', 'rue guy-hoquet'),
(654893, 'seloger', 'rue seloger'),
(692702, 'bouygues immobilier', 'rue bouygues');




Table Demande

FieldTypeNullKeyDefaultExtra
idDemandeint(5)NOPRIauto_increment
idPersonneint(3)NOMUL
typevarchar(100)NO
villevarchar(100)NO
budgetint(7)NO
superficieint(5)NO
categorievarchar(100)NO


idDemandeidPersonnetypevillebudgetsuperficiecategorie
11appartementparis530000120vente
23appartementbordeaux12000018vente
34appartementbordeaux14500021vente
45appartementbordeaux15200026vente
56appartementlyon20000055vente
69appartementparis17100040vente
713appartementparis16300025vente
816appartementparis13200015vente
919appartementparis35000080vente
1022appartementlyon60020location
1125appartementlyon18800065vente
1227appartementparis40015location
1328appartementparis330500100vente
1431appartementparis9000015vente
1532appartementlyon12380021vente
1635appartementlyon120070vente
1737appartementlyon1500100vente
1843appartementparis60020location
1944appartementparis75030location
2045appartementbordeaux68030location
2146appartementbordeaux21300040vente
2247appartementbordeaux70045location
2348appartementparis19500040vente
2449appartementparis25000060vente
2550appartementlyon11000012vente
2651appartementlyon50017location
2752appartementparis80040location
2853appartementparis85050location
2954appartementparis17700040vente
3055appartementparis63020location


SQL / BDD Immobilier / Table Agence
CREATE TABLE IF NOT EXISTS demande (
  idDemande int(5) NOT NULL AUTO_INCREMENT,
  idPersonne int(3) NOT NULL,
  genre varchar(100) NOT NULL,
  ville varchar(100) NOT NULL,
  budget int(7) NOT NULL,
  superficie int(5) NOT NULL,
  categorie varchar(100) NOT NULL,
  PRIMARY KEY (idDemande),
  KEY idPersonne (idPersonne)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO demande (idDemande, idPersonne, genre, ville, budget, superficie, categorie) VALUES
(1, 1, 'appartement', 'paris', 530000, 120, 'vente'),
(2, 3, 'appartement', 'bordeaux', 120000, 18, 'vente'),
(3, 4, 'appartement', 'bordeaux', 145000, 21, 'vente'),
(4, 5, 'appartement', 'bordeaux', 152000, 26, 'vente'),
(5, 6, 'appartement', 'lyon', 200000, 55, 'vente'),
(6, 7, 'appartement', 'paris', 400000, 55, 'vente'),
(7, 9, 'appartement', 'paris', 171000, 40, 'vente'),
(8, 13, 'appartement', 'paris', 163000, 25, 'vente'),
(9, 16, 'appartement', 'paris', 132000, 15, 'vente'),
(10, 19, 'appartement', 'paris', 350000, 80, 'vente'),
(11, 22, 'appartement', 'lyon', 600, 20, 'location'),
(12, 25, 'appartement', 'lyon', 188000, 65, 'vente'),
(13, 27, 'appartement', 'paris', 400, 15, 'location'),
(14, 28, 'appartement', 'paris', 330500, 100, 'vente'),
(15, 31, 'appartement', 'paris', 90000, 15, 'vente'),
(16, 32, 'appartement', 'lyon', 123800, 21, 'vente'),
(17, 35, 'appartement', 'lyon', 1200, 70, 'vente'),
(18, 37, 'appartement', 'lyon', 1500, 100, 'vente'),
(19, 43, 'appartement', 'paris', 600, 20, 'location'),
(20, 44, 'appartement', 'paris', 750, 30, 'location'),
(21, 45, 'appartement', 'bordeaux', 680, 30, 'location'),
(22, 46, 'appartement', 'bordeaux', 213000, 40, 'vente'),
(23, 47, 'appartement', 'bordeaux', 700, 45, 'location'),
(24, 48, 'appartement', 'paris', 195000, 40, 'vente'),
(25, 49, 'appartement', 'paris', 250000, 60, 'vente'),
(26, 50, 'appartement', 'lyon', 110000, 12, 'vente'),
(27, 51, 'appartement', 'lyon', 500, 17, 'location'),
(28, 52, 'appartement', 'paris', 800, 40, 'location'),
(29, 53, 'appartement', 'paris', 850, 50, 'location'),
(30, 54, 'appartement', 'paris', 177000, 40, 'vente'),
(31, 55, 'appartement', 'paris', 630, 20, 'location');




Table Logement

FieldTypeNullKeyDefaultExtra
idLogementint(4)NOPRIauto_increment
typevarchar(100)NO
villevarchar(100)NO
prixint(7)NO
superficieint(5)NO
categorievarchar(100)NO


idLogementtypevilleprixsuperficiecategorie
5067appartementparis18500061vente
5089appartementparis11500015vente
5091maisonparis510000130vente
5122appartementbordeaux55017location
5189appartementlyon42014location
5245appartementparis16000040vente
5246appartementparis67035location
5249appartementlyon11000016vente
5269appartementbordeaux16150033vente
5278appartementparis20200090vente
5324appartementlyon69031location
5336appartementbordeaux12960027vente
5378appartementbordeaux12190026vente
5412appartementparis68040location
5636appartementparis15000037vente
5661appartementbordeaux14860036vente
5723appartementbordeaux17060045vente
5770appartementparis13900038vente
5778appartementbordeaux12860043vente
5779appartementparis310000105vente
5786appartementparis57020location
5860appartementbordeaux10500018vente
5869appartementlyon18360060vente
5873appartementlyon17670065vente
5898appartementparis69040location
5961appartementbordeaux65045location
5963appartementparis22000060vente


SQL / BDD Immobilier / Table Logement
CREATE TABLE IF NOT EXISTS logement (
  idLogement int(4) NOT NULL AUTO_INCREMENT,
  genre varchar(100) NOT NULL,
  ville varchar(100) NOT NULL,
  prix int(7) NOT NULL,
  superficie int(5) NOT NULL,
  categorie varchar(100) NOT NULL,
  PRIMARY KEY (idLogement)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO logement (idLogement, genre, ville, prix, superficie, categorie) VALUES
(5067, 'appartement', 'paris', 685000, 61, 'vente'),
(5089, 'appartement', 'paris', 115000, 15, 'vente'),
(5091, 'maison', 'paris', 1510000, 130, 'vente'),
(5122, 'appartement', 'bordeaux', 550, 17, 'location'),
(5189, 'appartement', 'lyon', 420, 14, 'location'),
(5245, 'appartement', 'paris', 360000, 40, 'vente'),
(5246, 'appartement', 'paris', 970, 35, 'location'),
(5249, 'appartement', 'lyon', 110000, 16, 'vente'),
(5269, 'appartement', 'bordeaux', 171500, 33, 'vente'),
(5278, 'appartement', 'paris', 802000, 90, 'vente'),
(5324, 'appartement', 'lyon', 1090, 31, 'location'),
(5336, 'appartement', 'bordeaux', 229600, 27, 'vente'),
(5378, 'appartement', 'bordeaux', 121900, 26, 'vente'),
(5412, 'appartement', 'paris', 1680, 40, 'location'),
(5636, 'appartement', 'paris', 370000, 37, 'vente'),
(5661, 'appartement', 'bordeaux', 248600, 36, 'vente'),
(5723, 'maison', 'bordeaux', 370600, 45, 'vente'),
(5770, 'appartement', 'paris', 339000, 38, 'vente'),
(5778, 'appartement', 'bordeaux', 228600, 43, 'vente'),
(5779, 'appartement', 'paris', 1310000, 105, 'vente'),
(5786, 'appartement', 'paris', 570, 20, 'location'),
(5860, 'appartement', 'bordeaux', 98000, 18, 'vente'),
(5869, 'appartement', 'lyon', 683600, 60, 'vente'),
(5873, 'appartement', 'lyon', 676700, 65, 'vente'),
(5898, 'appartement', 'paris', 1890, 40, 'location'),
(5961, 'appartement', 'bordeaux', 2650, 45, 'location'),
(5963, 'appartement', 'paris', 520000, 60, 'vente'),
(5964, 'appartement', 'paris', 280000, 38, 'vente');




Table logement_agence

FieldTypeNullKeyDefaultExtra
idLogementAgenceint(5)NOPRIauto_increment
idAgenceint(6)NOMUL
idLogementint(4)NOMUL
fraisint(7)NO


idLogementAgenceidAgenceidLogementfrais
1257400506715000
238350550671000
325740050898633
469270250897623
5654178509128621
6544688509134564
76548935122700
86088705189350
9257400524510856
10544688524514230
116088705246800
12257400524916358
1360887052497625
1425740052699500
15544688526911890
16544688527825689
17608870527819653
185446885324600
1954468853369542
20608870533616985
2150458553788652
22608870537815230
232574005412680
2454468856365963
25608870563613654
2665489356619462
27654178566111656
28608870572316233
29504585572319654
30692702577013655
3165417857708903
3238350557786350
33654658577812655
34654178577926754
35654658577945032
366541785786898
373835055786520
38257400586012566
3965465858608905
40544688586923685
41654893586919321
42257400587313504
432574005898900
443835055898250
4569270258981300
4625740059611240
475045855961300
486927025961890
49257400596327542
50692702596342502
51383505596318455


SQL / BDD Immobilier / Table logement_agence
CREATE TABLE IF NOT EXISTS logement_agence (
  idLogementAgence int(5) NOT NULL AUTO_INCREMENT,
  idAgence int(6) NOT NULL,
  idLogement int(4) NOT NULL,
  frais int(7) NOT NULL,
  PRIMARY KEY (idLogementAgence),
  KEY idAgence (idAgence),
  KEY idLogement (idLogement)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;


INSERT INTO logement_agence (idLogementAgence, idAgence, idLogement, frais) VALUES
(1, 257400, 5067, 34250),
(2, 383505, 5067, 30000),
(3, 257400, 5089, 5750),
(4, 692702, 5089, 7623),
(5, 654178, 5091, 75500),
(6, 544688, 5091, 56050),
(7, 654893, 5122, 700),
(8, 608870, 5189, 350),
(9, 257400, 5245, 18856),
(10, 544688, 5245, 14230),
(11, 608870, 5246, 800),
(12, 257400, 5249, 5500),
(13, 608870, 5249, 7625),
(14, 257400, 5269, 9500),
(15, 544688, 5269, 8575),
(16, 544688, 5278, 25689),
(17, 608870, 5278, 40100),
(18, 544688, 5324, 600),
(19, 544688, 5336, 9542),
(20, 608870, 5336, 11480),
(21, 504585, 5378, 8652),
(22, 608870, 5378, 6095),
(23, 257400, 5412, 680),
(24, 544688, 5636, 18500),
(25, 608870, 5636, 13654),
(26, 654893, 5661, 9462),
(27, 654178, 5661, 11656),
(28, 608870, 5723, 16233),
(29, 504585, 5723, 19654),
(30, 692702, 5770, 13655),
(31, 654178, 5770, 16950),
(32, 383505, 5778, 11430),
(33, 654658, 5778, 12655),
(34, 654178, 5779, 65500),
(35, 654658, 5779, 45032),
(36, 654178, 5786, 898),
(37, 383505, 5786, 520),
(38, 257400, 5860, 4900),
(39, 654658, 5860, 8905),
(40, 544688, 5869, 23685),
(41, 654893, 5869, 34180),
(42, 257400, 5873, 33835),
(43, 257400, 5898, 900),
(44, 383505, 5898, 250),
(45, 692702, 5898, 1300),
(46, 257400, 5961, 1240),
(47, 504585, 5961, 300),
(48, 692702, 5961, 890),
(49, 257400, 5963, 27542),
(50, 692702, 5963, 26000),
(51, 383505, 5963, 18455);




Table logement_personne

FieldTypeNullKeyDefaultExtra
idLogementPersonneint(5)NOPRIauto_increment
idPersonneint(3)NOMUL
idLogementint(4)NOUNI


idLogementPersonneidPersonneidLogement
1405067
2415089
3425091
425122
5395189
675245
785246
8105249
9185269
10215278
11175324
12365336
13205378
14295412
15245636
16345661
17145723
18575770
19265778
20565779
21125786
22115860
23235869
24385873
25335898
26155961
27305963


SQL / BDD Immobilier / Table logement_personne
CREATE TABLE IF NOT EXISTS logement_personne (
  idLogementPersonne int(5) NOT NULL AUTO_INCREMENT,
  idPersonne int(3) NOT NULL,
  idLogement int(4) NOT NULL,
  PRIMARY KEY (idLogementPersonne),
  UNIQUE KEY idLogement (idLogement),
  KEY idPersonne (idPersonne)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;


INSERT INTO logement_personne (idLogementPersonne, idPersonne, idLogement) VALUES
(1, 40, 5067),
(2, 41, 5089),
(3, 42, 5091),
(4, 2, 5122),
(5, 39, 5189),
(6, 7, 5245),
(7, 8, 5246),
(8, 10, 5249),
(9, 18, 5269),
(10, 21, 5278),
(11, 17, 5324),
(12, 36, 5336),
(13, 20, 5378),
(14, 29, 5412),
(15, 24, 5636),
(16, 34, 5661),
(17, 14, 5723),
(18, 57, 5770),
(19, 26, 5778),
(20, 56, 5779),
(21, 12, 5786),
(22, 11, 5860),
(23, 23, 5869),
(24, 38, 5873),
(25, 33, 5898),
(26, 15, 5961),
(27, 30, 5963),
(28, 56, 5964);




Table Personne

FieldTypeNullKeyDefaultExtra
idPersonneint(3)NOPRIauto_increment
prenomvarchar(100)NO


idPersonneprenom
1william
2gaetan
3mehdi
4charles
5brigitte
6sarah
7lucas
8quentin
9patrick
10emmanuel
11elodie
12agathe
13valentine
14charlotte
15alice
16samuel
17mathieu
18noemie
19simon
20florian
21clement
22yvon
23lea
24chloe
25camille
26alexandre
27julie
28leo
29antoine
30lola
31celia
32anna
33caroline
34adele
35sabrina
36nathalie
37franck
38tom
39johan
40priscillia
41assia
42nathan
43aurore
44marie
45oceane
46enzo
47ines
48hugo
49jonathan
50axelle
51morgane
52melissa
53kevin
54ophelie
55victoria
56alexis
57robin


SQL / BDD Immobilier / Table Personne
CREATE TABLE IF NOT EXISTS personne (
  idPersonne int(3) NOT NULL AUTO_INCREMENT,
  prenom varchar(100) NOT NULL,
  PRIMARY KEY (idPersonne)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;


INSERT INTO personne (idPersonne, prenom) VALUES
(1, 'william'),
(2, 'gaetan'),
(3, 'mehdi'),
(4, 'charles'),
(5, 'brigitte'),
(6, 'sarah'),
(7, 'lucas'),
(8, 'quentin'),
(9, 'patrick'),
(10, 'emmanuel'),
(11, 'elodie'),
(12, 'agathe'),
(13, 'valentine'),
(14, 'charlotte'),
(15, 'alice'),
(16, 'samuel'),
(17, 'mathieu'),
(18, 'noemie'),
(19, 'simon'),
(20, 'florian'),
(21, 'clement'),
(22, 'yvon'),
(23, 'lea'),
(24, 'chloe'),
(25, 'camille'),
(26, 'alexandre'),
(27, 'julie'),
(28, 'leo'),
(29, 'antoine'),
(30, 'lola'),
(31, 'celia'),
(32, 'anna'),
(33, 'caroline'),
(34, 'adele'),
(35, 'sabrina'),
(36, 'nathalie'),
(37, 'franck'),
(38, 'tom'),
(39, 'johan'),
(40, 'priscillia'),
(41, 'assia'),
(42, 'nathan'),
(43, 'aurore'),
(44, 'marie'),
(45, 'oceane'),
(46, 'enzo'),
(47, 'ines'),
(48, 'hugo'),
(49, 'jonathan'),
(50, 'axelle'),
(51, 'morgane'),
(52, 'melissa'),
(53, 'kevin'),
(54, 'ophelie'),
(55, 'victoria'),
(56, 'alexis'),
(57, 'robin');




Clés étrangères

SQL / BDD Immobilier / Foreign Key
ALTER TABLE demande
  ADD CONSTRAINT demande_ibfk_1 FOREIGN KEY (idPersonne) REFERENCES personne (idPersonne);

ALTER TABLE logement_agence
  ADD CONSTRAINT logement_agence_ibfk_2 FOREIGN KEY (idLogement) REFERENCES logement (idLogement),
  ADD CONSTRAINT logement_agence_ibfk_1 FOREIGN KEY (idAgence) REFERENCES agence (idAgence);

ALTER TABLE logement_personne
  ADD CONSTRAINT logement_personne_ibfk_2 FOREIGN KEY (idPersonne) REFERENCES personne (idPersonne),
  ADD CONSTRAINT logement_personne_ibfk_1 FOREIGN KEY (idLogement) REFERENCES logement (idLogement);
	


Fichier SQL complet ici : immobilier.sql

Evaluation : Questions/Réponses - Les requêtes

Dans le cadre de cette évaluation, La question et la réponse (résultat) sont données, il vous suffit de trouver la requête qui mène de la question à la réponse.

Question 1 : Affichez le nom des agences

SQL / BDD Immobilier / Requete n°1
SELECT nom FROM agence;

résultat 1 :

nom
logic-immo
century21
laforet
fnaim
orpi
foncia
guy-hoquet
seloger
bouygues immobilier



Question 2 : Affichez le numéro de l’agence « Orpi »

SQL / BDD Immobilier / Requete n°2
SELECT idAgence FROM agence WHERE nom='orpi';

résultat 2 :

idAgence
608870



Question 3 : Affichez le premier enregistrement de la table logement

SQL / BDD Immobilier / Requete n°3
SELECT * FROM logement LIMIT 0,1;

résultat 3 :

idLogementgenrevilleprixsuperficiecategorie
5067appartementparis68500061vente



Question 4 : Affichez le nombre de logements (Alias : Nombre_de_logements)

SQL / BDD Immobilier / Requete n°4
SELECT COUNT(*) AS 'nombre de logements' FROM logement;

résultat 4 :

nombre de logements
28



Question 5 : Affichez les logements à vendre à moins de 150 000 € dans l’ordre croissant des prix:

SQL / BDD Immobilier / Requete n°5
SELECT * FROM logement WHERE prix < 150000 AND categorie = 'vente' ORDER BY prix;

résultat 5 :

idLogementgenrevilleprixsuperficiecategorie
5860appartementbordeaux9800018vente
5249appartementlyon11000016vente
5089appartementparis11500015vente
5378appartementbordeaux12190026vente



Question 6 : Affichez le nombre de logements à la location (alias : nombre)

SQL / BDD Immobilier / Requete n°6
SELECT COUNT(idLogement) as 'nombre' FROM logement WHERE categorie = 'location';

résultat 6 :

nombre
8



Question 7 : Affichez les villes différentes recherchées par les personnes demandeuses d'un logement

SQL / BDD Immobilier / Requete n°7
SELECT DISTINCT ville FROM demande;

résultat 7 :

ville
paris
bordeaux
lyon



Question 8 : Affichez le nombre de biens à vendre par ville

SQL / BDD Immobilier / Requete n°8
SELECT ville, COUNT(ville) as 'nombre' FROM demande WHERE categorie='vente' GROUP BY ville;

résultat 8 :

villenombre
bordeaux4
lyon5
paris11



Question 9 : Quelles sont les id des logements destinés à la location ?

SQL / BDD Immobilier / Requete n°9
SELECT idLogement FROM logement WHERE categorie = 'location';

résultat 9 :

idLogement
5122
5189
5246
5324
5412
5786
5898
5961



Question 10 : Quels sont les id des logements entre 20 et 30m² ?

SQL / BDD Immobilier / Requete n°10
SELECT idLogement FROM logement WHERE superficie BETWEEN 20 AND 30;

résultat 10 :

idLogement
5336
5378
5786



Question 11 : Quel est le prix vendeur (hors commission) du logement le moins cher à vendre ? (Alias : prix minimum)

SQL / BDD Immobilier / Requete n°11
SELECT MIN(prix) AS 'prix minimum' FROM logement WHERE categorie = 'vente';

résultat 11 :

prix minimum
98000



Question 12 : Dans quelle ville se trouve les maisons à vendre ?

SQL / BDD Immobilier / Requete n°12
SELECT genre, ville FROM logement WHERE genre = 'maison';

résultat 12 :

genreville
maisonparis
maisonbordeaux



Question 13 : L’agence Orpi souhaite diminuer les frais qu’elle applique sur le logement ayant l'id « 5246 ». Passer les frais de ce logement de 800 à 730€

SQL / BDD Immobilier / Requete n°13
UPDATE logement_agence SET frais='730' WHERE idLogement = 5246 and idAgence = (SELECT idAgence FROM agence WHERE nom='orpi');

résultat 13 :

Query OK, 1 row affected


Question 14 : Quels sont les logements gérés par l’agence « laforet »

SQL / BDD Immobilier / Requete n°14
SELECT idLogement FROM logement_agence WHERE idAgence = (SELECT idAgence FROM agence WHERE nom = 'laforet');

résultat 14 :

idLogement
5378
5723
5961



Question 15 : Affichez le nombre de propriétaires dans la ville de Paris (Alias : Nombre)

SQL / BDD Immobilier / Requete n°15
SELECT COUNT(DISTINCT(lp.idPersonne)) AS 'nombre' FROM logement_personne lp, logement l WHERE lp.idLogement = l.idLogement AND l.ville = 'paris';

résultat 15 :

nombre
13



Question 16 : Affichez les informations des trois premieres personnes souhaitant acheter un logement

SQL / BDD Immobilier / Requete n°16
SELECT p.*, d.* FROM personne p, demande d WHERE p.idPersonne = d.idPersonne AND d.categorie = 'vente' LIMIT 0,3;

résultat 16 :

idPersonneprenomidDemandeidPersonnegenrevillebudgetsuperficiecategorie
1william11appartementparis530000120vente
3mehdi23appartementbordeaux12000018vente
4charles34appartementbordeaux14500021vente



Question 17 : Affichez le prénom du vendeur pour le logement ayant la référence « 5770 »

SQL / BDD Immobilier / Requete n°17
SELECT p.prenom FROM personne p, logement_personne lp WHERE p.idPersonne = lp.idPersonne and lp.idLogement = 5770;

résultat 17 :

prenom
robin



Question 18 : Affichez les prénoms des personnes souhaitant accéder à un logement sur la ville de Lyon

SQL / BDD Immobilier / Requete n°18
SELECT p.prenom FROM personne p, demande d where p.idPersonne = d.idPersonne AND d.ville = 'lyon';

résultat 18 :

prenom
sarah
yvon
camille
anna
sabrina
franck
axelle
morgane



Question 19 : Affichez les prénoms des personnes souhaitant accéder à un logement en location sur la ville de Paris

SQL / BDD Immobilier / Requete n°19
SELECT p.prenom FROM personne p, demande d where p.idPersonne = d.idPersonne AND d.ville = 'paris' AND d.categorie = 'location'; 

résultat 19 :

prenom
julie
aurore
marie
melissa
kevin
victoria



Question 20 : Affichez les prénoms des personnes souhaitant acheter un logement de la plus grande à la plus petite superficie

SQL / BDD Immobilier / Requete n°20
SELECT p.prenom, d.superficie FROM personne p, demande d WHERE p.idPersonne = d.idPersonne AND d.categorie='vente' ORDER BY d.superficie DESC;

résultat 20 :

prenomsuperficie
william120
leo100
simon80
sabrina70
camille65
jonathan60
lucas55
sarah55
hugo40
enzo40
ophelie40
patrick40
brigitte26
valentine25
charles21
anna21
mehdi18
samuel15
celia15
axelle12



Question 21 : Quel sont les prix finaux proposés par les agences pour la maison à la vente ayant la référence « 5091 » ? (Alias : prix frais d'agence inclus)

SQL / BDD Immobilier / Requete n°21
SELECT (l.prix+la.frais) AS 'prix frais d\'agence inclu' FROM logement l, logement_agence la WHERE l.idLogement = la.idLogement and l.idLogement = 5091; 

résultat 21 :

prix frais d'agence inclus
1585500
1566050



Question 22 : Indiquez les frais ajoutés par l’agence immobilière pour le logement ayant la référence « 5873 » ?

SQL / BDD Immobilier / Requete n°22
SELECT l.idLogement, l.prix, la.frais, (l.prix+la.frais) as 'prix total' FROM logement l, logement_agence la WHERE la.idLogement = 5873 AND la.idLogement=l.idLogement;

résultat 22 :

idLogementprixfraisprix total
587367670033835710535



Question 23 : Si l’ensemble des logements étaient vendus ou loués demain, quel serait le bénéfice généré grâce aux frais d’agence et pour chaque agence (Alias : benefice, classement : par ordre croissant des gains)

SQL / BDD Immobilier / Requete n°23
SELECT a.nom, SUM(la.frais) AS benefice FROM agence a, logement_agence la WHERE a.idAgence = la.idAgence GROUP BY la.idAgence ORDER BY benefice; 

résultat 23 :

nombenefice
laforet28606
seloger44342
bouygues immobilier49468
century2160655
guy-hoquet66592
orpi96337
logic-immo142953
fnaim156871
foncia170504



Question 24 : Affichez les id des biens en location, les prix, suivis des frais d’agence (classement : dans l’ordre croissant des prix) :

SQL / BDD Immobilier / Requete n°24
SELECT a.nom, l.idLogement, la.frais FROM logement_agence la, logement l, agence a WHERE la.idLogement = l.idLogement AND l.categorie='location' AND la.idAgence=a.idAgence ORDER BY l.prix;

résultat 24 :

nomidLogementfrais
orpi5189350
seloger5122700
foncia5786898
century215786520
orpi5246800
fnaim5324600
logic-immo5412680
century215898250
bouygues immobilier58981300
logic-immo5898900
logic-immo59611240
laforet5961300
bouygues immobilier5961890



Question 25 : Quel est le prénom du propriétaire proposant le logement le moins cher à louer ?

SQL / BDD Immobilier / Requete n°25
SELECT prenom FROM personne WHERE idPersonne IN (SELECT idPersonne FROM logement_personne WHERE idLogement IN (SELECT idLogement FROM logement where categorie='location' AND prix = (SELECT MIN(prix) FROM logement WHERE categorie='location')));  

résultat 25 :

prenom
johan



Question 26 : Affichez le prénom et la ville où se trouve le logement de chaque propriétaire

SQL / BDD Immobilier / Requete n°26
SELECT p.prenom, l.ville FROM personne p, logement l, logement_personne lp WHERE p.idPersonne = lp.idPersonne AND l.idLogement = lp.idLogement;

résultat 26 :

prenomville
priscilliaparis
assiaparis
nathanparis
gaetanbordeaux
johanlyon
lucasparis
quentinparis
emmanuellyon
noemiebordeaux
clementparis
mathieulyon
nathaliebordeaux
florianbordeaux
antoineparis
chloeparis
adelebordeaux
charlottebordeaux
robinparis
alexandrebordeaux
alexisparis
agatheparis
elodiebordeaux
lealyon
tomlyon
carolineparis
alicebordeaux
lolaparis
alexisparis



Question 27 : Quel est l’agence immobilière s’occupant de la plus grande gestion de logements répertoriés à Paris ? (alias : nombre, classement : trié par ordre décroissant)

SQL / BDD Immobilier / Requete n°27
SELECT a.nom, COUNT(l.ville) as 'nombre' FROM agence a, logement_agence la, logement l WHERE la.idAgence = a.idAgence and la.idLogement=l.idLogement and l.ville = 'paris' GROUP BY a.nom ORDER BY nombre DESC; 

résultat 27 :

nomnombre
logic-immo6
foncia4
fnaim4
century214
bouygues immobilier4
orpi3
guy-hoquet1



Question 28 : Affichez le prix et le prénom des vendeurs dont les logements sont proposés à 130000 € ou moins en prix final avec frais appliqués par les agences (alias : prix final, classement : ordre croissant des prix finaux) :

SQL / BDD Immobilier / Requete n°28
	SELECT p.prenom, (l.prix+la.frais) as 'prix final'
	FROM personne p, logement l, logement_agence la, logement_personne lp
	WHERE (l.prix+la.frais) <= 130000
	AND p.idPersonne=lp.idPersonne
	AND l.idLogement=la.idLogement
	AND l.categorie='vente'
	AND lp.idLogement=l.idLogement
	AND lp.idLogement=la.idLogement
	ORDER BY (l.prix+la.frais);

résultat 28 :

prenomprix final
elodie102900
elodie106905
emmanuel115500
emmanuel117625
assia120750
assia122623
florian127995



Question 29 : Affichez le nombre de logements à la vente dans la ville de recherche de « hugo » (alias : nombre)

SQL / BDD Immobilier / Requete n°29
SELECT COUNT(l.idLogement) as 'nombre'
FROM personne p, demande d, logement l
WHERE p.idPersonne = d.idPersonne 
AND d.ville = l.ville 
AND p.prenom='hugo' 
AND l.categorie='vente'; 

résultat 29 :

nombre
10



Question 30 : Affichez le nombre de logements à la vente dans la ville de recherche de « hugo » et dans la superficie minimum qu’il attend ou dans une superficie supérieure (alias : nombre):

SQL / BDD Immobilier / Requete n°30
SELECT COUNT(l.idLogement) as 'nombre'
FROM personne p, demande d, logement l
WHERE p.idPersonne = d.idPersonne 
AND d.ville = l.ville 
AND d.superficie <= l.superficie 
AND p.prenom='hugo' 
AND l.categorie='vente'; 

résultat 30 :

nombre
6



Question 31 : Affichez le nombre d’opportunités d’achats dans la ville de recherche de « hugo » dans la superficie minimum qu’il attend ou dans une superficie supérieure et en prenant en compte tous ses autres critères de sélection (alias : nombre):

SQL / BDD Immobilier / Requete n°31
SELECT COUNT(l.idLogement) as 'nombre'
FROM personne p, demande d, logement l, logement_agence la, agence a
WHERE d.genre = l.genre 
AND d.ville = l.ville 
AND d.budget >= (l.prix+la.frais) 
AND d.superficie <= l.superficie
AND la.idLogement = l.idLogement 
AND p.idPersonne = d.idPersonne 
AND l.categorie='vente' 
AND a.idAgence = la.idAgence
AND p.prenom='hugo'; 

résultat 31 :

nombre
2



Question 32 : Affichez les prénoms des personnes souhaitant accéder à un logement en location sur la ville de Paris

SQL / BDD Immobilier / Requete n°32
SELECT p.prenom, d.genre AS 'genre recherche', d.ville AS 'ville recherche', d.budget AS 'budget max', d.superficie AS 'superficie min', d.categorie AS 'categorie recherche', l.idLogement, a.nom AS 'agence', l.genre AS 'genre propose', l.ville AS 'ville propose', (l.prix+la.frais) AS 'prix final', l.superficie AS 'superficie propose', l.categorie AS 'categorie propose'
    FROM demande d, logement l, logement_agence la, personne p, agence a 
    WHERE d.genre = l.genre 
    AND d.ville = l.ville 
    AND d.budget >= (l.prix+la.frais) 
    AND d.superficie <= l.superficie 
    AND la.idLogement = l.idLogement 
    AND p.idPersonne = d.idPersonne 
    AND l.categorie='vente'
    AND a.idAgence = la.idAgence 
    AND p.prenom='hugo'; 

résultat 32 :

prenomgenre rechercheville recherchebudget maxsuperficie mincategorie rechercheidLogementagencegenre proposeville proposeprix finalsuperficie proposecategorie propose
hugoappartementparis49500040vente5245logic-immoappartementparis37885640vente
hugoappartementparis49500040vente5245fnaimappartementparis37423040vente



Question 33 : En prenant en compte le « fichier client » avec leurs critères de sélection répertoriés sur la table « demande », quelle est l’agence immobilière susceptible de faire le plus de ventes ? (alias : nombre)

SQL / BDD Immobilier / Requete n°33
	SELECT a.nom as 'agence', COUNT(a.nom) as 'nombre'
	FROM demande d, logement l, logement_agence la, personne p, agence a 
	WHERE d.genre = l.genre 
	AND d.ville = l.ville 
	AND d.budget >= (l.prix+la.frais)
	AND d.superficie <= l.superficie 
	AND la.idLogement = l.idLogement 
	AND p.idPersonne = d.idPersonne 
	AND l.categorie='vente' 
	AND a.idAgence = la.idAgence 
	GROUP BY a.nom 
	ORDER BY nombre DESC;

résultat 33 :

agencenombre
logic-immo6
bouygues immobilier4
century213
orpi2
guy-hoquet2
fnaim2
laforet2



Question 34 : Affichez les prénoms des personnes cherchant un logement ainsi que les noms des agences (s’occupant de la gestion des logements) pour une mise en relation dans le cadre d'une susceptible location immobilière (tout en affichant les informations qui permettront de mettre en évidence une première année d'éventuels contrats, voir résultat).

SQL / BDD Immobilier / Requete n°34
SELECT p.prenom, d.genre as 'genre recherche', d.ville as 'ville recherche', (d.budget*12) as 'budget premiere annee', d.superficie as 'superficie min', d.categorie as 'categorie recherche', a.nom as 'agence', l.idLogement, l.genre as 'genre propose', l.ville as 'ville propose', (l.prix*12)+la.frais as 'prix premiere annee', l.superficie as 'superficie propose', l.categorie as 'categorie propose'
FROM demande d, logement l, logement_agence la, personne p, agence a 
WHERE d.genre = l.genre 
AND d.ville = l.ville 
AND (d.budget*12) >= (l.prix*12)+la.frais 
AND d.superficie <= l.superficie 
AND la.idLogement = l.idLogement 
AND p.idPersonne = d.idPersonne 
AND l.categorie='location' 
AND d.categorie='location' 
AND a.idAgence = la.idAgence; 

résultat 34 :

prenomgenre rechercheville recherchebudget premiere anneesuperficie mincategorie rechercheagenceidLogementgenre proposeville proposeprix premiere anneesuperficie proposecategorie propose
victoriaappartementparis756020locationcentury215786appartementparis736020location



Question 35 : Affichez les prénoms des acheteurs potentiels, les prénoms des vendeurs ainsi que les agences s’occupant de la gestion de leurs logements pour une mise en relation dans le cadre d'une susceptible vente immobilière (tout en affichant les informations qui permettront de mettre en évidence cette éventuelle transaction, voir résultat).

SQL / BDD Immobilier / Requete n°35
SELECT p.prenom as 'acheteur', d.genre as 'genre recherche', d.ville as 'ville recherche', d.budget as 'budget max', d.superficie as 'superficie min', d.categorie as 'categorie recherche', a.nom as 'agence', p2.prenom as 'vendeur', l.genre as 'genre propose', l.ville as 'ville propose', (l.prix+la.frais) as 'prix final', l.superficie as 'superficie propose', l.categorie as 'categorie propose'
FROM demande d, logement l, logement_agence la, personne p, personne p2, agence a, logement_personne lp 
WHERE d.genre = l.genre 
AND l.idLogement = lp.idLogement
AND lp.idPersonne = p2.idPersonne 
AND d.ville = l.ville 
AND d.budget >= (l.prix+la.frais)
AND d.superficie <= l.superficie 
AND la.idLogement = l.idLogement 
AND p.idPersonne = d.idPersonne 
AND l.categorie=d.categorie 
AND a.idAgence = la.idAgence 
AND a.idAgence = la.idAgence ; 

résultat 35 :

acheteurgenre rechercheville recherchebudget maxsuperficie mincategorie rechercheagencevendeurgenre proposeville proposeprix finalsuperficie proposecategorie propose
mehdiappartementbordeaux12000018ventelogic-immoelodieappartementbordeaux10290018vente
mehdiappartementbordeaux12000018venteguy-hoquetelodieappartementbordeaux10690518vente
charlesappartementbordeaux14500021ventelaforetflorianappartementbordeaux13055226vente
charlesappartementbordeaux14500021venteorpiflorianappartementbordeaux12799526vente
brigitteappartementbordeaux17200026ventelaforetflorianappartementbordeaux13055226vente
brigitteappartementbordeaux17200026venteorpiflorianappartementbordeaux12799526vente
lucasappartementparis60000055ventelogic-immololaappartementparis54754260vente
lucasappartementparis60000055ventecentury21lolaappartementparis53845560vente
lucasappartementparis60000055ventebouygues immobilierlolaappartementparis54600060vente
samuelappartementparis16200015ventelogic-immoassiaappartementparis12075015vente
samuelappartementparis16200015ventebouygues immobilierassiaappartementparis12262315vente
celiaappartementparis14500015ventelogic-immoassiaappartementparis12075015vente
celiaappartementparis14500015ventebouygues immobilierassiaappartementparis12262315vente
enzoappartementbordeaux41300040ventecentury21alexandreappartementbordeaux24003043vente
enzoappartementbordeaux41300040venteguy-hoquetalexandreappartementbordeaux24125543vente
hugoappartementparis49500040ventelogic-immolucasappartementparis37885640vente
hugoappartementparis49500040ventefnaimlucasappartementparis37423040vente
jonathanappartementparis65000060ventelogic-immololaappartementparis54754260vente
jonathanappartementparis65000060ventecentury21lolaappartementparis53845560vente
jonathanappartementparis65000060ventebouygues immobilierlolaappartementparis54600060vente
ophelieappartementparis37750040ventefnaimlucasappartementparis37423040vente



Question 36 : Supprimer la personne n°idPersonne 13 (Valentine).

SQL / BDD Immobilier / Requete n°36
DELETE FROM personne WHERE idPersonne = 13 ; 

résultat 36 :

Query OK



Question 37 : Afficher toutes les demandes enregistrées avec la personne à l'origine de la demande (Afficher également les demandes d'anciennes personnes n'existant plus dans notre base de données).

SQL / BDD Immobilier / Requete n°37
SELECT p.prenom, d.superficie, d.genre, d.ville, d.budget, d.categorie FROM personne p LEFT JOIN demande d ON p.idPersonne = d.idPersonne ; 

résultat 37 :

prenomsuperficiegenrevillebudgetcategorie
william120appartementparis530000vente
gaetan
mehdi18appartementbordeaux120000vente
charles21appartementbordeaux145000vente
brigitte26appartementbordeaux172000vente
sarah55appartementlyon450000vente
lucas55appartementparis600000vente
quentin
patrick40appartementparis371000vente
emmanuel
elodie
agathe
valentine25appartementparis253000vente
charlotte
alice
samuel15appartementparis162000vente
mathieu
noemie
simon80appartementparis720000vente
florian
clement
yvon20appartementlyon680location
lea
chloe
camille65appartementlyon558000vente
alexandre
julie15appartementparis490location
leo100appartementparis1100000vente
antoine
lola
celia15appartementparis145000vente
anna21appartementlyon123800vente
caroline
adele
sabrina70appartementlyon690000vente
nathalie
franck100appartementlyon1500location
tom
johan
priscillia
assia
nathan
aurore20appartementparis600location
marie30appartementparis750location
oceane30appartementbordeaux680location
enzo40appartementbordeaux413000vente
ines45appartementbordeaux700location
hugo40appartementparis495000vente
jonathan60appartementparis650000vente
axelle12appartementlyon110000vente
morgane17appartementlyon500location
melissa40appartementparis800location
kevin50appartementparis850location
ophelie40appartementparis377500vente
victoria20appartementparis630location
alexis
robin



Question 38 : Afficher toutes les personnes enregistrées avec leur demandes correspondantes (Afficher également les personnes n'ayant pas formulé de demandes).

SQL / BDD Immobilier / Requete n°36
SELECT p.prenom, d.superficie, d.genre, d.ville, d.budget, d.categorie FROM demande d LEFT JOIN personne p ON p.idPersonne = d.idPersonne ; 

résultat 38 :

prenomsuperficiegenrevillebudgetcategorie
william120appartementparis530000vente
mehdi18appartementbordeaux120000vente
charles21appartementbordeaux145000vente
brigitte26appartementbordeaux172000vente
sarah55appartementlyon450000vente
lucas55appartementparis600000vente
patrick40appartementparis371000vente
25appartementparis253000vente
samuel15appartementparis162000vente
simon80appartementparis720000vente
yvon20appartementlyon680location
camille65appartementlyon558000vente
julie15appartementparis490location
leo100appartementparis1100000vente
celia15appartementparis145000vente
anna21appartementlyon123800vente
sabrina70appartementlyon690000vente
franck100appartementlyon1500location
aurore20appartementparis600location
marie30appartementparis750location
oceane30appartementbordeaux680location
enzo40appartementbordeaux413000vente
ines45appartementbordeaux700location
hugo40appartementparis495000vente
jonathan60appartementparis650000vente
axelle12appartementlyon110000vente
morgane17appartementlyon500location
melissa40appartementparis800location
kevin50appartementparis850location
ophelie40appartementparis377500vente
victoria20appartementparis630location



Question 39 : Affichez toutes les personnes enregistrées avec leur demandes correspondantes (Afficher également les personnes n'ayant pas formulé de demandes ainsi que les demandes d'anciennes personnes n'existant plus dans notre base de données).

SQL / BDD Immobilier / Requete n°39
SELECT p.prenom, d.superficie, d.genre, d.ville, d.budget, d.categorie FROM demande d LEFT JOIN personne p ON p.idPersonne = d.idPersonne 
UNION DISTINCT
SELECT p.prenom, d.superficie, d.genre, d.ville, d.budget, d.categorie FROM personne p LEFT JOIN demande d ON p.idPersonne = d.idPersonne ; 

résultat 39 :

prenomsuperficiegenrevillebudgetcategorie
william120appartementparis530000vente
mehdi18appartementbordeaux120000vente
charles21appartementbordeaux145000vente
brigitte26appartementbordeaux172000vente
sarah55appartementlyon450000vente
lucas55appartementparis600000vente
patrick40appartementparis371000vente
25appartementparis253000vente
samuel15appartementparis162000vente
simon80appartementparis720000vente
yvon20appartementlyon680location
camille65appartementlyon558000vente
julie15appartementparis490location
leo100appartementparis1100000vente
celia15appartementparis145000vente
anna21appartementlyon123800vente
sabrina70appartementlyon690000vente
franck100appartementlyon1500location
aurore20appartementparis600location
marie30appartementparis750location
oceane30appartementbordeaux680location
enzo40appartementbordeaux413000vente
ines45appartementbordeaux700location
hugo40appartementparis495000vente
jonathan60appartementparis650000vente
axelle12appartementlyon110000vente
morgane17appartementlyon500location
melissa40appartementparis800location
kevin50appartementparis850location
ophelie40appartementparis377500vente
victoria20appartementparis630location
gaetan
quentin
emmanuel
elodie
agathe
charlotte
alice
mathieu
noemie
florian
clement
lea
chloe
alexandre
antoine
lola
caroline
adele
nathalie
tom
johan
priscillia
assia
nathan
alexis
robin



Question 40 : Afficher la liste des personnes ayant plusieurs logements à vendre.

SQL / BDD Immobilier / Requete n°40
SELECT p.prenom
FROM personne p, logement l, logement_personne lp
WHERE p.idPersonne = lp.idPersonne
AND lp.idLogement = l.idLogement
GROUP BY lp.idPersonne
HAVING   COUNT(lp.idPersonne) > 1 ; 

résultat 40 :

prenom
alexis



Question 41 : Afficher la liste des personnes avec le logement qu'elles vendent, ou la demande de logement qu'elles recherchent.

SQL / BDD Immobilier / Requete n°41
	SELECT p.idPersonne, p.prenom, d.genre, d.ville, d.budget, d.superficie, d.categorie, l.genre, l.ville, l.prix, l.superficie, l.categorie
	FROM personne p LEFT JOIN logement_personne lp ON p.idPersonne = lp.idPersonne
	LEFT JOIN demande d ON p.idPersonne = d.idPersonne 
	LEFT JOIN logement l ON l.idLogement = lp.idLogement ;

résultat 41 :

idPersonneprenomgenre demandéville demandébudget demandésuperficie demandécategorie demandégenre proposéville proposéprix proposésuperficie proposécategorie proposé
1williamappartementparis530000120vente
2gaetanappartementbordeaux55017location
3mehdiappartementbordeaux12000018vente
4charlesappartementbordeaux14500021vente
5brigitteappartementbordeaux17200026vente
6sarahappartementlyon45000055vente
7lucasappartementparis60000055venteappartementparis36000040vente
8quentinappartementparis97035location
9patrickappartementparis37100040vente
10emmanuelappartementlyon11000016vente
11elodieappartementbordeaux9800018vente
12agatheappartementparis57020location
13valentineappartementparis25300025vente
14charlottemaisonbordeaux37060045vente
15aliceappartementbordeaux265045location
16samuelappartementparis16200015vente
17mathieuappartementlyon109031location
18noemieappartementbordeaux17150033vente
19simonappartementparis72000080vente
20florianappartementbordeaux12190026vente
21clementappartementparis80200090vente
22yvonappartementlyon68020location
23leaappartementlyon68360060vente
24chloeappartementparis37000037vente
25camilleappartementlyon55800065vente
26alexandreappartementbordeaux22860043vente
27julieappartementparis49015location
28leoappartementparis1100000100vente
29antoineappartementparis168040location
30lolaappartementparis52000060vente
31celiaappartementparis14500015vente
32annaappartementlyon12380021vente
33carolineappartementparis189040location
34adeleappartementbordeaux24860036vente
35sabrinaappartementlyon69000070vente
36nathalieappartementbordeaux22960027vente
37franckappartementlyon1500100location
38tomappartementlyon67670065vente
39johanappartementlyon42014location
40priscilliaappartementparis68500061vente
41assiaappartementparis11500015vente
42nathanmaisonparis1510000130vente
43auroreappartementparis60020location
44marieappartementparis75030location
45oceaneappartementbordeaux68030location
46enzoappartementbordeaux41300040vente
47inesappartementbordeaux70045location
48hugoappartementparis49500040vente
49jonathanappartementparis65000060vente
50axelleappartementlyon11000012vente
51morganeappartementlyon50017location
52melissaappartementparis80040location
53kevinappartementparis85050location
54ophelieappartementparis37750040vente
55victoriaappartementparis63020location
56alexisappartementparis1310000105vente
56alexisappartementparis28000038vente
57robinappartementparis33900038vente



Question 42 : Afficher la liste des personnes ayant à la fois un logement à vendre ou louer et une demande d'achat ou de location.

SQL / BDD Immobilier / Requete n°42
SELECT p.prenom
FROM personne p, logement l, logement_personne lp, demande d
WHERE p.idPersonne = lp.idPersonne
AND lp.idLogement = l.idLogement
AND d.idPersonne = p.idPersonne
GROUP BY lp.idPersonne
HAVING   COUNT(lp.idPersonne) > 0 AND  COUNT(d.idPersonne) > 0;

résultat 42 :

prenom
lucas