/* TP 22 correction : Langage SQL partie 1
   BCPST1B 2025-2026
   Lycée Hoche, Versailles
   L.-C. LEFÈVRE
*/

/* exemples */
SELECT name, iso_country FROM airports;
SELECT * FROM airports WHERE iso_country='FR';
SELECT name FROM airports WHERE type='large_airport' AND iso_region='FR-IDF';

/* exercice 1 */
SELECT name FROM countries;
SELECT name FROM countries WHERE continent='EU';
SELECT * FROM airports WHERE type='seaplane_base';
SELECT * FROM airports WHERE type='seaplane_base' AND continent='EU';
SELECT name, municipality FROM airports WHERE iso_country='FR' AND (type='large_airport' OR type='medium_airport');
SELECT * FROM airports WHERE elevation_ft < 0;

/* exemples */
SELECT name AS "Nom", municipality AS "Ville" FROM airports WHERE type='large_airport' AND iso_region='FR-IDF';
SELECT DISTINCT continent FROM countries;
SELECT code, name FROM countries ORDER BY code ASC;

/* exercice 2 */
SELECT * FROM airports WHERE iso_country='FR' AND (type='large_airport' OR type='medium_airport') ORDER BY latitude_deg DESC;
SELECT municipality FROM airports WHERE iso_country='US' AND type='large_airport' ORDER BY longitude_deg ASC;
SELECT name, municipality FROM airports WHERE iso_country='FR' AND type='heliport' AND elevation_ft * 0.3048 >= 1000;
SELECT name, municipality FROM airports WHERE iso_country='FR' AND type='heliport' AND elevation_ft * 0.3048 >= 1000 ORDER BY elevation_ft DESC;
SELECT name, municipality, elevation_ft * 0.3048 AS "Altitude (mètres)" FROM airports WHERE iso_country='FR' AND type='heliport' AND "Altitude (mètres)" >= 1000 ORDER BY "Altitude (mètres)" DESC;
SELECT DISTINCT iso_country FROM airports WHERE continent='AF' AND type='large_airport';
SELECT name, code FROM regions WHERE iso_country='DE' ORDER BY name ASC;
SELECT DISTINCT municipality FROM airports WHERE iso_region='DE-NW' ORDER BY municipality ASC;

/* exemple */
SELECT COUNT(id) AS "Nombre total" FROM countries;
SELECT AVG(elevation_ft) AS "Altitude moyenne" FROM airports;

/* exercice 3 */
SELECT AVG(elevation_ft * 0.3048) AS "Altitude moyenne (mètres)" FROM airports WHERE iso_country='NL';
SELECT AVG(elevation_ft * 0.3048) AS "Altitude moyenne (mètres)" FROM airports WHERE iso_country='CH';
SELECT AVG(elevation_ft * 0.3048) AS "Altitude moyenne (mètres)" FROM airports WHERE iso_country='NP';
SELECT COUNT(*) FROM airports WHERE continent='EU' and type='large_airport';
SELECT COUNT(*) FROM regions WHERE iso_country='DE';

/* exemple */
SELECT COUNT(id) AS "Nombre", continent FROM countries GROUP BY continent;
SELECT COUNT(id) AS "Nombre", continent FROM countries GROUP BY continent HAVING "Nombre" >= 20;

/* exercice 4 */
SELECT COUNT(*) AS "Nombre de régions", iso_country FROM regions GROUP BY iso_country;
SELECT AVG(elevation_ft * 0.3048) AS "Altitude moyenne (mètres)", iso_country FROM airports WHERE (type='large_airport' OR type='medium_airport') AND continent='SA' GROUP BY iso_country ORDER BY "Altitude moyenne (mètres)" DESC;
SELECT COUNT(*) AS "Nombre d'aéroports", municipality FROM airports WHERE iso_country='FR' GROUP BY municipality HAVING "Nombre d'aéroports" >= 3 ORDER BY "Nombre d'aéroports" DESC;
SELECT municipality, COUNT(*) AS "Nombre d'aéroports" FROM airports WHERE continent='EU' AND (type='large_airport' OR type='medium_airport') GROUP BY municipality HAVING "Nombre d'aéroports" > 1;

/* exemple */
SELECT * FROM airports JOIN regions ON airports.iso_region=regions.code WHERE airports.iso_country='FR' AND airports.type='large_airport';
SELECT airports.name AS "Nom", airports.municipality AS "Ville", regions.name AS "Région" FROM airports JOIN regions ON airports.iso_region=regions.code WHERE airports.iso_country='FR' AND airports.type='large_airport';

/* exercice 5 */
SELECT airports.name AS "Nom", regions.name AS "Région" FROM airports JOIN regions ON airports.iso_region=regions.code WHERE airports.continent='EU' AND airports.type='large_airport';
SELECT airports.name AS "Nom", airports.municipality AS "Ville", countries.name AS "Pays" FROM airports JOIN countries ON airports.iso_country=countries.code WHERE airports.continent='AS' AND airports.type='large_airport' ORDER BY airports.longitude_deg ASC;
SELECT countries.name AS "Pays", airports.municipality AS "Ville" FROM airports JOIN countries ON airports.iso_country=countries.code WHERE airports.iata_code='SCL';
SELECT countries.name AS "Pays", regions.name AS "Région" FROM airports JOIN countries ON airports.iso_country=countries.code JOIN regions ON regions.code=airports.iso_region WHERE airports.iata_code='DPS';
SELECT countries.name AS "Pays", COUNT(airports.iata_code) AS "Nombre de codes IATA" FROM airports JOIN countries ON airports.iso_country=countries.code GROUP BY "Pays" ORDER BY "Nombre de codes IATA" DESC;
SELECT countries.name AS "Pays", COUNT(airports.iata_code) AS "Nombre de codes IATA" FROM airports JOIN countries ON airports.iso_country=countries.code GROUP BY "Pays" HAVING "Nombre de codes IATA" >= 100 ORDER BY "Nombre de codes IATA" DESC;
SELECT countries.name AS "Pays", AVG(airports.elevation_ft * 0.3048) AS "Altitude moyenne (mètre)" FROM airports JOIN countries ON airports.iso_country=countries.code GROUP BY "Pays" ORDER BY "Altitude moyenne (mètre)" DESC;
