Waiting for answer This question has not been answered yet. You can hire a professional tutor to get the answer.
Given the following tables… PERSON ----------------------------------- PK person_id NUMBER(10) NOT NULL first_name VARCHAR2(100) preferred_first_name VARCHAR2(100)
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.