Question

Write SQLQuery for:

1. Find names and area of regions with area more than the total area of region 'Middle East' for 2005. (Hint: Use group by clause)
2. Find the region with largest population. Do not use MAX. You will need SUM to compute region totals. You need to return both region and its population. (Hint: use self join)

SQL TABLE:

CREATE TABLE country (
name char(50),
year char(4),
region char(60),
area decimal(10),
population decimal(11),
gdp number,
primary key (name,year)
);

INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Afghanistan',1989,'Asia',647500,14825013,0.0);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Afghanistan',1990,'Asia',647500,15862293,0.0);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Afghanistan',1991,'Asia',647500,16450304,0.0);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Afghanistan',1992,'Asia',647500,16095664,0.0);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',1989,'Middle East',620,496759,0.0);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',1990,'Middle East',620,520186,0.0);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',1991,'Middle East',620,536974,0.025);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',1992,'Middle East',620,551513,0.067);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',1993,'Middle East',620,NULL,0.03);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',1994,'Middle East',620,585683,0.04);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',1995,'Middle East',620,575925,0.022);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',1996,'Middle East',620,590042,-0.02);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',1997,'Middle East',620,603318,0.03);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',2005,'Middle East',620,688345,0.056);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',2006,'Middle East',620,698585,0.059);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',2007,'Middle East',620,708573,0.078);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',2008,'Middle East',620,708573,0.066);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',2009,'Middle East',620,727785,0.061);
INSERT INTO country (name,year,region,area,population,gdp) VALUES ('Bahrain',2010,'Middle East',620,738004,0.031);

Answer 1

1. Find names and area of regions with area more than the total area of region 'Middle East' for 2005. (Hint: Use group by clause)

select name, region from country where area>=(select sum(area) from country where region="Middle East" and year= 2005 ) group by name, region;

2.Find the region with largest population. Do not use MAX. You need to return both region and its population. (Hint: use self join)

Select region, population from country where population IN (
SELECT population
FROM country
WHERE population NOT IN (
SELECT Smaller.population
FROM country AS Larger
JOIN country AS Smaller ON Smaller.population < Larger.population
))