EEOS 381 - Applications of Geographic Information Systems
Spring, 2008

- Lab 7 -

 
Objective:
Become familiar with some basic SQL (Structured Query Language) commands for creating and working with tables in Oracle. You will use the SQL*Plus application, using your account, to interact with the database.

Steps:

Part 1
In order to learn some basics about SQL, the first part of the lab is to complete the exercises in the online SQL tutorial at http://sqlcourse.com.

1. Complete Exercises 1 through 8. You will enter the commands in the text box on each page and hit the ‘Submit Query’ button to view the results of the command. After viewing the results, hit the ‘Back’ button in your Web browser to return to the exercise.
http://SQLCourse2.com. Read through Exercises 3, 6, 7, 8 and 10.

Part 2  (10 points)
Using what you learned with the SQL tutorial, complete the following.

1. Open the Oracle SQL*Plus application by choosing Start > All Programs > Oracle > OraHome92 > Application Development > SQL*Plus
2. In the Log On dialog, enter your Oracle user name and password (both are your first initial and full last name) and the Host String, orgis
3. You will now be at the SQL> prompt, which is where you can enter commands.
4. Create a table in your account called GISTABLE with the following fields:
• STATE       varchar (20)
• ABBREV      varchar (2)
• GIS_USERS   number (6)
  
  
5. Insert three records into the table so that it looks like this:
• Vermont     VT   2536
• New Mexico  NM   3895
• California  CA   10562
  
  
6. Grant read privilege to the datareader user so that everyone else can see the table with the command
     GRANT SELECT ON GISTABLE TO DATAREADER;
   

Part 3  (25 points)
Answer the following questions using data in SDE (the table MGISDATA.BOUNDARY_POLY). Copy and paste both your commands and the results into a Notepad document.

1. List the table definition (fields and data types) of MGISDATA.BOUNDARY_POLY
2. What TOWN has TOWN_ID 100? (Display the TOWN and TOWN_ID)
3. What is the TOWN_ID of WORCESTER? (Display the TOWN and TOWN_ID)
4. How many records in the MGISDATA.BOUNDARY_POLY table?
5. How many towns have a 2000 population greater than 100,000?
6. Which towns have a 2000 population (POP2000) less than 500? (Display the TOWN names and their populations)
7. Display the same results from question 6 in ascending order of 2000 population.
8. Which towns have a 2000 population between 50,000 and 60,000? (Display the TOWN names and their populations in descending order of the population values).
9. Display the towns in Berkshire County (FIPS_STCO = 25003) that have a 2000 population greater than 5000. Order the results in ascending order of 2000 population
10. Using a join of MGISDATA.BOUNDARY_POLY to the table MGISDATA.TOWNS_POLY_AREACODE on the TOWN_ID field to display the town names and area codes of all towns that begin with the letter ‘T’. (The fields to display are TOWN in MGISDATA.BOUNDARY_POLY and AREACODE in MGISDATA.TOWNS_POLY_AREACODE). See Exercise 10 in the SQLCourse2.com tutorial for help with this question.
    

Copy your document with the BOTH the commands AND the results into T:\EEOS_381\381_labs_submit\lab7. Name it <your_username>_lab7.txt.

Due on Monday, March 24, 2008.