yFMmK5df

1-----------------------------------------------------
2-- Table  Client 
3-- -----------------------------------------------------
4CREATE TABLE IF NOT EXISTS Client (
5   id  SERIAL NOT NULL,
6   region  TEXT NOT NULL,
7   nom  TEXT NOT NULL,
8  PRIMARY KEY ( id ));
9
10-- -----------------------------------------------------
11-- Table  Activite 
12-- -----------------------------------------------------
13CREATE TABLE IF NOT EXISTS  Activite  (
14   Libelle  VARCHAR(45) NOT NULL,
15  PRIMARY KEY ( Libelle ));
16
17-- -----------------------------------------------------
18-- Table  Station 
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS  Station  (
21   idStation  SERIAL NOT NULL,
22   nomStation  TEXT NOT NULL,
23   region  TEXT NOT NULL,
24   capacite  INT NOT NULL,
25   tarif  DECIMAL(8,0) NOT NULL,
26  CHECK ( tarif >=0),
27  CHECK ( capacite >=0),
28  PRIMARY KEY ( idStation ));
29
30-- -----------------------------------------------------
31-- Table  Sejour 
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS Sejour (
34   idSejour SERIAL NOT NULL,
35   debut  DATE NOT NULL,
36   nbPlaces  INT NOT NULL,
37   Client_id  INT NOT NULL,
38   Station_idStation  INT NOT NULL,
39  CHECK ( nbPlaces >=0),
40  PRIMARY KEY ( idSejour ,  Client_id ,  Station_idStation ),
41  CONSTRAINT  fk_Sejour_Client 
42    FOREIGN KEY ( Client_id )
43    REFERENCES  Client  ( id )
44    ON DELETE NO ACTION
45    ON UPDATE NO ACTION,
46  CONSTRAINT  fk_Sejour_Station1 
47    FOREIGN KEY ( Station_idStation )
48    REFERENCES  Station  ( idStation )
49    ON DELETE NO ACTION
50    ON UPDATE NO ACTION);
51
52-- -----------------------------------------------------
53-- Table  Propose 
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS  Propose  (
56   prix  DECIMAL(8,0) NOT NULL,
57   Activite_Libelle  VARCHAR(45) NOT NULL,
58   Station_idStation  INT NOT NULL,
59  CHECK ( prix >=0),
60  PRIMARY KEY ( prix ,  Activite_Libelle ,  Station_idStation ),
61  CONSTRAINT  fk_Propose_Activite1 
62    FOREIGN KEY ( Activite_Libelle )
63    REFERENCES  Activite  ( Libelle )
64    ON DELETE NO ACTION
65    ON UPDATE NO ACTION,
66  CONSTRAINT  fk_Propose_Station1 
67    FOREIGN KEY ( Station_idStation )
68    REFERENCES  Station  ( idStation )
69    ON DELETE NO ACTION
70    ON UPDATE NO ACTION);
71
72ALTER TABLE sejour ADD CONSTRAINT UNI_OPE_SESSION UNIQUE (client_id,debut);
73
74INSERT INTO Client VALUES ( 111 , 'Royaume Champignon' , 'Mario' );
75INSERT INTO Client VALUES ( 222 , 'Royaume Champignon' , 'Peach' );
76INSERT INTO Client VALUES ( 333 , 'Ile des Yoshi' , 'Yoshi' );
77INSERT INTO Station VALUES ( 444 , 'Yunnanville' , 'Port Lacanaie' , 20 , 1200 );
78INSERT INTO Station VALUES ( 555 , 'Gelato les Flots' , 'Ile Delphino' , 100 , 2270 );
79INSERT INTO Station VALUES ( 666 , 'Chateau de Bowser' , 'Royaume Champignon' , 5 , 15750 );
80INSERT INTO Sejour VALUES ( 123 , '1998-08-03' , 4 , 111 , 444 );
81INSERT INTO Sejour VALUES ( 456 , '2004-07-22' , 2 , 111 , 444 );
82INSERT INTO Sejour VALUES ( 789 , '2007-09-03' , 2 , 222 , 555 );
83INSERT INTO Activite VALUES ( 'Voile' );
84INSERT INTO Activite VALUES ( 'Plongee' );
85INSERT INTO Activite VALUES ( 'Spectacle' );
86INSERT INTO Activite VALUES ( 'Sauna' );
87INSERT INTO Propose VALUES ( 150 , 'Voile' , 555 ),( 120 , 'Plongee' , 555 ),( 50 , 'Spectacle' , 444 ),( 20 , 'Sauna' , 444 ),( 0 , 'Sauna' , 666 );
88
89
90--1
91SELECT nomStation FROM Station;
92
93--2
94SELECT nom FROM client WHERE region='Royaume Champignon';
95
96--3
97SELECT nomStation FROM Station WHERE region='Port Lacanaie' OR region='Ile des Yoshis';
98
99--4
100SELECT libelle FROM activite JOIN propose ON libelle = activite_libelle WHERE station_idStation = (SELECT idStation FROM station WHERE nomStation ='Gelato les Flots');
101
102--5
103SELECT debut, nomStation FROM Sejour JOIN Station ON idStation = Station_idstation WHERE client_id = (SELECT id FROM client WHERE nom = 'Mario');
104
105--6
106SELECT debut, nom FROM Sejour JOIN Client ON id = client_id WHERE station_idStation = (SELECT idStation FROM station WHERE nomStation = 'Yunnanville');
107
108--7
109SELECT DISTINCT nom FROM client JOIN sejour ON id = client_id WHERE station_idStation = (SELECT idStation FROM station WHERE nomStation = 'Yunnanville');
110
111--8
112SELECT nomStation FROM station WHERE region !='Royaume Champignon';
113
114--9
115SELECT nomStation FROM station ORDER BY (SELECT count(activite_libelle) FROM Propose JOIN Station ON station_idStation = idStation);
116
117--10
118SELECT nom, sum(tarif*nbPlaces) FROM station JOIN sejour ON idStation = station_idstation JOIN client ON id = client_id GROUP BY id;
119
120--11
121SELECT sum(tarif*nbPlaces) FROM sejour JOIN station ON idStation = station_idstation;
122
123--12
124SELECT nomStation, sum(tarif*nbPlaces) FROM station JOIN sejour ON idStation = station_idstation GROUP BY nomStation;