Lab Four

This labsheet will cover this week and the next, leading up to the mid-semester break.

You will need to look ahead to the material covered in the lectures entitled "SQL III" and "SQL IV" to complete questions for this lab.

This lab is based on writing more SQL queries using all of the functionality that we have covered

Using the "world" database, answer the following queries:

• Which countries are larger then Germany and smaller than Indonesia by population?
• Which countries are between Germany and Indonesia by area?
• Find the names of all countries where more than 10% of the population speak English.
• Find the names of all countries for which English is the only official language.
• List all pairs of regions/languages such that the language is an official language of at least one country in that region.
• List the number of official languages spoken in each region.
• Find all countries (if any) that have NO official languages.
• For each country, list its name and the percentage of its population that speak any of the official languages of that country.
• Which two countries have the most official languages?

Using the "Sakila" database answer the following queries:

• List the films featuring Groucho Sinatra (sanity check: there are 26)
• List the total number of films that each actor appears in (sanity check: use previous qn)
• List the movies that feature just a single actor (sanity check: there are 21)
• List the actors that appear in either "Werewolf Lola" or "Watch Tracy"
• List the actors that appear in both "Werewolf Lola" AND "Watch Tracy"
• List the actors that appear in "Werewolf Lola" but NOT "Watch Tracy"
• What is the longest movie featuring Groucho Sinatra?

Once you have completed this work, you should return to the previous labs to see how you might re-write your previous solutions.