HomeOracle Page 2 - Learning to Query in Oracle XE
Running SQL Queries - Oracle
Querying the database is one of the most common tasks that you may have to carry out routinely. This tutorial shows you the options for running SQL queries and how to run the queries against the database in the Oracle Express Edition, Oracle XE for short.
In the HR database there are a couple of tables, which include DEPARTMENTS and Employees. Here is the table design of the Employees table as seen in the Object Browser (review the first article).
Select query against the Employees table
Let's just run a query to return everything in the Employees table with this simple query Select * from employees. This query can be saved or executed by clicking the Run button. The results are seen in this next picture. Since we have chosen to display 10 rows of results, the Results pane shows just the 10 rows. If you want you may show more rows by clicking on the drop-down, Display. As you can see from the design view there are more columns in this table than shown in this screen. You could scroll to the right, and see the rest of the columns. Once the query is run you may find other details. The other details are accessed by clicking on the tabs in the bottom, tabbed window.
Click on the Explain tab in the bottom pane. This brings up the next screen which shows the Query Plan, the indexes on columns, and details of the table columns. This is really nice without having to move to a new window.
Next you click on the Describe tab, and you will get a message which asks you to type the PL/SQL statement "Describe." "Describe" (or just Desc) is a keyword in Oracle's SQL with the following Oracle syntax:
Lists the column definitions for the specified table,
view or synonym, or the specifications for the
specified function or procedure.
Typing in the statement Describe Employees produces the result in the next screen. You will get complete design information on the table. From this tab you cannot go back to the Explain tab because the context has changed. You need to run the query once again to go back to what you interrupted by typing in the "Describe Employees" statement. Of course you could have saved the query before running the "Describe employees" query, which allows you to come back to any of your saved queries, as you will see shortly.
Saving the Query
Let's run the query "Select * from Employees" again and save the query this time, after reviewing the results. Unless you Save the query, the Saved SQL tab will show "No Saved SQL Found." Now click on the Save button to save the query and you will be asked to provide a Name a Description to the query you want to save, as shown in the next picture.
The next time you click on Saved SQL you will see the following screen. It shows the owner, name, description it was saved with, and other details. You may bring up the query by clicking on the hyperlink on the Name ("AllEmpl" in this screen) of the query and run again to get some fresh results.
The last tab, History, shows the chronological query-related information. This is very useful information if you want to backtrack your work. In this screen you can search for saved queries by a chosen owner, and display the results. After placing a check mark for the query, you can even delete it from the saved list. After going back in history, you can bring up an old query and the statement gets copied to the SQL Pane; you can now run it if you like. It is a great way to bring up old queries and refine them as per the situation. The only complaint I have against this UI is that both the panes should have had adjustable scroll bars (or is it a limitation of my display device?). When all the results are shown the UI occupies the entire screen height.