Home arrow Oracle arrow 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.

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

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.



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