Oracle database queries examples
After completing this How-To, you should be able to understand:
How to use the SQL Worksheet to enter SQL and SQL*Plus commands
Table of Contents
- Write a Select Statement
- Retrieve columns
- Retrieve rows
Oracle SQL Developer provides a SQL Worksheet that you can use to query data, by writing simple or complex SQL statements. In this How-To, we look at the most basic of these, select all the data in a table, and restricting this query by reducing the columns or rows you retrieve.
3.1. Write a Select Statement
Once you have a database connection, you are ready to browse the schema, query and modify data.1.
Once you have created a database connection, you need to ensure you have the SQL Worksheet open. If the worksheet is not open, use the context menu to open it.2.
Once connected, you should see the SQL Worksheet window.3.
Now you are ready to start. Query all the data in the DEPARTMENTS table. Enter
select * from departments;
Click Execute Statement or F9.
Notice the number of rows retrieved is displayed below the Results tab.
3.2. Retrieve Columns
Instead of selecting all the columns from a table, you can itemize them, selecting only the data you require.1.
Instead of typing in each column name, you can just drag the table name from the Connection Navigator. Expand the Tables node and drag the EMPLOYEES table onto the worksheet.
For more complex queries or statements, use the Format function (Ctrl+F7) to make it easier to read the SQL. This can be found in the context menu.2.
Now delete the last 3 columns from the statement. An example is shown below.
Remove the ', ' after the column SALARY and click F9 to retrieve the records.
3.3. Retrieve Rows
Often you want to restrict the number of records returned. You add a Where Clause to do this. These can be complex and involved. We will start with the most basic.1.
Using the same Select statement you have above, remove the ' ; ' and add
where department_id = 100;
Click F9. The Results tab shows fewer records returned.
4. Use SQL*Plus commands
The SQL Worksheet allows you to use a selection of SQL*Plus commands. SQL*Plus commands have to be interpreted by the SQL Worksheet before being passed to the database. Some commands are not supported and are hence ignored and are not sent to the Oracle database. For a list of unsupported SQL*Plus commands, see SQL Developer on OTN.1.