--Mõned tekstfunktsioonid
SELECT TOP 5 Code +', '+ CountryName as Koos, Concat(Code, ', ',CountryName) Koos_Funktsiooniga,
    LEFT(CountryName,1) as _1_taht, Upper(RIGHT(CountryName,1)) as Viimane_taht,
    SUBSTRING(CountryName,1,3) as Kolm_tahti, LEN(CountryName) as Pikkus
FROM tCountry

(5 rows affected)

Total execution time: 00:00:00.009

Koos	Koos_Funktsiooniga	_1_taht	Viimane_taht	Kolm_tahti	Pikkus
ABW, Aruba	ABW, Aruba	A	A	Aru	5
AFG, Afghanistan	AFG, Afghanistan	A	N	Afg	11
AGO, Angola	AGO, Angola	A	A	Ang	6
AIA, Anguilla	AIA, Anguilla	A	A	Ang	8
ALB, Albania	ALB, Albania	A	A	Alb	7

---Kuupäeva funktsioonid
SELECT getdate() as Aeg, CURRENT_TIMESTAMP as aeg2,
    YEAR(getdate()) as Aasta, MONTH(getdate()) as Kuu, DATEPART(year, getdate()) as Aasta,
    DATEDIFF(day,getdate(),'2022-01-01') Vahe_paevades

(1 row affected)

Total execution time: 00:00:00.010

Aeg	aeg2	Aasta	Kuu	Aasta	Vahe_paevades
2021-12-06 18:06:04.440	2021-12-06 18:06:04.440	2021	12	2021	26

--Teiseldamise funktsioonid
SELECT CAST(GETDATE() as varchar(50) ) as tekst,
        CONVERT(varchar(50), getdate()) as tekst

(1 row affected)

Total execution time: 00:00:00.007

tekst	tekst
Dec 6 2021 2:25PM	Dec 6 2021 2:25PM

--Suurte linnade arv kontinetdi ja regiooni kaupa
SELECT R.Continent, R.Region, COUNT(L.ID) as COuntCities
FROM tCountry R JOIN tCity AS L ON L.CountryCode=R.Code
WHERE L.Population>=100000
GROUP BY  R.Continent, R.Region

(21 rows affected)

Total execution time: 00:00:00.020

Continent	Region	COuntCities
Africa	Northern Africa	90
South America	South America	416
Europe	Eastern Europe	325
Oceania	Melanesia	1
North America	Central America	168
Oceania	Australia and New Zealand	21
Europe	Baltic Countries	15
North America	North America	290
Asia	Southeast Asia	254
Europe	Western Europe	162
Africa	Eastern Africa	55
North America	Caribbean	32
Europe	British Islands	66
Europe	Nordic Countries	27
Asia	Middle East	156
Africa	Southern Africa	44
Africa	Central Africa	35
Asia	Eastern Asia	689
Africa	Western Africa	100
Europe	Southern Europe	124
Asia	Southern and Central Asia	498

--With CUBE
SELECT ISNULL(R.Continent,'Region Total') as Continent , ISNULL(R.Region,'Continent Total') AS Region, COUNT(L.ID) as CountCities
FROM tCountry R JOIN tCity AS L ON L.CountryCode=R.Code
WHERE L.Population>=100000
GROUP BY  R.Continent, R.Region WITH CUBE
ORDER BY Continent

(49 rows affected)

Total execution time: 00:00:00.016

Continent	Region	CountCities
Africa	Central Africa	35
Africa	Eastern Africa	55
Africa	Northern Africa	90
Africa	Southern Africa	44
Africa	Western Africa	100
Africa	Continent Total	324
Asia	Continent Total	1597
Asia	Southern and Central Asia	498
Asia	Southeast Asia	254
Asia	Middle East	156
Asia	Eastern Asia	689
Europe	Baltic Countries	15
Europe	British Islands	66
Europe	Nordic Countries	27
Europe	Eastern Europe	325
Europe	Southern Europe	124
Europe	Western Europe	162
Europe	Continent Total	719
North America	Continent Total	490
North America	North America	290
North America	Caribbean	32
North America	Central America	168
Oceania	Australia and New Zealand	21
Oceania	Melanesia	1
Oceania	Continent Total	22
Region Total	South America	416
Region Total	Western Europe	162
Region Total	Continent Total	3568
Region Total	Western Africa	100
Region Total	Southern Europe	124
Region Total	Southern and Central Asia	498
Region Total	Melanesia	1
Region Total	Eastern Europe	325
Region Total	Nordic Countries	27
Region Total	Middle East	156
Region Total	North America	290
Region Total	Northern Africa	90
Region Total	Southeast Asia	254
Region Total	Southern Africa	44
Region Total	Australia and New Zealand	21
Region Total	Baltic Countries	15
Region Total	Caribbean	32
Region Total	British Islands	66
Region Total	Central America	168
Region Total	Central Africa	35
Region Total	Eastern Asia	689
Region Total	Eastern Africa	55
South America	South America	416
South America	Continent Total	416

--With ROLLUP
SELECT ISNULL(R.Continent,'Region Total')AS Continent, ISNULL(R.Region,'Continent Total') AS Region, COUNT(L.ID) as CountCities
FROM tCountry R JOIN tCity AS L ON L.CountryCode=R.Code
WHERE L.Population>=100000
GROUP BY  R.Continent, R.Region WITH ROLLUP
ORDER BY Continent

(28 rows affected)

Total execution time: 00:00:00.019

Continent	Region	CountCities
Africa	Central Africa	35
Africa	Eastern Africa	55
Africa	Northern Africa	90
Africa	Southern Africa	44
Africa	Western Africa	100
Africa	Continent Total	324
Asia	Eastern Asia	689
Asia	Middle East	156
Asia	Southeast Asia	254
Asia	Southern and Central Asia	498
Asia	Continent Total	1597
Europe	Baltic Countries	15
Europe	British Islands	66
Europe	Eastern Europe	325
Europe	Nordic Countries	27
Europe	Southern Europe	124
Europe	Western Europe	162
Europe	Continent Total	719
North America	Caribbean	32
North America	Central America	168
North America	North America	290
North America	Continent Total	490
Oceania	Australia and New Zealand	21
Oceania	Melanesia	1
Oceania	Continent Total	22
Region Total	Continent Total	3568
South America	South America	416
South America	Continent Total	416

--PIVOT TABLE - Kontinendi lõikes
SELECT  'Linnade Arv' as Continent,[Asia], [Europe], [Africa], [Oceania], [North America],[South America] --pais
FROM (SELECT Continent, L.ID 
        FROM tCountry as R INNER JOIN tCIty AS L ON L.COuntryCode=R.Code
        WHere L.Population >100000) AS Andmed --andmed
PIVOT (COUNT(ID) FOR 
    Continent IN ([Asia], [Europe], [Africa], [Oceania], [North America],[South America])) as Tabel

(1 row affected)

Total execution time: 00:00:00.058

Continent	Asia	Europe	Africa	Oceania	North America	South America
Linnade Arv	1595	711	324	22	490	416

--PIVOT TABLE - Kontinendi ja regioni lõikes
SELECT  Region 'Linnade Arv' ,[Asia], [Europe], [Africa], [Oceania], [North America],[South America] --pais
FROM (SELECT Region, Continent, L.ID 
        FROM tCountry as R INNER JOIN tCIty AS L ON L.COuntryCode=R.Code
        WHere L.Population >100000) AS Andmed --andmed
PIVOT (COUNT(ID) FOR 
    Continent IN ([Asia], [Europe], [Africa], [Oceania], [North America],[South America])) as Tabel

(21 rows affected)

Total execution time: 00:00:00.058

Linnade Arv	Asia	Europe	Africa	Oceania	North America	South America
Australia and New Zealand	0	0	0	21	0	0
Baltic Countries	0	9	0	0	0	0
British Islands	0	65	0	0	0	0
Caribbean	0	0	0	0	32	0
Central Africa	0	0	35	0	0	0
Central America	0	0	0	0	168	0
Eastern Africa	0	0	55	0	0	0
Eastern Asia	689	0	0	0	0	0
Eastern Europe	0	324	0	0	0	0
Melanesia	0	0	0	1	0	0
Middle East	156	0	0	0	0	0
Nordic Countries	0	27	0	0	0	0
North America	0	0	0	0	290	0
Northern Africa	0	0	90	0	0	0
South America	0	0	0	0	0	416
Southeast Asia	253	0	0	0	0	0
Southern Africa	0	0	44	0	0	0
Southern and Central Asia	497	0	0	0	0	0
Southern Europe	0	124	0	0	0	0
Western Africa	0	0	100	0	0	0
Western Europe	0	162	0	0	0	0