Question

Open the World database on MySQL. Write the SQL code to display the name and population of all cities that meet two conditions:

a.) the city has a population more than 100,000; and

b.) the city name has (anywhere) the letter a followed by any character followed by the letter z.

Use a regular expression for the second condition. Sequence the query results in descending order by population. What is the city with the largest population in the output?

Answer 1

SQL code for above question :

select city_name, population from WorldDatabase WHERE population>1,00,000 AND city_name LIKE '%a_z%' , '%A_Z%' ORDER BY population DESC;

The city with the largest population in the output will be the city name in the very first row of the output.

However u can also write sql query for finding the city with the largest population in the previous output satisfying both the conditions given in question. Here, is the sql query :

select city_name from WorldDatabase WHERE population>1,00,000 AND city_name LIKE '%a_z%' , '%A_Z%' ORDER BY population DESC LIMIT 1;