Database Lab 5

Due October 16


The purpose of this lab is to give experience with designing an building more complex SQL queries. You will be doing this is teams of two or three. Make sure each team member understands the query.


You will work together as teams. Take time to make sure EVERY student has mastered every query.

# Members
Team 1 Norckauer, Nicholas
Mullet, Logan C
Team 2 Ayers, Kaleb D
Porter, Trent M
Team 3 Cooper, Brandon
Motley, Aaron R
Team 4 Aleshire, Joshua M
Fitzgerald, Trenton K
Skon, Luke
Team 5 Kadrich, Joseph P
Radcliffe, Jason
Team 6 Mc Peak, David M
Selle, Joshua D


Plan and complete each of the following queries. Each student will run each query. Build up each query step by step, and turn in the steps you used.

  1. For each capital city, list the City name, District, Country.
    • How do you know if a city is the capital?
    • How do you combine each country with it's capital city?
  2. For each country, list the country name and population density, sorted desendingby the population density.
    • How can you get the population density?
  3. For each language, list the langauge name and the number of speakers of that language in the world.
    • How can you find the number of speakers of a particular language for a particular country, for example Spanish in the USA?
    • How can you create a list of how many Spanish speakers there are in every country it is spoken?
    • How can you find the total (sum) of all the Spanish speakers in all the countries?
    • Given any one langauge, how can you find the total (sum) of all the speakers of that language in all the countries?
    • Create a list of all Langauges in world.
    • Using the list of all languages from above, and the query to find the number of speakers for each language, create a list of all the numbers of speakers of each language.
  4. For each Language in the database, which country has the most speakers? List the language, the country, and the number of speakers, sorted by the number of speakers.
    • For this one list the steps, build up solutions to parts of the problem, and then assembling them in to the final solutions.
    • Show your steps.

Turn in

  1. Use MySQL
  2. Each student should run each query on THEIR copy of the world database
  3. Turn in a single Word document with the following for each questions above:
    • The steps involved in sollving the query.
    • The query created in each step.
    • The final query
    • The first ten lines of the output for the final query.
Topic revision: r3 - 2013-10-18 - 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