Querying a single table - Oracle

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.