Discussion :

[mariniouf]

Bonjour,

Mon professeur de SQL nous a demandé de faire un exercice que je n'arrive pas à résoudre :
On travaille sur une base de donnée modélisant une entreprise de transport. On dispose entre autres des tables suivantes :
- Etapes(NumeroTrajet, NomVille, DateArrivé, DateDépart) symbolisant une étape d'un trajet dans lequel le bus s'y arrête de la DateArrivé à la DateDépart
- Reservations(NumeroReservation, NuméroClient, NuméroTrajet, VilleDépart, VilleArrivée, NombrePlaces) qui symbolise le nombre de places réservées par un client voulant aller d'une ville de départ à une ville d'arrivée

On nous demande d'abord d'écrire une vue ReservationsAvecHoraires qui retourne la description de chaque réservation augmentée des dates/horaires de départ et d'arrivée. Je suis parvenu à la faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE VIEW ReservationsAvecHoraires
AS SELECT R.NumR, R.NumC, R.NumT, R.NbPlaces, R.VilleD, R.VilleA, TO_CHAR(E1.DateD, 'DD/MM/YYYY HH24:Mi') AS DateD, TO_CHAR(E2.DateA, 'DD/MM/YYYY HH24:Mi') AS DateA
    FROM Reservations R, Etapes E1, Etapes E2
    WHERE R.NumT = E1.NumT AND R.NumT = E2.NumT AND R.VilleD = E1.NomV AND R.VilleA = E2.NomV;

Le problème : maintenant on nous demande d'écrire la vue NbPlacesRéservéesEtapes qui doit retourner à chaque étapes d'un trajet, le nombre de places qui sont réservées à ce moment là (il faut prendre en compte dans la somme les places réservées aux étapes précédantes et qui ne se sont pas encore libérées au moment de cette étape)
Par exemple : si un trajet comprend 5 villes, qu'un premier client réserve des places de Ville1 à Ville3, et un autre de Ville2 à Ville4, il faut sommer le nombre de places de la réservation 1 et 2 au niveau de la Ville2 (car le premier client n'est pas encore descendu).

Je vous remercie d'avance si vous pouvais m'éclairer !

[tbc92]

Tu es sûr que l'exercice est formulé comme ça ?
- Soit effectivement, il y a cette notion de ville étape, et dans ce cas, il manque une table : tel trajet est est constitué de telle(s) et telle(s) étape(s), dans quel ordre.
- Soit un trajet est constitué d'une ville de départ, et d'une ville d'arrivée, sans notion d'étape. Et dans ce cas, tu peux répondre à la question par une vue sur les tables existantes.

[JeitEmgie]

Quick and dirty:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
create table t_etapes (
    nsq number(19,0) primary key,
    num_trajet number(19,0) NOT NULL,
    nom_ville varchar2(256 char) NOT NULL,
    date_arrivee date NOT NULL,
    date_depart date NOT NULL 
    ) ;
 
create table t_clients (
    nsq number(19,0) primary key,
    nom_client varchar2(256 char) NOT NULL
);
 
 
create table t_reservations (
    nsq number(19,0) primary key,
    num_client number(19,0) ,
    num_trajet number(19,0) NOT NULL,
    ville_depart varchar2(256 char) NOT NULL,
    ville_arrivee varchar2(256 char) NOT NULL,
    nombre_places number(19,0) constraint ck_nombre_places check(nombre_places > 0)
);
 
 
alter table t_reservations add constraint fk_num_client foreign key (num_client) references t_clients;
 
 
create or replace view v_trajets as 
select num_trajet, nom_ville, date_arrivee, date_depart,
    row_number() over(partition by num_trajet order by date_arrivee) as num_etape
    from t_etapes
    ;
 
 
create or replace view v_reservations as 
select r.nsq , r.num_client, r.num_trajet, r.ville_depart, t1.num_etape as num_etape_depart, r.ville_arrivee, t2.num_etape as num_etape_arrivee, t.num_etape, r.nombre_places, c.nom_client
from t_reservations r
    join v_trajets t on t.num_trajet = r.num_trajet
    join v_trajets t1 on t1.num_trajet = r.num_trajet and r.ville_depart = t1.nom_ville
    join v_trajets t2 on t2.num_trajet = r.num_trajet and r.ville_arrivee = t2.nom_ville
    join t_clients c on r.num_client = c.nsq
where t1.num_etape <= t.num_etape and t.num_etape <= t2.num_etape
;
 
 
insert into t_clients(nsq,nom_client) values (1,'Dupont') ;
insert into t_clients(nsq,nom_client) values (2,'Durand') ;
insert into t_clients(nsq,nom_client) values (3,'Martin') ;
insert into t_clients(nsq,nom_client) values (4,'Dumoulin') ;
 
 
insert into t_etapes(nsq,  num_trajet, nom_ville, date_arrivee, date_depart) values (1, 1, 'City 1', to_date('01-01-2018 08:00', 'dd-mm-yyyy hh24:mi'), to_date('01-01-2018 08:10', 'dd-mm-yyyy hh24:mi')) ;
insert into t_etapes(nsq,  num_trajet, nom_ville, date_arrivee, date_depart) values (2, 1, 'City 2', to_date('01-01-2018 09:00', 'dd-mm-yyyy hh24:mi'), to_date('01-01-2018 09:10', 'dd-mm-yyyy hh24:mi')) ;
insert into t_etapes(nsq,  num_trajet, nom_ville, date_arrivee, date_depart) values (3, 1, 'City 3', to_date('01-01-2018 10:00', 'dd-mm-yyyy hh24:mi'), to_date('01-01-2018 10:10', 'dd-mm-yyyy hh24:mi')) ;
insert into t_etapes(nsq,  num_trajet, nom_ville, date_arrivee, date_depart) values (4, 1, 'City 4', to_date('01-01-2018 11:00', 'dd-mm-yyyy hh24:mi'), to_date('01-01-2018 11:10', 'dd-mm-yyyy hh24:mi')) ;
insert into t_etapes(nsq,  num_trajet, nom_ville, date_arrivee, date_depart) values (5, 1, 'City 5', to_date('01-01-2018 12:00', 'dd-mm-yyyy hh24:mi'), to_date('01-01-2018 12:10', 'dd-mm-yyyy hh24:mi')) ;
 
 
insert into t_etapes(nsq,  num_trajet, nom_ville, date_arrivee, date_depart) values (6, 2, 'City 1', to_date('01-01-2018 08:30', 'dd-mm-yyyy hh24:mi'), to_date('01-01-2018 08:40', 'dd-mm-yyyy hh24:mi')) ;
insert into t_etapes(nsq,  num_trajet, nom_ville, date_arrivee, date_depart) values (7, 2, 'City 6', to_date('01-01-2018 09:30', 'dd-mm-yyyy hh24:mi'), to_date('01-01-2018 09:40', 'dd-mm-yyyy hh24:mi')) ;
insert into t_etapes(nsq,  num_trajet, nom_ville, date_arrivee, date_depart) values (8, 2, 'City 7', to_date('01-01-2018 10:30', 'dd-mm-yyyy hh24:mi'), to_date('01-01-2018 10:40', 'dd-mm-yyyy hh24:mi')) ;
insert into t_etapes(nsq,  num_trajet, nom_ville, date_arrivee, date_depart) values (9, 2, 'City 8', to_date('01-01-2018 11:30', 'dd-mm-yyyy hh24:mi'), to_date('01-01-2018 11:40', 'dd-mm-yyyy hh24:mi')) ;
insert into t_etapes(nsq,  num_trajet, nom_ville, date_arrivee, date_depart) values (10, 2, 'City 9', to_date('01-01-2018 12:30', 'dd-mm-yyyy hh24:mi'), to_date('01-01-2018 12:40', 'dd-mm-yyyy hh24:mi')) ;
 
 
insert into t_reservations(nsq, num_client, num_trajet, ville_depart, ville_arrivee, nombre_places) values (1, 1, 1, 'City 1', 'City 3', 2) ;
insert into t_reservations(nsq, num_client, num_trajet, ville_depart, ville_arrivee, nombre_places) values (2, 2, 1, 'City 2', 'City 3', 1) ;
insert into t_reservations(nsq, num_client, num_trajet, ville_depart, ville_arrivee, nombre_places) values (3, 3, 1, 'City 1', 'City 5', 4) ;
insert into t_reservations(nsq, num_client, num_trajet, ville_depart, ville_arrivee, nombre_places) values (4, 4, 1, 'City 3', 'City 5', 3) ;
 
 
select * from v_trajets ;
select * from v_reservations ;
 
create or replace view v_nreservationetapes as
select t.num_trajet, t.nom_ville, t.num_etape,
    sum(r.nombre_places) as n_reservations
    from v_trajets t
    join v_reservations r on r.num_trajet = t.num_trajet and r.num_etape = t.num_etape
    group by t.num_trajet, t.nom_ville, t.num_etape
    order by t.num_trajet, t.num_etape
    ;

NUM_TRAJET	NOM_VILLE	NUM_ETAPE	N_RESERVATIONS
1	City 1	1	6
1	City 2	2	7
1	City 3	3	10
1	City 4	4	7
1	City 5	5	7