Home arrow Oracle arrow Page 5 - Using Query Builder in the Oracle 10g Express Edition

Creating a SELECT query on two tables which are related - Oracle

In an earlier tutorial we saw how to use SQL commands in the Oracle 10g Express Edition, or simply Oracle XE. We also saw how to use the PL/SQL commands as well. In this tutorial we will see how to use the Query Builder, the graphical interface for fashioning and running SQL in the Oracle XE. The tutorial shows how to interact with the query builder to create a SELECT query in the context of a single table as well as a SELECT query from two related tables.

TABLE OF CONTENTS:
  1. Using Query Builder in the Oracle 10g Express Edition
  2. Querying a single table
  3. A simple SELECT query
  4. Creating a SELECT query with a WHERE clause
  5. Creating a SELECT query on two tables which are related
By: Jayaram Krishnaswamy
Rating: starstarstarstarstar / 14
June 14, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Tables in relational databases have relationships established when tables are designed. For example, the following picture shows two tables being added to the design pane. You see a line connecting one table to the other. This is the line that shows what kind of relationship exists. When you add the table, this line does not exist, but you can manually "link" the tables to make the relationship visible as well as enter the link into the SQL statement as a "join" condition. Unlike in the first example, the EMPLOYEES and DEPARTMENT objects are tables. These have a primary key, the unique identifier of the table which can be found in the "Object Browser." For object browser details, please refer to the previous tutorial.

Making a Join is easy, click on the rectangle to the right of the column of one table and go to another table, pick a column and click on the extreme right, rectangle. This establishes the 'grey' line making a 'join'.

This shows the Primary key of one table joined to the Foreign key of the other table.

There are different kinds of joins to expose columns from the two tables. In the query editor, if you right click on the line joining the two tables, you will reveal a small pick-list from which you may specify the type of join you desire to make.

If you pick the set "Left outer join" the editor sets up the requested type of join. If you want to look at what kind of join you made, just hover over the line and you should see the type of join as shown.

This shows the design view with left outer join set.  The association between the Design view and the SQL Query is through the small "+" symbol in the Query Editor view of the table and the "+" inside the parenthesis in the "Where" clause which establishes the "join" as shown in the next two pictures. The last picture also shows the result of this query.

 

 

Summary

The Query Editor affords all the necessary functions to tailor a query, run, and save the query. The Query Editor gives immediate access to all the objects that are needed for the query. One may feel that it would have been nicer to alter the SQL statement in the SQL tab, but because a separate object (SQL Command) has been designed, this functionality was left out in the query editor. I am sure you will find it quick and easy to design SQL queries with this interface to your heart's content.



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