3, answer the following questions 2, 3, and 4. Please provide SQL
SELECT statements compatible with Oracle. You may also use an
online (sqliteonline.com) or your own Oracle system if you want to.
The SQL files for the database are available under the “University
DB Source files” (30 points
course
- By Using The Universitydb Pdf File Under Course Module 3 Answer The Following Questions 2 3 And 4 Please Provide S 1 (53.87 KiB) Viewed 39 times
- By Using The Universitydb Pdf File Under Course Module 3 Answer The Following Questions 2 3 And 4 Please Provide S 2 (130.79 KiB) Viewed 39 times
- By Using The Universitydb Pdf File Under Course Module 3 Answer The Following Questions 2 3 And 4 Please Provide S 3 (105.41 KiB) Viewed 39 times
- By Using The Universitydb Pdf File Under Course Module 3 Answer The Following Questions 2 3 And 4 Please Provide S 4 (159.28 KiB) Viewed 39 times
- By Using The Universitydb Pdf File Under Course Module 3 Answer The Following Questions 2 3 And 4 Please Provide S 5 (105.27 KiB) Viewed 39 times
- By Using The Universitydb Pdf File Under Course Module 3 Answer The Following Questions 2 3 And 4 Please Provide S 6 (89.48 KiB) Viewed 39 times
- By Using The Universitydb Pdf File Under Course Module 3 Answer The Following Questions 2 3 And 4 Please Provide S 7 (30 KiB) Viewed 39 times
create table Student ( stdNo char(11) not null, stdFirstName varchar2(30) not null, stdLastName varchar2(30) not null, stdCity varchar2 (30) not null, stdstate char(2) not null, stdZip char(10) not null, stdMajor char(6), stdClass char(2), stdGPA decimal(3,2), CONSTRAINT StudentPk PRIMARY KEY (StdNo) ); INSERT INTO student (stdNo, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdzip) VALUES ('123-45-6789', 'HOMER', 'WELLS', 'SEATTLE', 'WA', 'IS', 'FR',3.00, '98121-1111'); INSERT INTO student (stdNo, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip) VALUES ('124-56-7890', 'BOB', 'NORBERT', 'BOTHELL', 'WA', 'FIN', 'JR', 2.70, '98011-2121'); INSERT INTO student (stdNo, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdzip) VALUES ('234-56-7890', 'CANDY', 'KENDALL', 'TACOMA', 'WA', 'ACCT', 'R',3.50, '99042-3321'); INSERT INTO student (stdNo, stdFirstName, stdLastName, stdCity, stdstate, stdMajor, stdClass, stdGPA, stdzip) VALUES ('345-67-8901', 'WALLY', 'KENDALL', 'SEATTLE', 'WA', 'IS', 'SR',2.80, '98123-1141'); INSERT INTO student (stdNo, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdzip) VALUES ('456-78-9012', 'JOE', 'ESTRADA', 'SEATTLE', 'WA', 'FIN', 'SR',3.20, '98121-2333'); INSERT INTO student (stdNo, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip) VALUES ('567-89-0123', 'MARIAH', 'DODGE', 'SEATTLE', 'WA', 'IS', 'IR',3.60, '98114-0021'); INSERT INTO student (stdNo, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip) VALUES ('678-90-1234', 'TESS', 'DODGE', 'REDMOND', 'WA', 'ACCT', 'S',3.30, '98116-2344'); INSERT INTO student (stdNo, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip) VALUES ('789-01-2345', 'ROBERTO', 'MORALES', 'SEATTLE', 'WA', 'FIN', 'IR',2.50, '98121-2212'); INSERT INTO student (stdNo, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdzip) VALUES ('876-54-3210', 'CRISTOPHER', 'COLAN', 'SEATTLE', 'WA', 'IS', 'SR',4.00, '98114-1332'); INSERT INTO student (stdNo, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdZip) VALUES ('890-12-3456', 'LUKE', 'BRAZZI', 'SEATTLE', 'WA', 'IS', 'SR',2.20, '98116-0021'); INSERT INTO student (stdNo, stdFirstName, stdLastName, stdCity, stdState, stdMajor, stdClass, stdGPA, stdzip) VALUES ('901-23-4567', 'WILLIAM', 'PILGRIM', 'BOTHELL', 'WA', 'IS', 'SO',3.80, 98113-1885');
create table Faculty FacNo char(11) not null, FacFirstName varchar2(30) not null, FacLastName varchar2(30) not null, FacCity varchar2 (30) not null, FacState char(2) not null, FacZipCode char(10) not null, FacRank char(4), FacHireDate date, FacSalary decimal(10,2), FacSupervisor char(11), FacDept char(6), CONSTRAINT FacultyPK PRIMARY KEY (Facno), CONSTRAINT SupervisorFK FOREIGN KEY (FacSupervisor) REFERENCES Faculty ); INSERT INTO faculty (Facno, FacFirstName, FacLastName, FacCity, FacState, FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode) VALUES ('543-21-6987', 'VICTORIA', 'EMMANUEL', 'BOTHELL', 'WA', 'MS', 'PROF', 120000.0,'','15-Apr-2005', '98011-2242'); INSERT INTO faculty (Facno, FacFirstName, FacLastName, Faccity, Facstate, FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode) VALUES ('765-43-2109', 'NICKI', 'MACON', 'BELLEVUE', 'WA', 'FIN', 'PROF', 65000.00,'','11-Apr-2006', '98015-9945'); INSERT INTO faculty (Facno, FacFirstName, FacLastName, FacCity, FacState, FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode) VALUES ('654-32-1098', 'LEONARD', 'FIBON', 'SEATTLE', 'WA', 'MS', 'ASSC', 70000.00, '543-21-0987', '01-May-2003', '98121-0094'); INSERT INTO faculty (Facno, FacFirstName, FacLastName, FacCity, FacState, FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode) VALUES ('898-76-5432', 'LEONARD', 'VINCE', 'SEATTLE', 'WA', 'M', 'ASST', 35000.00, '654-32-1098', '10-Apr-2004', '98111-9921'); INSERT INTO faculty (Facno, FacFirstName, FacLastName, FacCity, FacState, FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode) VALUES ('876-54-3210', 'CRISTOPHER', 'COLAN', 'SEATTLE', 'WA', 'M', 'ASST', 40000.00, '654-32-1098','01-Mar-2008', '98114-1332'); INSERT INTO faculty (Facno, FacFirstName, FacLastName, Faccity, FacState, FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode) VALUES ('987-65-4321','JULIA', 'MILLS', 'SEATTLE', 'WA', 'FIN', 'ASSC', 75000.00, '765-43-2109', '15-Mar-2009', '98114-9954');
create table offering OfferNo INTEGER not null, CourseNo char(6) not null, OffTerm char(6) not null, OffYear INTEGER not null, OffLocation varchar2(30), OffTime varchar2(10), FacNo char(11), OffDays char(4), CONSTRAINT OfferingPK PRIMARY KEY (ОfferNo), CONSTRAINT CourseFK FOREIGN KEY (CourseNo) REFERENCES Course, CONSTRAINT FacultyFK FOREIGN KEY (FacNo) REFERENCES Faculty ); INSERT INTO offering (Offerno, CourseNo, OffTerm, OffYear, OffLocation, OffTime, Facno, OffDays) VALUES (1111,'IS320', 'SUMMER',2017,'BLM302', '10:30:00',NULL, 'MW'); INSERT INTO offering (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, Facno, OffDays) VALUES (1234, 'IS320', 'FALL', 2016, 'BLM302', '10:30:00', '098-76-5432', 'MW'); INSERT INTO offering (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays) VALUES (2222, 'IS460', 'SUMMER', 2016, 'BLM412', '13:30:00',NULL, 'TTH'); INSERT INTO offering (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, Facno, OffDays) VALUES (3333, '15320', 'SPRING',2017,'BLM214', '08:30:00', '098-76-5432', 'MW'); INSERT INTO offering (Offerno, CourseNo, OffTerm, OffYear, OffLocation, OffTime, Facno, OffDays) VALUES (4321, 'IS320', 'FALL', 2016, 'BLM214', '15:30:00', '098-76-5432', 'TTH'); INSERT INTO offering (Offerno, Courselo, OffTerm, OffYear, OffLocation, OffTime, Facno, OffDays) VALUES (4444, 'IS320', 'WINTER',2017,'BLM302', '15:30:00', '543-21-8987', 'ITH'); INSERT INTO offering (Offerno, Courselo, OffTerm, OffYear, OffLocation, OffTime, Facno, OffDays) VALUES (5555, 'FIN300', 'WINTER',2017,'BLM207','08:30:00', '765-43-2109', 'MW'); INSERT INTO offering (Offerno, Courselo, OffTerm, OffYear, OffLocation, OffTime, Facno, OffDays) VALUES (5678, '15480', 'WINTER',2017,'BLM302', '10:30:00', '987-65-4321', 'MW'); INSERT INTO offering (Offerno, CourseNo, OffTerm, OffYear, OffLocation, OffTime, Facno, OffDays) VALUES (5679, '15480', 'SPRING',2017,'BLM412', '15:30:00', '876-54-3210', 'TTH'); INSERT INTO offering (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, Facno, OffDays) VALUES (6666, 'FIN450', 'WINTER',2017,'BLM212', '10:30:00', '987-65-4321', 'ITH'); INSERT INTO offering (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays) VALUES (7777, 'FIN480', 'SPRING',2017,'BLM305', '13:30:00', '765-43-2109', 'MW'); INSERT INTO offering (Offerno, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays) VALUES (8888,'IS320', 'SUMMER',2017,'BLM405', '13:30:00', '654-32-1098', 'MW'); INSERT INTO offering (OfferNo, CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacNo, OffDays) VALUES (9876, '15460', 'SPRING',2017,'BLM307', '13:30:00', '654-32-1098', 'TTH');
create table Enrollment OfferNo INTEGER not null, StdNo char(11) not null, EnrGrade decimal(3,2), CONSTRAINT EnrollmentPK PRIMARY KEY (ОfferNo, StdNo), CONSTRAINT OfferingFK FOREIGN KEY (ОfferNo) REFERENCES Offering ON DELETE CASCADE, CONSTRAINT StudentFK FOREIGN KEY (StdNo) REFERENCES Student ON DELETE CASCADE ); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (1234, '123-45-6789',3.30); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES(1234, 234-56-7890',3.50); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES(1234, 345-67-8901', 3.20); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES(1234, 456-78-9012',3.10); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (1234,567-89-0123',3.80); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (1234, 678-90-1234",3.40); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES (4321, '123-45-6789',3.50); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (4321, '124-56-7890', 3.20); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (4321, '789-01-2345',3.50); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (4321, '876-54-3210',3.10); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (4321, '890-12-3456',3.40); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (4321, '901-23-4567', 3.10); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES(5555, '123-45-6789',3.20); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (5555, '124-56-7890', 2.70);
INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES(5678, '123-45-6789',3.20); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES (5678, '234-56-7890', 2.80); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES (5678, 345-67-8901',3.30); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES(5678,456-78-9012', 3.40); INSERT INTO enrollment (OfferNo, StdNo, EnrGrade) VALUES(5678, '567-89-0123', 2.60); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (5679, '123-45-6789', 2.00); INSERT INTO enrollment (OfferNo, StdNo, EnrGrade) VALUES(5679, '124-56-7890', 3.70); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES (5679, '678-90-1234', 3.30); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES(5679, '789-01-2345', 3.80); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES (5679, '890-12-3456', 2.9); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (5679,'901-23-4567', 3.1); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES (6666, '234-56-7890', 3.1); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES( 6666, '567-89-0123',3.6); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES (7777, '876-54-3210', 3.4); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES(7777, '890-12-3456',3.7); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (7777, 901-23-4567', 3.4); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES (9876, '124-56-7890', 3.5); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (9876, '234-56-7890', 3.2); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES (9876, '345-67-8901',3.2); INSERT INTO enrollment (OfferNO, StdNo, EnrGrade) VALUES (9876, '456-78-9012', 3.4);
INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES(9876, '567-89-0123', 2.6); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES (9876, '678-90-1234',3.3); INSERT INTO enrollment (Offerno, StdNo, EnrGrade) VALUES (9876, '901-23-4567', 4);