Select | Nitin Patil

Select first 5 records of table

SELECT * FROM world LIMIT 5

	name	continent	area	population	gdp	capital	tld	flag
0	Afghanistan	Asia	652230	25500100	20364000000	Kabul	.af	//upload.wikimedia.org/wikipedia/commons/a/a8/...
1	Albania	Europe	28748	2821977	12044000000	Tirana	.al	//upload.wikimedia.org/wikipedia/commons/3/36/...
2	Algeria	Africa	2381741	38700000	207021000000	Algiers	.dz	//upload.wikimedia.org/wikipedia/commons/7/77/...
3	Andorra	Europe	468	76098	3222000000	Andorra la Vella	.ad	//upload.wikimedia.org/wikipedia/commons/1/19/...
4	Angola	Africa	1246700	19183590	116308000000	Luanda	.ao	//upload.wikimedia.org/wikipedia/commons/9/9d/...

Get countries of Eurasia continent Using WHERE

SELECT * 
FROM   world 
WHERE  continent = 'Eurasia'

	name	continent	area	population	gdp	capital	tld	flag
0	Armenia	Eurasia	29743	3017400	9950000000	Yerevan	.am	//upload.wikimedia.org/wikipedia/commons/2/2f/...
1	Russia	Eurasia	17125242	146000000	2029812000000	Moscow	.ru	//upload.wikimedia.org/wikipedia/commons/f/f3/...

Select records using IN to tell list of mutiple countries we are interested in.

SELECT name, 
       population 
FROM   world 
WHERE  name IN ( 'Brazil', 'United States' );

	name	population
0	Brazil	202794000
1	United States	318320000

BETWEEN allows range checking (range specified is inclusive of boundary values).

SELECT name, 
       population 
FROM   world 
WHERE  population BETWEEN 100000000 AND 150000000;

	name	population
0	Japan	127090000
1	Mexico	119713203
2	Russia	146000000

LIKE operator allow us to provide wildcard entries. - % - The percent sign represents zero, one, or multiple characters - _ - The underscore represents a single character

SELECT name 
FROM   world 
WHERE  name LIKE ( 'United%' )

	name
0	United Arab Emirates
1	United Kingdom
2	United States

Multiple conditions

SELECT name, 
       gdp 
FROM   world 
WHERE  continent = 'Asia' 
       AND gdp > 1875213000000

	name	gdp
0	China	8358400000000
1	Japan	5960180000000

We can use the comparision operator = > < >= <= !=
We can use AND &&, OR ||, NOT ! to combine multiple condition in WHERE condition.

Select within Select

Show the countries in Europe with a per capita GDP greater than ‘United Kingdom’.

SELECT name 
FROM   world 
WHERE  continent = 'Europe' 
       AND gdp / population > (SELECT gdp / population 
                               FROM   world 
                               WHERE  name = 'United Kingdom')

	name
0	Andorra
1	Austria
2	Belgium
3	Denmark
4	Finland
5	France
6	Germany
7	Iceland
8	Ireland
9	Liechtenstein
10	Luxembourg
11	Monaco
12	Netherlands
13	Norway
14	San Marino
15	Sweden
16	Switzerland

List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

SELECT name, 
       continent 
FROM   world 
WHERE  continent IN (SELECT continent 
                     FROM   world 
                     WHERE  name IN( 'Argentina', 'Australia' )) 
ORDER  BY name

	name	continent
0	Argentina	South America
1	Australia	Oceania
2	Bolivia	South America
3	Brazil	South America
4	Chile	South America
5	Colombia	South America
6	Ecuador	South America
7	Fiji	Oceania
8	Guyana	South America
9	Kiribati	Oceania
10	Marshall Islands	Oceania
11	Micronesia, Federated States of	Oceania
12	Nauru	Oceania
13	New Zealand	Oceania
14	Palau	Oceania
15	Papua New Guinea	Oceania
16	Paraguay	South America
17	Peru	South America
18	Saint Vincent and the Grenadines	South America
19	Samoa	Oceania
20	Solomon Islands	Oceania
21	Suriname	South America
22	Tonga	Oceania
23	Tuvalu	Oceania
24	Uruguay	South America
25	Vanuatu	Oceania
26	Venezuela	South America

Which country has a population that is more than Canada but less than Poland? Show the name and the population.

SELECT name, 
       population 
FROM   world 
WHERE  population BETWEEN (SELECT population 
                           FROM   world 
                           WHERE  name = 'Canada') + 1
                           AND (SELECT population 
                                   FROM   world 
                                   WHERE  name = 'Poland') - 1

As BETWEEN range is inclusive of boundary values, adjusted range to get desired output.

	name	population
0	Iraq	36004552
1	Sudan	37289406

Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany. Show the name and the population of big ten countries in Europe. Show the population as a percentage of the population of Germany.

SELECT name, 
       ROUND(100 * population / (SELECT population 
                                 FROM   world 
                                 WHERE  name = 'Germany')) AS percetile_pop 
FROM   world 
WHERE  continent = 'Europe' 
ORDER  BY percetile_pop DESC 
LIMIT  10

	name	percetile_pop
0	Germany	100
1	France	82
2	United Kingdom	79
3	Italy	75
4	Spain	58
5	Ukraine	53
6	Poland	48
7	Romania	25
8	Netherlands	21
9	Greece	14

Which countries have a GDP greater than every country in Europe? (Some countries may have NULL gdp values)

SELECT name 
FROM   world 
WHERE  gdp > ALL (SELECT gdp 
                  FROM   world 
                  WHERE  continent = 'Europe' 
                         AND gdp > 0)

	name
0	China
1	Japan
2	United States

ALL allow to use >= or > or < or <= to act over a list.

Find the largest country (by area) in each continent, show the continent, the name and the area

SELECT continent, 
       name, 
       area 
FROM   world x 
WHERE  area >= ALL (SELECT area 
                    FROM   world y 
                    WHERE  y.continent = x.continent 
                           AND area > 0)

	continent	name	area
0	Africa	Algeria	2381741
1	Oceania	Australia	7692024
2	South America	Brazil	8515767
3	North America	Canada	9984670
4	Asia	China	9596961
5	Caribbean	Cuba	109884
6	Europe	France	640679
7	Eurasia	Russia	17125242

List each continent and the name of the country that comes first alphabetically.

We can refer outer SELECT values within inside inner SELECT. Here we have named the outer table as x and inner as y, in order to compare their values.

The inner SELECT gives list of area of a continent countries. Using ALL outer SELECT find out the country with largest area in a continent.

Using ALL

SELECT continent, 
       name 
FROM   world x 
WHERE  name <= ALL (SELECT name 
                    FROM   world y 
                    WHERE  x.continent = y.continent)

	continent	name
0	Asia	Afghanistan
1	Europe	Albania
2	Africa	Algeria
3	Caribbean	Antigua and Barbuda
4	South America	Argentina
5	Eurasia	Armenia
6	Oceania	Australia
7	North America	Belize

Using GROUP BY and MIN

SELECT continent, 
       MIN(name) 
FROM   world 
GROUP  BY continent 
ORDER  BY continent;

	continent	MIN(name)
0	Africa	Algeria
1	Asia	Afghanistan
2	Caribbean	Antigua and Barbuda
3	Eurasia	Armenia
4	Europe	Albania
5	North America	Belize
6	Oceania	Australia
7	South America	Argentina

Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

SELECT name, 
       continent, 
       population 
FROM   world x 
WHERE  25000000 >= ALL (SELECT population 
                        FROM   world y 
                        WHERE  x.continent = y.continent)

	name	continent	population
0	Antigua and Barbuda	Caribbean	86295
1	Australia	Oceania	23545500
2	Bahamas	Caribbean	351461
3	Barbados	Caribbean	285000
4	Cuba	Caribbean	11167325
5	Dominica	Caribbean	71293
6	Dominican Republic	Caribbean	9445281
7	Fiji	Oceania	858038
8	Grenada	Caribbean	103328
9	Haiti	Caribbean	10413211
10	Jamaica	Caribbean	2717991
11	Kiribati	Oceania	106461
12	Marshall Islands	Oceania	56086
13	Micronesia, Federated States of	Oceania	101351
14	Nauru	Oceania	9945
15	New Zealand	Oceania	4538520
16	Palau	Oceania	20901
17	Papua New Guinea	Oceania	7398500
18	Saint Lucia	Caribbean	180000
19	Samoa	Oceania	187820
20	Solomon Islands	Oceania	581344
21	Tonga	Oceania	103036
22	Trinidad and Tobago	Caribbean	1328019
23	Tuvalu	Oceania	11323
24	Vanuatu	Oceania	264652

Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

SELECT name, 
       continent 
FROM   world x 
WHERE  population > ALL (SELECT population * 3 
                         FROM   world y 
                         WHERE  x.continent = y.continent 
                                AND x.name != y.name)

	name	continent
0	Australia	Oceania
1	Brazil	South America
2	Russia	Eurasia

Select within Select

References