=======================================================================================
(1) write a query, Find out countries which have 5 and more cities
	Output country names and number of cities.
=======================================================================================
select
  *
from
(
    select 
       Name,
       cnt_city
    from	
            (select
              count(name) cnt_city,
              countrycode
            from city
            group by countrycode) a 
    inner join country on a.countrycode=country.code
) b
where cnt_city > 5;

PASS

Suggestion: 
	putting "having cnt_city > 5" inside () a will make it smaller and improve performance
	
select
  *
from
(
    select 
	   Name,
	   cnt_city
    from	
    (
        select
            count(name) cnt_city,
            countrycode
        from city
        group by countrycode
        having cnt_city > 5
    ) a 
    inner join country on a.countrycode=country.code
) b
 
=============================================================================================================
(2) Write a query: Find out countries and for each country, its mostly used official langauge.
	Output country name, and language name found.
=============================================================================================================
select
   name Country,
   language 'Mostly Sopken Language'
from
(
    select
	   language,
	   countrycode
    from
    (
        select 
          countrycode cocode,
          max(percentage) max_per
        from countrylanguage
        where isofficial = 'T'
        group by countrycode
    ) a
    inner join countrylanguage on a.cocode=countrylanguage.countrycode and a.max_per=countrylanguage.percentage
    -- inner join on multiple conditions
) b
inner join country on b.countrycode=country.code;

PASS

=============================================================================================================
(3) Write a query: Find out each Language and number of countries which speak the language.
	Output language name and number of countries.
=============================================================================================================
select
   language,
   count(countrycode) 'Number of Countries'
from countrylanguage
group by language;

PASS



=============================================================================================================
(4) Using MySql Workbench UI features, Create a new schema, then under it, create two tables: 

    a. table: employee 
	   Columns: id, firstname, lastname, birthday, dept_id.
	     
    b. table: department
	   Clumns: id, name, building_no, manager_id
	   
	Please create primary keys, and create foreign key in employee table pointing to 
	dept table.
=============================================================================================================
(5) 
Choose top 5 populated countries which have 20 and less cities, and at least have two languages.
	
	Output countries' names, population and number of cities. 
=============================================================================================================
select 
  *
from
(
	select
	  name,
	  population,
	  cnt_city
	from
	(
		select 
		   countrycode,
		   cnt_city
		from
		(
			select
			  countrycode cocode,
			  count(name) cnt_city
			from city
			group by countrycode
			having cnt_city <= 20
                ) a 
                inner join
                (
			select
			  countrycode,
			  count(language) cnt_lan
			from countrylanguage
			group by countrycode
			having cnt_lan >= 2
                ) b
		on a.cocode = b.countrycode
	) c 
	inner join country on c.countrycode=country.code
)d
order by population desc
limit 5; -- select top cannot be used


PASS

=============================================================================================================
(6)
 Output each region name and number of languages being spoken in each region.
=============================================================================================================

select
  region,
  count(language) cnt_lang
from
(    
    select distinct
	  region,
	  language
	from country a
	inner join countrylanguage b
	on a.code = b.countrycode
	where region is not null and region <> ''
) c
group by region


------------------------------------------------------
-- If the question asks for city.district
------------------------------------------------------

select
  district,
  count(language) cnt_lang
from
(    
    select distinct
	  district,
	  language
	from city a
	inner join countrylanguage b
	on a.countrycode = b.countrycode
	where district is not null and district <> ''
) c
group by district

-- count all the official language for each district
select
  district,
  count(language) cnt_lang
from
(    
    select distinct
	  district,
	  language
	from city a
	inner join 
    (
		select
		  *
		from countrylanguage
		where isofficial = 'T'
     ) b    
	on a.countrycode = b.countrycode
	where district is not null and district <> ''
) c
group by district

PASS

=============================================================================================================
(7) Find out those countries which population is smaller than the summary of population of all their own cities.
    Output countries' names, population and summary of all thier own cities's population.
=============================================================================================================
select
   name,
   population,
   city_popu
from   
(    
    select
	   countrycode,
	   sum(population) city_popu
	from  
	(   
		select
		   countrycode,
		   population
		from city
		where name is not null
	) a
	group by countrycode
) b
inner join country c
on b.countrycode = c.code
having population < city_popu;   

PASS

Suggestion: 
	(1) This query is not necessary at all.
	
	select
		   countrycode,
		   population
		from city
		where name is not null
	(2) Change "having" to "where"	
		
	
select
   name,
   population,
   city_popu
from   
(    
    select
	   countrycode,
	   sum(population) city_popu
	from city	
	group by countrycode
) b
inner join country c
on b.countrycode = c.code
where population < city_popu;   

=============================================================================================================
(8) Find out top 10 spoken languages (based on population ) around the world
=============================================================================================================

select
   language
from
(    
    select 
	  countrycode,
	  language,
	  percentage/100*population popu_lan
	from countrylanguage a
	inner join country b
	on a.countrycode = b.code
) c
group by language
order by sum(popu_lan) desc
limit 10;

pass


=============================================================================================================
(9) Output top 20 biggest surface area countries and their own total number of cities.
=============================================================================================================

select
   country,
   count(b.name) cnt_city
from
(    
    select 
       name country,
	   code,
	   surfacearea
	from country
	order by SurfaceArea desc
	limit 20
) a
inner join city b
on a.code = b.countrycode
group by country;

pass

=============================================================================================================
(10) Output region name and their average life expectation among countries in each region.
=============================================================================================================

select
   region,
   avg(LifeExpectancy) avg_life
from country
where LifeExpectancy is not null
group by region;

pass

=============================================================================================================
(11) Find out country code in country table not being used in city table.
=============================================================================================================

select
   code
from country
left join city
on country.code = city.countrycode
where countrycode is null;

PASS

=============================================================================================================
(12) Write a query to verify the foreign key in city is indeed applied.
=============================================================================================================

select
   countrycode,
   Code
from city a
left join country b
on a.countrycode = b.code
where code is null;

pass

=============================================================================================================
(13) Find out all un-ooficial languages which is in at least one country but is official in at least another country.
=============================================================================================================

select distinct
   off_lan
from
(    
    select 
	   language un_off_lan
	from countrylanguage
	where isofficial = 'F'
) a
inner join
(
	select
	   language off_lan
	from countrylanguage
	where isofficial = 'T'
) b
on a.un_off_lan = b.off_lan;

pass

=============================================================================================================
(14) Find out all languages which are used in all continents.
=============================================================================================================

select
   language,
   continent
from countrylanguage a
inner join country b
on a.countrycode = b.code;

FAIL

Suggestion:

(1) from below query we know there countrycodes which is in country table but not in countrylanguage table
 five of them are in continent: Antarctica.
 

select * from country 
where code in
(

select 
	country.code
from country
left join countrylanguage
on country.code = countrylanguage.countrycode
where countrylanguage.countrycode is null
)

(2) From below query we know none of any records from countrylanguage table 
belongs to continent: Antarctica


select 
	country.code
from country
inner join countrylanguage
on country.code = countrylanguage.countrycode
where country.continent = 'Antarctica'

Final answer: There is no language which is spoken in all continents.

Suggestion for one query for final:

select distinct b.language
from
(
	select /* count( distinct continent ) */ 6 total_cnt_continent from country
) a
inner join
(
     select language, count(*) cnt_continent
     from
     (
		 select distinct language, continent
		 from country a inner join countrylanguage b
		 on a.code = b.countrycode
     ) a
     group by language
) b
on a.total_cnt_continent = b.cnt_continent





=============================================================================================================
(15) Find out all languages which have no official language status in any countries.
=============================================================================================================

select distinct
   un_off_lan
from
(    
    select 
	   language un_off_lan
	from countrylanguage
	where isofficial = 'F'
) a
left join
(
	select
	   language off_lan
	from countrylanguage
	where isofficial = 'T'
) b
on a.un_off_lan = b.off_lan
where off_lan is null;

pass

=============================================================================================================
(16) Is any region which is crossing any two or even three different continents?
=============================================================================================================

select
  region,
  count(continent) cnt_con
from country
group by region
having cnt_con > 1;


FAIL

Suggestion: The answer is NONE

select 
	region,
    count(*) cnt_continent
from
(
select
  distinct region, continent
from country
) a
group by region
having cnt_continent > 1

=============================================================================================================
(17) How many different types of government forms around the world and which one is the most populous?
=============================================================================================================

select 
  GovernmentForm,
  sum(population) total_population
from country
group by governmentform
order by total_population desc
-- limit 1;

pass

=============================================================================================================
(18) Which government form manage the most population?
=============================================================================================================

select 
  GovernmentForm,
  sum(population) sum_popu
from country
group by governmentform
order by sum_popu desc
limit 1;

pass

=============================================================================================================
(19) Give the the number of total population which is in countries of top 5 highest life expectation. 
=============================================================================================================

select
   name,
   population
from country
order by LifeExpectancy desc
limit 5;

pass

=============================================================================================================
(20) Give the total number of districts in each country.
=============================================================================================================

select
  b.name,
  count(district) cnt_dis
from city a
inner join country b
on a.countrycode = b.code
group by b.name;

FAIL

Suggestion:

select 
	a.*,
	b.name
from
(
	select 
		countrycode,
		count(*) cnt_district
	from
	(
		select 
			distinct countrycode , district 
		from city 
	) a 
	group by countrycode
) a
inner join country b
on a.countrycode = b.code
order by name


=============================================================================================================
(21) Create a function to output "Small" when input population is less than 50000, "Medium" when population is between 50000 and 1000000,
    and "large" when population is greater than 1000000. Then create query output country names, population, and the function output.
=============================================================================================================

select
   name,
   population,
   case when population < 50000 then 'Small'
		when population between 50000 and 1000000 then 'Medium'
        when population > 1000000 then 'Large'
        else 'NA'
   end size
from country;

-- Question 21 with function
select
   name,
   population,
   pop_size(population) size
from country;

NA

Suggestion:

Paste function details

=============================================================================================================
(22) Is there any invalid percentage data in countrylanguage table? Output them.
=============================================================================================================
select * from countrylanguage
where percentage is null or percentage = '' or percentage = '0%'

=============================================================================================================
(23)  Output continent names and total number of coutries for each.
=============================================================================================================
select 
    continent,
    count(*) cnt_country
from country
group by continent

=============================================================================================================
(24) Output all city names which include "sh".
=============================================================================================================
select name from city where name like '%sh%'

=============================================================================================================
(25) Output all city's id and name which country is China, United States, and Mexico.
=============================================================================================================
select 
    id, city.name
from city
inner join country
on country.code = city.countrycode
where country.name in ( 'china', 'united states', 'mexico' )


=============================================================================================================
(26) Output all languages and their countries which percentage is greater than 60%
=============================================================================================================
select 
    language, name country_name, percentage
from countrylanguage a
inner join country b
on a.countrycode = b.code
where percentage > 60

=============================================================================================================
(27) Which un-official language has the highest percentage and in which country?
=============================================================================================================
select 
    language, name country_name
from countrylanguage a
inner join country b
on a.countrycode = b.code
where isofficial = 'F'
order by percentage desc
limit 1

=============================================================================================================
(28) Give the total number of  official and un-official languages.
=============================================================================================================

select 
    ( select count(*) from countrylanguage where isofficial = 'T' ) cnt_off_lang,
    ( select count(*) from countrylanguage where isofficial = 'F' ) cnt_unoff_lang
from dual

=============================================================================================================
(29) Output continent and total number languages spoken in each continent.
=============================================================================================================

select
	a.continent,
    case when cnt_lang is null then 0
		else cnt_lang
	end cnt_lang
from
(
	select
		distinct continent
	from country
) a
left join
(
	select
		continent,
		count(*) cnt_lang
	from
	(
		select
			distinct 
			continent,
			language
		from country a
		inner join countrylanguage b
		on a.code = b.countrycode
	) a
	group by continent
) b
on a.continent = b.continent

=============================================================================================================
(30) Which continent has the bigesst surface area?
=============================================================================================================
select 
    continent,
    sum( surfacearea ) sum_surfacearea
from country
group by cotinent
order by sum_surfacearea desc
limit 1

=============================================================================================================
(31) List each continent and their total population.
=============================================================================================================
select 
    continent,
    sum( population ) sum_population
from country
group by cotinent


=============================================================================================================
(32) Which continent has the highest english-spoken population
=============================================================================================================

select
    continent,
    sum( spoken_popu ) sum_spoken_popu
from
(
select 
    continent,
    countrycode,
    language,
    percentage * population spoken_popu
from country a
inner join countrylanguage b
on a.code = b.countrycode
where language = 'english'
) a
group by continent
order by sum_spoken_popu desc 
limit 1


=============================================================================================================
(33) List all continents and their total number of cities.
=============================================================================================================

select
    continent,
    count(*) cnt_city
from
(
    select
        continent,
        b.name
    from country a
    left join city b
    on a.code = b.countrycode
) a 
group by continent

=============================================================================================================
(34) How many districts in china, canada, and the US
=============================================================================================================

select
    name country_name,
    count(*) cnt_district
from
(
    select 
        distinct a.district, b.name
    from city a
    inner join country b
    on a.countrycode = b.code
    where b.name in ( 'china', 'canada', 'united states' )
) a
group by name

=============================================================================================================
(35) What is the percentage of cites for the world which has the population less than 200000?
=============================================================================================================
select 
    concat( 
        ( select count(*) from city where population < 200000 ) 
        /
        ( select count(*) from city ) 
        * 100,
        '%'
    ) percentage
from dual

=============================================================================================================
(36) List the top 10 countries which have the highest population density in the world.
=============================================================================================================

select 
    population / surfscearea popu_density,
    name
from country
order by population / surfscearea desc
limit 10

=============================================================================================================
(37) List top 5 contries and total number of cities which have the highest life expentancy in the world.
=============================================================================================================

select 
    name,
    count(*) cnt_city
from
(
    select
        a.id,
        b.name
    from city a
    inner join
    (
        select 
            name,
            code
        from country
        order by lifeexpectancy desc
        limit 5
    ) b
    on a.countrycode = b.code
) a
group by name

=============================================================================================================
(38) List all countries' names which own top 50 populated cities in the world
=============================================================================================================

select 
    country.name
from
(
    select 
        countrycode
    from city
    order by population desc
    limit 50
) b
inner join country
on b.countrycode = country.code

=============================================================================================================
(39) Which region has the fewest number of cities?
=============================================================================================================

select
    region,
    count(*) cnt_city
from
(
    select 
        distinct region,
        b.id
    from country a
    left join city b
    on a.code = b.countrycode
) a
group by region
order by cnt_city


=============================================================================================================
(40) Among top 10 life xpentancy countries, what is the top 3 highest percentage official languages.
=============================================================================================================

select 
    language,
    percentage
from countrylanguage
where countrycode in (
    select code
    from
    (
        select
           code
        from country
        order by lifeexpectancy desc
        limit 10
    ) a
)
and isofficial = 'T'
order by percentage desc
limit 3;


=============================================================================================================
Web Report:

(1) For query (1) , create a table view, put its link on left side menu.


=============================================================================================================
(2) Create a chart report, show 6 most populated countries and total number of cities.
=============================================================================================================
select
  name country,
  cnt_city 'Number of Cities'
from
(    
    select
	  countrycode,
	  count(name) cnt_city,
	  sum(population) sum_pol
	from city
	group by countrycode
	order by sum_pol desc
	limit 6
) a
inner join country b
on a.countrycode=b.code


FAIL

Suggestion: Get population directly from table: country

select
  name country,
  cnt_city 'Number of Cities'
from
(    
    select 
		countrycode,
        count(name) cnt_city
    from city
    where countrycode in
		(
			select * from (
				select
				  code
				from country
				order by population desc
				limit 6
            ) a
		) 
    group by countrycode
) a
inner join country b
on a.countrycode=b.code
order by cnt_city desc



=============================================================================================================
(3) Create a database view based on question (19) and then create a BI report table view based on the view.

(4) Create a stored procedure based on question (21) and register this procedure in BI report and create table view.
=============================================================================================================