This tutorial shows you how to use the Query Builder feature in Oracle SQL Developer 3.0.
Approximately 15 minutes.
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.
The following is a list of software requirements:
Before starting this tutorial, you should:
. |
Install Oracle SQL Developer 3.0 from OTN. Follow the readme instructions here. |
---|---|
. |
Install Oracle Database 11g with the Sample schema. |
. |
Unlock the HR user. Login to SQL Developer as the SYS
user and execute the following command: Note: This tutorial is developed using Oracle SQL Developer
3.0. |
---|
. |
Download and unzip the files.zip to a local folder on your file system. In this tutorial, we use the C:\sqldev3.0 folder. |
---|
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.
. |
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).
|
---|---|
. |
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.
|
. |
Your Oracle SQL Developer opens.
|
. |
In the Connections navigator, right-click Connections and select New Connection.
|
. |
The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test. Connection Name: HR_ORCLUser Name: hr Password: <your_password> (Select Save Password) Hostname: localhost SID: <your_own_SID>
|
. |
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.
|
. |
The connection was saved and you see the newly created connection in the Connections list.
|
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.
. |
Select the Query Builder tab to open Query Builder window.
|
---|
. |
Drag and drop the EMPLOYEES table from the Connections navigator to the Query Builder window.
|
---|
. |
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. 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:
|
---|---|
. |
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.
|
. |
Click Run 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.
|
. |
To view the SQL that was generated for you, click the SQL icon in the Query Result window. The Query Result SQL window overlays your report results. You can copy this query to another editor if desired. Click the close icon in the upper right corner.
|
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:
. |
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. When you Uncheck All, the columns are removed from the middle pane in the Query Builder.
|
---|---|
. |
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.
|
. |
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. The relationship line is removed.
|
. |
To sort your results, click the Sort Type field next to the DEPARTMENTS.DEPARTMENT_NAME expression. Choose Ascending.
|
. |
Click Run
|
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:
. |
To clear the query builder window, close the EMPLOYEES and DEPARTMENTS tables by clicking the close icon.
|
---|---|
. |
Switch to the SQL Worksheet window by clicking the Worksheet tab. Note: You can always open the SQL worksheet, from the main menu by selecting Tools > SQL Worksheet.
|
. |
Type the following query into the SQL Worksheet. SELECT employee_id, city, department_name Note: You can copy the contents of the 3join.sql script from the files.zip file you downloaded in the prerequisites section.
|
. |
Click the Query Builder tab to switch to the Query Builder view.
|
.
|
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.
|
. |
Click Run
|
. |
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.
|
. |
Click the Worksheet tab. Observe that the criteria you added in the Query Builder window is reflected in the Worksheet.
|
. |
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.
|
In this tutorial, you have learned how to:
![]() |
About
Oracle |Oracle and Sun | ![]() |