Here is the P4 university database

---Drop tables --Drop Table FACULTY Cascade Constraints; --Drop Table OFFERING Cascade Constraints; --Drop Table STUDENT Cascade Constraints; --Drop Table COURSE Cascade Constraints; --Drop Table ENROLLMENT Cascade Constraints;

Create Table STUDENT( Std_ID NUMBER(3) Constraint Student_STD_ID_FK PRIMARY KEY, Std_FN Varchar2 (9), Std_LN Varchar2 (8) , Std_City Varchar2 (10), Std_State Varchar2 (4), Std_Zip Number (5), Std_Major Varchar2 (6), Std_Class Varchar2 (3), Std_GPA NUMBER (3,2));

---Insert records into Student Insert Into STUDENT Values( 101, 'Joe', 'Smith','Eau Clare','WI',18121,'IS','FR',3.8); Insert Into STUDENT Values( 102,'Rob', 'King','Melrose','MN',56352,'IS' ,'JR',3.2); Insert Into STUDENT Values( 103,'Dan','Robinson','Sartell','MN',98042,'IS' ,'JR',3.5); Insert Into STUDENT Values( 104,'Sue','Williams','St.Cloud','MN',56301,'ACCT','SR',3.2); Insert Into STUDENT Values( 105,'Don','Robinson','St.Paul','MN',55103,'MKTG','SR',3.4); ----Create COURSE Table Create Table COURSE( COURSE_NO Varchar2 (10) Constraint COURSE_COURSE_NO_PK PRIMARY KEY, Crs_Desc Varchar2 (35), Crs_Credits Number (2));

----Insert records Into COURSE Insert Into COURSE Values( 'CSCI200','Elements of Computing',3); Insert Into COURSE Values( 'IS 250','Application Program Dev.I',3); Insert Into COURSE Values( 'IS 251','Application Program Dev.II',3); Insert Into COURSE Values( 'IS 454', 'Data Mining for Decision Support',3); Insert Into COURSE Values( 'IS 356',' Systems Analysis and Design I',3); Insert Into COURSE Values( 'IS 460',' Project Management',3); Insert Into COURSE Values( 'ACCT291','Accounting Principles II',3); Insert Into COURSE Values( 'IS 443',' Databse Design',3);

---Creatte FACULTY Table

Create Table FACULTY( Fac_ID Number (5) Constraint FACULTY_Fac_ID_PK PRIMARY KEY, Fac_FN Varchar2(9), Fac_LN Varchar2(6), Fac_Dept Varchar2(4), Fac_Rank Varchar2(4), Fac_HierDate Date, Fac_Salary Number(6), Fac_Supervisor Number(5));

---Insert records into FACULTY

Insert Into FACULTY Values( 9001,'Leonard','Vince','IS','ASST','12-Apr-1997',67000,9003); Insert Into FACULTY Values( 9002,'Victor','Strong','CSCI','ASSO','8-Aug-1999',70000,9003); Insert Into FACULTY Values( 9003,'Nicki','Colan','IS','PROF','20-Aug-1981',75000,9010); Insert Into FACULTY Values( 9004,'Fred','Wells','ACCT','ASST','28-Aug-1996',60000,9010); Insert Into FACULTY Values( 9010,'Chris','Macon','ACCT','ASST','4-Aug-1980',75000,Null);

Create Table OFFERING( Offer_NO Number (5) Constraint OFFERING_Offer_NO_FK PRIMARY KEY, COURSE_NO Varchar2(10) Constraint OFFERING_COURSE_NO_PK REFERENCES COURSE (COURSE_NO), Off_Term Varchar2 (7), Off_Year Number(4), Off_Loca Varchar2 (6), Off_Time Varchar2 (8), Off_Day Varchar2 (4), Fac_SSN Number (4) Constraint OFFERING_Fac_SSN_FK REFERENCES FACULTY (Fac_ID));

---Insert records Into OFFERING

Insert Into OFFERING Values(2201,'CSCI200', 'Spring', 2017,'ECC135','10:30am','MWF',9002); Insert Into OFFERING Values(2202,'CSCI200','Spring', 2017,'ECC135','8:00am','TTH',9002); Insert Into OFFERING Values(1102,'ACCT291', 'Spring', 2017,'CH14A','2:00pm','MWF',9004); Insert Into OFFERING Values(2203,'IS 356','Fall', 2017,'CH494','3:30pm','TTH',9001);

Insert Into OFFERING Values(2204,'IS 251','Fall', 2017,'CH494','12:30pm','TTH',9003); Insert Into OFFERING Values(1101,'ACCT291','Fall', 2017,'CH350','12:30pm','MWF',9010); Insert Into OFFERING Values(2205,'IS 443','Fall', 2017,'CH494','9:30pm','MWF',9003);

---Create ENROLLMENT Table Create Table ENROLLMENT( Std_ID Number (3) Constraint ENROLLMENT_STD_ID_FK REFERENCES STUDENT (Std_ID), Offer_NO Number (4) Constraint ENROLLMENT_Offer_NO_FK REFERENCES OFFERING (Offer_NO), Enr_Grade Varchar(1) Constraint Enr_check_Grade CHECK (Enr_Grade in ('A','B','C')), constraint ENROLMENT_PK primary key( std_ID,offer_NO));

---Insert records into ENROLLMENT Table Insert into ENROLLMENT Values(101,2201,'A'); Insert Into ENROLLMENT Values(102,2201,'B'); Insert Into ENROLLMENT Values(102,2203,'C'); Insert Into ENROLLMENT Values(103,2003,'B'); Insert Into ENROLLMENT Values(103,2201,'C'); Insert Into ENROLLMENT Values(103,1101,'B'); Insert Into ENROLLMENT Values(104,2202,'A'); Insert Into ENROLLMENT Values(101,2203,'A'); Insert Into ENROLLMENT Values(101,1101,'B'); Insert Into ENROLLMENT Values(101,2205,'C'); Insert Into ENROLLMENT Values(102,2205,'B'); Insert Into ENROLLMENT Values(104,2205,'B');

-- Display Table SELECT * FROM STUDENT; SELECT * FROM COURSE; SELECT * FROM FACULTY; SELECT * FROM OFFERING; SELECT * FROM ENROLLMENT;

--QUERY

--Question 1 -- The key word DISTINCT IS USED TO AVOID REPITIONS (Duplicates) AND THE OUTPUT IS SORTED OUT BY THE KEYWORD ORDER BY AS SHOWN BELOW. Select DISTINCT S.Std_ID, S.Std_FN, S.Std_LN From STUDENT S, OFFERING O, ENROLLMENT E Where O.COURSE_NO = 'CSCI200' AND O.Off_Term = 'Spring' AND E.Offer_NO = O.Offer_NO and E.Std_ID=S.Std_ID Order BY S.Std_ID; --Question 2 -- The key word DISTINCT IS USED TO AVOID Duplicate records Select DISTINCT S.Std_FN, S.Std_LN, S.Std_Major From STUDENT S, OFFERING O, ENROLLMENT E Where E.Enr_Grade ='A' And O.Off_Year = 2017 AND O.Off_Term = 'Spring' AND E.Offer_NO = O.Offer_NO and E.Std_ID=S.Std_ID;

--Question 3 Select S.Std_FN, S.Std_LN, S.Std_State ,S.Std_Major From STUDENT S Where S.Std_State = 'MN' and S.Std_Major = 'IS' ;

--Question 4 -- The key word 'DISTINCT' IS USED TO AVOID Duplicate records

Select Distinct C.COURSE_NO, C.Crs_Credits, F.Fac_LN From Course C, Faculty F, Student S, OFFERING O, ENROLLMENT E Where S.Std_FN = 'Rob' and S.Std_LN = 'King' and S.Std_ID = E.Std_ID and E.Offer_NO = O.Offer_NO and O.COURSE_NO = C.COURSE_NO and O.Fac_SSN = F.Fac_ID;

--Question 5

-- SELECT F.FAC_FN, F.FAC_LN, F.FAC_DEPT, F.FAC_SALARY FROM FACULTY F JOIN OFFERING O ON F.FAC_ID = O.FAC_SSN WHERE F.FAC_SALARY > (SELECT AVG(FAC_SALARY) FROM FACULTY F) AND O.OFF_YEAR = '2017' GROUP BY F.FAC_ID, F.FAC_FN, F.FAC_LN, F.FAC_DEPT, F.FAC_SALARY HAVING COUNT(O.FAC_SSN) < 2;

--Question 6 --

SELECT F.FAC_FN, F.FAC_LN, TO_CHAR(ROUND((SYSDATE - FAC_HIERDATE) / 365)) YEARS FROM FACULTY F WHERE FAC_HIERDATE >= '04-Aug-1980';

-- Question 7

Select Distinct S.Std_Major, AVG(S.Std_GPA) From Student S Group BY S.Std_Major;

-- Question 8

Select F.Fac_FN, F.Fac_LN, count(F.Fac_ID) As Couses_Taught From Faculty F Right OUTER JOIN OFFERING O ON F.Fac_ID = O.Fac_SSN Group BY F.Fac_FN, F.Fac_LN Order by Couses_Taught;

-- Question 9

Select S.Std_FN, S.Std_LN, Sum(C.Crs_Credits) AS Total_Credits From Student S, Course C, OFFERING O, ENROLLMENT E Where S.Std_FN = 'Joe' and S.Std_LN = 'Smith' and S.Std_ID = E.Std_ID and E.Offer_NO = O.Offer_NO and O.COURSE_NO = C.COURSE_NO Group BY S.Std_FN, S.Std_LN;

-- Question 10

SELECT C.COURSE_NO, C.CRS_DESC, COUNT(S.STD_ID) TOTAL_ENROLLMENT, SUM(C.CRS_CREDITS) TOTAL_CREDITS FROM STUDENT S JOIN ENROLLMENT E ON S.STD_ID = E.STD_ID JOIN OFFERING O ON O.OFFER_NO = E.OFFER_NO JOIN COURSE C ON O.COURSE_NO = C.COURSE_NO GROUP BY C.COURSE_NO, C.CRS_DESC;