Chernykh Kyrylo Oppimap/Portfoolio

use World
select * from City;
select * from country;
select * from CountryLanguage;
--select laused 2 tabelite pohjal
--vigane parind
select * from city, country;
--oige paring
select * from country, city
where city.ID=country.Capital;
--INNER JOIN - Внутреннее соеденение
select * from country inner join city
on city.ID=country.Capital;
--Использовать псевдонимы таблиц
select * from country as c
inner join city as ci
on ci.ID=c.Capital;

--1.a--
Select c.Name AS Riik, ci.Name AS Pealinn,
ci.population AS 'Pealinna Elanike Arv'
from country AS c
INNER JOIN city AS ci
ON ci.ID=c.Capital
WHERE c.Continent LIKE 'Europe'
ORDER by ci.population desc;

--1.b--
Select c.Name AS Riik, ci.Name AS Pealinn,
ci.population AS 'Pealinna Elanike Arv'
from country AS c
left JOIN city AS 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.Kokku 48 rida--
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 Kontinent, 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) AS 'Language 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;

SELECT C.Name AS Riik, L.Language AS Keel, ci.population AS 'Inimiste arv'
FROM (Country AS C INNER JOIN City AS ci
ON C.Capital = Ci.ID)
INNER JOIN CountryLanguage AS L
ON C.Code = L.CountryCode
WHERE c.continent LIKE 'Asia' AND C.Code = L.CountryCode AND L.IsOfficial = 1
ORDER BY C.Name

--7.a--
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
inner JOIN City as Ci
ON Ci.ID = Co.Capital
ORDER BY Co.Name;

Select CL.Language AS Language, sum (C.Population*Cl.Percentage) AS 'Kokku Inimest'
FROM country c
INNER JOIN CountryLanguage AS 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;

select c.Name as Riik,
c.Continent as Continent,
c.SurfaceArea as Pindala,
c.IndepYear as IseseisAasta,
c.Population as ElanikudeArv,
c.GovernmentForm as GovernmentForm,
c.HeadOfState as Headofstate,
ci.name as Pealinn,
cl.language as Keel

from country as c,
city as ci,
CountryLanguage as cl
where cl.countrycode = c.code and Ci.ID = c.capital and cl.isofficial = 1
order by c.name