Exercise 8.1

Write SQL queries that return the following information from your Monopoly database:

1. Retrieve a list of all the games, ordered by date with the most recent game coming first.
2. Retrieve all the games that occurred in the past week.
3. Retrieve a list of players who have (non-NULL) names.
4. Retrieve a list of IDs for players who have some game score larger than 2000.
5. Retrieve a list of players who have GMail accounts.

Create an SQL command file storing all of your queries. Work with this file as you worked with the database schema file you built in lab #7. Once you have a query working, comment it out using the SQL comment syntax (a line starting with -- ) and go on to the next. Save this file so that you can submit it as part of this lab exercise.

Exercise 8.2

Write PostgreSQL queries that return the following information from your Monopoly database:

1. Retrieve all “The King”’s game scores in decreasing order.
2. Retrieve the name of the winner of the game played on 2006-06-28 13:20:00.
3. So what does that P1.ID < P2.ID clause do in the last example query (i.e., the from SQL Examples)?
4. The query that joined the Player table to itself seems rather contrived. Can you think of a realistic situation in which you’d want to join a table to itself?

Save your command file again so that you can submit it as part of this lab exercise.

Exercise 8.3

Continue work on you team’s database design, focusing on the necessary queries.