Countries.cs

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;

class Program
{
    static void Main()
    {

        Console.OutputEncoding = Encoding.UTF8;

        string connectionString = ConfigurationManager.ConnectionStrings["CountriesDB"].ConnectionString;

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            DataTable countriesTable = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Countries", connection))
            {
                adapter.Fill(countriesTable);
            }

            var allCountries = countriesTable.AsEnumerable();
            foreach (var country in allCountries)
                Console.WriteLine(
                    Console.WriteLine($"Id: {country.Field<int>("id_country"),-4}")
                    Console.WriteLine($"Назва: {country.Field<string>("country_name"),-11}")
                    Console.WriteLine($"Населення: {country.Field<long>("country_population"),-12}")
                    Console.WriteLine($"Площа: {country.Field<double>("country_area"),-10}")
                    Console.WriteLine($"Чи є у ЄС: {country.Field<bool>("is_in_eu"),-7})
                    Console.WriteLine($"Ідентифікатор континента: {country.Field<int>("id_continent"),-10}")
                );
            

            var countryNames = allCountries
                .Select(row => row.Field<string>("country_name"))
                .ToList();
            foreach (var name in countryNames)
                Console.WriteLine($"Назва країни: {name}");
            

            DataTable citiesTable = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Cities WHERE is_capital = 1", connection))
            {
                adapter.Fill(citiesTable);
            }

            var capitalNames = citiesTable.AsEnumerable()
                .Select(row => row.Field<string>("city_name"))
                .ToList();
            foreach (var name in capitalNames)
                Console.WriteLine($"Назва столиці: {name}");
            

            string countryName = "Ukraine";

            DataTable bigCitiesTable = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter($"SELECT * FROM Cities WHERE city_population > 100000 AND id_region IN (SELECT id_region FROM Regions WHERE id_country IN (SELECT id_country FROM Countries WHERE country_name = '{countryName}'))", connection))
            {
                adapter.Fill(bigCitiesTable);
            }

            var bigCitiesOfCountry = bigCitiesTable.AsEnumerable()
                .Select(row => row.Field<string>("city_name"))
                .ToList();
            foreach (var name in bigCitiesOfCountry)
                Console.WriteLine($"Назва найбільшого міста: {name}\n");

            var bigCapitals = citiesTable.AsEnumerable()
                .Where(row => row.Field<long>("city_population") > 5000000
                && row.Field<bool>("is_capital"))
                .Select(row => row.Field<string>("city_name"))
                .ToList();
            foreach (var name in bigCapitals)
                Console.WriteLine($"Назва найбільшої столиці: {name}\n");
            

            var europeanCountries = countriesTable.AsEnumerable()
                .Where(row => row.Field<bool>("is_in_eu"))
                .Select(row => row.Field<string>("country_name"))
                .ToList();
            foreach (var name in europeanCountries)
                Console.WriteLine($"Назва Європейської країни: {name}\n");
            

            double areaRange = 480000;

            var largeCountries = countriesTable.AsEnumerable()
                .Where(row => row.Field<double>("country_area") > areaRange)
                .Select(row => row.Field<string>("country_name"))
                .ToList();
            foreach (var name in largeCountries)
                Console.WriteLine($"Назва найбільшої країни: {name}\n");
            




            var capitalsWithAandP = citiesTable.AsEnumerable()
                .Where(row => row.Field<string>("city_name")
                    .Contains("a", StringComparison.OrdinalIgnoreCase)
                && row.Field<string>("city_name")
                    .Contains("p", StringComparison.OrdinalIgnoreCase))
                .ToList();
            foreach (var capital in capitalsWithAandP)
                Console.WriteLine($"Назва столиці: {capital.Field<string>("city_name")}\n");
            

            var capitalsStartingWithK = citiesTable.AsEnumerable()
                .Where(row => row.Field<string>("city_name")
                    .StartsWith("k", StringComparison.OrdinalIgnoreCase))
                .ToList();
            foreach (var capital in capitalsStartingWithK)
                Console.WriteLine($"Назва столиці: {capital.Field<string>("city_name")}\n");
            
           

            double minRange = 200000;
            double maxRange = 900000;

            var countriesInAreaRange = countriesTable.AsEnumerable()
                .Where(row => row.Field<double>("country_area") >= minRange
                && row.Field<double>("country_area") <= maxRange)
                .ToList();
            foreach (var country in countriesInAreaRange)
                Console.WriteLine($"Назва країни: {country.Field<string>("country_name")}\n");
            

            long populationRange = 50000000;

            var countriesWithLargePopulation = countriesTable.AsEnumerable()
                .Where(row => row.Field<long>("country_population") > populationRange)
                .ToList();
            foreach (var country in countriesWithLargePopulation)
                Console.WriteLine($"Назва країни: {country.Field<string>("country_name")}\n");
            


            var top5CountriesByArea = countriesTable.AsEnumerable()
               .OrderByDescending(row => row.Field<double>("country_area"))
               .Take(5)
               .ToList();
            foreach (var country in top5CountriesByArea)
                Console.WriteLine(
                    $"Назва країни: {country.Field<string>("country_name"),-11}" +
                    $"Площа: {country.Field<double>("country_area")}\n"
                );
            

            using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Cities WHERE is_capital = 1", connection))
            {
                adapter.Fill(citiesTable);
            }

            var top5CapitalsByPopulation = citiesTable.AsEnumerable()
                .OrderByDescending(row => row.Field<long>("city_population"))
                .Take(5)
                .ToList();
            foreach (var capital in top5CapitalsByPopulation)
                Console.WriteLine(
                    $"Назва столиці: {capital.Field<string>("city_name"),-8}" +
                    $"Населення: {capital.Field<long>("city_population")}\n"
                );
            

            var countryWithLargestArea = countriesTable.AsEnumerable()
                .OrderByDescending(row => row.Field<double>("country_area"))
                .First();
            Console.WriteLine(
                $"Країна з найбільшою площею: {countryWithLargestArea.Field<string>("country_name"),-8}" +
                $"Площа: {countryWithLargestArea.Field<double>("country_area")}\n"
            );

            var capitalWithLargestPopulation = citiesTable.AsEnumerable()
                .Where(row => row.Field<bool>("is_capital"))
                .OrderByDescending(row => row.Field<long>("city_population"))
                .First();
            Console.WriteLine(
                $"Країна з найбільшим населенням: {capitalWithLargestPopulation.Field<string>("city_name"),-8}" +
                $"Населення: {capitalWithLargestPopulation.Field<long>("city_population")}\n"
            );

            var smallestEuropeanCountry = countriesTable.AsEnumerable()
                .Where(row => row.Field<bool>("is_in_eu"))
                .OrderBy(row => row.Field<double>("country_area"))
                .First();
            Console.WriteLine(
                $"Найменша країна Європи: {smallestEuropeanCountry.Field<string>("country_name"),-9}" +
                $"Площа: {smallestEuropeanCountry.Field<double>("country_area")}\n"
            );

            var averageAreaOfEuropeanCountries = countriesTable.AsEnumerable()
                .Where(row => row.Field<bool>("is_in_eu"))
                .Average(row => row.Field<double>("country_area"));
            Console.WriteLine(
                $"Середня площа країн Європи: {Math.Round(averageAreaOfEuropeanCountries, 2)}\n"
            );


            DataTable citiesOfCountryTable = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter($"SELECT * FROM Cities WHERE id_region IN (SELECT id_region FROM Regions WHERE id_country IN (SELECT id_country FROM Countries WHERE country_name = '{countryName}'))", connection))
            {
                adapter.Fill(citiesOfCountryTable);
            }

            var top3CitiesOfCountry = citiesOfCountryTable.AsEnumerable()
                .OrderByDescending(row => row.Field<long>("city_population"))
                .Take(3)
                .ToList();
            foreach (var city in top3CitiesOfCountry)
                Console.WriteLine($"Назва міста: {city.Field<string>("city_name"),-6}" +
                    $"Населення: {city.Field<long>("city_population")}\n"
                );

            var totalNumberOfCountries = countriesTable.AsEnumerable()
                .Count();
            Console.WriteLine(
                $"Загальна кількість країн: {totalNumberOfCountries}\n"
            );

            DataTable continentsTable = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Continents", connection))
            {
                adapter.Fill(continentsTable);
            }

            var continentWithMostCountries = continentsTable.AsEnumerable()
                .OrderByDescending(row => countriesTable.AsEnumerable()
                    .Count(c => c.Field<int>("id_continent") == row.Field<int>("id_continent")))
                .First();
            Console.WriteLine(
                $"Континент з найбільшою кількістю країн: {continentWithMostCountries.Field<string>("continent_name")}\n"
            );

            foreach (var continent in continentsTable.AsEnumerable())
            {
                var numOfCountriesInContinent = countriesTable.AsEnumerable()
                    .Count(c => c.Field<int>("id_continent") == continent.Field<int>("id_continent"));
                Console.WriteLine(
                    $"Континент: {continent.Field<string>("continent_name"),-15}" +
                    $"Ідентифікатор континента: {numOfCountriesInContinent}"
                );
            }
            
        }
    }
    
}