CSE594

Introduction to Database Systems

University of Washington

Winter 2000

Some OQL Queries

  1. Retrieve a literal value:
  2. 17

  3. Retrieve a single object:
  4. BestEmp

  5. Retrieve a single field of a single object:
  6. BestEmp.name

  7. Set operations (union, intersect, except):
  8. Employees union People

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

  11. Retrieve names of all Person objects in People set older than 10:
  12. select P.name from P in People

    where P.age > 10

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

  15. Retrieve name and age of all Person objects in People set:
  16. select struct (name: P.name, age: P.age) from P in People

  17. From the People set, select the salaries of all emps (uses type casting):
  18. 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

  19. Quantifiers: exists, forall :
  20. exists P in People: P.age > 15

  21. Sorting:
  22. sort P in People by P.name

    select x.name from x in (sort P in People by P.name)

  23. Extend the "." notation to use "path expressions":
  24. select P.DOB.year from P in People3

  25. Explicit dereferencing:
  26. BestEmp: result is an OID

    *BestEmp

  27. Equalities (identity, shallow equality, deep equality):
  28. 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)

  29. Ordered collections (lists, arrays)
  30. Companies [0:1]

    listtoset (Companies)

  31. Converting collections:
  32. flatten (select D.emps from D in Departments)

  33. Aggregates and Grouping:
  34. 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??)

  35. Extended "select" clause: Can be extended using path expressions (see earlier example) or by constructing complex objects:
  36. select struct (dname: D.name, ages: select E.age from E in D.emps)

    from D in Departments

  37. Extended "from" clause:
  38. select struct (dname: D.name, eage: E.age)

    from D in Departments, E in D.emps

  39. Extended "where" clause: Similar to SQL’s nested queries, but more flexible.
  40. 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