Question

1. if you do not have the world schema you can obtain the scripts to create the database schema from https://dev.mysql.com/doc/index-other.html

Queries

1. Write a SQL query that shows all the columns in the country table for the North America region. The results need to be sorted in ascending order by population.

2. Write a SQL query that shows the unique regions in the country table except for the regions from the continent of Oceania. The results need to be sorted in ascending order by region.

3. Write a SQL query that shows the unique language from the Countrylanguage table where the percentage is between 1.0 and 5.0.

4. Write a SQL query that shows the Name, Continent, Surface Area, LocalName where the LocalName ends with the word ‘Island’ and the surface area is greater than 100.

5. Write a SQL query that shows the country name of countries that have missing values for IndepYear and GNPOld.

6. Write a SQL query that shows the maximum population with an alias of Maxpopulation and minimum population with an alias of MinPopulation for the North America region.

7. Write a SQL query that shows each region once and the total number of countries in the region with an alias of TotalCountries and the total population with an alias of TotalPopulation in that region. The results should be sorted in ascending order by region.

8. Write a SQL query that shows the country name once and shows the number of cities in that country. The number of cities in the country is from the City table and should have an alias of NumberofCity.

9. Write a SQL subquery that shows the unique region names where the languages spoken are English, French or German.

10. Write a SQL query that shows the country name, region, language for the Caribbean region.

11. Write a SQL query that shows the country name and number of official languages with an alias NumberOfficial. Please note the condition is asking for the count where IsOfficial is true. Also, the results need to be sorteed by number of oficial language(NumberOficial) in descending order.

12. Write a SQL query that shows the Country Name and number of official language with alias NumberOfficial. Plese note the condition is asking for the count where IsOfficial is true.Also, the results should show only if the count number of official languages is greater than 2. The results need to be sorted by number of official language(NumberOffcial) in descending order.

Answer 1

Answered 1-7 SQL queries

1.Write a SQL query that shows all the columns in the country table for the North America region. The results need to be sorted in ascending order by population.

SELECT *FROM country WHERE continent = 'North America' ORDER BY population ASC;

2.Write a SQL query that shows the unique regions in the country table except for the regions from the continent of Oceania. The results need to be sorted in ascending order by region.

SELECT * FROM country ORDER BY region ASC

EXCEPT

SELECT * FROM country where continent = 'Oceania';

3.Write a SQL query that shows the unique language from the Countrylanguage table where the percentage is between 1.0 and 5.0.

SELECT DISTINCT language from countrylanguages where percentage BETWEEN 1.0 AND 5.0;

4.Write a SQL query that shows the Name, Continent, Surface Area, LocalName where the LocalName ends with the word ‘Island’ and the surface area is greater than 100.

SELECT name,continent, surface_area,local_name from country where local_name LIKE 'Island%' AND surface_area>100;

5.Write a SQL query that shows the country name of countries that have missing values for IndepYear and GNPOld.

SELECT name FROM country where independence_year IS NULL OR gnp_old IS NULL;

6.Write a SQL query that shows the maximum population with an alias of Maxpopulation and minimum population with an alias of MinPopulation for the North America region.

SELECT Max(population) as Maxpopulation, Min(population) as Minpopulation from country where continent = 'North America';

7.Write a SQL query that shows each region once and the total number of countries in the region with an alias of TotalCountries and the total population with an alias of TotalPopulation in that region. The results should be sorted in ascending order by region.

SELECT region, Count(*) as TotalCountries, Sum(population) as TotalPopulation GROUP BY region ASC;