/* TP 21 correction : Langage SQL partie 1
   BCPST1B 2023-2024
   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 FROM airports WHERE iso_country='FR' AND type='heliport' AND altitude >= 1000 ORDER BY altitude 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 FROM countries;
SELECT AVG(elevation_ft) FROM airports;

/* exercice 3 */
SELECT AVG(elevation_ft * 0.3048) FROM airports WHERE iso_country='NL';
SELECT AVG(elevation_ft * 0.3048) FROM airports WHERE iso_country='CH';
SELECT AVG(elevation_ft * 0.3048) 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 airports GROUP BY continent HAVING nombre >= 20;

/* exercice 4 */
SELECT COUNT(*), iso_country FROM regions GROUP BY iso_country;
SELECT AVG(elevation_ft * 0.3048) AS "Altitude moyenne", iso_country FROM airports WHERE (type='large_airport' OR type='medium_airport') AND continent='SA' GROUP BY iso_country ORDER BY "Altitude moyenne" DESC;
SELECT COUNT(*) AS nombre, municipality FROM airports WHERE iso_country='FR' GROUP BY municipality HAVING nombre >= 3 ORDER BY nombre DESC;
SELECT municipality, COUNT(*) AS nombre FROM airports WHERE continent='EU' AND (type='large_airport' OR type='medium_airport') GROUP BY municipality HAVING nombre > 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, airports.municipality, regions.name 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, regions.name FROM airports JOIN regions ON airports.iso_region=regions.code WHERE airports.continent='EU' AND airports.type='large_airport';
SELECT airports.name, airports.municipality, countries.name 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, airports.municipality FROM airports JOIN countries ON airports.iso_country=countries.code WHERE airports.iata_code='SCL';
SELECT countries.name, regions.name 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, COUNT(airports.iata_code) AS nombre FROM airports JOIN countries ON airports.iso_country=countries.code GROUP BY countries.name ORDER BY nombre DESC;
SELECT countries.name, COUNT(airports.iata_code) AS nombre FROM airports JOIN countries ON airports.iso_country=countries.code GROUP BY countries.name HAVING nombre >= 100 ORDER BY nombre DESC;
SELECT countries.name, AVG(airports.elevation_ft * 0.3048) AS "Altitude moyenne" FROM airports JOIN countries ON airports.iso_country=countries.code GROUP BY countries.name ORDER BY "Altitude moyenne" DESC;
