|
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
�oracle� password learning, 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 folder. For example:
c:_salary4sales_department.sql
Click �OK.�
Name the file
�emp_salary4sales_department� and click on
�Save;� and exit From �Query Builder.�
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. |
