Code in MySQL please
3. Write an SQL SELECT statement that will return the following: Name, Continent, Region, Population and percent of population for all of the countries in the world.country table that are in North America To calculate the percentage of population, divide the population by the sum of the population of countries that are in North America. Take that result (population/sum) and multiply by 100 to get a percentage number. Hint: the value that you use for the denominator (the sume of the population) can be an embedded SELECT statement. Sort in order by Region descending and by Name descending. Your results should look like the figure. Only the first 10 rows are shown NAME Region North America North America North America North America North America Central America Central America Central America Central America Central America Population PercentPop 278357000 57.6317 7000 56000 31147000 6.4487 65000 2856000 5074000 98881000 6485000 11385000 2.3572 Continent United States Saint Pierre and Miquelon North America Greenland North America 0.0014 0116 Canada Bermuda Panama Nicaragua Mexico Honduras Guatemala North America North America North America North America North America North America North America North America 0.0135 0.5913 1.0505 20.4726 1.3427 4. Historically, it was said "the sun never sets on the British Empire". Although the British Empire is no longer what it once was, there are still a number of countries around the world that recognize Queen Elizabeth of Britain as their Head of State. For this SELECT you will retrieve the following data from the country table: Name, Continent, Region, HeadOfState, GovernmentForm. Select only those rows where Elisabeth Il is the head of state. You will sort the rows in order to simulate the sun rising over the countries, starting near the International Data Line (out in the Pacific Ocean). The sort order will follow the sun, from east to west, over the countries. Sort the countries in this order, using the Continent and Region values: Oceania/Polynesia Oceania/Melansia Oceania/[all others] Africa/Eastern Africa Africa/Western Africa North America/Caribbean North America/Central America North America/[all others] After sorting by the Continent/Region values, sort by the Name. The figure shows all of the rows and values, in the required sort order. Continent HeadOfstate GovernmentForm Elisabeth II Nonmetropolitan Territory of New Zealand Elisabeth II Nonmetropolitan Territory of New Zealand Elisabeth II Dependent Territory of the UK Elisabeth II Nonmetropolitan Territory of New Zealand Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Name Oceania Polynesia Polynesia Polynes Polynesia Polynesia Melanesia Melanesia Australia and New Zealand Elisabeth II Constitutional Monarchy, Federation Australia and New Zealand Elisabeth II Territory of Australia Australia and New Zealand Elisabeth II Territory of Australia Australia and New Zealand Elisabeth II Constitutional Monarchy Australia and New Zealand Elisabeth II Territory of Australia Eastern Africa Western Africa Southern Europe British Islands Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Central America North America North America South America Pitcairn Oceania Tuvalu Papua New Guinea Oceania tra 115 Christmas Cocos (Keeling) Islands Oceania Norfolk Island British Indian Ocean Territory Saint Helena Gibraltar United Kingdorm Anguilla Antigua and Barbuda Bahamas Africa Elisabeth II Dependent Territory of the UK Elisabeth II Dependent Territory of the UK Elisabeth II Dependent Territory of the UK Elisabeth II Constitutional Monarchy Elisabeth II Dependent Territory of the UK Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Elisabeth II Dependent Territory of the UK Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Elisabeth II Dependent Territory of the UK Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Elisabeth II Dependent Territory of the UK Elisabeth II Dependent Territory of the UK Elisabeth II Constitutional Monarchy Elisabeth II Dependent Territory of the UK Elisabeth II Constitutional Monarchy, Federation Elisabeth II Dependent Territory of the UK Elisabeth II Territory of Australia Elisabeth II Dependent Territory of the UK North America North America North America America North America Cayman Islands Montserrat Saint Kitts and Nevis Saint Lucia Saint Vincent and the Grenadines Turks and Caicos Islands Virgin Islands, British Belize Bermuda North America North America North America North America North America North America North America North America South America Antarct Antarctica Falkland Islands Heard Island and McDonald Islands South Georgia and the South Sandwich Islands Antarctica 5. The SELECT statement shown in the figure retrieves a subset of the countries where Elisabeth Il is the head of state. The data that is retrieved is shown in the figure. Rewrite the WHERE clause so that it does not use a CASE expression. Submit the complete SELECT statement with the reworked WHERE clause. 1SELECT Name, Continent, Region, HeadOfState, GovernmentForm FROM country 4 WHERE Head0fstate LIKE "E1%, AND 6 7 CASE WHEN ContinentOceania WHEN ContinentAfrica WHEN Continent-Europe WHEN ContinentNorth America' AND RegionCaribbean WHEN Continent South America AND Region NOT LIKE esia 9 THEN 1 R BY Continent, Name Export Wrap Cl Content Result Grid Ftar Rows HeadOfState GovernmentForm Elisabeth II Dependent Territory of the UK Elisabeth II Constitutional Monarchy Elisabeth II Dependent Territory of the UK Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Elisabeth II Dependent Territory of the UK Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Elisabeth II Dependent Territory of the UK Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Elisabeth II Constitutional Monarchy Elisabeth II Dependent Territory of the UK Elisabeth II Dependent Territory of the UK Elisabeth II Dependent Territory of the UK Elisabeth II Dependent Territory of the UK Name Gibraltar United Kingdom Anguilla Antigua and Barbuda Continent Southern Europe British Islands Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Caribbean Eastern Africa Western Africa Australia and New Zealand Elisabeth II Constitutional Monarchy, Federation Australia and New Zealand Elisabeth II Territory of Australia Australia and New Zealand Elisabeth II Territory of Australia Australia and New Zealand Elisabeth II Constitutional Monarchy Australia and New Zealand Elisabeth II Territory of Australia South America urope urope North America North America North America North America North America North America North America North America North America North America North America North America North America Cayman Islands amaica Montserrat Saint Kitts and Nevis Saint Lucia Saint Vincent and the Grenadines Turks and Caicos Islands Virgin Islands, British British Indian Ocean Territory Saint Helena Australia Christmas Island Cocos (Keeling) Islands New Zealand Norfolk Island Falkland Islands Oceania Oceania Oceania Oceania South America Elisabeth II Dependent Territory of the UK
