Using Query Builder in Oracle SQL Developer 3.0

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.

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.

Select the Query Builder tab to open 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:

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).

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.

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 and view the results.

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
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.

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 . The results of the query is displayed. This query displays for an employee Id, his city and department name.

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.