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

Example 3. Three tables: Employees, Departments, and Locations - Oracle

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

In the case of three or more tables, Oracle joins two tables based on the join condition. The result of this will be joined with the third tables based on a different join condition as shown in the next listing. In the first join, it used the common column DEPARTMENT_ID; in joining the result with the Location tables it makes use of the common column LOCATION_ID as shown in the diagram.

select  "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID",
"EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
"EMPLOYEES"."LAST_NAME" as "LAST_NAME",
"EMPLOYEES"."SALARY" as "SALARY",
"DEPARTMENTS"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
"LOCATIONS"."POSTAL_CODE" as "POSTAL_CODE",
"LOCATIONS"."CITY" as "CITY" 
from  "LOCATIONS" "LOCATIONS",
"DEPARTMENTS" "DEPARTMENTS",
"EMPLOYEES" "EMPLOYEES" 
where "EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID" 
and "DEPARTMENTS"."LOCATION_ID"="LOCATIONS"."LOCATION_ID" and
"EMPLOYEES"."SALARY" > 9000

The amount of text to be typed for queries can be greatly reduced by using table and column aliases. Aliases make the code easier to read. The result of either of these yields the same result shown in the next picture. In the present case all the tables are from the same schema. When they come from different schemas, the tables have to be referred to together with the schema name, as schemaName.tableName. For referring to the columns in this case, you prefix the table and schema names, schemaName.tableName.columnName, provided the table name has been defined with its schema name. However, when the table alias is used, it is wrong to use the column name with the table name.

In the following query the letters e, d, and l (aliases) stand respectively for the tables "EMPLOYEES," "DEPARTMENTS" and "LOCATIONS."

 

 select e."EMPLOYEE_ID" as "EMPLOYEE_ID",
e."FIRST_NAME" as "FIRST_NAME",
e."LAST_NAME" as "LAST_NAME",
e."SALARY" as "SALARY",
d."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
l."POSTAL_CODE" as "POSTAL_CODE",
l."CITY" as "CITY" 
from  "LOCATIONS" l,
"DEPARTMENTS" d,
"EMPLOYEES" e 
where  e."DEPARTMENT_ID"=d."DEPARTMENT_ID"
and d."LOCATION_ID"=l."LOCATION_ID"
and   e."SALARY" > 9000



 
 
>>> 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: