9.5 Object Query Language (OQL)

OQL uses a syntax that is similar to SQL, but it operates on objects, not tables. One form for queries is

Line 1. SELECT open square bracket DISTINCT close square bracket expression hyphen list. Line 2. FROM from hyphen list. Line 3. Open square bracket WHERE condition close square bracket. Line 4. Open square bracket GROUP BY dot, dot, dot open square bracket HAVING condition close square bracket close square bracket. Line 5. Open square bracket ORDER BY expression close square bracket semicolon.

Although the syntax is similar to SQL, there are some important differences. The expression list may contain the names of attributes of objects, identified by using dot notation, as in

Line 1. SELECT s dot p I d comma s dot credits. Line 2. FROM Students s. Line 3. ORDER BY s dot p I d semicolon.

The use of dot notation invokes the default get() method for the attributes named in the SELECT line. The result will be the values of pId and credits for each of the Student instances in the Students extent.

In addition to attributes, we can use methods in the expression list, which retrieves the result of applying the method. We could write, for example

Line 1. SELECT p dot name comma p dot find Age open parentheses close parentheses. Line 2. FROM People p. Line 3. WHERE p dot p I d equals 1001 semicolon.

which will return the name and age of the person whose pId is 1001, where the findAge() method is written to calculate a person’s age.

We can also use a relationship in the expression list. This retrieves the object or set of objects related to the “calling” object through the relationship. For example

Line 1. SELECT s dot p I d comma s dot takes Class dot c No comma s dot takes Class dot section Code. Line 2. FROM Students s. Line 3. WHERE s dot p I d equals 999 semicolon.

retrieves the cNo and sectionCode of classes that student 999 takes, as well as the pId.

The list of variables in the FROM line is similar to defining an alias in SQL. Usually, we list the name of an extent, such as Students or People, and an identifier for the name of the variable, such as s or p, as we saw earlier. The variable is an iterator variable that ranges over the extent. There are alternate forms for declaring an iterator variable, such as

Line 1. FROM Students s. Line 2. FROM s IN Students. Line 3. FROM Students AS s.

In the WHERE clause, we are restricted to a Boolean expression having constants and variables defined in the FROM clause. We can use <, <=, >, >=, !=, AND, OR, and NOT in the expression, as well as IN, and quantifiers EXISTS and FORALL. As in SQL, OQL does not eliminate duplicates, so the return is a bag (multiset) rather than a set. If we wish to eliminate duplicates, we can add the keyword DISTINCT as in SQL, producing a set. We can optionally add an ORDER BY clause, as in SQL.

  • Example 1. Simple Retrieval

    Query Specification: Find the ID, name, and major of all undergraduates who have between 60 and 90 credits, in order by name.

    OQL Query:

    Line 1. SELECT s dot p I d comma s dot name comma s dot major. Line 2. FROM Undergrads AS s. Line 3. WHERE s dot credits greater than or equal to 60 AND s dot credits less than or equal to 90. Line 4. ORDER BY s dot name semicolon.

  • Example 2. Using a Relationship to Find Records through References

    Query Specification: Find the names of all faculty in the Biology department.

    OQL Query:

    Line 1. SELECT f dot name. Line 2. FROM Departments AS d comma d dot has Faculty AS f. Line 3. WHERE d dot dept Name equals single quote Biology single quote semicolon.

The first part of the FROM clause says that d is an iterator variable that ranges over the objects in the Departments extent, and the second part says that for each of the Department objects, the set of faculty identified by the relationship hasFaculty for that department is to be identified with the iterator variable f. This type of expression, in which one follows a relationship or a method to get to another object, is called a path expression. We are essentially setting up a nested loop that has d taking on each department in turn and f taking on each faculty member in that department. The WHERE line restricts the results to the Biology department, and the SELECT line displays just the faculty name.

  • Example 3. Subqueries in OQL

    Query Specification: The previous query can be expressed in a form similar to nested queries in SQL. Find the names of all faculty in the Biology department, using a subquery. We can first find the Biology department object.

    OQL Query:

    Line 1. SELECT d. Line 2. FROM d IN Departments. Line 3. WHERE d dot dept Name equals single quote Biology single quote semicolon.

We can use this as a subquery to provide the value for an iterator variable, which we will call b. Then we declare an iterator variable for b, which we will call f, for the relationship set hasFaculty. The entire query is expressed as follows.

OQL Query:

Line 1. SELECT f dot name. Line 2. FROM open parentheses SELECT d. Line 3. FROM d IN Departments. Line 4. WHERE d dot dept Name equals single quote Biology single quote close parentheses AS b comma. Line 5. b dot has Faculty AS f semicolon.

  • Example 4. Defining Structures for Returned Results within a Query

    Query Specification: Find the name, rank, and salary of all faculty in the English department and put the results in a structure, in descending order by salary.

    OQL Query:

    Line 1. SELECT STRUCT open parentheses name colon f dot name comma rank colon f dot rank comma salary colon f dot salary close parentheses. Line 2. FROM Fac AS f. Line 3. WHERE f dot belongs To equals single quote English single quote. Line 4. ORDER BY salary D E S C semicolon.

  • Example 5. Using OQL Aggregation Operators

    Like SQL, OQL includes the operators COUNT, SUM, AVG, MAX, and MIN, but they are used differently. In OQL, the operator is applied to a collection rather than to a column. COUNT returns an integer, while the return from the other operators has the same type as the collection. The operators can be used on the results of a query.

  1. Query Specification: Find the average salary of faculty members in the History department.

    OQL Query:

    Line 1. A V G open parentheses SELECT f dot salary. Line 2. FROM Fac AS f. Line 3. WHERE f dot belongs To equals single quote History single quote close parentheses semicolon.

  2. Query Specification: Find the number of undergraduates majoring in computer science.

    OQL Query:

    Line 1. COUNT open parentheses SELECT u. Line 2. FROM Undergraduate AS u. Line 3. WHERE u dot majors In equals single quote Computer Science single quote close parentheses semicolon.

  3. Query Specification: Find the course number and section of all the class sections that have more than 30 students in them.

    OQL Query:

    Line 1. SELECT s dot c No comma s dot section. Line 2. FROM Sections AS s. Line 3. WHERE COUNT open parentheses s dot has Students close parentheses greater than 30 semicolon.

  • Example 6. Using the Set Operations of UNION, INTERSECTION, and EXCEPT (Difference)

    Query Specification: Find the IDs and names of all students whose undergraduate major or graduate program is computer science.

    OQL Query:

    Line 1. Open parentheses SELECT u dot p I D comma u dot name. Line 2. FROM Undergrads AS u. Line 3. WHERE u dot major equals single quote Computer Science single quote close parentheses. Line 4. UNION. Line 5. Open parentheses SELECT g dot p I D comma g dot name. Line 6. FROM Grads AS g. Line 7. WHERE g dot program equals single quote Computer Science single quote close parentheses semicolon.

  • Example 7. Using Defined Queries

    Defined queries allow us to create views that can be operated on. We give the query a name and then perform the desired operations on the results of the query.

    Query Specification: Create a defined query to find all juniors and use the defined query to find the ID and name of all juniors.

    OQL Query:

    Line 1. DEFINE Juniors AS Line 2. SELECT s. Line 3. FROM s IN Undergrads. Line 4. WHERE s dot credits greater than or equal to 60 AND s dot credits less than 90 semicolon.

This definition can then be used in the query

SELECT j dot p I d comma j dot name FROM j IN Juniors semicolon.

We have chosen examples that are similar to SQL. OQL has many additional forms and capabilities that are not illustrated here.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.142.43.10