• Data retrieved: All players.

• Description: The FROM clause declares an identification variable named p, omitting the optional keyword AS. If the AS keyword were included, the clause would be written as follows:

  The Player element is the abstract schema name of the Player entity.

• See also: Identification Variables.

SELECT DISTINCT p
FROM Player p
WHERE p.position = ?1

• Data retrieved: The players with the position specified by the query’s parameter.

• Description: The DISTINCT keyword eliminates duplicate values.

  The WHERE clause restricts the players retrieved by checking their position, a persistent field of the Player entity. The ?1 element denotes the input parameter of the query.

• See also: Input Parameters and The DISTINCT Keyword.

SELECT DISTINCT p
FROM Player p
WHERE p.position = :position AND p.name = :name

• Data retrieved: The players having the specified positions and names.

• Description: The position and name elements are persistent fields of the Player entity. The WHERE clause compares the values of these fields with the named parameters of the query, set using the Query.setNamedParameter method. The query language denotes a named input parameter using a colon (:) followed by an identifier. The first input parameter is :position, the second is :name.

SELECT DISTINCT p
FROM Player p, IN (p.teams) t

• Data retrieved: All players who belong to a team.

• Description: The FROM clause declares two identification variables: p and t. The p variable represents the Player entity, and the t variable represents the related Team entity. The declaration for t references the previously declared p variable. The IN keyword signifies that teams is a collection of related entities. The p.teams expression navigates from a Player to its related Team. The period in the p.teams expression is the navigation operator.

  You may also use the JOIN statement to write the same query:

  SELECT DISTINCT p
  FROM Player p JOIN p.teams t

  This query could also be rewritten as:

  SELECT DISTINCT p
  FROM Player p
  WHERE p.team IS NOT EMPTY

SELECT t
FROM Team t JOIN t.league l
WHERE l.sport = 'soccer' OR l.sport ='football'

SELECT DISTINCT p
FROM Player p, IN (p.teams) AS t
WHERE t.city = :city

• Data retrieved: The players whose teams belong to the specified city.

• Description: This query is similar to the previous example but adds an input parameter. The AS keyword in the FROM clause is optional. In the WHERE clause, the period preceding the persistent variable city is a delimiter, not a navigation operator. Strictly speaking, expressions can navigate to relationship fields (related entities) but not to persistent fields. To access a persistent field, an expression uses the period as a delimiter.

  Expressions cannot navigate beyond (or further qualify) relationship fields that are collections. In the syntax of an expression, a collection-valued field is a terminal symbol. Because the teams field is a collection, the WHERE clause cannot specify p.teams.city (an illegal expression).

• See also: Path Expressions.

SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league = :league

• Data retrieved: The players who belong to the specified league.

• Description: The expressions in this query navigate over two relationships. The p.teams expression navigates the Player-Team relationship, and the t.league expression navigates the Team-League relationship.

SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league.sport = :sport

• Data retrieved: The players who participate in the specified sport.

• Description: The sport persistent field belongs to the League entity. To reach the sport field, the query must first navigate from the Player entity to Team (p.teams) and then from Team to the League entity (t.league). Because it is not a collection, the league relationship field can be followed by the sport persistent field.

SELECT p
FROM Player p
WHERE p.name LIKE 'Mich%'

• Data retrieved: All players whose names begin with "Mich."

• Description: The LIKE expression uses wildcard characters to search for strings that match the wildcard pattern. In this case, the query uses the LIKE expression and the % wildcard to find all players whose names begin with the string "Mich." For example, "Michael" and "Michelle" both match the wildcard pattern.

• See also: LIKE Expressions.

SELECT t
FROM Team t
WHERE t.league IS NULL

• Data retrieved: All teams not associated with a league.

• Description: The IS NULL expression can be used to check whether a relationship has been set between two entities. In this case, the query checks whether the teams are associated with any leagues and returns the teams that do not have a league.

• See also: NULL Comparison Expressions and NULL Values.

SELECT p
FROM Player p
WHERE p.teams IS EMPTY

• Data retrieved: All players who do not belong to a team.

• Description: The teams relationship field of the Player entity is a collection. If a player does not belong to a team, the teams collection is empty, and the conditional expression is TRUE.

• See also: Empty Collection Comparison Expressions.

SELECT DISTINCT p
FROM Player p
WHERE p.salary BETWEEN :lowerSalary AND :higherSalary

• Data retrieved: The players whose salaries fall within the range of the specified salaries.

• Description: This BETWEEN expression has three arithmetic expressions: a persistent field (p.salary) and the two input parameters (:lowerSalary and :higherSalary). The following expression is equivalent to the BETWEEN expression:

  p.salary >= :lowerSalary AND p.salary <= :higherSalary

• See also: BETWEEN Expressions.

SELECT DISTINCT p1
FROM Player p1, Player p2
WHERE p1.salary > p2.salary AND p2.name = :name

• Data retrieved: All players whose salaries are higher than the salary of the player with the specified name.

• Description: The FROM clause declares two identification variables (p1 and p2) of the same type (Player). Two identification variables are needed because the WHERE clause compares the salary of one player (p2) with that of the other players (p1).

• See also: Identification Variables.

UPDATE Player p
SET p.status = 'inactive'
WHERE p.lastPlayed < :inactiveThresholdDate

• Description: This query sets the status of a set of players to inactive if the player’s last game was longer ago than the date specified in inactiveThresholdDate.

DELETE
FROM Player p
WHERE p.status = 'inactive'
AND p.teams IS EMPTY

• Description: This query deletes all inactive players who are not on a team.