Introduction to Oracle Assignment

This assignment should be done using Oracle. I assume most of you will be doing the work at home or at the office. If you are planning to use the BCC labs for your work, please let me know. When you do this assignment, you should take pictures of the screen to show me the SQL you entered and to show me the results.

SQL> DESCRIBE donor;

Name Null? Type

-------------------------------

IDNO VARCHAR2(5)

NAME VARCHAR2(15)

STADR VARCHAR2(15)

CITY VARCHAR2(10)

STATE VARCHAR2(2)

ZIP VARCHAR2(5)

DATEFST DATE

YRGOAL NUMBER(7,2)

CONTACT VARCHAR2(12)

SQL> SELECT *

2 FROM donor;

IDNO
NAME             STADR           CITY       ST ZIP   DATEFST   YRGOAL
   CONTACT
-----
--------------- --------------- ---------- -- ----- ---------
--------- ------------
11111
Stephen Daniels 123 Elm St      Seekonk    MA 02345 03-JUL-98      
500 John Smith
12121
Jennifer Ames   24 Benefit St   Providence RI 02045 24-MAY-97      
400 Susan Jones
22222
Carl Hersey     24 Benefit St   Providence RI 02045 03-JAN-98        
  Susan Jones
23456
Susan Ash       21 Main St      Fall River MA 02720 04-MAR-92      
100 Amy Costa
33333
Nancy Taylor    26 Oak St       Fall River MA 02720 04-MAR-92       
50 John Adams
34567
Robert Brooks   36 Pine St      Fall River MA 02720 04-APR-98       
50 Amy Costa

6 rows selected.

PART A of Assignment #1:

Create a donor table of your own with the layout described above and the data shown above. Note that you need to enter null for the YRGOAL of Carl Hersey. To do this, use the word NULL instead of the data when you are inserting rows into your table. Take a picture of the screen to show me the structure/description of the table and another picture to show me the table with the data entered.

PART B of Assignment #1:

Do the following queries. Show the query and show the result. To pass this in, take the SQL and the results from Oracle and paste them into a Word document. You should use the Courier New font. Send me the Word document via email. If you use another word processor, please make sure I can open the document in Word.

1. Create a query to display 4 fields from the donor table. Each one should have an alias column header.

2. Create a query to display the unique or distinct states from the donor table that you just created.

3. Create a query to sort by city in descending order.

4. Create a query to sort by city in ascending order and within each city the yrgoal in descending order.

5. Create a query to concatenate the street address so you print stadr followed by a comma and a space and then city followed by a comma and a space and then state followed by a space and then zip.

6. Create a query to display all contacts that have a Jo in their name.

7. Create a query to display all names where the first name starts with letters in the J-R range. Use the BETWEEN clause.

8. Create a query to display all donors that live in Providence, Seekonk or Westport. Use the IN clause.

9. Create a query to display all people that gave their first contribution in 97 or 98.

10. Create a query to list all donors who have Providence or Fall River as their city and display them in the order of name within city within state.