Lab 5 - part 2

gistfile1.txt

DROP TABLE DINOSAUR CASCADE CONSTRAINTS;
DROP TABLE DISCOVERY CASCADE CONSTRAINTS;
DROP TABLE ARCHEOLOGIST CASCADE CONSTRAINTS;
DROP TABLE LOCATION CASCADE CONSTRAINTS;

CREATE TABLE DINOSAUR(
	DINOSAUR_ID NUMBER(8) PRIMARY KEY,
	DINOSAUR_NAME VARCHAR2(50) NOT NULL,
	DIET_TYPE VARCHAR2(10) NOT NULL,
	COST NUMBER(10,2)
);

CREATE TABLE DISCOVERY(
	DISCOVERY_ID NUMBER(8) PRIMARY KEY,
	DISCOVERY_DATE DATE NOT NULL,
	FK_DINOSAUR_ID NUMBER(8) NOT NULL,
	FK_ARCHEOLOGIST_ID NUMBER(8) NOT NULL,
	FK_LOCATION_ID NUMBER(8) NOT NULL
);

CREATE TABLE ARCHEOLOGIST(
	ARCHEOLOGIST_ID NUMBER(8) PRIMARY KEY,
	FIRST_NAME VARCHAR2(15) NOT NULL,
	LAST_NAME VARCHAR2(15) NOT NULL
);

CREATE TABLE LOCATION(
	LOCATION_ID NUMBER(8) PRIMARY KEY,
	LOCATION_NAME VARCHAR2(20) NOT NULL,
	STATE_NAME VARCHAR2(15) NOT NULL
);

ALTER TABLE DISCOVERY
ADD CONSTRAINT FK_DINO_ID
FOREIGN KEY(FK_DINOSAUR_ID)
REFERENCES DINOSAUR(DINOSAUR_ID);

ALTER TABLE DISCOVERY
ADD CONSTRAINT FK_ARCHEOLOGIST_ID
FOREIGN KEY(FK_ARCHEOLOGIST_ID)
REFERENCES ARCHEOLOGIST(ARCHEOLOGIST_ID);

ALTER TABLE DISCOVERY
ADD CONSTRAINT FK_LOCATION_ID
FOREIGN KEY(FK_LOCATION_ID)
REFERENCES LOCATION(LOCATION_ID);

INSERT INTO DINOSAUR (DINOSAUR_ID,DINOSAUR_NAME,DIET_TYPE,COST) VALUES(1,'Tyrannosaurus Rex','Carnivore',100000);
INSERT INTO DINOSAUR (DINOSAUR_ID,DINOSAUR_NAME,DIET_TYPE,COST) VALUES(2,'Allosaurus','Carnivore',500000);
INSERT INTO DINOSAUR (DINOSAUR_ID,DINOSAUR_NAME,DIET_TYPE,COST) VALUES(3,'Diplodocus','Herbivore',250000);
INSERT INTO DINOSAUR (DINOSAUR_ID,DINOSAUR_NAME,DIET_TYPE) VALUES(4,'Triceratops','Herbivore');
INSERT INTO DINOSAUR (DINOSAUR_ID,DINOSAUR_NAME,DIET_TYPE,COST) VALUES(5,'Nodosaur','Herbivore',500000);
INSERT INTO DINOSAUR (DINOSAUR_ID,DINOSAUR_NAME,DIET_TYPE,COST) VALUES(6,'Spinosaurus','Carnivore',750000);

INSERT INTO LOCATION(LOCATION_ID,LOCATION_NAME,STATE_NAME) VALUES(1,'Black Hills','South Dakota');
INSERT INTO LOCATION(LOCATION_ID,LOCATION_NAME,STATE_NAME) VALUES(2,'Los Angeles','California');
INSERT INTO LOCATION(LOCATION_ID,LOCATION_NAME,STATE_NAME) VALUES(3,'Grand Canyon','Arizona');
INSERT INTO LOCATION(LOCATION_ID,LOCATION_NAME,STATE_NAME) VALUES(4,'Tampa','Florida');
INSERT INTO LOCATION(LOCATION_ID,LOCATION_NAME,STATE_NAME) VALUES(5,'Rome','New York');
INSERT INTO LOCATION(LOCATION_ID,LOCATION_NAME,STATE_NAME) VALUES(6,'Calgary','Canada');
INSERT INTO LOCATION(LOCATION_ID,LOCATION_NAME,STATE_NAME) VALUES(7,'Milwaukee','Wisconsin');

INSERT INTO ARCHEOLOGIST(ARCHEOLOGIST_ID,FIRST_NAME,LAST_NAME) VALUES(1,'Ernest','Hemingway');
INSERT INTO ARCHEOLOGIST(ARCHEOLOGIST_ID,FIRST_NAME,LAST_NAME) VALUES(2,'Ayn','Rand');
INSERT INTO ARCHEOLOGIST(ARCHEOLOGIST_ID,FIRST_NAME,LAST_NAME) VALUES(3,'Greta','Garbo');
INSERT INTO ARCHEOLOGIST(ARCHEOLOGIST_ID,FIRST_NAME,LAST_NAME) VALUES(4,'Fred','Astaire');
INSERT INTO ARCHEOLOGIST(ARCHEOLOGIST_ID,FIRST_NAME,LAST_NAME) VALUES(5,'Ginger','Rogers');

INSERT INTO DISCOVERY(DISCOVERY_ID,DISCOVERY_DATE,FK_DINOSAUR_ID,FK_ARCHEOLOGIST_ID,FK_LOCATION_ID) VALUES (0,TO_DATE('12/12/2010','MM.DD.YYYY'),1,1,1);
INSERT INTO DISCOVERY(DISCOVERY_ID,DISCOVERY_DATE,FK_DINOSAUR_ID,FK_ARCHEOLOGIST_ID,FK_LOCATION_ID) VALUES (1,TO_DATE('12/15/2011','MM.DD.YYYY'),1,1,3);
INSERT INTO DISCOVERY(DISCOVERY_ID,DISCOVERY_DATE,FK_DINOSAUR_ID,FK_ARCHEOLOGIST_ID,FK_LOCATION_ID) VALUES (2,TO_DATE('07/18/2012','MM.DD.YYYY'),3,5,1);
INSERT INTO DISCOVERY(DISCOVERY_ID,DISCOVERY_DATE,FK_DINOSAUR_ID,FK_ARCHEOLOGIST_ID,FK_LOCATION_ID) VALUES (3,TO_DATE('06/15/2009','MM.DD.YYYY'),4,4,5);
INSERT INTO DISCOVERY(DISCOVERY_ID,DISCOVERY_DATE,FK_DINOSAUR_ID,FK_ARCHEOLOGIST_ID,FK_LOCATION_ID) VALUES (4,TO_DATE('05/10/2011','MM.DD.YYYY'),4,1,6);
INSERT INTO DISCOVERY(DISCOVERY_ID,DISCOVERY_DATE,FK_DINOSAUR_ID,FK_ARCHEOLOGIST_ID,FK_LOCATION_ID) VALUES (5,TO_DATE('08/20/2012','MM.DD.YYYY'),2,5,7);
INSERT INTO DISCOVERY(DISCOVERY_ID,DISCOVERY_DATE,FK_DINOSAUR_ID,FK_ARCHEOLOGIST_ID,FK_LOCATION_ID) VALUES (6,TO_DATE('05/10/2012','MM.DD.YYYY'),5,1,6);
INSERT INTO DISCOVERY(DISCOVERY_ID,DISCOVERY_DATE,FK_DINOSAUR_ID,FK_ARCHEOLOGIST_ID,FK_LOCATION_ID) VALUES (7,TO_DATE('07/25/2010','MM.DD.YYYY'),1,2,4);
INSERT INTO DISCOVERY(DISCOVERY_ID,DISCOVERY_DATE,FK_DINOSAUR_ID,FK_ARCHEOLOGIST_ID,FK_LOCATION_ID) VALUES (8,TO_DATE('08/01/2016','MM.DD.YYYY'),5,5,2);

/* Question 1*/
SELECT DISCOVERY.DISCOVERY_ID,DINOSAUR.DINOSAUR_NAME,ARCHEOLOGIST.LAST_NAME ,LOCATION.LOCATION_NAME 
FROM DISCOVERY
JOIN DINOSAUR
    ON DISCOVERY.FK_DINOSAUR_ID = DINOSAUR.DINOSAUR_ID
JOIN ARCHEOLOGIST
    ON DISCOVERY.FK_ARCHEOLOGIST_ID = ARCHEOLOGIST.ARCHEOLOGIST_ID
JOIN LOCATION
    ON DISCOVERY.FK_LOCATION_ID = LOCATION.LOCATION_ID
ORDER BY Discovery.discovery_id;
/* Question 2 */
SELECT COUNT(*) AS Total_Discoveries FROM DISCOVERY;
/*Question 3 */
SELECT * FROM (
    SELECT DINOSAUR_NAME, MAX(DINOSAUR.COST) AS MAXCOST 
    FROM DINOSAUR 
    WHERE (DINOSAUR.COST > 1)
    GROUP BY DINOSAUR_NAME
    ORDER BY MAXCOST 
    DESC
) WHERE ROWNUM = 1;

SELECT * FROM (
    SELECT DINOSAUR_NAME, MIN(DINOSAUR.COST) AS MINCOST 
    FROM DINOSAUR 
    WHERE (DINOSAUR.COST > 1)
    GROUP BY DINOSAUR_NAME
    ORDER BY MINCOST 
    ASC
) WHERE ROWNUM = 1;

/*Question 6*/ 
SELECT * FROM (
    SELECT COUNT(DISCOVERY.DISCOVERY_ID) AS DISCOVERY_COUNT,ARCHEOLOGIST.FIRST_NAME,ARCHEOLOGIST.LAST_NAME FROM ARCHEOLOGIST
    FULL JOIN DISCOVERY
        ON ARCHEOLOGIST.ARCHEOLOGIST_ID = DISCOVERY.FK_ARCHEOLOGIST_ID
    GROUP BY FIRST_NAME,LAST_NAME
) WHERE DISCOVERY_COUNT > 2;


/* Question 7
A museum requests the first and last names of all archeologists,
as well as the number of locations where they have found Tyrannosaurus Rex dinosaurs.
Note that some archeologists have not found any dinosaurs, and some have not found any 
Tyrannosaurus Rex dinosaurs, but still should be included in the list.*/

SELECT count(DISCOVERY.FK_LOCATION_ID) AS REXLOCATIONS ,ARCHEOLOGIST.FIRST_NAME,ARCHEOLOGIST.LAST_NAME
FROM ARCHEOLOGIST
FULL JOIN DISCOVERY 
    ON ARCHEOLOGIST.ARCHEOLOGIST_ID = DISCOVERY.FK_ARCHEOLOGIST_ID
        AND DISCOVERY.FK_DINOSAUR_ID = (SELECT DINOSAUR_ID FROM DINOSAUR WHERE DINOSAUR_NAME ='Tyrannosaurus Rex')
FULL JOIN LOCATION
    ON DISCOVERY.FK_LOCATION_ID = LOCATION.LOCATION_ID 
FULL JOIN DINOSAUR
    ON DISCOVERY.FK_DINOSAUR_ID = DINOSAUR.DINOSAUR_ID
WHERE ARCHEOLOGIST.FIRST_NAME IS NOT NULL
GROUP BY ARCHEOLOGIST.ARCHEOLOGIST_ID,ARCHEOLOGIST.FIRST_NAME,ARCHEOLOGIST.LAST_NAME;


/* Last question */

SELECT count(*) AS DINOSAUR_DISCOVERIES_2012 FROM DISCOVERY
WHERE DISCOVERY_DATE >= TO_DATE('2012', 'YYYY') 
    AND DISCOVERY_DATE < TO_DATE('2013', 'YYYY');