FinLab.sql

--Give the name and past description of every political party
--that hasn't worked with a president whose term ended 
--before 6/15/1850
CREATE OR REPLACE VIEW FinQuery1 AS
SELECT DISTINCT PRTY.PRTY_NAME, PRTY.PRTY_PASTDESC
FROM UPOLITICALPARTY PRTY
WHERE NOT EXISTS 
   (SELECT WW.*
    FROM UWORKEDWITH WW
    WHERE WW.PRTY_NAME = PRTY.PRTY_NAME 
    AND NOT EXISTS 
       (SELECT PRES.*
        FROM UPRESIDENT PRES
        WHERE PRES.PRES_ID = WW.PRES_ID
        AND PRES.TERM_END > '15-JUN-1850')); 

--Give only politician id and relationship of first ladies 
--who have lost in an election
CREATE OR REPLACE VIEW FinQuery2 AS
SELECT DISTINCT FL.POL_ID, FL.FL_RELATION
FROM UFIRSTLADY FL
WHERE FL.POL_ID NOT IN  
    (SELECT POL2.POL_ID 
    FROM UPOLITICIAN POL2 
    WHERE POL2.POL_ID NOT IN
        (SELECT RAN.ELCT_LOSER 
        FROM URANIN RAN)); 

--Give the first name, last name, and date of death of
--all politicians who have not ran in an election
--before 2020
CREATE OR REPLACE VIEW FinQuery3 AS
SELECT DISTINCT POL.POL_FNAME, POL.POL_LNAME, POL.POL_DOD
FROM UPOLITICIAN POL
WHERE POL.POL_ID NOT IN
    (SELECT RAN.ELCT_LOSER
     FROM URANIN RAN
     WHERE RAN.ELCT_YEAR < 2020)
AND POL.POL_ID NOT IN
    (SELECT PRES.POL_ID
     FROM UPRESIDENT PRES
     WHERE PRES.TERM_START < '01-JAN-2020');


--Give the first name, last name, and the date they started their term of all vice presidents, 
--and give all, if any, of the dates they started being president.
CREATE OR REPLACE VIEW FinQuery4 AS
SELECT DISTINCT POL.POL_FNAME, POL.POL_LNAME, VP.TERM_START AS VP_TERMSTART, PRES.TERM_START AS PRES_TERMSTART
FROM UPOLITICIAN POL, UVICEPRES VP LEFT JOIN UPRESIDENT PRES ON VP.POL_ID = PRES.POL_ID
WHERE POL.POL_ID = VP.POL_ID;

--Give full details of all presidents, and give the first and last names
--of presidents who had lost an election, if any. 
CREATE OR REPLACE VIEW FinQuery5 AS
SELECT DISTINCT POL.POL_FNAME, POL.POL_LNAME, PRES.*
FROM UPOLITICIAN POL JOIN URANIN RAN ON POL.POL_ID = RAN.ELCT_LOSER 
RIGHT JOIN UPRESIDENT PRES ON RAN.ELCT_LOSER = PRES.POL_ID;

--Give all president ids and vice president ids of VPs who started their term on the same date
--as a president's term, if any, along with all vice president ids and president ids of presidents 
--who started their term on the same date as the VP's term, if any. Order results by president ids.
CREATE OR REPLACE VIEW FinQuery6 AS
SELECT DISTINCT PRES.PRES_ID, VP.VP_ID
FROM UPRESIDENT PRES FULL JOIN UVICEPRES VP ON PRES.TERM_START = VP.TERM_START
ORDER BY PRES.PRES_ID;

--Give the names of events that happened when the president in office
--ran in an election with less than a 55% voter turnout, along with the
--first and last names of their first ladies if they were wives of said president
CREATE OR REPLACE VIEW FinQuery7 AS
SELECT DISTINCT HAP.EVNT_NAME, POL.POL_FNAME, POL.POL_LNAME
FROM UELECTION ELCT, URANIN RAN, UPRESIDENT PRES, UHAPPENED HAP, UFIRSTLADY FL, UPOLITICIAN POL
WHERE ELCT.ELCT_YEAR = RAN.ELCT_YEAR
AND RAN.ELCT_WINNER = PRES.PRES_ID
AND PRES.PRES_ID = HAP.PRES_ID
AND PRES.PRES_ID = FL.PRES_ID
AND FL.POL_ID = POL.POL_ID
AND ELCT.ELCT_VTURNOUT < 55.0
AND FL.FL_RELATION = 'Wife';

--Give the first and last names of all presidents who worked
--with a Republican majority senate and a Republican administration.
CREATE OR REPLACE VIEW FinQuery8 AS
SELECT DISTINCT POL.POL_FNAME, POL.POL_LNAME
FROM UVICEPRES VP, UPOLITICIAN POL
WHERE POL.POL_ID = VP.POL_ID
AND VP.SENATE_MAJORITY = 'Republican'
GROUP BY POL.POL_FNAME, POL.POL_LNAME
HAVING COUNT(VP.SENATE_MAJORITY) <
    (SELECT COUNT(WW.PRTY_NAME)
     FROM UWORKEDWITH WW
     WHERE WW.PRTY_NAME = 'Republican');

--Get details on all events that occurred in a Democratic-Republican administration
CREATE OR REPLACE VIEW FinQuery9 AS
SELECT DISTINCT EVNT.* 
FROM UEVENT EVNT
WHERE EVNT.EVNT_NAME IN 
    (SELECT HAP.EVNT_NAME 
    FROM UHAPPENED HAP
    WHERE HAP.PRES_ID IN 
        (SELECT WW.PRES_ID 
        FROM UWORKEDWITH WW 
        WHERE WW.PRTY_NAME = 'Democratic-Republican')); 

--Get the names of political parties, along with the number of
--administrations they worked with, if they worked with less than 4 admins
CREATE OR REPLACE VIEW FinQuery10 AS
SELECT WW.PRTY_NAME, COUNT(WW.PRES_ID) AS TOTAL_PRESIDENTS 
FROM UWORKEDWITH WW 
GROUP BY WW.PRTY_NAME 
HAVING COUNT(WW.PRES_ID) < 4;