Home arrow Oracle arrow Multi-Table Queries with Oracle 10G XE

Multi-Table Queries with Oracle 10G XE

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.

TABLE OF CONTENTS:
  1. Multi-Table Queries with Oracle 10G XE
  2. Example 1. Single Table queries
  3. Example 2. Two Tables: Join Employees and Departments tables
  4. Example 3. Three tables: Employees, Departments, and Locations
  5. Example 4. Four tables: Employees, Departments, Locations and Countries
By: Jayaram Krishnaswamy
Rating: starstarstarstarstar / 14
November 29, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jayaram Krishnaswamy
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: