iSelfSchooling.com - Copyright © 1999-2007 iSelfSchooling.com  References  Job Openings  |  Secure Login
    Home  | Search more...  |  FREE Online VIDEO Oracle Training  |  Gift Store  |  Bookstore

   Unlimited access!   

    Oracle  Syntax  | Suggestions Your Contribution  |  FREE Legal Forms

 

Email2aFriend Homepage us! |  Bookmark   -  Copyright & User Agreement

Products/Services

 Vision/Mission

 Community Sharing

 Services

  Products

 Biography

 Contact Us

 FAQ

 Current News

 Website Traffic

 Bookstore

 FREE Training

 SQL

 PL/SQL

 Forms 

 Reports

 Other TOOLS

 Fundamentals

 Performance

 OEM

 Application Server

 Grid Control

 Articles

 Prepare for OCP

Oracle SYNTAX

 Oracle Functions

 Oracle Syntax

 Oracle 10g Syntax

  PL/SQL Syntax

UNIX and more...

 UNIX for DBAs

 LINUX for DBAs

 DB using PHP

  A+ Certification

 Basics of JAVA  

 Tips of  SEO

Finance/Jobs

 Financial Aid

 Skilled

 Oracle

 Jobs

  Magazine

More Training

 Q & Answers

 SQL-PL/SQL

 DBA

 Developer

 Important Notes

 Case Studies

 9i New Features

 10g New Features

 10g Qs/As

 Grid Control

 OracleAS # I

 OracleAS # II

  LDAP and OID

  HTTP Server

 Instructor-Led

  Virtual Hosts

 Community Sharing

More to know...

Acknowledgement**

 FREE Legal Forms

 Who is who

 Market Place

 University Directory

 Advisory Articles

 Links...

 

 

FREE Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

BASICS

SQL | PL/SQL

DEVELOPERS

FORMS 2 | REPORTS | Other TOOLS

DBAs

FUNDAMENTALS 2 | PERFORMANCE | OEM

ADVANCE

APPLICATION SERVER | GRID CONTROL | ARTICLES 2 3 4

Developers - Other Tools

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 | Lesson 27 | Lesson 28 | Lesson 29 | Lesson 30 | Lesson 31 | Lesson 32 | Lesson 33 | Lesson 34 | Lesson 35 |

Lesson 02

"Success usually comes to those who are too busy to be looking for it" - Henry David Thoreau (1817-1862)

Read first then play the video: Basic Information about Oracle Query Builder (Oracle Training)

Introduction to Query Builder

Introduction

Query Builder will facilitate you to write “SQL” code by providing a visual interface to the database objects being linked. It writes your simple or complex query. You can save your query at your client-side. It allows you to set your table relationships and visualize your database objects that being linked, including the tables, columns, and datatypes.

 

Hands-On

In this Hands-On, you will learn how to: Create a query, Join Multiple tables (the “dept” table and the “emp” table), Set table relationship, Execute Query, Sort query, Add where clause condition, Listing query, Saving query.

 

Open Query Builder tool

Open the “Query Builder” tool.

 

Login as “iself” password schooling, and then click “CONNECT.”

 

If you get the ‘Note’ window, click on the “OK” button.

 

Create a Query

In the ‘Start Query Builder’ window, click "OK" to create new query.

 

In the ‘Select Data Tables’ window, select the "department" (DEPT) table and then click on “include.” This action will include the ‘Dept’ table in the ‘Query’ window.

Select the “Employee” (EMP) table and click on “include.”

Close the ‘Start Query Builder’ window.

 

Maximize the "Query Builder" window if it is needed.

 

Set Table Relationship

Click on the "set table relationship” icon on the horizontal tool bar below the Main menu.

In the ‘Set Relationship’ window, type “dept.deptno” the department number of the department table on the "A" box; and “emp.deptno” the department number of the employee table on the "B" box. Then Click "OK." This will represent the relationship between primary key and foreign key. Notice that you want to use the ‘Suppress Mismatch’ option to avoid outer join syntax. The ‘A not found in B’ or ‘B not found in A’ option is a form of an outer join statement.

 

Select columns

Checkmark all the columns in the ‘Query’ window that you want to query. In this hands-on, select the department name (DNAME) column, the employee name (ENAME) column and the salary (SAL) column.

 

SQL Statement

To check your SQL statement, click on the “Show SQL” icon on the horizontal tool bar below the Main menu.

 

Notice: The “where” clause in the SQL statement. It joins two tables by referencing the primary of a table to the foreign key of the other table.

 

Run the query

In the ‘Show Query’ window, click "execute."

In the ‘Results’ window, check the output.

 

In the ‘Results’ window, select the DNAME column by clicking on it. Then choose the "sort ascending” icon on the horizontal tool bar below the Main menu. Then go to the “Show SQL” window and execute the query. Table was sorted. Close the window.

 

Query condition

To restrict the query output, click on the condition box and add a “where” clause to display all the employees of the sales department whom their salaries are more than 2000. The condition box is in the ‘Query’ window at the left most side of window. For example: EMP.SAL > 2000 and DEPT.DNAME = ‘SALES’

 

Show SQL

Press the enter key. Click on the “Show SQL” icon.

 

Notice: The where clause condition were added to the “SQL” statement.

Execute the statement.

 

Check the output and close the “Show SQL” window.

 

Save Query

Click on the “Query” window; and then choose the “File” option and click on “Save as” or “Save” to save the query in the “iself” folder. For example: c:_salary4sales_department.sql

 

Click “OK.”

 

Name the file “emp_salary4sales_department” and click on “Save;” and exit From “Query Builder.”

 

 

 

"While we are postponing, life speeds by." - Seneca (3BC - 65AD)

Questions:

Q: Create a query using the "Query Builder" tool.

Q: How can you join multiple tables using the "Query Builder" tool?

Q: How can you establish table relationship between multiple tables using the "Query Builder" tool?

Q: Execute a query using the "Query Builder" tool.

Q: Sort a query using the "Query Builder" tool.

Q: How can you add a "WHERE" clause condition to a query using the "Query Builder" tool?

Q: List a query using the "Query Builder" tool.

Q: Save a query using the "Query Builder" tool.

 

 

 
 
Google
 
Web web site