SELECT päring (MS SQL Server Management Studio) - Darja Suhhanova (Portnova)

https://moodle.edu.ee/pluginfile.php/1213077/mod_resource/content/1/ru/index_ru.html

USE World;
SELECT * FROM City;
SELECT * FROM country;
SELECT * FROM CountryLanguage;

SELECT laused 2 tabelite põhjal

--vigane päring
SELECT * FROM City, Country;

--õige päring
SELECT * FROM Country, City
WHERE city.ID=country.Capital;

--INNER JOIN
SELECT * FROM Country 
INNER JOIN City
ON city.ID=country.Capital;

--use tables nickname
SELECT * FROM Country AS c
INNER JOIN City AS ci
ON ci.ID=c.Capital;

SELECT * FROM Country c
INNER JOIN City ci
ON ci.ID=c.Capital;

Покажите названия стран континента Европа, их столицы, количество населения проживающего в столице, отсортированные в порядке убывания населения:

– учитывая только страны, имеющие столицу (см.ответ)

– учитывая все страны (см.ответ)

--1.a--
SELECT c.name 'Riik', ci.name 'Pealinn', ci.Population 'Pealinna Elanike Arv' 
FROM Country c
INNER JOIN City ci
ON ci.ID=c.Capital
WHERE c.Continent LIKE 'Europe'
ORDER BY ci.Population desc;

--1.b--
SELECT c.name 'Riik', ci.name 'Pealinn', ci.Population 'Pealinna Elanike Arv' 
FROM Country c
LEFT JOIN City ci
ON ci.ID=c.Capital
ORDER BY c.Name;

Покажите страны, в которых название страны и название столицы совпадают (см.ответ)

--2--
SELECT c.name AS 'Riik', ci.Name AS Pealinn
FROM Country AS c
INNER JOIN City AS ci
ON ci.ID=C.Capital
WHERE c.Name=ci.Name;

Найдите города, которые имеют одинаковые названия и находятся в одной стране. (см.ответ)

--3--
SELECT a.id, a.name, a.district, a.countryCode
FROM city AS a, City AS b, country AS c
WHERE a.Name = b.name AND a.ID <> b.ID
AND A.CountryCode=c.code AND b.CountryCode=c.code
ORDER BY a.name;

Покажите, количество языков, на которых говорят в каждой стране (назывние страны, контитент, количество языков). Отсоритуйте результат по этому количеству. (см.ответ)

--4--
SELECT c.Name AS Riik, c.Continent AS Con,
COUNT(co.Language) AS 'Language ARV'
FROM Country AS c
INNER JOIN CountryLanguage AS co
ON c.Code=co.CountryCode
GROUP BY c.name, c.continent
ORDER BY COUNT(co.Language) DESC;

Покажите, количество официальных языков в каждой стране (назывние страны, контитент, количество официальных языков). Отсоритуйте результат по этому количеству. (см.ответ)

--5--
SELECT c.name 'Riik',
c.Continent 'Kontinent',
COUNT(cl.Language) 'Keelte Arv' 
FROM Country c, CountryLanguage cl
WHERE c.Code=cl.CountryCode
AND cl.IsOfficial = 1
GROUP BY c.name, c.continent
ORDER BY COUNT(cl.Language) DESC;

Покажите, на каких языках в каком количестве разговаривает население Азии. (см.ответ)

--6--
SELECT c.name Riik,
cl.Language Keel,
(c.Population * cl.Percentage) 'Keelte Arv' 
FROM Country c, CountryLanguage cl
WHERE c.Code=cl.CountryCode
AND c.continent LIKE 'Asia'
ORDER BY c.Name

Найдите для каждой страны какой процент (округлить до одного десятичного знака) от населения страны приходится на население столицы.

– учитывая только страны, имеющие столицу (см.ответ)

– учитывая все страны (см.ответ)

--7.a--
SELECT c.Name Riik,
c.Population 'Riigi Elanikut' ,
ci.Name AS Pealinn,
ci.Population AS 'Pealinna Elanike Arv',
round( (cast(Ci.Population AS FLOAT)/cast(c.Population AS float))*100,1) Protsent
FROM Country as c
INNER JOIN City as ci
ON Ci.ID = c.Capital
ORDER BY c.Name;

--7.b--
SELECT Co.Name AS Riik, Co.Population as 'Riigi Elanikut' ,
Ci.Name AS Pealinn ,Ci.Population AS 'Pealinna Elanike Arv',
round( (cast(Ci.Population AS FLOAT)/cast(Co.Population AS float))*100,1) Protsent
FROM Country as Co
LEFT JOIN City as Ci
ON Ci.ID = Co.Capital
ORDER BY Co.Name;

Найдите общее, сумарное количество человек, говорящих на каждом из языков, представленных в базе данных (см.ответ)

--8--
SELECT cl.Language Language,
SUM (c.Population * cl.Percentage) 'Kokku Inimest'
FROM country c
INNER JOIN CountryLanguage cl
ON cl.CountryCode=c.Code
GROUP BY cl.Language
ORDER BY SUM (c.Population*cl.Percentage) desc

Найдите общее, ссумарное количество человек для стран Европы, говорящих на каждом из языков, представленных в базе данных. (см.ответ)

--9.--
Select cl.Language AS Language, 
SUM(c.Population * cl.Percentage ) AS 'Kokku Inimest'
FROM Country AS c
INNER JOIN CountryLanguage AS cl 
ON cl.CountryCode = c.Code
WHERE c.Continent = 'Europe'
GROUP BY cl.Language
ORDER BY SUM(C.Population * cl.Percentage) desc;

Вывести полную информацию обо всех странах, представленных в базе данных – название, континент, год обретения независимости, площадь, население, форма правления, столица, офицальный язык (языки) (см.ответ)

--10--
SELECT c.Name Riik,
c.Continent Kontinent,
c.SurfaceArea Pindala, 
c.IndepYear 'Iseseis Aasta',
c.Population 'Elanikude Arv',
c.GovernmentForm GovernmentForm,
c.HeadOfState HeadOfState,
ci.Name Pealinn,
cl.Language Keel
from country c, city ci, CountryLanguage cl
WHERE cl.countrycode=c.code AND ci.ID = c.Capital AND cl.IsOfficial=1
ORDER BY c.name