Here are some examples of basic SQL queries based on the Monopoly database used in labs 7–9. You can run them in PostgreSQL.

Single-Table Queries in PostgreSQL

The simplest SQL queries are those queries that reference only one table.

• Simple queries - The simplest queries don’t include all the SELECT clauses. Examples include:

  SELECT 1 + 1;
  
  SELECT *
  FROM Player;

  The first shows that the SELECT clause can carry expressions (e.g., 1 + 1) and the second uses the * , which returns all fields in Player. SQL syntax is not case sensitive (except for string constants).

• Ordering the output - The following command returns the game records in inverse chronological order:

  SELECT *
  FROM Game
  ORDER BY time DESC;

  Dates and time are represented as numbers that increase over time.

• NULL values - Fields with no represented value are treated as NULL. NULL is not really a value. As an example, recall that the Monopoly database command file did not specify the name of one of the players (see cs262-code/lab07/monopoly.sql) — the resulting value in the database is NULL. Consider the following two queries:

  SELECT *
  FROM Player
  WHERE name IS NULL;
  
  SELECT *
  FROM Player
  WHERE name = NULL;

  Do they return the same result or not? Be sure that you understand why this is.

• Wildcard conditions - SQL supports wildcards in the WHERE clause string values. The following query returns all Players whose email address includes the string “calvin”:

  SELECT *
  FROM Player
  WHERE emailAddress LIKE '%calvin%';

  The wildcard character is % and the string delimitation character is ' .

• Limit queries - PostgreSQL supports the use of LIMIT clauses, which limit the number of records that are returned. The following query returns the highest game score recorded in the Monopoly database:

  SELECT score
  FROM PlayerGame
  ORDER BY score DESC
  LIMIT 1;

  LIMIT is not supported by all SQL implementations.

Multiple-Table Queries in PostgreSQL

Many useful queries require that we link two or more tables together. These multi-table queries are commonly called join queries. Here are some examples:

• Two-table queries - The following query returns Dogbreath’s scores from all the games he/she played in:

  SELECT score
  FROM Player, PlayerGame
  WHERE Player.ID = PlayerGame.playerID
    AND Player.name = 'Dogbreath';

  We need to join both Player and PlayerGame because Player has the name we’re focusing on (i.e., 'Dogbreath') and PlayerGame has the score we’re looking for. Note that the WHERE clause includes both a join condition (i.e., Player.ID = PlayerGame.playerID specifies how to join the two tables) and a restricting condition (e.g., Player.name = 'Dogbreath' restricts the output to only Dogbreath’s entries).

• Three-table queries - We can join as many tables as we would like to. The following query returns the names of all the players that played in game #2:

  SELECT Player.name, score
  FROM Player, PlayerGame, Game
  WHERE Player.ID = PlayerGame.playerID
    AND PlayerGame.gameID = Game.ID
    AND Game.ID = 2;

  This one has 2 join conditions and one restricting condition. The dot notation (i.e., Player.ID) disambiguates the field name ID, which is used in two of the tables in the query.

• Unrestricted joins - The WHERE clause is not required. Consider the following query:

  SELECT *
  FROM Player, PlayerGame, Game;

  Figure out what this query is doing and why. What fields does it return? Why? How many records does it return? Why?

• Joining a table with itself - The following query retrieves the names of the players who share the same name.

  SELECT P1.name
  FROM Player AS P1, Player AS P2
  WHERE P1.name = P2.name
    AND P1.ID < P2.ID;

  You can join a table with itself, but you must use the AS clause to give unique names to the two instances of the table you are joining (e.g., P1 and P2 in the example). You’ll have to add a new record to the Player table with the same name as an existing record to get this to return anything. Can you explain what the P1.ID < P2.ID does in this example?