Question

NOTE: If the default database date format for the Oracle Server you are using does not show a four digit date you can issue a command to change the default to something you prefer. Examples:

alter session set nls_date_format ='DD-MON-YYYY';

alter session set nls_date_format ='Month DD, YYYY';

1. Create a logical view of your presidents table named ModernPresidents that shows all of the presidents since the end of World War II. Harry Truman was the president at that time so you can start with him.   In your view please show these columns:

PRESNUM

NAME

TOOKOFFICE

LEFTOFFICE

DAYSINOFFICE (the number of days they were in office)

Specify that the order of the table should be PRESNUM ascending

Hint: If you subtract one date from another what is returned is the number of days between those two dates.

2. Create a view name PresidentStates that has two columns: The name of every state in the presidents table and the number of presidents that have been from that state. Name that column NUMPRESIDENTS and have the rows in your view in order from the state with the most presidents to the state with the fewest presidents. So that the number of presidents in each state doesn’t have to be computed over and over again every time someone uses this view make it a materialized view. Since updates to this table will not be very frequent make any update of the materialized view a full update and have it happen whenever a change to the presidents table is committed.    You can test if your MV works by change a presidents home state, looking at your view, and then change the state back again.

Answer 1

Solution:

Part 1 View :

it has been assumed that PRESIDENT Table has got all the required columns to be used in the view. The WHERE clause specifies the condition to pick up only the PRESIDENTS which had TOOKOFFICE date > WWII end date. (Kindly replace the MM-DD in green below, with the date to be used for the WW-II end date). The expression LEFTOFFICE - TOOKOFFICE will yield the days in office.

CREATE VIEW ModernPresidents

(PRESNUM, NAME, TOOKOFFICE, LEFTOFFICE,DAYSINOFFICE)

AS SELECT PRESNUM, NAME, TOOKOFFICE, LEFTOFFICE,LEFTOFFICE - TOOKOFFICE

FROM PRESIDENT

WHERE TOOKOFFICE > DATE '1945-MM-DD'

Part 2 : Materialized View

It has been assumed that PRESIDENT table has got the "State" column which stores the name of the state.

Brief Description: The REFRESH COMPLETE will take care of the full update requirement. ON COMMIT clause will fulfill the requirement for the refresh to happen when table PRESIDENT is committed. ORDER BY will sort the values in descending order of the "number of presidents" .

create materialized view PresidentStates

REFRESH COMPLETE ON COMMIT

as

select count(PRESNUM) as NUMPRESIDENTS, state

from President

Group by state

order by count(PRESNUM) DESC

;