Lab Three

This lab continues practice with multiple table SELECT statements in SQL, but now using aggregate functions.

We will use the database "dbbook" that was set up in Lab 2, and also a database called "world" which you should create from the appropriate script in the Downloads area. You will need to examine the schema for "world" in order to work out exactly what the tables contain.

Your task is simply to express each query below in MySQL, record your query in your workbook, and then check that you obtain the correct results when you run the query on the database.

Queries on "dbbook"

1. Find the age of the oldest student who is a History major.
2. For each major, list its name and the number of students doing that major (in a single query).
3. For each class, list its name and the number of students enrolled in that class (in a single query).
4. For each class with at least 5 students enrolled, list its name (and only its name).
5. Find the level (i.e. freshman, sophomore etc), and the average age of the students in that level, for each level.
6. Find the number of students for each combination of age and level, along with totals for each age (in a single query).
7. Find the names of all classes that either meet in room R128 or have at least 5 students enrolled [Hint: Look up the UNION clause in the MySQL documentation]
8. Find the names of all faculty members who teach less than 5 students in total.

Queries on "world"

1. Find the names of all countries which have English as the official language.
2. Find the names of all countries which have at least two official languages.
3. For each language, find how many countries have that as an official language.
4. For each language, find how many countries with at least 5 million people have that as the official language.
5. Find the average population of the cities listed for each country with at least 5 million people.
6. Find the total population of each of the world's regions.
7. Find the population of the largest city in each of the world's regions.

With respect to Q7, can you find the NAME of the city with the largest population in each of the world's regions using the SQL we have learned so far? Or do you need some other construct?