SQL Q&A1 | EntropyObserver

type

status

date

slug

summary

SELECT BASIC

1.Introduction

Observe the result of running this SQL command to show the name, continent and population of all countries.

Correct answer

name	continent	population
Afghanistan	Asia	25500100
Albania	Europe	2821977
Algeria	Africa	38700000
Andorra	Europe	76098
Angola	Africa	19183590
Antigua and Barbuda	Caribbean	86295
Argentina	South America	42669500
Armenia	Eurasia	3017400
Australia	Oceania	23545500
Austria	Europe	8504850
Azerbaijan	Asia	9477100
Bahamas	Caribbean	351461
Bahrain	Asia	1234571
Bangladesh	Asia	156557000
Barbados	Caribbean	285000
Belarus	Europe	9467000
Belgium	Europe	11198638
Belize	North America	349728
Benin	Africa	9988068
Bhutan	Asia	749090
Bolivia	South America	10027254
Bosnia and Herzegovina	Europe	3791622
Botswana	Africa	2024904
Brazil	South America	202794000
Brunei	Asia	393162
Bulgaria	Europe	7245677
Burkina Faso	Africa	17322796
Burundi	Africa	9420248
Cambodia	Asia	15184116
Cameroon	Africa	20386799
Canada	North America	35427524
Cape Verde	Africa	491875
CÃ´te d'Ivoire	Africa	23919000
Central African Republic	Africa	4709000
Chad	Africa	13211000
Chile	South America	17773000
China	Asia	1365370000
Colombia	South America	47662000
Comoros	Africa	743798
Congo, Democratic Republic of	Africa	69360000
Congo, Republic of	Africa	4559000
Costa Rica	North America	4667096
Croatia	Europe	4290612
Cuba	Caribbean	11167325
Cyprus	Asia	865878
Czech Republic	Europe	10517400
Denmark	Europe	5634437
Djibouti	Africa	886000
Dominica	Caribbean	71293
Dominican Republic	Caribbean	9445281

2.Large Countries

How to use WHERE to filter records. Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

Correct answer

name

Brazil

China

India

Indonesia

United States

3.Per capita GDP

Give the name and the per capita GDP for those countries with a population of at least 200 million.

name	gdp/population
Brazil	11115.2648
China	6121.7106
India	1504.7931
Indonesia	3482.0205
United States	51032.2945

4.South America In millions

Show the name and population in millions for the countries of the continent 'South America'. Divide the population by 1000000 to get population in millions.

name	population/1000000
Argentina	42.6695
Bolivia	10.0273
Brazil	202.7940
Chile	17.7730
Colombia	47.6620
Ecuador	15.7742
Guyana	0.7849
Paraguay	6.7834
Peru	30.4751
Saint Vincent and the Grenadines	0.1090
Suriname	0.5342
Uruguay	3.2863
Venezuela	28.9461

5.France, Germany, Italy

Show the name and population for France, Germany, Italy

name	population
France	65906000
Germany	80716000
Italy	60782668

6.United

Show the countries which have a name that includes the word 'United'

name

United Arab Emirates

United Kingdom

United States

7.Two ways to be big

Two ways to be big: A country is big if it has an area of more than 3 million sq km or it has a population of more than 250 million.

Show the countries that are big by area or big by population. Show name, population and area.

name	population	area
Australia	23545500	7692024
Brazil	202794000	8515767
Canada	35427524	9984670
China	1365370000	9596961
India	1246160000	3166414
Indonesia	252164800	1904569
Russia	146000000	17125242
United States	318320000	9826675

8.One or the other (but not both)

Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.

Australia has a big area but a small population, it should be included.

Indonesia has a big population but a small area, it should be included.

China has a big population and big area, it should be excluded.

United Kingdom has a small population and a small area, it should be excluded.

name	population	area
Australia	23545500	7692024
Brazil	202794000	8515767
Canada	35427524	9984670
Indonesia	252164800	1904569
Russia	146000000	17125242

9.Rounding

Show the name and population in millions and the GDP in billions for the countries of the continent 'South America'. Use the ROUND function to show the values to two decimal places.

For Americas show population in millions and GDP in billions both to 2 decimal places.

Millions and billions

Missing decimals

name	population_millions	gdp_billions
Argentina	42.67	477.03
Bolivia	10.03	27.04
Brazil	202.79	2254.11
Chile	17.77	268.31
Colombia	47.66	369.81
Ecuador	15.77	87.50
Guyana	0.78	2.85
Paraguay	6.78	25.94
Peru	30.48	204.68
Saint Vincent and the Grenadines	0.11	0.69
Suriname	0.53	5.01
Uruguay	3.29	49.92
Venezuela	28.95	382.42

10.Trillion dollar economies

Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.

Show per-capita GDP for the trillion dollar countries to the nearest $1000.

name	per_capita
Australia	66000
Brazil	11000
Canada	45000
China	6000
France	40000
Germany	42000
India	2000
Italy	33000
Japan	47000
Mexico	10000
Russia	14000
South Korea	22000
Spain	28000
United Kingdom	39000
United States	51000

11.Name and capital have the same length

Greece has capital Athens.

Each of the strings 'Greece', and 'Athens' has 6 characters.

Show the name and capital where the name and the capital have the same number of characters.

You can use the LENGTH function to find the number of characters in a string

For Microsoft SQL Server the function LENGTH is LEN

name	capital
Algeria	Algiers
Angola	Luanda
Armenia	Yerevan
Botswana	Gaborone
Canada	Ottowa
Djibouti	Djibouti
Egypt	Cairo
Estonia	Tallinn
Fiji	Suva
Gambia	Banjul
Georgia	Tbilisi
Ghana	Accra
Greece	Athens
Luxembourg	Luxembourg
Mauritania	Nouakchott
Peru	Lima
Poland	Warsaw
Russia	Moscow
Rwanda	Kigali
San Marino	San Marino
Singapore	Singapore
Taiwan	Taipei
Turkey	Ankara
Zambia	Lusaka

12.Matching name and capital

The capital of Sweden is Stockholm. Both words start with the letter 'S'.

Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.

You can use the function LEFT to isolate the first character.

You can use <> as the NOT EQUALS operator.

name	capital
Algeria	Algiers
Andorra	Andorra la Vella
Barbados	Bridgetown
Belize	Belmopan
Brazil	BrasÃlia
Brunei	Bandar Seri Begawan
Burundi	Bujumbura
Guatemala	Guatemala City
Guyana	Georgetown
Kuwait	Kuwait City
Maldives	MalÃ©
Marshall Islands	Majuro
Mexico	Mexico City
Monaco	Monaco-Ville
Mozambique	Maputo
Niger	Niamey
Panama	Panama City
Papua New Guinea	Port Moresby
Sao TomÃ© and PrÃncipe	SÃ£o TomÃ©
South Korea	Seoul
Sri Lanka	Sri Jayawardenepura Kotte
Sweden	Stockholm
Taiwan	Taipei
Tunisia	Tunis

13.All the vowels

Equatorial Guinea and Dominican Republic have all of the vowels (a e i o u) in the name. They don't count because they have more than one word in the name.

Find the country that has all the vowels and no spaces in its name.