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.

Transcribed Image Text:

Name Antigua and Barbuda Bosnia and Herzegovina Heard Island and McDonald Islands Saint Kitts and Nevis Saint Pierre and Miquelon Saint Vincent and the Grenadines Sao Tome and Principe Region Caribbean Southern Europe Antarctica Caribbean North America Caribbean Central Africa South Georgia and the South Sandwich Islands Antarctica Svalbard and Jan Mayen Trinidad and Tobago Turks and Caicos Islands Wallis and Futuna Nordic Countries Caribbean Caribbean Polynesia Population LifeExpectancy 68000 3972000 0 38000 7000 114000 147000 0 3200 1295000 17000 15000 70.5 71.5 PRALL 70.7 77.6 72.3 65.3 PRALL 68.0 73.3 Name Russian Federation Antarctica Canada China United States Brazil Australia India Argentina Kazakstan Region Eastern Europe Antarctica North America Eastern Asia North America South America Australia and New Zealand Southern and Central Asia South America Southern and Central Asia SurfaceAreaSK SurfaceAreaSM 6592848.82286400 5065660.33920000 3849674.05751760 3696117.36746400 3615275.29720320 3300170.76069048 2988901.76303520 1269219.34478808 1073518.44566400 1052089.77578400 17075400.00 13120000.00 9970610.00 9572900.00 9363520.00 8547403.00 7741220.00 3287263.00 2780400.00 2724900.00 Name ▸ Maldives Bangladesh Nauru Tuvalu Mayotte Marshall Islands Comoros India El Salvador Haiti Rwanda Region Southern and Central Asia Southern and Central Asia Micronesia Polynesia Eastern Africa Micronesia Eastern Africa Southern and Central Asia Central America Caribbean Eastern Africa SurfaceAreaSM Population Density 115.05844368 55597.93883568 8.10814536 10.03865616 144.01610568 69.88449096 718.92222192 286000 129155000 12000 12000 149000 64000 578000 1269219.34478808 1013662000 8123.97554856 6276000 10714.33494000 8222000 10169.15869008 7733000 LifeExpectancy 2485.693277717382 62.2 2323.017771966660 60.2 1479.993200319240 60.8 1195.379123334771 66.3 1034.606506657485 59.5 915.796897435110 65.5 803.981268594474 60.0 798.649976587971 62.5 772.528174473942 69.7 767.383141001564 49.2 760.436554849275 39.3 CountryCode Name НТІ HTI RWA HTI HTI MYT Port-au-Prince Carrefour Kigali Delmas Le-Cap-Haïtien Mamout zou District Ouest Ouest Kigali Ouest Nord Mamoutzou Population 884472 290204 286000 240429 102233 12000 Name Antigua and Barbuda Bosnia and Herzegovina Heard Island and McDonald Islands Saint Kitts and Nevis Saint Pierre and Miquelon Saint Vincent and the Grenadines Sao Tome and Principe Region Caribbean Southern Europe Antarctica Caribbean North America Caribbean Central Africa South Georgia and the South Sandwich Islands Antarctica Svalbard and Jan Mayen Trinidad and Tobago Turks and Caicos Islands Wallis and Futuna Nordic Countries Caribbean Caribbean Polynesia Population LifeExpectancy 68000 3972000 0 38000 7000 114000 147000 0 3200 1295000 17000 15000 70.5 71.5 PRALL 70.7 77.6 72.3 65.3 PRALL 68.0 73.3 Name Russian Federation Antarctica Canada China United States Brazil Australia India Argentina Kazakstan Region Eastern Europe Antarctica North America Eastern Asia North America South America Australia and New Zealand Southern and Central Asia South America Southern and Central Asia SurfaceAreaSK SurfaceAreaSM 6592848.82286400 5065660.33920000 3849674.05751760 3696117.36746400 3615275.29720320 3300170.76069048 2988901.76303520 1269219.34478808 1073518.44566400 1052089.77578400 17075400.00 13120000.00 9970610.00 9572900.00 9363520.00 8547403.00 7741220.00 3287263.00 2780400.00 2724900.00 Name ▸ Maldives Bangladesh Nauru Tuvalu Mayotte Marshall Islands Comoros India El Salvador Haiti Rwanda Region Southern and Central Asia Southern and Central Asia Micronesia Polynesia Eastern Africa Micronesia Eastern Africa Southern and Central Asia Central America Caribbean Eastern Africa SurfaceAreaSM Population Density 115.05844368 55597.93883568 8.10814536 10.03865616 144.01610568 69.88449096 718.92222192 286000 129155000 12000 12000 149000 64000 578000 1269219.34478808 1013662000 8123.97554856 6276000 10714.33494000 8222000 10169.15869008 7733000 LifeExpectancy 2485.693277717382 62.2 2323.017771966660 60.2 1479.993200319240 60.8 1195.379123334771 66.3 1034.606506657485 59.5 915.796897435110 65.5 803.981268594474 60.0 798.649976587971 62.5 772.528174473942 69.7 767.383141001564 49.2 760.436554849275 39.3 CountryCode Name НТІ HTI RWA HTI HTI MYT Port-au-Prince Carrefour Kigali Delmas Le-Cap-Haïtien Mamout zou District Ouest Ouest Kigali Ouest Nord Mamoutzou Population 884472 290204 286000 240429 102233 12000

Answer rating: 100% (QA)

1 This query can be written using the LIKE operator Note that there is space at the beginning and at ... View the full answer