Multi-Table Queries with Oracle 10G XE (
Page 1 of 5 )
Queries are quantitative questions you pose to a database. Questions such as "how many employees make more than $20,000.00 a year?" or "which employee has worked for more than two years?" are typical examples. In some cases, to answer questions like the ones above, you may go no further than looking it up in one table. However some questions may need more than one table, and it is not uncommon that several tables must be used to provide the answer.The strength of relational databases is in providing answers to such cases where several tables may be employed. Whereas writing a SELECT query against a table is quite straightforward, to write a query addressing several tables requires joins or sub-queries. This tutorial looks at multiple table queries using joins. The discussions are mostly in relation to the Oracle 10G XE, the little Hercules, and its graphical interface for building queries. The GUI generated SQL statement uses Oracle's traditional syntax for the joins.
This tutorial assumes that the following tutorials have been reviewed by the reader as some of the related information has already been described:
Tables from Oracle 10G XE used in this tutorial
This tutorial will be using the Employees, Departments, Locations and Countries tables in the hr database to show how multiple tables are queried. The tutorial will mostly look at the SELECT cause which must always be accompanied by the FROM clause. The SELECT clause can select columns, columns with aliases, expressions, aggregates, and so forth. The FROM Clause shows in which of the table(s) the columns are to be found, the columns that participate in the aggregates, and so on. In the case of columns coming from multiple tables, there are two ways to retrieve them, the traditional way (Oracle Syntax), or the ISO/ANSI SQL 1999 standard. These differ in the way the join -- or the manner in which the relationship that exists between the tables -- is leveraged in retrieving the columns. Although only tables have been mentioned, it is also possible to include views in the queries.
We will begin with single table queries and develop multiple table queries after we get used to working with Oracle 10G XE's Query Builder. But first, let us look at some of the tables used in our tutorial. The next four pictures show the design view of the four tables. Pay attention to the Primary keys and Foreign keys in the different tables. It is these keys that relate the tables and help us in identifying common columns that relate the tables.
Employees Table
The Employees table has Employee_ID as the primary key and possibly a couple of other foreign keys with the ID suffix. These are possible primary keys of other tables.
Departments Table
The Departments table has DEPARTMENT_ID as the primary key and possibly a couple of other foreign keys with the ID suffix. These are possible primary keys of other tables. The Departments table has a column labeled LOCATION_ID and in the next screen shot, if you were to click on the Constraints tab, you will see that it is a foreign key.
Locations table
The Locations table has LOCATION_ID as the primary key. In the Departments table the LOCATION_ID is a foreign key.
/ 9 
