Using Query Builder in Oracle SQL Developer 3.0

Purpose

This tutorial shows you how to use the Query Builder feature in Oracle SQL Developer 3.0.

Time to Complete

Approximately 15 minutes.

Overview

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using SQL Developer, you can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Developed in Java, SQL Developer runs on Windows, Linux and the Mac OS X. This is a great advantage to the increasing number of developers using alternative platforms. Multiple platform support also means that you can install SQL Developer on the Database Server and connect remotely from their desktops, thus avoiding client server network traffic.

Default connectivity to the database is through the JDBC Thin driver, so no Oracle Home is required. To install SQL Developer simply unzip the downloaded file. With SQL Developer users can connect to any supported Oracle Database.

In this tutorial, you learn how to use the Query Builder feature to graphically and intuitively build your queries.

Software and Hardware Requirements

The following is a list of software requirements:

Prerequisites

Before starting this tutorial, you should:

1 .

Install Oracle SQL Developer 3.0 from OTN. Follow the readme instructions here.

2 .

Install Oracle Database 11g with the Sample schema.

3.

Unlock the HR user. Login to SQL Developer as the SYS user and execute the following command:
alter user hr identified by hr account unlock;

Note: This tutorial is developed using Oracle SQL Developer 3.0.

4 .

Download and unzip the files.zip to a local folder on your file system. In this tutorial, we use the C:\sqldev3.0 folder.

Creating a Database Connection

The first step to managing database objects using Oracle SQL Developer 3.0 is to create a database connection.

Perform the following steps to create a database connection:

Note: If you have already created a database connection, you do not need to perform the following steps. You can move to the Adding objects to Query Builder topic.

1 .

If you installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer and move to Step 4. If you do not have the icon located on your desktop, perform the following steps to create a shortcut to launch SQL Developer 3.0 from your desktop.

Open the directory where the SQL Developer 3.0 is located, right-click sqldeveloper.exe (on Windows) or sqldeveloper.sh (on Linux) and select Send to > Desktop (create shortcut).

Show Screenshot for Step

 

2 .

On the desktop, you will find an icon named Shortcut to sqldeveloper.exe. Double-click the icon to open SQL Developer 3.0.

Note: To rename it, select the icon and then press F2 and enter a new name.

Show Screenshot for Step

 

3 .

Your Oracle SQL Developer opens.

Show Screenshot for Step

 

4 .

In the Connections navigator, right-click Connections and select New Connection.

Show Screenshot for Step

 

5 .

The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.

Connection Name: HR_ORCL
User Name: hr
Password: <your_password> (Select Save Password)
Hostname: localhost
SID: <your_own_SID>

Show Screenshot for Step

 

6 .

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. Click Save then click Connect.

Show Screenshot for Step

 

7 .

The connection was saved and you see the newly created connection in the Connections list.

Show Screenshot for Step

 

Adding Objects to the Query Builder and Building a Query

In this topic, you learn to add objects to the Query Builder interface and then create a query based on those objects.

In this section, you create a query based on the EMPLOYEES and DEPARTMENTS tables. You will generate output that shows per department name, the employees who work in that department.

Perform the following steps to add the EMPLOYEES table to the Query Builder.

1 .

Select the Query Builder tab to open Query Builder window.

Show Screenshot for Step

 

2 .

Drag and drop the EMPLOYEES table from the Connections navigator to the Query Builder window.

Show Screenshot for Step

 

3 .

Drag and drop the DEPARTMENTS table object over to the Query Builder window, so that the two table objects are displayed in the Query Builder window.

Show Screenshot for Step

Because there are two relationships defined between the EMPLOYEES and DEPARTMENTS tables, you see two lines between the tables identifying the columns that are linked together through foreign key constraints.

The two relationships are defined as:

  1. The MANAGER_ID in the DEPARTMENTS table must be a value of the EMPLOYEE_ID in the EMPLOYEES table (managers must exist as employees).
  2. The DEPARTMENT_ID in the EMPLOYEES table must be a value of the DEPARTMENT_ID in the DEPARTMENTS table (an employee's department must exist in the DEPARTMENTS table, or be null).

 

4 .

Next, you select the columns that you want to see in the report.

In the EMPLOYEES table, check the FIRST_NAME, LAST_NAME, JOB_ID, SALARY, and DEPARTMENT_ID columns. As you select these columns, note that they are added in the order that you select them into the window below the table objects.

In the DEPARTMENTS table, check the DEPARTMENT_NAME column.

Show Screenshot for Step

 

5 .

Click Run .

Show Screenshot for Step

The results are displayed in the Query Result window. Note that the department name in the report is coming from the DEPARTMENTS table and the rest of the data shown is from the EMPLOYEES table.

 

6 .

To view the SQL that was generated for you, click the SQL icon in the Query Result window.

Show Screenshot for Step

The Query Result SQL window overlays your report results.

Show Screenshot for Step

You can copy this query to another editor if desired. Click the close icon in the upper right corner.

 

Sorting the Results

Oracle SQL Developer 3.0 makes it very easy to make changes to results. In this topic, you sort the results using the sort type and sort order columns.

Perform the following steps:

1 .

In this section, you build a report to show per department (sorted alphabetically), the employees within the department.

In the Main Query Builder window, right click on the EMPLOYEES table name. Select Uncheck All from the sub-menu.

Show Screenshot for Step

When you Uncheck All, the columns are removed from the middle pane in the Query Builder.

Show Screenshot for Step

 

2 .

In this report, you want to have the DEPARTMENT_NAME column listed first, and per department name, show the employee names and salaries.

Check the FIRST_NAME, LAST_NAME, and SALARY columns in the EMPLOYEES table object. Note that these columns are added in the window below, in the order that you select them.

Show Screenshot for Step

 

3 .

In this report, we are not interested in the relationship between the EMPLOYEE_ID column in the EMPLOYEES table and the MANAGER_ID column in the DEPARTMENTS table.

Remove the relationship by selecting the line that links the two columns. Right click and select Remove from the sub-menu.

Show Screenshot for Step

The relationship line is removed.

Show Screenshot for Step

 

4 .

To sort your results, click the Sort Type field next to the DEPARTMENTS.DEPARTMENT_NAME expression.

Show Screenshot for Step

Choose Ascending.

Show Screenshot for Step

 

5 .

Click Run and view the results.

Show Screenshot for Step

 

Adding the Query to the SQL Worksheet

As you work in the Query Builder window, your SQL statement is built and is visible in the SQL Worksheet area. You can modify the query either in the Worksheet or in the Query Builder.

Perform the following steps:

1 .

To clear the query builder window, close the EMPLOYEES and DEPARTMENTS tables by clicking the close icon.

Show Screenshot for Step

 

Show Screenshot for Step

 

2 .

Switch to the SQL Worksheet window by clicking the Worksheet tab.

Show Screenshot for Step

Note: You can always open the SQL worksheet, from the main menu by selecting Tools > SQL Worksheet.

 

3 .

Type the following query into the SQL Worksheet.

SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

Note: You can copy the contents of the 3join.sql script from the files.zip file you downloaded in the prerequisites section.

Show Screenshot for Step

 

4 .

Click the Query Builder tab to switch to the Query Builder view.

Show Screenshot for Step

 

5 .

 

The graphical view of the Select statement from the worksheet is displayed. You can move the objects to better view them by dragging an object to another location. This is handy when you have multiple objects in the graphical view and you want to better organize them.

Show Screenshot for Step

 

Show Screenshot for Step

 

6 .

Click Run . The results of the query is displayed. This query displays for an employee Id, his city and department name.

Show Screenshot for Step

 

7 .

You can apply filters to this query. For example, show only Seattle employees.

Go to the criteria column and enter I.city as Seattle, then press Enter.

Show Screenshot for Step

 

8 .

Click the Worksheet tab.

Show Screenshot for Step

Observe that the criteria you added in the Query Builder window is reflected in the Worksheet.

 

9 .

Go back to the Query Builder by clicking the Query Builder tab.

Click Run .

Observe that only employees who live in Seattle are displayed in the output.

Show Screenshot for Step

 

Summary

In this tutorial, you have learned how to:

Hardware and Software Engineered to Work Together About Oracle |Oracle and Sun | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights