Database Lab 5

SQL Queries

Due November 11, 2011

Goal

To refine skills and understanding of complex and multi-table SQL queries.

Turn in

Log all work to a document, and turn in.

Create a Word document with. In it include all answers to each query.

In the document also describe and significant issues you had, and how you resolved them.

  • Start up MySQL and change to the database “world”.
  • Do a “describe” of each table to see what is in it.
  • Do a “show tables” to see the tables.
This is a database of world country data.

Complete the following queries: (These are all meant to be done as a single query).

  1. List the names of the Districts in the US and Russia.
  2. List the names of the Districts in the US along with the total city population for each district.
  3. List the average, min, and maximum life expectancy for each continent.
  4. For each non-independent country list the Government Form, Region, and Capital.
  5. How many countries have a life expectancy greater then the US?
  6. Find the average GNP of countries with less then 50,000,000 people.
  7. What is the Surface Area of the countries in Eastern Asia, and use ROLLUP (see this.) to find the total area.
  8. List every country along with the names of cities in each country (using GROUP_CONCAT*), the average population of the cities for that country, the SUM of the cities population for that country, and to total population of the country.
  9. For each country give the list of official languages (using GROUP_CONCAT*)
  10. List all the countries that have a language in common with México.
  11. Give the country name, continent name, region name, and city name of every city with has less then 200,000 people.
  12. For each country that speaks Italian, list the city names that have less then 50,000 people.
  13. List the countries (names) in Asia that have less people then those in Vietnam;
  14. List the biggest city in every country that speaks French.
  15. For every country that has more then 20% Arabic speakers, list the Head of state;
  16. List each country (name) that has a higher percentage of Spanish speakers then the US.
  17. List each language of the world, and the number of countries iit is spoken in.
  18. What is the most number of languages spoken in any country?
  19. Create a view for South America, that includes Name, SurfaceArea, GNP, LifeExpectancy.
  20. Create a view for all cities over 5,000,000 ithat includes Name, District, Population, Country Name.
  21. Create a view of languages and cities that gives City name and Language name and percent spoken for each city and language.
  22. Create a view of countries where English is spoken, including Name, GovernmentForm, and HeadOfState.
  23. Create a view of Republics or Federations that speak English or French. Include Country name and head of state and government form and number of cities.
* Information on the GROUP_CONCAT function can be found here: GROUP_CONCAT

-- JimSkon - 2011-11-02

Topic revision: r5 - 2011-11-11 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback