CSE594

Database Management Systems

University of Washington

Fall 1999

Some OQL Queries
  1. Retrieve a literal value:

    2. 17
  2. Retrieve a single object:

    3. BestEmp
  3. Retrieve a single field of a single object:

    4. BestEmp.name
  4. Set operations (union, intersect, except):

    5. Employees union People
  5. Retrieve names of all Person objects in People set:

    6. People.name
  6. Retrieve names of all Person objects in People set:

    7. select P.name from P in People

    where P.age > 10
  7. Retrieve name and age of all Person objects in People set:

    8. select P.name, P.age from P in People
  8. Retrieve name and age of all Person objects in People set:

    9. select struct (name: P.name, age: P.age) from P in People
  9. From the People set, select the salaries of all emps (uses type casting):

    10. select Emp) E).sal from E in People

    select ((Emp) E).sal from E in People where E in Employees

    select ((Emp) E).sal from E in People where E->isEmp
  10. Quantifiers: exists, forall :

    11. exists P in People: P.age > 15

     

  11. Sorting:

    12. sort P in People by P.name

    select x.name from x in (sort P in People by P.name)
  12. Extend the "." notation to use "path expressions":

    13. select P.DOB.year from P in People3
  13. Explicit dereferencing:

    14. BestEmp: result is an OID

    *BestEmp
  14. Equalities (identity, shallow equality, deep equality):

    15. select E from E in Employees where E = BestEmp

    select E from E in Employees where *E = *BestEmp

    select E from E in Employees where E->deep_equal (BestEmp)
  15. Ordered collections (lists, arrays)

    16. Companies [0:1]

    listtoset (Companies)

  16. Converting collections:

    17. select D.emps from D in Departments
  17. Aggregates and Grouping:

    18. avg (select E.age from E in Employees)

    group E in Employees by (age: E.age)

    To add average salary for each group, append:

    with (avgsal: avg (select P.sal from P in partition))

    Note that there is no need for a "having" clause in OQL (why not??)

  18. Extended "select" clause: Can be extended using path expressions (see earlier example) or by constructing complex objects:

    19. select struct (dname: D.name, ages: select E.age from E in D.emps)

    from D in Departments
  19. Extended "from" clause:

    20. select struct (dname: D.name, eage: E.age)

    from D in Departments, E in D.emps
  20. Extended "where" clause: Similar to SQL’s nested queries, but more flexible.
  21. Note that the need for joins is greatly reduced, but we still can (and sometimes must) ask join queries. For example, suppose we want the name, age, and dept. chair name for all people with the same name as some department:

select struct (pname: P.name, page: P.age, chairname: D.chair.name)

from P in People, D in Departments

where P.name = D.name