Given the following tables…

PERSON

———————————–

PK  person_id             NUMBER(10)    NOT NULL

first_name            VARCHAR2(100)

preferred_first_name  VARCHAR2(100)

last_name             VARCHAR2(100) NOT NULL

date_of_birth         DATE

hire_date             DATE

occupation            VARCHAR2(1)

ADDRESS

———————————–

PK  address_id            NUMBER(10)    NOT NULL

FK  person_id             NUMBER(10)    NOT NULL

address_type          VARCHAR2(4)   NOT NULL

street_line_1         VARCHAR2(100)

city                  VARCHAR2(100)

state                 VARCHAR2(100)

zip_code              VARCHAR2(30)

1) Write a query to select all rows from person.  If the person row has a value in preferred_first_name, select the preferred name instead of the value in first name.  Alias the column as REPORTING_NAME.

2) Write a query to select all rows from person that have a NULL occupation.

3) Write a query to select all rows from person that have a date_of_birth before August 7th, 1990.

4) Write a query to select all rows from person that have a hire_date in the past 100 days.

5) Write a query to select rows from person that also have a row in address with address_type = ‘HOME’.

6) Write a query to select all rows from person and only those rows from address that have a matching billing address (address_type = ‘BILL’).  If a matching billing address does not exist, display ‘NONE’ in the address_type column.

7)  Write a query to count the number of addresses per address type.

Output:

address_type     count

————-   ——

HOME                99

BILL               150

8)  Write a query to select data in the following format:

last_name           home_address                          billing_address

——————  ————————————  —————————————

Smith               89 Lyon Circle, Clifton, VA 12345     25 Science Park, New Haven, CT 06511

Jones               212 Maple Ave, Manassas, VA 22033     275 Winchester Ave, New Haven, CT 06511

9) Write a query to update the person.occupation column to ‘X’ for all rows that have a ‘BILL’ address in the addresstable.

Please send a word document with the SQL and the tested output results screenshots.