Sur le serveur bd_2023 hébergé par etu-pgsql, dans votre schéma personnel, créer les vues correspondant aux cinq questions suivantes.

Question 1

Schéma nycflights13

Créer dans votre schéma personnel une vue nommée cc2_vue_1 de schéma (n integer) qui répond à la question : combien de vols sont arrivés avec un retard d’au moins une heure en partant à l’heure de l’aéroport JFK ?

solution 
CREATE OR REPLACE MATERIALZED VIEW foobar42.v_1 AS (
WITH A as (
    SELECT * FROM nycflights13.flights AS f
    WHERE f.origin = 'JFK'
    AND f.arr_delay >= 60
    AND f.dep_delay <= 0
) SELECT COUNT(*)::integer AS n
FROM A 
);

Question 2

Schéma nycflights13

Créer dans votre schéma personnel une vue nommée cc2_vue_2 de schéma (n integer) qui répond à la question : Combien de modèles différents ont été faits par la compagnie BOEING ?

solution 
CREATE OR REPLACE VIEW cc2_vue_2 AS (
SELECT COUNT(distinct model)::integer  AS N
FROM nycflights13.planes
GROUP BY manufacturer
HAVING manufacturer = 'BOEING'
);

Question 3

Schéma babynames

Créer dans votre schéma personnel une vue nommée cc2_vue_3 de schéma (prenom TEXT) qui répond à la question :

Quels sont les prénoms donnés durant toutes les décennies pour lesquelles la base de données comporte des informations, sans doublon, par ordre alphabétique.

844 lignes attendues (dont _PRENOMS_RARES)

solution 
WITH decennie AS (
    SELECT COUNT(DISTINCT annee/10) AS nb F
    ROM bebes)
SELECT DISTINCT prenom 
FROM bebes 
GROUP BY prenom 
HAVING COUNT(DISTINCT annee/10)=(SELECT nb FROM decennie) 
ORDER BY prenom ;

Question 4

Schéma ou babynames

Créer dans votre schéma personnel une vue nommée cc2_vue_4 de schéma (prenom, n_feminin, n_masculin) qui contient les prénoms mixtes, c’est-à-dire les prénoms attribués plus de 5000 fois à des filles et à des garcons (MARIE a été attribué 26897 fois à des petits garcons). n_feminin représente le nombre total d’attributions du prénom à des filles et n_masculin représente le nombre total d’attributions du prénom à des garcons.

Votre vue contiendra (entre autres) la ligne :

+---------+---------+----------+
|prenom   |n_feminin|n_masculin|
+---------+---------+----------+
| ...     | ...     | ...      |
|MARIE    |  2232238|     26897|
| ...     | ...     | ...      |
| ...     | ...     | ...      |

Question 5

Schéma nycflights13

Créer dans votre schéma personnel une vue nommée cc2_vue_5 de schéma (name text) qui répond à la question : Quelles sont les noms des destinations accessibles par un vol direct depuis toutes les origines ?

solution 
WITH r AS (
    SELECT f.dest
    FROM nycflights13.flights f   
    GROUP BY f.dest 
    HAVING COUNT(DISTINCT f.origin) >= ALL 
      (SELECT COUNT(DISTINCT f2.origin) 
       FROM nycflights13.flights f2 )
)
SELECT a."name"::text 
FROM nycflights13.airports a JOIN r ON (a.faa = r.dest) ;
42 lignes attendues