Please answer each query and display the output

Query 1: Create a new table, FACULTY5, and populate it with faculties in department ID 5, using an existing table. Use faculty ID, name, and salary columns.

Query 2: Add more rows to FACULTY5 table with faculty in department ID 3. Write a SQL statement to list all columns for the FACULTY5 table.

Query 3: Create a SQL command that will modify the name of thecourse CS-347 from Database System Concepts to Introduction to Relational Databases.

Query 4: Write a SQL statement to increase the salary of each FACULTY in the Computer Science department by 10%. Department ID is 2. Display the rows in the FACULTY table.

Query 5: Write an INSERT statement that adds this row to the DEPT table: Dept ID: 10 Department name: Mathematics building: Painter budget: 200000

Query 6: Modify the row from Query 5 to the DEPT table. This statement should change the dept_name column to Chemistry, and use the dept_id column to identify the row.

Query 7: Write an INSERT statement that adds this row to the faculty table: Name: George Faculty ID: 76665 Department ID: 10 Salary: 95000

Query 8: Write an UPDATE statement that modifies the row from Query 7. This statement should change the name of the instructor column to Alexander, and use the faculty ID column to identify the row.

Query 9: Delete the row added to the DEPT table from Query 5. When executing this statement, an error will produce since the department has related rows in the faculty table. To fix this error, precede the DELETE statement with another DELETE statement that deletes all faculties in this department.

Required files:

DROP TABLE prereq CASCADE CONSTRAINTS; DROP TABLE advisor CASCADE CONSTRAINTS; DROP TABLE registration CASCADE CONSTRAINTS; DROP TABLE student CASCADE CONSTRAINTS; DROP TABLE teaches CASCADE CONSTRAINTS; DROP TABLE section CASCADE CONSTRAINTS; DROP TABLE semester CASCADE CONSTRAINTS; DROP TABLE faculty CASCADE CONSTRAINTS; DROP TABLE course CASCADE CONSTRAINTS; DROP TABLE major CASCADE CONSTRAINTS; DROP TABLE dept CASCADE CONSTRAINTS; DROP TABLE classroom CASCADE CONSTRAINTS; commit;

/* create the classrom table */ CREATE TABLE classroom ( building VARCHAR2(15), room_number VARCHAR2(7), capacity NUMERIC(4,0), PRIMARY KEY (building, room_number) ); /* create the departments table */ CREATE TABLE dept ( dept_id NUMBER (3), dept_name VARCHAR2(20), building VARCHAR2(15), budget NUMERIC(12,2) CHECK (budget > 0), PRIMARY KEY (dept_id) ); /* create the major table */ CREATE TABLE major ( major_id CHAR (3), major_desc VARCHAR2 (25), CONSTRAINT major_majored_pk PRIMARY KEY (major_id) ); /* create the course table */ CREATE TABLE course ( course_id VARCHAR2(10), title VARCHAR2(50), dept_id NUMBER (3), credits NUMERIC(2,0) CHECK (credits > 0), prereq VARCHAR2(10), PRIMARY KEY (course_id), FOREIGN KEY (dept_id) REFERENCES dept(dept_id) ON DELETE SET NULL ); /* create the faculty table */ CREATE TABLE faculty ( faculty_id VARCHAR2(5), name VARCHAR2(20) NOT NULL, dept_id NUMBER (3), salary NUMERIC(8,2) CHECK (salary > 29000), PRIMARY KEY (faculty_id), FOREIGN KEY (dept_id) REFERENCES dept(dept_id) ON DELETE SET NULL ); CREATE TABLE semester ( semester_id CHAR (4), semester_desc VARCHAR2 (11), start_date DATE, end_date DATE, CONSTRAINT semester_semesterid_pk PRIMARY KEY (semester_id) ); /* create the faculty table */ CREATE TABLE section ( course_id VARCHAR2(10), sec_id VARCHAR2(8), semester_id CHAR(4), YEAR NUMERIC(4,0) CHECK (YEAR > 1701 AND YEAR < 2100), building VARCHAR2(15), room_number VARCHAR2(7), PRIMARY KEY (course_id, sec_id, semester_id, YEAR), FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE CASCADE, FOREIGN KEY (semester_id) REFERENCES semester (semester_id) ON DELETE CASCADE, FOREIGN KEY (building, room_number) REFERENCES classroom(building, room_number) ON DELETE SET NULL ); /* create the teach table */ CREATE TABLE teaches ( faculty_id VARCHAR2(5), course_id VARCHAR2(10), sec_id VARCHAR2(8), semester_id CHAR(4), YEAR NUMERIC(4,0), PRIMARY KEY (faculty_id, course_id, sec_id, semester_id, YEAR), FOREIGN KEY (course_id,sec_id, semester_id, YEAR) REFERENCES section (course_id,sec_id, semester_id, YEAR) ON DELETE CASCADE, FOREIGN KEY (faculty_id) REFERENCES faculty (faculty_id) ON DELETE CASCADE ); /* create the student table */ CREATE TABLE student ( student_id CHAR (5), last_name VARCHAR2 (15) NOT NULL, first_name VARCHAR2 (15) NOT NULL, street VARCHAR2 (25), city VARCHAR2 (15), state CHAR (2) DEFAULT 'NJ', birth_date DATE, dept_id NUMBER (3), major_id CHAR(3), Phone CHAR (10), CONSTRAINT student_studentid_pk PRIMARY KEY (student_id), FOREIGN KEY (dept_id) REFERENCES dept (dept_id) ON DELETE SET NULL, FOREIGN KEY (major_id) REFERENCES major (major_id) ON DELETE SET NULL ); /* create the registration table */ CREATE TABLE registration ( student_id CHAR(5), course_id VARCHAR2(8), sec_id VARCHAR2(8), semester_id CHAR(4), YEAR NUMERIC(4,0), grade VARCHAR2(2), PRIMARY KEY (student_id, course_id, sec_id, semester_id, YEAR), FOREIGN KEY (course_id,sec_id, semester_id, YEAR) REFERENCES section ON DELETE CASCADE, FOREIGN KEY (student_id) REFERENCES student ON DELETE CASCADE ); /* create the advisor table */ CREATE TABLE advisor ( student_id CHAR(5), faculty_id VARCHAR2(5), PRIMARY KEY (student_id), FOREIGN KEY (faculty_id) REFERENCES faculty (faculty_id) ON DELETE SET NULL, FOREIGN KEY (student_id) REFERENCES student (student_id) ON DELETE CASCADE ); /* create the prereq table */ CREATE TABLE prereq ( course_id VARCHAR2(10), prereq_id VARCHAR2(10), PRIMARY KEY (course_id, prereq_id), FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE CASCADE ); COMMIT;

/*

/* Insert data into the tables */

/* insert sample data into table */ DELETE FROM prereq; DELETE FROM advisor; DELETE FROM registration; DELETE FROM student; DELETE FROM teaches; DELETE FROM semester; DELETE FROM section; DELETE FROM faculty; DELETE FROM course; DELETE FROM major; DELETE FROM dept; DELETE FROM classroom;

/* insert sample data into the classroom table */ INSERT INTO classroom VALUES ('Packard', '101', '500'); INSERT INTO classroom VALUES ('Painter', '514', '10'); INSERT INTO classroom VALUES ('Taylor', '3128', '70'); INSERT INTO classroom VALUES ('Watson', '100', '30'); INSERT INTO classroom VALUES ('Watson', '120', '50');

/* insert sample data into the departments table */ INSERT INTO dept VALUES (1, 'Biology', 'Watson', '90000'); INSERT INTO dept VALUES (2, 'Comp. Sci.', 'Taylor', '100000'); INSERT INTO dept VALUES (3, 'Elec. Eng.', 'Taylor', '85000'); INSERT INTO dept VALUES (4, 'Finance', 'Painter', '120000'); INSERT INTO dept VALUES (5, 'History', 'Painter', '50000'); INSERT INTO dept VALUES (6, 'Music', 'Packard', '80000'); INSERT INTO dept VALUES (7, 'Physics', 'Watson', '70000'); INSERT INTO dept VALUES (8, 'English', 'Watson', '60000');

/* insert sample data into the major table */ INSERT INTO major VALUES ('100', 'AAS-Biology'); INSERT INTO major VALUES ('200', 'AAS-Computer Science'); INSERT INTO major VALUES ('300', 'AAS-lec. Eng.'); INSERT INTO major VALUES ('400', 'BS-Finance'); INSERT INTO major VALUES ('500', 'BS-History'); INSERT INTO major VALUES ('600', 'BS-Music'); INSERT INTO major VALUES ('700', 'BS-Physics'); INSERT INTO major VALUES ('800', 'BS-English');

/* insert sample data into the course table */ INSERT INTO course VALUES ('BIO-101', 'Intro. to Biology', 1 , '4', 'EN-100'); INSERT INTO course VALUES ('BIO-301', 'Genetics', 1, '4', 'BIO-101'); INSERT INTO course VALUES ('BIO-399', 'Computational Biology', 1, '3', ''); INSERT INTO course VALUES ('CS-101', 'Intro. to Computer Science', 2, '4', 'EN-100'); INSERT INTO course VALUES ('CS-190', 'Game Design', 2 , '4', 'CS-101'); INSERT INTO course VALUES ('CS-315', 'Robotics', 2, '3', 'CS-101'); INSERT INTO course VALUES ('CS-319', 'Image Processing', 2, '3', ''); INSERT INTO course VALUES ('CS-347', 'Database System Concepts', 2, '3', ''); INSERT INTO course VALUES ('EE-181', 'Intro. to Digital Systems', 3, '3', ''); INSERT INTO course VALUES ('FIN-201', 'Investment Banking', 4, '3',''); INSERT INTO course VALUES ('HIS-351', 'World History', 5, '3', ''); INSERT INTO course VALUES ('MU-199', 'Music Video Production', 6, '3',''); INSERT INTO course VALUES ('PHY-101', 'Physical Principles', 7, '4',''); INSERT INTO course VALUES ('EN-1000', 'Basic English', 8,'1', '');

/* insert sample data into the faculty table */ INSERT INTO faculty VALUES ('10101', 'Srinivasan', 2, '65000'); INSERT INTO faculty VALUES ('12121', 'Wu', 4, '90000'); INSERT INTO faculty VALUES ('15151', 'Mozart', 6, '40000'); INSERT INTO faculty VALUES ('16665', 'Einstein', 7, '95000'); INSERT INTO faculty VALUES ('32343', 'El Said', 5, '60000'); INSERT INTO faculty VALUES ('33456', 'Gold', 7, '87000'); INSERT INTO faculty VALUES ('45565', 'Katz', 2, '75000'); INSERT INTO faculty VALUES ('58583', 'Califieri', 5, '62000'); INSERT INTO faculty VALUES ('76544', 'Singh', 4, '80000'); INSERT INTO faculty VALUES ('76766', 'Crick', 1, '72000'); INSERT INTO faculty VALUES ('83821', 'Brandt', 2, '92000'); INSERT INTO faculty VALUES ('98345', 'Kim', 3, '80000'); INSERT INTO faculty VALUES ('98377', 'Brad', 8, '80000');

/* insert sample data into the semester table */

INSERT INTO semester VALUES ('Su09', 'Summer 2009', '28-June-09', '31-AUG-09'); INSERT INTO semester VALUES ('Fa09', 'Fall 2009', '08-SEP-09', '20-DEC-09'); INSERT INTO semester VALUES ('Sp09', 'Spring 2009', '05-JAN-09', '18-APR-09'); INSERT INTO semester VALUES ('Sp10', 'Spring 2010', '09-JAN-10', '15-MAY-10'); INSERT INTO semester VALUES ('Su10', 'Summer 2010', '07-JUNE-10', '19-AUG-10');

/* insert sample data into the section table */

INSERT INTO section VALUES ('BIO-101', '1', 'Su09', '2009', 'Painter', '514'); INSERT INTO section VALUES ('BIO-301', '1', 'Su10', '2010', 'Painter', '514'); INSERT INTO section VALUES ('CS-101', '1', 'Fa09', '2009', 'Packard', '101'); INSERT INTO section VALUES ('CS-101', '1', 'Sp10', '2010', 'Packard', '101'); INSERT INTO section VALUES ('CS-190', '1', 'Sp09', '2009', 'Taylor', '3128'); INSERT INTO section VALUES ('CS-190', '2', 'Sp09', '2009', 'Taylor', '3128'); INSERT INTO section VALUES ('CS-315', '1', 'Sp10', '2010', 'Watson', '120'); INSERT INTO section VALUES ('CS-319', '1', 'Sp10', '2010', 'Watson', '100'); INSERT INTO section VALUES ('CS-319', '2', 'Sp10', '2010', 'Taylor', '3128'); INSERT INTO section VALUES ('CS-347', '1', 'Sp09', '2009', 'Taylor', '3128'); INSERT INTO section VALUES ('EE-181', '1', 'Sp09', '2009', 'Taylor', '3128'); INSERT INTO section VALUES ('FIN-201', '1', 'Sp10', '2010', 'Packard', '101'); INSERT INTO section VALUES ('HIS-351', '1', 'Sp10', '2010', 'Painter', '514'); INSERT INTO section VALUES ('MU-199', '1', 'Sp10', '2010', 'Packard', '101'); INSERT INTO section VALUES ('PHY-101', '1', 'Fa09', '2009', 'Watson', '100'); INSERT INTO section VALUES ('EN-1000', '1', 'Sp09', '2009', 'Watson', '100');

/* insert sample data into the teaches table */ INSERT INTO teaches VALUES ('10101', 'CS-101', '1', 'Fa09', '2009'); INSERT INTO teaches VALUES ('10101', 'CS-315', '1', 'Sp10', '2010'); INSERT INTO teaches VALUES ('10101', 'CS-347', '1', 'Sp09', '2009'); INSERT INTO teaches VALUES ('12121', 'FIN-201', '1', 'Sp10', '2010'); INSERT INTO teaches VALUES ('15151', 'MU-199', '1', 'Sp10', '2010'); INSERT INTO teaches VALUES ('16665', 'PHY-101', '1', 'Fa09', '2009'); INSERT INTO teaches VALUES ('32343', 'HIS-351', '1', 'Sp10', '2010'); INSERT INTO teaches VALUES ('45565', 'CS-101', '1', 'Sp10', '2010'); INSERT INTO teaches VALUES ('45565', 'CS-319', '1', 'Sp10', '2010'); INSERT INTO teaches VALUES ('76766', 'BIO-101', '1', 'Su09', '2009'); INSERT INTO teaches VALUES ('76766', 'BIO-301', '1', 'Su10', '2010'); INSERT INTO teaches VALUES ('83821', 'CS-190', '1', 'Sp09', '2009'); INSERT INTO teaches VALUES ('83821', 'CS-190', '2', 'Sp09', '2009'); INSERT INTO teaches VALUES ('83821', 'CS-319', '2', 'Sp10', '2010'); INSERT INTO teaches VALUES ('98345', 'EE-181', '1', 'Sp09', '2009'); INSERT INTO teaches VALUES ('98377', 'EN-1000', '1', 'Sp09', '2009');

/* insert sample data into the student table */ INSERT INTO student VALUES ('00128', 'Zhang', 'Jose', '1 Ford Avenue #7','Hill', 'NJ', TO_DATE ('02/12/1993', 'mm/dd/yyyy'), 2, '200' , '9735551111'); INSERT INTO student VALUES ('12345', 'Shankar', 'Mickey', '12 Morris Avenue','Bronx', 'NY', TO_DATE ('03/18/1994', 'mm/dd/yyyy'), 2, '200', '7185552222'); INSERT INTO student VALUES ('19991', 'Brandt', 'Rajesh', '25 River Road #3','Edison', 'NJ', TO_DATE ('12/12/1995', 'mm/dd/yyyy'), 5, '500', '7325553333'); INSERT INTO student VALUES ('23121', 'Chavez', 'Deborah', '100 Main Street', 'Iselin', 'NJ', TO_DATE ('10/20/1970', 'mm/dd/yyyy'), 4, '400', '7325554444'); INSERT INTO student VALUES ('44553', 'Peltier', 'Brian', '2845 First Lane', 'Hope', 'NY', TO_DATE ('11/28/1985', 'mm/dd/yyyy'), 7, '700', '2125555555'); INSERT INTO student VALUES ('45678', 'Levy', 'Amir', '213 Broadway', 'Clifton', 'NJ', TO_DATE ('07/07/1984', 'mm/dd/yyyy'), 7, '700', '2015556666'); INSERT INTO student VALUES ('54321', 'Williams', 'Amr', '246 Broadway', 'Clifton','NJ', TO_DATE ('07/08/1994', 'mm/dd/yyyy'), 7, '700', '2456556666'); INSERT INTO student VALUES ('55739', 'Sanchez', 'Sam', '1 Ford Avenue #7', 'Hill', 'NJ', TO_DATE ('02/11/1993', 'mm/dd/yyyy'), 6, '600', '9735551111'); INSERT INTO student VALUES ('70557', 'John', 'Snow', '1453 Morris Avenue', 'Bronx', 'NY', TO_DATE ('05/18/1994', 'mm/dd/yyyy'), 7, '700', '7185557777'); INSERT INTO student VALUES ('76543', 'Brown', 'Snow', '117 Morris Avenue','Bronx', 'NY', TO_DATE ('05/18/1991', 'mm/dd/yyyy'), 3, '300', '7185556789'); INSERT INTO student VALUES ('8765', 'Bourikas', 'Brian', '2234 Second Lane', 'Hope', 'NY', TO_DATE ('10/27/1989', 'mm/dd/yyyy'), 3, '300', '2125558975'); INSERT INTO student VALUES ('98988', 'George', 'Tanaka', '1 Ford Avenue #7', 'Hill', 'NJ', TO_DATE ('02/11/1993', 'mm/dd/yyyy'), 1, '100', '9735553423');

INSERT INTO student VALUES ('98765', 'John', 'Ho', '1412 Morris Avenue', 'Bronx', 'NY', TO_DATE ('04/18/1991', 'mm/dd/yyyy'), 2, '200', '7185551212');

/* insert sample data into the registration table */

INSERT INTO registration VALUES ('00128', 'CS-101', '1', 'Fa09', '2009', 'A'); INSERT INTO registration VALUES ('00128', 'CS-347', '1', 'Sp09', '2009', 'A-'); INSERT INTO registration VALUES ('12345', 'CS-101', '1', 'Fa09', '2009', 'C'); INSERT INTO registration VALUES ('12345', 'CS-190', '2', 'Sp09', '2009', 'A'); INSERT INTO registration VALUES ('12345', 'CS-315', '1', 'Sp10', '2010', 'A'); INSERT INTO registration VALUES ('12345', 'CS-347', '1', 'Sp09', '2009', 'A'); INSERT INTO registration VALUES ('19991', 'HIS-351', '1', 'Sp10', '2010', 'B'); INSERT INTO registration VALUES ('23121', 'FIN-201', '1', 'Sp10', '2010', 'C+'); INSERT INTO registration VALUES ('44553', 'PHY-101', '1', 'Fa09', '2009', 'B-'); INSERT INTO registration VALUES ('45678', 'CS-101', '1', 'Fa09', '2009', 'F'); INSERT INTO registration VALUES ('45678', 'CS-101', '1', 'Sp10', '2010', 'B+'); INSERT INTO registration VALUES ('45678', 'CS-319', '1', 'Sp10', '2010', 'B'); INSERT INTO registration VALUES ('54321', 'CS-101', '1', 'Fa09', '2009', 'A-'); INSERT INTO registration VALUES ('54321', 'CS-190', '2', 'Sp09', '2009', 'B+'); INSERT INTO registration VALUES ('55739', 'MU-199', '1', 'Sp10', '2010', 'A-'); INSERT INTO registration VALUES ('76543', 'CS-101', '1', 'Fa09', '2009', 'A'); INSERT INTO registration VALUES ('76543', 'CS-319', '2', 'Sp10', '2010', 'A'); INSERT INTO registration VALUES ('98765', 'EE-181', '1', 'Sp09', '2009', 'C'); INSERT INTO registration VALUES ('98765', 'CS-101', '1', 'Fa09', '2009', 'C-'); INSERT INTO registration VALUES ('98765', 'CS-315', '1', 'Sp10', '2010', 'B'); INSERT INTO registration VALUES ('98988', 'BIO-101', '1', 'Su09', '2009', 'A'); INSERT INTO registration VALUES ('98988', 'BIO-301', '1', 'Su10', '2010', NULL);

/* insert sample data into the advisor table */ INSERT INTO advisor VALUES ('00128', '45565'); INSERT INTO advisor VALUES ('12345', '10101'); INSERT INTO advisor VALUES ('23121', '76544'); INSERT INTO advisor VALUES ('44553', '16665'); INSERT INTO advisor VALUES ('45678', '16665'); INSERT INTO advisor VALUES ('76543', '45565'); INSERT INTO advisor VALUES ('98988', '76766');

/* insert sample data into the prereq table */ INSERT INTO prereq VALUES ('BIO-301', 'BIO-101'); INSERT INTO prereq VALUES ('BIO-399', 'BIO-101'); INSERT INTO prereq VALUES ('CS-190', 'CS-101'); INSERT INTO prereq VALUES ('CS-315', 'CS-101'); INSERT INTO prereq VALUES ('CS-319', 'CS-101'); INSERT INTO prereq VALUES ('CS-347', 'CS-101'); INSERT INTO prereq VALUES ('EE-181', 'PHY-101'); commit;

/*

Please answer each query and display the output