Портфолио
et
request-free-img

SQL. Выборка данных из нескольких таблиц

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;
--использовать псевдонимы таблиц
Select * from  Country as c
inner join City as ci
On ci.ID=c.Capital;

https://moodle.edu.ee/mod/forum/view.php?id=2562044

Kasutame SQL Server Managment Stuudio ja andmebaas

  1. Покажите названия стран континента Европа, их столицы, количество населения проживающего в столице, отсортированные в порядке убывания населения
    1. учитывая только страны, имеющие столицу (см.ответ)
    2. учитывая все страны (см.ответ)
--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 desc;

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

--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 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 AS Riik,
c.Continent,
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;

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

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
ORDER BY C.Name
  • 7. Найдите для каждой страны какой процент (округлить до одного десятичного знака) от населения страны приходится на население столицы.
    1. учитывая только страны, имеющие столицу (см.ответ) — Aleks K
    2. учитывая все страны (см.ответ) — Martin
-- 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 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;

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

select cl.Language, sum(c.Population*Cl.Percentage) as 'Kokku inimesed'
from Country as 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

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

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