Using Query Builder in the Oracle 10g Express Edition

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.

Opening the Query Builder

It is assumed that you have downloaded Oracle 10g Express Edition and that you have unlocked the HR database so that you can log on to this database as a user. Now from the shortcut on your desktop you can log in by clicking on the Go to the Database Homepage… shortcut, which can be accessed from All Programs–>Oracle 10g Express Edition. This opens up the following window.

Log in to the database using the credentials Username: hr and Password: hr as shown in the above picture. This should open up the following window. There are five main objects (nodes). The Object Browser shows all the objects in the database. Now click on the SQL icon.

This will open up a window which gives you access to all the SQL functions in Oracle XE, SQL Commands, SQL Scripts, and Query Builder as shown. Please review the SQL Command icon related tutorial as it will help you in understanding the present tutorial.

Clicking on the Query Builder opens up the Query Builder specific window as shown in the next screen. It is in this window that you will bring in your table/view, or tables. You can set up the conditions that you may want to establish for the sets that will be returned by the query, by interacting with the bottom pane. This will be explained in this tutorial. The annotations in the different areas are by the author of this tutorial and are not part of Oracle’s documentation. In this window you can also search for other objects by entering even a single letter and clicking on the search area shown in the Search for database objects. There are already objects in the hr database shown in the indicated area “Database Objects.” Clicking a table, for example, in this area will transfer a copy of the table to the design area on the right.

While designing a query you will bring in the table/view from the left hand side, from the object listing, to the right hand, top pane – the design area. In combination with the bottom pane, you design the query, imposing the conditions which are typically the Where, Order by, Group by,  clauses of an SQL query. This will be shown in the next section.

 

{mospagebreak title=Querying a single table}

In this example you will be querying a single table (in fact it is a “view” and not a table) from the object list. Click on the view EM_DE_L on the left. This will bring the view to the right pane. This view has seven columns. How do I know that this is a view? I know because I created this view.

If you click on the top-left of the table, on the icon which looks like ‘#’, you will open up a small window with a tool tip labeled “Table Actions,” where you can place a check mark and select all seven columns in the view to be included in the query; these will be transported to seven rows in the bottom pane. You can close the window, and other windows in Oracle GUI, by clicking the “x” at the top right of the window. You may also minimize or maximize a window clicking on the  Min/Max button to the left of “x.” The table also shows the data type of each column. You may also choose a single column, or a number of columns by placing a check mark in the check box next to the chosen column or columns.

This next window shows that the column FIRST_NAME has been chosen and a corresponding item has been added to the bottom pane.

Now take a look at the bottom pane. From left to right you have the following in the bottom page: the first row shows four tabs, Condition, SQL, Results, Saved SQL. When you add a table or view to the design pane, the Condition tab comes up as a default tab. In this tabbed page you will see a graphic user interface for defining how your set based result should look, and how your SQL statement should be defined. There are 10 items arranged horizontally. They are, from left to right:

  • Column: name of column.
  • Alias: descriptive, or short name, used especially to reduce ambiguity.
  • Object: to which object the column belongs.
  • Condition: filtering the Select, the Where clause.
  • Sort Type: Ascending (Asc) or Descending (Desc).
  • Sort Order: numerical Order, the Order by clause.
  • Show: to include in a query, but show/hide it in display.
  • Function: SQL functions like Count, Count Distinct, Sum, Avg, etc.
  • Group By: This clause is for use when the condition requires.
  • Delete: click to delete the column from the query.

In the settings shown in the next picture three columns are chosen. By clicking on the up/down arrows at the extreme left, you can set up which column has to appear first (on the left) and which has to appear next, and so on. Instead of FIRST_NAME showing on the left, first, the LAST_NAME has been brought up. The query will show only FIRST_NAME and LAST_NAME, because HIRE_DATE is set not to show (“Show” is not checked for this column). Whereas LAST_NAME is in descending order, the FIRST_NAME is in ascending order. Also the order by will be LAST_NAME first followed by FIRST_NAME.

{mospagebreak title=A simple SELECT query}

Let us follow a simple query. We’ll be doing nothing fancy; three columns are chosen and nothing else. We show just the default view of the condition tab when the three columns are added.

Clicking on the SQL tab will show the following window. This screen cannot be edited here. If you want to replace “LAST_NAME” by a friendly Name “Last name” you have to return to the Condition tab and change the “alias.”

Now click on the Results tab. You will see the following screen.

{mospagebreak title=Creating a SELECT query with a WHERE clause}

Employees hail  from different cities. You could find all those from, say, “Seattle” by imposing a condition. The following qualified Select will show just that. You qualify your Select by typing in the condition ‘=’Seattle’ in the Condition part of the GUI as shown.

Now the window in the SQL tab will show the following content:

The Result tab will show the following window.

{mospagebreak title=Creating a SELECT query on two tables which are related}

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.

Google+ Comments

Google+ Comments