1. Euroopa riigid, mis on saanud iseseisvaks 19. sajandil

In [1]:
SELECT *
FROM tCountry
WHERE LEFT(COuntryName,1)=RIGHT(CountryName,1)
(20 rows affected)
Total execution time: 00:00:00.023
Out[1]:
CodeCountryNameContinentRegionSurfaceAreaIndepYearPopulationLifeExpectancyGNPGNPOldLocalNameGovernmentFormHeadOfStateCapital
ABWArubaNorth AmericaCaribbean193,00NULL10300078,4828,00793,00ArubaNonmetropolitan Territory of The NetherlandsBeatrix129
AGOAngolaAfricaCentral Africa1246700,0019751287800038,36648,007984,00AngolaRepublicJosé Eduardo dos Santos56
AIAAnguillaNorth AmericaCaribbean96,00NULL800076,163,20NULLAnguillaDependent Territory of the UKElisabeth II62
ALBAlbaniaEuropeSouthern Europe28748,001912340120071,63205,002500,00ShqiptarëtRepublicRexhep Mejdani34
ANDAndorraEuropeSouthern Europe468,0012787800083,51630,00NULLAndorraParliamentary CoprincipalityNULL55
ARGArgentinaSouth AmericaSouth America2780400,0018163703200075,1340238,00323310,00ArgentinaFederal RepublicFernando de la R?a69
ARMArmeniaAsiaMiddle East29800,001991352000066,41813,001627,00HajastanRepublicRobert Kot?arjan126
ASMAmerican SamoaOceaniaPolynesia199,00NULL6800075,1334,00NULLAmerika SamoaUS TerritoryGeorge W. Bush54
ATAAntarcticaAntarcticaAntarctica13120000,00NULL0NULL0,00NULL–Co-administratedNULLNULL
ATGAntigua and BarbudaNorth AmericaCaribbean442,0019816800070,5612,00584,00Antigua and BarbudaConstitutional MonarchyElisabeth II63
AUSAustraliaOceaniaAustralia and New Zealand7741220,0019011888600079,8351182,00392911,00AustraliaConstitutional Monarchy, FederationElisabeth II135
AUTAustriaEuropeWestern Europe83859,001918809180077,7211860,00206025,00ÖsterreichFederal RepublicThomas Klestil1523
CAFCentral African RepublicAfricaCentral Africa622984,001960361500044,01054,00993,00Centrafrique/B?-Afr?kaRepublicAnge-Félix Patassé1889
CZECzech RepublicEuropeEastern Europe78866,0019931027810074,555017,0052037,00ČeskáRepublicV?clav Havel3339
DZAAlgeriaAfricaNorthern Africa2381741,0019623147100069,749982,0046966,00Al-Jaza’’ir/AlgérieRepublicAbdelaziz Bouteflika35
KNASaint Kitts and NevisNorth AmericaCaribbean261,0019833800070,7299,00NULLSaint Kitts and NevisConstitutional MonarchyElisabeth II3064
SGSSouth Georgia and the South Sandwich IslandsAntarcticaAntarctica3903,00NULL0NULL0,00NULLSouth Georgia and the South Sandwich IslandsDependent Territory of the UKElisabeth IINULL
SLBSolomon IslandsOceaniaMelanesia28896,00197844400071,3182,00220,00Solomon IslandsConstitutional MonarchyElisabeth II3161
SYCSeychellesAfricaEastern Africa455,0019767700070,4536,00539,00Sesel/SeychellesRepublicFrance-Albert René3206
VCTSaint Vincent and the GrenadinesNorth AmericaCaribbean388,00197911400072,3285,00NULLSaint Vincent and the GrenadinesConstitutional MonarchyElisabeth II3066

Kõige laiemalt levinud juhtimisvorm Euroopas

In [4]:
SELECT TOP 1 GovernmentForm, COUNT(*) as Kokku
FROM tCountry
WHERE Continent LIKE 'Europe'
GROUP BY GovernmentForm
ORDER BY Count(*) DESC
(1 row affected)
Total execution time: 00:00:00.016
Out[4]:
GovernmentFormKokku
Republic25
In [5]:
--5.	Riikide arv, mille nimetus algab iga (A, B, …, Z) tähega
SELECT LEFT(CountryName,1) as Taht, COUNT(*) AS Kokku
FROM tCountry
GROUP BY LEFT(CountryName,1)
ORDER BY 1
(25 rows affected)
Total execution time: 00:00:00.619
Out[5]:
TahtKokku
A15
B20
C22
D4
E8
F8
G15
H6
I8
J3
K5
L9
M22
N15
O1
P12
Q1
R4
S29
T13
U8
V5
W2
Y2
Z2
In [7]:
--6.	Mitu riiki on igal sajandil saanud iseseisvaks
SELECT Indepyear/100+1 as Sajand, COUNT(*) As Kokku
FROM tCountry
WHERE IndepYear IS NOT NULL
GROUP BY Indepyear/100+1
ORDER BY Indepyear/100+1
(13 rows affected)
Total execution time: 00:00:00.013
Out[7]:
SajandKokku
-141
-91
-51
94
111
121
131
141
152
161
182
1927
20149
In [9]:
--7.	Kõige väiksema pindalaga kontinent
SELECT TOP 1 Continent, SUM(SurfaceArea) as PindalaKokku
FROM tCountry
GROUP BY Continent
ORDER BY SUM(SurfaceArea)
(1 row affected)
Total execution time: 00:00:00.013
Out[9]:
ContinentPindalaKokku
Oceania8564294,00