Previous | Table of Contents | Next

Page 116

within the object is related, the developer does not need to worry about join conditions. Of course, if the query is combining the data from two or more objects, the same join techniques outlined in this chapter may be used to combine attributes from the objects.

The new Oracle8 object technology has the ability to reduce the complexity of many queries, but as long as the world is comprised of different objects, the developer will still need to use the techniques outlined in this chapter to combine data into information.

Summary

This chapter covered combining rows from different tables through the use of a join. Multiple tables are included in the from clause, and the join condition is recorded in the where clause. When the tables contain columns with the same name, you must qualify the columns with the name of the table. Performance problems and unexpected results can occur when using joins. You can use the outer join symbol (+) to select a record that does not have a matching value in the other table. You place the outer join symbol after the column name in the where clause that does not contain the value.

The set operators Union, Minus and Intersect, are used to combine records from multiple tables. Union combines records from the tables discarding any duplicated records. The Minus operator selects records from the first table that do not exist in the second. The Intersect operator selects records that exist in both tables.

From Here…

The next chapter discusses how to set up the database. The chapter covers the methods of creating, altering, and dropping the various objects. It also covers the attributes and settings of the various objects. It discusses database constraints used to maintain database integrity. In the following chapter, you will learn how to set up the user account, tablespace, tables, and indexes.

Review Questions

  1. Produce a listing of the employees who have purchased tools. The report should also list the tools these employees purchased.

  2. Develop a report that you can pass out to each of the employees. The report should list the tools that were purchased. Be certain to include the employee's department name in the heading of the report.

  3. Produce a listing of departments and the amount of money spent by each department to purchase glasses for its employees. The listing should be in descending order with the highest department at the top of the list.

  4. Develop a query that lists the employees and the most recent date they purchased a tool and the most recent date they purchased a pair of glasses.

Page 117

  1. Develop a report similar to #2, except include the employee's glasses on the report.

  2. Produce a list of employees that have purchased tools but not glasses.

  3. Produce a list of employees by department that have purchased glasses but not tools.

  4. Produce a list of employees by department that have purchased both glasses and tools.

Page 118

Previous | Table of Contents | Next