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. Passwords for the BCC labs will be available next class. Therefore this assignment can be passed in at the end of next week. However, there will be another assignment coming, so try to complete it as soon as possible.

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.

Create a donor table of your own with the layout described above and the data shown above.

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. Send me the Word document via email. If you use another word processor, please make sure I can open the document in Word. Please note, if you do not have an email account you must get one immediately. Free accounts are available from Administrative Computing in K Building or you can get a free account with Juno NetZero or some other free service provider.

1. Create a query that displays 4 fields from the donor database and give each field an alias/column header.
2. Create a query to display the unique/distinct cities.
3. 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 comma and a space and then ZIP.
4. Create a query to sort by yrgoal in descending order.
5. Create a query to sort by city within state.
6. Create a query to display all names where the first name starts with letters in the J-R range. Use the BETWEEN clause.
7. Create a query to display all donors that have the letters an in their names.
8. Create a query to display all donors that live in TX, MA, CN or NY. Use the IN clause.
9. Create a query to list all donors who live in Providence or Seekonk and display in order by name within city.
10. Create a query to display all people that gave their first contribution in 98.