1. Write the SQL statement to all countries in the world.country table that contain the word “and” in the name. The “and” must appear as a conjunction of two names, it must not appear within the country name. Do not use a regular expression. Also display the Region, Population, and LifeExpectancy.
The result set should look like the below figure. 2. In the world.country table, the SurfaceArea is given in square kilometers. Write an SQL SELECT statement to return the country name, region, surface area in square kilometers as SurfaceAreaSK, and surface area in square miles as SurfaceAreaSM. Return the data in order by descending SurfaceAreaSM.
For reference, 1 sq. kilometer = 0.38610216 sq. mile Formula: SurfaceArea * .38610216
The result set should look like the below figure. 3. Write an SQL SELECT statement that will return the following: Name, Region, SurfaceArea in square miles as SurfaceAreaSM, Population, population density per square mile as Density, life expectancy.
Include only those countries where the population density is greater than 750 people per square mile and the life expectancy is less than 70.
Sort in order by descending the highest density, then by region and name.
For reference, 1 sq. kilometer = 0.38610216 sq. mile Formula: SurfaceArea ‘ .38610216
For reference population density is the population / area Formula: Population/(SurfaceArea • .38610216)
The result set should look like the below figure.
4. In the “world” schema, the “city” table contains data about cities around the world. Write an SQL SELECT statement that will return the following: city.CountryCode, city.Name, city.District, city.Population.
Include only those cities where the population density per square mile of the country is greater than 750 and the life expectancy is less than 60.
Sort in order by descending population.
The result set should look like the below figure.
