Lazy loaded image
Technology
Lazy loaded imageSQL Q&A1
Words 706Read Time 2 min
May 8, 2020
May 16, 2025
type
status
date
slug
summary
tags
category
icon
password

SELECT BASIC

 
notion image

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.
  • You can use the phrase name NOT LIKE '%a%' to exclude characters from your results.
  • The query shown misses countries like Bahamas and Belarus because they contain at least one 'a'
name

Mozambique

 
上一篇
SQL Window Function
下一篇
SQL Q&A2 Nobel Tutorial