|
"Success usually
comes to those who are too busy to be looking for it" -
Henry David Thoreau (1817-1862) |
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.
|