A language that doesn’t affect the way you think about programming is not worth knowing. – Alan Perlis, Epigrams in Computing, 1982.

In this lab, you’ll continue work on relational databases and meet with your team.

Querying a Relational Database

You can find some basic SQL examples and exercises here: SQL Examples.

As with all lab exercises, do this work manually, without using Copilot, to ensure that you understand SQL (which will be on the exams) and that you follow the standard class script pattern (which is somewhat unusual).

Single-Table Queries in PostgreSQL

Exercise 8.1

Create an SQL command file storing 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.
Work with this file as you worked with the database schema file you built in lab #7. Once you have a query working, document it 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.

Multiple-Table Queries in PostgreSQL

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.

Continuing Work on Your Team Project

We’ll focus today on the database management system for your team project. Clean up your database schema and consider what queries will be required.

Exercise 8.3

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

Checking In