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

Example 4. Four tables: Employees, Departments, Locations and Countries - 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

By similar reasoning, Oracle successively takes two tables at a time and creates the join as shown in the next picture. Observe the circuitous route to find the country name associated with an employee.

select  "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID",
"EMPLOYEES"."LAST_NAME" as "LAST_NAME",
"EMPLOYEES"."SALARY" as "SALARY",
"DEPARTMENTS"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
"LOCATIONS"."CITY" as "CITY",
"COUNTRIES"."COUNTRY_NAME" as "COUNTRY_NAME" 
from  "COUNTRIES" "COUNTRIES",
"LOCATIONS" "LOCATIONS",
"DEPARTMENTS" "DEPARTMENTS",
"EMPLOYEES" "EMPLOYEES" 
where "DEPARTMENTS"."DEPARTMENT_ID"="EMPLOYEES"."DEPARTMENT_ID" and
"DEPARTMENTS"."LOCATION_ID"="LOCATIONS"."LOCATION_ID" and
"LOCATIONS"."COUNTRY_ID"="COUNTRIES"."COUNTRY_ID" and
"EMPLOYEES"."SALARY" > 9000 and "LOCATIONS"."CITY" !='Seattle'

Summary

Querying multiple tables was discussed using the Query Builder tool in Oracle 10G XE. Oracle's traditional syntax for the join was discussed, which places the join in the WHERE clause. This is also the syntax for joins generated by the Query Builder tool. Table aliases are implied in the queries built using the Query Builder. In order to use shortened names for table aliases the GUI generated code may have to be modified. While column aliases can be configured, the column names instead of the aliases are generated by the code in the order by clause. The use of Venn diagrams will aid considerably in understanding the joins.



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