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 subquery that shows the unique region names where the languages spoken are English, French or German.

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

3. 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.

4. 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. The results need to be sorted by number of official languages (NumberOfficial) in descending order.

5. 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 should show only if the count of the number of official languages is greater than 2. The results need to be sorted by number of official languages (NumberOfficial) in descending order.

Answer 1

Query

/*1. Write a SQL subquery that shows the unique region names where the languages spoken are English, French or German.*/
Select Name from country where Code in(Select CountryCode from countrylanguage where Language in('English','French','German'));

/* 2. Write a SQL query that shows the country name, region, language for the Caribbean region*/
   Select country.Name,region,GROUP_CONCAT( Language ) as "Language"from country inner join countrylanguage on country.Code=countrylanguage.CountryCode where region='Caribbean' group by country.Name;

/* 3. 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.*/
   Select country.Name,GROUP_CONCAT(City.Name) as "City" from country inner join City on country.Code=City.CountryCode group by country.Name;

/*4. 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. The results need to be sorted by number of official languages (NumberOfficial) in descending order.*/
Select country.Name,count(*)"NumberOfficial" from country inner join countrylanguage on countrylanguage.CountryCode=country.Code where IsOfficial='T' group by country.Name;

/* 5. 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 should show only if the count of the number of official languages is greater than 2. The results need to be sorted by number of official languages (NumberOfficial) in descending order.*/
Select country.Name,count(*)"NumberOfficial" from country inner join countrylanguage on countrylanguage.CountryCode=country.Code where IsOfficial='T' group by country.Name having count(*)>2 order by NumberOfficial desc;

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........