Examen sur les bases de données

PARITE 1

-------------------------------------------------------------------

Q1.1

WITH RECURSIVE hierarchie(num_agence, num_mere, nom_agence) AS

(

SELECT num_agence, num_mere, nom_agence

FROM agence

WHERE nom_agence='Band'

UNION ALL

SELECT a.num_agence, a.num_mere, a.nom_agence

FROM hierarchie h JOIN agence a ON h.num_mere=a.num_agence

)

SELECT * FROM hierarchie;

Q1.2

WITH RECURSIVE hierarchie(num_agence, num_mere, nom_agence) AS

(

SELECT num_agence, num_mere, nom_agence

FROM agence

WHERE nom_agence='Cakewalk sud'

UNION ALL

SELECT a.num_agence, a.num_mere, a.nom_agence

FROM hierarchie h JOIN agence a ON a.num_mere=h.num_agence

)

SELECT * FROM hierarchie;

Q1.3

WITH RECURSIVE hierarchie(num_agence, num_mere, nom_agence) AS

(

SELECT num_agence , num_mere , nom_agence

FROM agence

WHERE nom_agence='Sibelius'

UNION ALL

SELECT a.num_agence, a.num_mere, a.nom_agence

FROM hierarchie h JOIN agence a ON a.num_mere=h.num_agence

WHERE a.nom_agence!='Alvisa'

)

SELECT * FROM hierarchie ORDER BY hierarchie.num_agence;

Q1.4

WITH RECURSIVE hierarchie(num_agence, num_mere, nom_agence) AS

(

SELECT num_agence, num_mere, nom_agence

FROM agence

WHERE nom_agence='Alvisa'

UNION ALL

SELECT a.num_agence, a.num_mere, a.nom_agence

FROM hierarchie h JOIN agence a ON a.num_mere=h.num_agence

)

SELECT h.num_agence, h.nom_agence, COUNT(r.num_client) AS nb_clients

FROM hierarchie h JOIN reservation r ON h.num_agence=r.depart_agence

GROUP BY h.num_agence, h.nom_agence;

Q1.5

SELECT nom_client, pre_client, type_client, pts_bonus_acquis, AVG(pts_bonus_acquis) OVER(ORDER BY pts_bonus_acquis), max(pts_bonus_acquis)

...