Using World database on MySQL Server as the sample database, write SQL statement

Question

Using World database on MySQL Server as the sample database, write SQL statements to solve the following questions (5points/each).
1. Select the cities (*) in the United States of America or Brazil, and sort the result first by country and second by city name. 2. Select the cities in the USA and the population larger than 1,000,000, and sort the result by population from largest to smallest. 3. Select the cities in either Texas or California and the population larger than 500,000, and sort the result by population in descending order. 4. Select the cities that have name starting from “Sal”, and sort the result by country. 5. Count the number (name the result as “NoOfCity”), the average population (name it as "AvgPopulation"), and the total population (name it as "TotalPopulation) in the USA. 6. List all the countries in North America, and sort the result first by region, and second by surface area in descending order. 7. Find out what is the official language in USA, Canada, China, Mexico, and Brazil. 8. Find out the city that has the largest population in China (need to display both the name of the city and the population). 9. Find out the city that has the lowest population in the USA (need to display both the name of the city and the population). 10. Find out the top 10 cities that have the largest population in the USA (need to display both the name of the cities and the corresponding population).
The world database is online but I just need the sql statements please and thank you! Using World database on MySQL Server as the sample database, write SQL statements to solve the following questions (5points/each).
1. Select the cities (*) in the United States of America or Brazil, and sort the result first by country and second by city name. 2. Select the cities in the USA and the population larger than 1,000,000, and sort the result by population from largest to smallest. 3. Select the cities in either Texas or California and the population larger than 500,000, and sort the result by population in descending order. 4. Select the cities that have name starting from “Sal”, and sort the result by country. 5. Count the number (name the result as “NoOfCity”), the average population (name it as "AvgPopulation"), and the total population (name it as "TotalPopulation) in the USA. 6. List all the countries in North America, and sort the result first by region, and second by surface area in descending order. 7. Find out what is the official language in USA, Canada, China, Mexico, and Brazil. 8. Find out the city that has the largest population in China (need to display both the name of the city and the population). 9. Find out the city that has the lowest population in the USA (need to display both the name of the city and the population). 10. Find out the top 10 cities that have the largest population in the USA (need to display both the name of the cities and the corresponding population).
The world database is online but I just need the sql statements please and thank you!
1. Select the cities (*) in the United States of America or Brazil, and sort the result first by country and second by city name. 2. Select the cities in the USA and the population larger than 1,000,000, and sort the result by population from largest to smallest. 3. Select the cities in either Texas or California and the population larger than 500,000, and sort the result by population in descending order. 4. Select the cities that have name starting from “Sal”, and sort the result by country. 5. Count the number (name the result as “NoOfCity”), the average population (name it as "AvgPopulation"), and the total population (name it as "TotalPopulation) in the USA. 6. List all the countries in North America, and sort the result first by region, and second by surface area in descending order. 7. Find out what is the official language in USA, Canada, China, Mexico, and Brazil. 8. Find out the city that has the largest population in China (need to display both the name of the city and the population). 9. Find out the city that has the lowest population in the USA (need to display both the name of the city and the population). 10. Find out the top 10 cities that have the largest population in the USA (need to display both the name of the cities and the corresponding population). 1. Select the cities (*) in the United States of America or Brazil, and sort the result first by country and second by city name. 2. Select the cities in the USA and the population larger than 1,000,000, and sort the result by population from largest to smallest. 3. Select the cities in either Texas or California and the population larger than 500,000, and sort the result by population in descending order. 4. Select the cities that have name starting from “Sal”, and sort the result by country. 5. Count the number (name the result as “NoOfCity”), the average population (name it as "AvgPopulation"), and the total population (name it as "TotalPopulation) in the USA. 6. List all the countries in North America, and sort the result first by region, and second by surface area in descending order. 7. Find out what is the official language in USA, Canada, China, Mexico, and Brazil. 8. Find out the city that has the largest population in China (need to display both the name of the city and the population). 9. Find out the city that has the lowest population in the USA (need to display both the name of the city and the population). 10. Find out the top 10 cities that have the largest population in the USA (need to display both the name of the cities and the corresponding population).
The world database is online but I just need the sql statements please and thank you!

Explanation / Answer

1.SELECT ci.name FROM city ci ,country co WHERE ci.countrycode = co.countrycode AND co.name IN ('United States of America' ,'Brazil') ORDER BY co.name, ci.name;

2.SELECT ci.name FROM city ci ,country co WHERE ci.countrycode = co.countrycode AND co.name = 'USA' AND co.Population > 1000000 ORDER BY co.Population ASC;

3.SELECT ci.name FROM city ci ,country co WHERE ci.countrycode = co.countrycode AND ci.name IN ('Texas','California') AND co.Population > 500000 ORDER BY co.Population DESC;

4.SELECT ci.name FROM city ci ,country co WHERE ci.countrycode = co.countrycode AND ci.name LIke 'Sal%' ORDER BY co.countrycode DESC;

5.SELECT COUNT(ci.name) AS NoOfCity, AVG(co.population) AS AvgPopulation, SUM(co.Population) AS TotalPopulation FROM city ci ,country co WHERE ci.countrycode = co.countrycode AND co.name = 'USA' ;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.