HomeOracle Page 2 - Multi-Table Queries with Oracle 10G XE
Example 1. Single Table queries - 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.
Using the Employees table in the hr database in Oracle 10G XE, the single table query will be demonstrated. Queries can be designed in Oracle 10g XE by the query builder which may be accessed as shown from the Home page.
The single table query will use the built-in Query designer which can be accessed from Home-->SQL-->Query Builder as shown in the following picture. You may choose the columns you want to return in the result as well as any filtering and ordering you want by making appropriate changes in the designer as shown. After making the changes run the query by hitting the RUNbutton. If you are developing queries that may end up in something complicated, it may be a good idea to save them and make changes to the saved queries as you go along.
The corresponding SQL statement generated by the machine is shown in the next paragraph. The double reference to the "Employees" is because of the lack of support for setting the table alias in the GUI. However, the SQL generated by the GUI adds the table alias. Although Oracle 10G XE shows the table alias for a single table without joins, it is not usual to show the alias.
select "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID",
"EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
"EMPLOYEES"."LAST_NAME" as "LAST_NAME",
"EMPLOYEES"."PHONE_NUMBER" as "PHONE_NUMBER",
"EMPLOYEES"."SALARY" as "SALARY"
from "EMPLOYEES" "EMPLOYEES" where "EMPLOYEES"."SALARY" >9000
order by EMPLOYEES.LAST_NAME DESC
The result returned by this query is shown in the next picture as you may verify for yourself. The filtering and the ordering are in accordance with your choice.
In addition to the table alias which is implicit, you may also use the column alias, which may be used for column ordering but not in the column condition as shown in the SQL statement generated by the GUI. Note that although the GUI generated the alias for the columns in the SQL, it retained the original column names in the order by clause. Ordering is specified over multiple columns -- in this case two columns.
select "EMPLOYEES"."EMPLOYEE_ID" as "Emp_ID",
"EMPLOYEES"."FIRST_NAME" as "Fname",
"EMPLOYEES"."LAST_NAME" as "Lname",
"EMPLOYEES"."PHONE_NUMBER" as "Phone",
"EMPLOYEES"."SALARY" as "Salary" from "EMPLOYEES" "EMPLOYEES"
where "EMPLOYEES"."SALARY" >9000
order by EMPLOYEES.LAST_NAME DESC, EMPLOYEES.FIRST_NAME DESC
The result returned by the query is shown in the next picture.
The GUI does not substitute the column alias in the order by clause, but you may run this select statement in the SQL Command window by substituting "EMPLOYEES"."LAST_NAME by "Lname", and "EMPLOYEES" "FIRST_NAME" by "Fname" to obtain the same result.
/ 14 
