Question

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;

Answer 1

Query 1. Out put

Run SQL Command Line SQL row created. QL〉 Insert into ENROLLMENT Ualues(103 , 2003,' B'); Insert Into ENROLLMENT Ualues (103.2003.'B' > ERROR at line 1: ORA-02291: integrity constraint parent key not found SYSTEM. ENROLLMENT-OFFER-NO-FX> violated- QL〉 Insert row created. Insert row created. Insert row created. Insert row created. Insert row created. Insert row created. Insert row created. Insert row created. Select into ENROLLMENT Ualues(103 , 2201 ,' C'); QL〉 into ENROLLMENT Ualues(103 , 1101,' B'); QL〉 into ENROLLMENT Ualues(104,2202 ,'A"); QL〉 into ENROLLMENT Ualues(101 , 2203 ,'A"); QL〉 into ENROLLMENT Ualues(101 , 1101,'B'); QL〉 Into ENROLLMENT Ualues(101 , 2205 ,' C'); QL〉 into ENROLLMENT Ualues(102 , 2205 ,' B'); QL〉 into ENROLLMENT Ualues(104,2205 ,' B'); SQL〉 DISTINCT S-Std-ID, S.StdーFN, S.Std_LN 2 From STUDENT S, OFFERING 0, ENROLLMENT E 3 Where O·COURSE-NO- ,CSC1200, AND 0.0ff-Term- ,Spring, AND 4 E.Offer_N00.0ffer_NO and E.Std_ID-S.Std ID 5 Order BY S.Std_ID STD-1 D STD_FN STDIN 101 Joe 102 Rob 103 Darn Smith King Robinson Williams 104 Sue

Query 2 output

Run SQL Command Line QL〉. constraint ENROLMENT-PK primary-key( std-ID'offer.NO》 SP2-0734: unknown command beginning "constraint.....- rest of їǐne ignored. SQL〉 Select DISTINCT S-Std-FN, S-Std-LN, S.Std-Major 2 From STUDENT S. OFFERING 0. ENROLLMENT E 3 Where E-Enr-Grade -A, And O.Off-year = 2017 AND O.OFf-Tern- ,Spring, AND 4 E.Offer NO0.offer_NO and E.Std_ID-S.Std_ID; STDFN STDIN STD_AA Joe Smith IS Williams ACCT Sue SQL>

query 3 output:

Run SQL Command Line SQL〉 Select S-Std-FN, S.Std_LN, S.Std-State ,s.Std_Aajor 2 From STUDENT S 3 Where S.Std-State = 'MN, and S.Std-Major = , IS, . STD_FNSTDLN STD STD_MA BahKonnson HNIS Rob Dan Robinson MN IS SQL>

query 4 output:
Run SQL Command Line SQL QL〉 Select Distinct C-COURSE-NO, C.Crs-Credits, F.Fac_LN 2 From Course C. Faculty F. Student S. OFFERING 0. ENROLLMENT E 3 Where S.Std-FN = , Rob, and S.Std_LN "King, and S-Std-ID E.Std-ID and 4 E.Of ferNO = O-OfferNO and 0-COURSENO C.COURSENO and O-FacSSN = F-Fac ----- OURSE-NO CRS-CREDITS FACİN SCI200 IS 356 IS 443 3 StHong 3 Uince 3 Colan
query 5 output::
SQL〉 SELECT F.FAC-FN, F.FAC_LN F.FAC_DEPT F.FAC_ SALARY 4 5 FROM FACULTY F 6 JOIN OFFERING O ON F.FAC-1 D = O.FAC_SSN ? WHERE F.FACSALARY 〉 - 8 SELECT AUGKFAC_SALARY> FROM FACULTY F> AND 0.OFF_YEAR201?' 10 11 GROUP BY F.FAC_ID. 12 13 14 15 F-FAC-FN F.FAC_LN F.FAC_DEPT F-FAC-SALARY 16 HAUING COUNTC0.FAC. SSN>く2; FAC-FN FACİN FAC- FAC-SALARY Chris 75000 SQL>

query 6 output:

QL〉 SELECT F-FAC-FN, F.FAC_LN TOCHARCROUND«SYSDATE FAC HIERDATE> 365» YEARS - - - 4FROM FACULTY F 5 WHERE FAC_HIERDATE '04-Aug1980' AC-FN FACİN YEARS Victor Nicki Fred eonard ince 21 Strong 19 Colan 3? Wells 22 Macon 38 hris

query 7 output:query 8 output:

Run SQL Command Line SQL〉 Select Distinct s.Std-Major, AUGCS . Std-GPA 2 From Student S 3 Group BY S.Std_Major; STD MA AUGCS.STD_GPA IS ACCT MKTG 3.5 3.2 3.4 SQL>

Run SQL Command Line SQL〉 Select Distinct s.Std-Major, AUGCS . Std-GPA 2 From Student S 3 Group BY S.Std_Major; STD MA AUGCS.STD_GPA IS ACCT MKTG 3.5 3.2 3.4 SQL>
query 9 output:
SQL> SQL> SQL〉 Select S.Std-FN, S.Std_LN, SunCC.Crs-Credits) AS Total-Credits 2 From Student S. Course C, OFFERING 0, ENROLLMENT E 3 Where S.Std-FN- ,Joe, and S.Std_LN = , Snith, and S.Std-ID = E.Std-ID and 4 E-OfferNO=0.0f ferNO and 0-COURSENO=C.COURSENO 5 Group BYS-Std-FN, S.Std_LN ; and Efes.staand;0sCOURSENOSnithCOURSE.Nota_ID· ---- STD-FN Joe SQL> STDIN TOTAL-CREDITS Smith 12
query 10 output:
SQL〉 SELECT C.COURSE.NO. C.CRS_DESC COUNT S.STD_ID> TOTAL_ENROLLMENT SUMCC.CRSCREDITS) TOTAL CREDITS 4 5 FROM STUDENT S JOIN ENROLLMENT E ON S-STDID = E-STD1D - - JOIN OFFERING 0 ON 0 OFFER_NO E OFFER_ NO JO IN COURSE C ON 0-COURSE.NO = C-COURSE.NO 9 GROUP BY C.COURSE_NO. 10 C.CRS_DESC; COURSE_NO CRS_DESC TOTAL_ENROLLMENT TOTAL_CREDITS CSCI200 ACCT291 IS 356 IS 443 Elements of Computing Accounting Principles II 12 6 6 4 Systems Analysis and Design I Databse Design SQL>