Примеры запросов к базе данных Всемирного банка

wb-data-example.sql

/*

PART 2: BASIC FUNCTIONS

*/

-- смотрим все колонки и все строки
SELECT  * 
FROM IndicatorsSmall;

-- смотрим все колонки и первые 10 строк
SELECT * 
FROM IndicatorsSmall 
LIMIT 10;

-- смотрим IndicatorName и первые 10 строк
SELECT IndicatorName 
FROM IndicatorsSmall 
LIMIT 10;

---- WHERE
-- смотрим Россию 
SELECT * 
FROM IndicatorsSmall 
WHERE Countrycode = 'RUS' ;

-- смотрим все в 2012 году
SELECT * 
FROM IndicatorsSmall 
WHERE Year = 2012;

---- AND
-- смотрим все в 2012 году в России
SELECT *
FROM IndicatorsSmall 
WHERE
	Year = 2012 
	-- ATTENTION -- AND -- добавляем 2ое условие в WHERE
	AND 
	Countrycode = 'RUS' ;
	

---- OR
-- у оператора AND приоритет в выполнении, поэтому берем OR в скобки
-- смотрим CO2 emissions.... в 2012 году в России и Франции
SELECT CountryName,IndicatorName, Year, Value 
FROM IndicatorsSmall 
WHERE 
	Year = 2012 
	-- ATTENTION -- OR
	AND (Countrycode = 'RUS'  OR  Countrycode = 'FRA' )
	AND IndicatorName = 'CO2 emissions from residential buildings and commercial and public services (% of total fuel combustion)'
;


---- NOT
-- смотрим CO2 emissions.... в 2012 году у всех кроме России
SELECT CountryName,IndicatorName, Year, Value 
FROM IndicatorsSmall 
WHERE 
		-- ATTENTION -- NOT -- выбираем всё кроме России
	NOT Countrycode = 'RUS'  
	AND Year = 2012
	AND IndicatorName = 'CO2 emissions from residential buildings and commercial and public services (% of total fuel combustion)'
;

	
-- IN
-- смотрим CO2 emissions.... в 2012 году в России, Франции, Венгрии
SELECT CountryName,IndicatorName, Year, Value 
FROM IndicatorsSmall 
WHERE 
	-- ATTENTION -- IN 
	Countrycode IN ('RUS','FRA','HUN')  
	-- берем страны, чей Countrycode лежит в списке ('RUS','FRA','HUN')
	-- второй вариант:
	-- (Countrycode = 'RUS'  OR  Countrycode = 'FRA' OR  Countrycode = 'HUN' )
	AND Year = 2012
	AND IndicatorName = 'CO2 emissions from residential buildings and commercial and public services (% of total fuel combustion)'
;

-- BETWEEN
SELECT CountryName,IndicatorName, Year, Value 
FROM IndicatorsSmall 
WHERE
	IndicatorName = 'Gross savings (% of GDP)'
	-- ATTENTION -- BETWEEN
	-- берем значения IndicatorName от 20 до 50 включительно
	-- значения из колонки Value
	AND Value BETWEEN 20 and 50
	and Year =2012
;

-- LIKE и немного регулярных выражений для поиска части букв в строках
-- ищем страны, где есть th в названии (ShortName)
SELECT ShortName
FROM Country
WHERE 
	ShortName LIKE '%th%'
;
-- ищем страны, где c окончанием ia
SELECT ShortName
FROM Country
WHERE 
	ShortName LIKE '%ia'
;
-- ищем страны, где c префиксом ca
SELECT ShortName
FROM Country
WHERE 
	ShortName LIKE 'ca%'
;
-- ищем страны, где c содержанием Russia
SELECT ShortName
FROM Country
WHERE
	ShortName like 'Russia'
;


-- ORDER BY
-- сортировка

-- берем определенные колонки
SELECT Countrycode, IndicatorName, Year, Value 
-- из этой таблицы
FROM IndicatorsSmall 
WHERE 
	-- берем часть стран
	Countrycode in ('RUS','FRA','HUN')  
	-- берем один индикатор
	and IndicatorName = 'CO2 emissions from residential buildings and commercial and public services (% of total fuel combustion)'
-- ATTENTION -- сортируем
ORDER BY 
	-- сортируем по году по возрастанию
	Year ASC, 
	-- ПОТОМ сортируем по Countrycode по убыванию
	Countrycode DESC
;


-- Функции
-- ищем диапазон лет
SELECT 
	-- min(Year) – считаем минимальный год
	-- AS min_year – называем столбец в мини-табличке
	min(Year) AS min_year, 
	max(Year)  AS max_year
FROM IndicatorsSmall
;

-- DISTINCT берет уникальные значения
SELECT 
	COUNT(DISTINCT Countrycode)  as number_of_countries
from IndicatorsSmall
;

-- GROUP BY
-- группируем данные по странам и году
SELECT
	-- берем CountryName и Year как индексы
	-- потом по ним сгруппируем
	CountryName, Year, 
	-- смотрим, какое количество индикаторов есть в каждой стране и в каждом году
	count(IndicatorName) AS cnt_IndicatorName
-- из какой таблицы мы всё берем
FROM IndicatorsSmall
-- ATTENTION -- группировка в конце запроса
GROUP
	-- группируем по Countrycode и Year
	BY Countrycode, Year
;

-- HAVING
-- оператор для фильтрации группировок
SELECT 
	CountryName, Year, 
	count(IndicatorName) as cnt_IndicatorName
FROM IndicatorsSmall
GROUP BY 
	Countrycode, Year
-- ATTENTION -- фильтре в конце запроса после группировки
HAVING 
	-- делаем фильтр, чтобы были только те страны, где больше 500 индикатров
	cnt_IndicatorName >=500
;

-- CASE + WHEN THEN
SELECT 
	Year,
	-- ATTENTION -- CASE
	-- создаем новый временный столбец под названием Century
	CASE
		-- тут пишем, что мы хотим написать в во временном столбце
		WHEN Year<=2000 THEN 'XX'
		WHEN Year>2000 THEN 'XXI'
	-- тут пишем название временного столбца 
	END AS Century
from IndicatorsSmall
GROUP by Year
;


-- Подзапросы (двойные запросы)
-- смотрим сначала первый подзапрос, по которому будет фильтрация
-- потом смотрим второй

---------- тут второй подзапрос
-- берем полные названия стран из 2ой таблицы – Country
SELECT LongName 
FROM Country
-- пишем условие, по которому берем страны из 1го подзапроса, пользуемся IN
WHERE Countrycode IN (
	-------- тут первый запрос
	-- мы делаем запрос по 1ой таблице - IndicatorsSmall
	-- берем странны с ВВП на душу населения в 2011 больше 50к
	SELECT Countrycode
	-- смотрим индикаторы стран
	FROM IndicatorsSmall 
	-- фильтруем страны по индикаторам
	WHERE 
		-- берем только ВВП на душу населения
		IndicatorName like '%GDP per capita (current US%'
		-- берем страны с большим ВВП
		AND Value > 50000
		-- берем один года
		AND Year = 2011
	)
;