Для учебки

напиши для таблицы: CREATE TABLE [dbo].[Contry](
[name] nvarchar NULL,
[capital] nvarchar NULL,
[squer] [int] NULL,
[population] [int] NULL,
[continent] nvarchar NULL
) ON [PRIMARY]
GO
следующие запросы:
–1. Вывести список стран и процентное соотношение площади каждой из них к общей площади всех стран мира.

–2. Вывести список стран мира, плотность населения которых больше, чем средняя плотность населения всех стран мира.

–3. С помощью подзапроса вывести список европейских стран, население которых меньше 5 млн. чел.

–4. Вывести список стран и процентное соотношение их площади к суммарной пло-щади той части мира, где они находятся.

–5. Вывести список стран мира, площадь которых больше, чем средняя площадь стран той части света, где они находятся.

–6. Вывести список стран мира, которые находятся в тех частях света, средняя плот-ность населения которых превышает общемировую.

–7. Вывести список южноамериканских стран, в которых живет больше людей, чем в любой африканской стране.

–8. Вывести список африканских стран, в которых живет больше людей, чем хотя бы в одной южноамериканской стране.

–9. Если в Африке есть хотя бы одна страна, площадь которой больше 2 млн. кв. км, вывести список всех африканских стран.

–10. Вывести список стран той части света, где находится страна «Фиджи».

–11. Вывести список стран, население которых не превышает население страны «Фиджи».

–12. Вывести название страны с наибольшим населением среди стран с наименьшей площадью на каждом континенте.

To answer these SQL queries, we need to first understand the table schema and then construct the appropriate SQL statements. The provided schema is for a table named Contry (presumably a typo for Country) with columns for name, capital, area (squer), population, and continent. Let’s address each query individually.

1. Percentage of each country’s area relative to the total world area.

SELECT
    name,
    (CAST(squer AS FLOAT) / SUM(squer) OVER ()) * 100 AS percentage_of_world_area
FROM
    Contry;

This query uses a window function SUM(squer) OVER () to calculate the total area of all countries. Then, it divides each country’s area by the total area and multiplies by 100 to get the percentage.

2. Countries with population density above the world average.

SELECT
    name
FROM
    Contry
WHERE
    CAST(population AS FLOAT) / squer > (SELECT AVG(CAST(population AS FLOAT) / squer) FROM Contry);

This query calculates the average population density using a subquery and then selects countries whose density exceeds this average.

3. European countries with a population under 5 million using a subquery.

SELECT
    name
FROM
    Contry
WHERE
    continent = 'Europe' AND population < 5000000;

This query uses a simple WHERE clause to filter for European countries with populations under 5 million. A subquery isn’t strictly necessary here.

4. Percentage of each country’s area relative to its continent’s total area.

SELECT
    name,
    (CAST(squer AS FLOAT) / SUM(squer) OVER (PARTITION BY continent)) * 100 AS percentage_of_continent_area
FROM
    Contry;

This query uses a window function with PARTITION BY continent to calculate the total area for each continent separately. Then, it calculates the percentage for each country within its continent.

5. Countries with area larger than the average area of their continent.

SELECT
    name
FROM
    Contry
WHERE
    squer > (SELECT AVG(squer) FROM Contry WHERE continent = Contry.continent);

This uses a correlated subquery to compare each country’s area to the average area of its continent.

6. Countries located in continents with above-average world population density.

SELECT
    name
FROM
    Contry
WHERE
    continent IN (SELECT continent FROM Contry GROUP BY continent HAVING AVG(CAST(population AS FLOAT) / squer) > (SELECT AVG(CAST(population AS FLOAT) / squer) FROM Contry));

This query uses a subquery to identify continents with above-average density and then selects countries from those continents.

7. South American countries with populations exceeding any African country.

SELECT
    name
FROM
    Contry
WHERE
    continent = 'South America' AND population > (SELECT MAX(population) FROM Contry WHERE continent = 'Africa');

This query finds the maximum population of any African country and selects South American countries exceeding that value.

8. African countries with populations exceeding at least one South American country.

SELECT
    name
FROM
    Contry
WHERE
    continent = 'Africa' AND population > ANY (SELECT population FROM Contry WHERE continent = 'South America');

This uses the ANY operator to check if an African country’s population is greater than at least one South American country’s population.

9. If any African country has an area over 2 million sq km, list all African countries.

SELECT
    name
FROM
    Contry
WHERE
    continent = 'Africa'
    AND EXISTS (SELECT 1 FROM Contry WHERE continent = 'Africa' AND squer > 2000000);

This uses EXISTS to check if the condition is met; if so, it returns all African countries.

10. Countries in the same continent as Fiji.

SELECT
    name
FROM
    Contry
WHERE
    continent = (SELECT continent FROM Contry WHERE name = 'Fiji');

This subquery finds Fiji’s continent and then selects countries from that continent.

11. Countries with populations not exceeding Fiji’s population.

SELECT
    name
FROM
    Contry
WHERE
    population <= (SELECT population FROM Contry WHERE name = 'Fiji');

This query selects countries with populations less than or equal to Fiji’s population.

12. Country with the largest population among those with the smallest area on each continent.

WITH RankedCountries AS (
    SELECT
        name,
        continent,
        squer,
        population,
        ROW_NUMBER() OVER (PARTITION BY continent ORDER BY squer ASC, population DESC) as rn
    FROM
        Contry
)
SELECT
    name,
    continent
FROM
    RankedCountries
WHERE
    rn = 1;

This uses a CTE (Common Table Expression) to rank countries within each continent based on area (ascending) and then population (descending). It then selects the top-ranked country (rn=1) for each continent.

Remember to replace Contry with Country if that was a typo in your original schema. These queries assume that squer represents area in square kilometers and that population data is accurate. Always test these queries on your specific data to ensure they produce the expected results.