Answer to Question #83645 in Databases | SQL | Oracle | MS Access for Juan

Question

Name Region Area Population GDP
Afghanistan South Asia 652225 260000000
Albania Europe 28728 3200000 6656000000
Algeria Middle East 2400000 32900000 75012000000
Andorra Europe 468 64000
Bangladesh South Asia 143998 152600000 67144000000
United Kingdom Europe 242514 59600000 2022824000

Now based on the above table

a) Write a query that shows the name, region and population of the smallest country in each region.

b) Select the code that shows the countries with population smaller than Russia but bigger than Denmark

Answer 1

As the table name hadn't provided lets assume that it's 'table' name. As there some missed values in example table then we assume that it's null values possible in provided data

Answers:

# a) 
SELECT t1.name, t1.region, t1.population
FROM table t1
INNER JOIN
(SELECT region, min(area) FROM table WHERE area is not null GROUP BY region) t2 ON t1.region = t2.region and t1.area = t2.area
#b) 
SELECT name FROM table
WHERE population < (SELECT population FROM table WHERE name = 'Russia') AND population > (SELECT population FROM table WHERE name = 'Denmark') AND population is not null