Lab 1 Introduction to Oracle and SQL This lab exercise has two primary purposes 1 to acquaint you with the computing environment and basic tools that we will be using this semester and 2 to introduce you to simple relational joins and database queries using the structured query language SQL For the SQL queries we will use a set of seven small tables that are similar in structure and content to the types of information about property ownership and landuse that are maintained by most local assessing and planning offices For ArcView we will use the lab exercises from 11 204 one of the prerequisites for this course Part I Attach CRL Lockers to your computer In order to access the files in the CRL space you need to attach several CRL lockers to your computer These network storage lockers will already be attached if you are doing these exercises from CRL PCs If you are an MIT student and wish to do the exercises on your own laptop while connected to the internet talk to the staff You ll need the SQL Plus application software or an ODBC driver for Oracle Open my computer in the menu Tools choose Map Network Drive Map grotto crlclass as the Drive K Click on different user name in the new windows type in your CRL user account and password make sure put crl before your username Then click OK Click Finish Wait for a minute for the locker being attached In the same way add in the following lockers You don t need to specify the username and password again after you map the first one since Windows remember the username and password during the session Drive M crypt massgis Drive S grotto crlspace1 Drive X agora crlbucket The naming system of the lockers is slightly different from the one in CRL H drive is used here as the Athena locker rather than the CRL locker You can access your CRL space through drive S open Drive S and go one level down to find your folder PART II ORACLE SQLPlus9i SETUP For our SQL exercises we will use databases stored in an Oracle database engine running on a CRL server named bulfinch mit edu We will use so called client server technology to query these databases via MITnet using SQL Plus an Oracle client that connects to the backend database engine using Oracle s Net9 with standard TCP IP protocols Most of this is transparent to the user Here are the steps needed to connect to the database engine from any windows XP computers Steps A Login to the windows XP using your Athena account Make sure the domain is set to ATHENA MIT EDU Kerberos Realm B Start Programs Oracle SQL Plus C Fill in the user name and password of your CRL Oracle account Use CRL as the Host String Then press OK On the first class you are going to use the public account user name parcels and the password will be given by the instructor in class Then we will collect your information and create a CRL Oracle account for you In the following labs you are going to use your own CRL Oracle account D The interface of Oracle SQL Plus looks like the following SQL is the prompt in Oracle SQL Plus which indicates that it is ready for Oracle SQL Plus to accept your commands PART III Running Basic SQL Select Statement Queries At this point you have all the connections and windows in place and you are ready to begin doing SQL queries Now let s use our small hypothetical parcel and property tax database to get a feel for how we can use SQL Plus to run simple queries Elsewhere on the class help pages we describe this sample parcel database and the basic structure of the SELECT command Type in some simple queries or you can use cut and paste to grab pieces of queries from any text editors You will probably find it helpful to edit the queries in a separate window with text editors such as Notepad When you are finished editing a query you can cut and paste it into the SQL window Try out each of the following SQL examples of SELECT statements Query Tasks The simplest query 1 Kindly refer to the Labs section SQL Statements SELECT FROM tax 2 3 4 5 6 7 listing the TAX table A query with an expression and a column alias A simple query that aggregates groups over a whole table This example uses a literal string in the SELECT list A simple query using the IS NULL syntax to list only those parcels with at least 20 000 sq ft and no missing value Compare the results of the query 5 with query 6 Why are they different A query to find values within a certain range 9 Another way of writing the query above using the BETWEEN keyword A simple join between PARCELS and FIRES Note that only those parcels which had fires are listed 10 A slight variation of query 9 8 SELECT parcelid landval bldval tot val tax FROM tax SELECT COUNT MIN tax MAX tax AVG tax FROM tax SELECT parcelid Total prop value is landval bldval tax FROM tax SELECT FROM parcels WHERE sqft 20000 OR sqft IS NULL SELECT FROM parcels WHERE sqft 20000 OR sqft NULL SELECT FROM parcels WHERE sqft 10000 AND sqft 100000 SELECT FROM parcels WHERE sqft BETWEEN 10000 AND 100000 SELECT p f estloss FROM parcels p fires f WHERE p parcelid f parcelid SELECT p parcelid p sqft p landuse f estloss FROM parcels p fires f WHERE p parcelid f parcelid ORDER BY f estloss PART IV TUNING SQL PLUS AND SPOOLING OUTPUT TO A FILE The end of the SQL Help web page contains some useful tips and tricks to customize your SQL Plus environment to save and retrieve SQL queries and to spool SQL output tables into text files Review these tips and tricks paying particular attention to 1 the use of set linesize nnn to set the line width to nnn characters 2 the use of COLUMN sss FORMAT A5 and the like to control formatting of column output and Kindly refer to the Labs section 3 the use of the spool file command to start and stop the spooling of SQL Plus output to a Unix file Regarding the spool command if you do not specify a full path name for the file it will be written in the default directory that is your H drive Try out the spool command by spooling to a file running one or two SQL queries turning off the spooling and then opening up the spooled file in your text editor window PART V PRINTOUT We cannot print the SQL statement and the query result directly from Oracle SQL Plus There are two alternative ways to print them out 1 Spool the SQL statements to a LST file open it in a text editor such as Notepad then print it there 2 Copy the SQL statements and paste them in a text editor then print …