Lab 4: Querying Oracle from a Client Application Objectives SQL*Plus is not the only interface to Oracle. You can use widely available desktop office productivity software (specifically, Microsoft Access and Microsoft Excel) to connect to the Oracle database and run queries. These packages offer a graphical user interface for database queries, which contrasts starkly with the text-based SQL*Plus interface we have used up to now. This lab will help familiarize you with using Access to query Oracle. Agenda You will use Microsoft Access to: • Connect to the Oracle database • Run some familiar queries • Save query output as a dBase DBF file • Save query output to a delimited text file • Use the SQL view to alter a SQL statement directly. Querying Oracle Using Microsoft Access Before You Start: Oracle and ODBC Checkout Using Microsoft Access Launch Microsoft Access using the Start > Programs > Microsoft > Access menu item. In the right-hand pane "New", choose to New Blank Database.In the file dialog that follows, name your new database as "username_lab4.mdb."You should now see a database window like the one below: Click on the "New" button, then select "Link Table" as shown in the window below.In the file dialog that appears next, in the "Files of Type" area, select "ODBC Databases()".In the "Select Data Source" window, click on the "Machine Data Source" tab, then pick "CRL Oracle" as the data source.When you click OK, you'll have to log on. Log onto your Oracle account (the username is not case sensitive). After a pause, you'll see a "Link Tables" window like the one below.The list of tables here looks different from the output of "SELECT * FROM CAT;" in SQL*Plus. Notice that many tables owned by users are visible here. Here, all the non-system objects that are visible to your account are shown in "fully qualified" form, meaning that tables are shown in the USER.TABLE format. Hence, an object such as the FIRES table in the parcels database shows up as PARCELS_DATA.FIRES. Scroll down the list, then select the tables PARCELS_DATA.FIRES, PARCELS_DATA.OWNERS, and PARCELS_DATA.PARCELS. You can select more than one table by clicking on each one. Your window, with tables selected, should look like this: (Note: Sometimes when you link a table, you will be asked to select the "Unique Record Identifier" (a.k.a. the primary key) for each table. You can select multiple fields (up to 10) for the primary key. That does not happen with these tables because the primary keys were defined in Oracle, and Access recognizes that.) Now your database window will look like this, showing your linked tables.Notice that Access prepended the name of the user who owned the table to its name. This is inconvenient for us, so let's rename the tables back to FIRES, OWNERS, and PARCELS. RIGHT-click on the table's name, select "Rename" from the context menu, and rename the tables. After you're done, your window should look like this: Now, let's build and run a query in Access:Click on the "Queries" tab, and then click on the "New" button to create a new query. Choose "Design View", and then click "OK". • The "Show Table" dialog box lets you add one or more tables to a query by selecting the table and clicking the "Add" button. The window will stay open to let you add more tables. Close the window with the "Close" button. Add the tables to the query in this order: o FIRES o PARCELS o OWNERSAfter you close the "Show Table" dialog box, you will see the query window. It should resemble the image below. Notice that the unique identifier columns you specified earlier are shown in bold. Also notice the line connecting the PARCELID column in the FIRES and OWNERS tables. This line represents a join between these two tables, and Access draws it automatically because it recognizes that the columns are both keys (or part of a key) and have the same name. However, since the relationship between PARCELS and OWNERS is not drawn, because the columns do not have the same name. • Let's join PARCELS and OWNERS together. You will probably find it easier to do this if you: o Maximize the query window o Move the PARCELS table in between OWNERS and FIRES (if it isn't already) o Resize the top pane of the window to provide more room for the table descriptions o Lengthen the size of the PARCELS table so you can see all its columns.Now, click and hold on a column name (e.g., OWNERNUM in OWNERS), then drag until you are over the corresponding name in another table (e.g., ONUM in PARCELS) to describe a join between the columns. When you are done, your query window should look like this: • Now we need to indicate which columns we want to show in the results. Double click on the following columns to make them appear in the output: o PARCELS.PARCELID o PARCELS.SQFT o OWNERS.ONAME o FIRES.FDATEo FIRES.ESTLOSS • in the toolbar. Your results should look like this: Run the query now by choosing Query > Run or clicking on the "Run" button• Use the View > SQL View menu item to see the SQL text of your query. Here is the query again, reformatted for readability: SELECT PARCELS.PARCELID, PARCELS.SQFT, OWNERS.ONAME,FIRES.FDATE, FIRES.ESTLOSSFROM (FIRES INNER JOIN PARCELS ON FIRES.PARCELID =PARCELS.PARCELID)INNER JOIN OWNERS ON PARCELS.ONUM = OWNERS.OWNERNUM; Notice that Access uses a different syntax than we have seen to specify joins. Instead of using the WHERE clause, it uses an "INNER JOIN" syntax in the FROM clause that explicitly names the columns being joined. Although this is unfamiliar (and incompatible with Oracle when using the SQL*Plus interface), in some ways this method is better than Oracle's, since it makes it very clear what table is joined to what, whether the join is an inner (i.e., "normal") or outer join, and what columns are needed to perform the join. Oracle's syntax buries this information in the WHERE clause where it can become obscured.• You can edit the query in the SQL View window. Add the condition "WHERE ESTLOSS >= 40000" to the SQL statement (remember to place this clause before the semicolon). Now switch back to design view with View > Design View. Notice that your new condition has been added to the criteria in the graphical interface (look under ESTLOSS in the lower pane of the window). • Now, let's see the results. Click on the "Run" button to run your query. After resizing the ONAME column and the window, the results should look like the image below.• Now, let's change