/*Please answer the Queries and display the code and output.*/

This assignment focuses on the use of the aggregate functions. These functions combine all the results to perform a statistical operation that returns a single value. This assignment uses the tables associated with the university database. It is important that you read this before you try to do this assignment. This assignment reinforces the following objectives: Write queries with aggregate functions: SUM, AVG, COUNT, MAX, and MIN. Use the GROUP BY clause to answer complex managerial questions. Nest aggregate functions. Use the GROUP BY clause with NULL values. Use the GROUP BY clause with the WHERE and ORDER BY clauses. Use the HAVING clause to filter out rows from a result table. Write SQL statements to perform the following queries:

Query 1: How many students are enrolled in Section #1 in the Fall 2009 semester?

Query 2: Display the section IDs of all sections of course CS-190 offered during the Spring semester of 2009.

Query 3: How many students did professor Katz teach during the Fall 2009 semester? This query requires the use of the teaches relation. Display the number of students.

Query 4: Display the average, highest, and lowest age of the students.

Query 5: Count number of faculty members by each department. Display the department ID and the number of faculty members.

Query 6: Display the total number of instructors who taught a course in the semester of Spring 2010.

Query 7: Display the number of distinct instructors in the advisor table. You should label the column Number of Instructors.

Query 8: Display the number of instructors in each department who taught a course in the Spring 2010 semester. Display the department ID and the number of instructors.

Query 9: Display the list of all departments, with the total number of instructors in each department. Display the department name and the number of instructors.

Query 10: Display the number of enrollments for section #1 of Course ID 'EN-1000'. Display the section ID, the course ID and the number of enrollments.

Query 11: Show the total enrollment for course BIO-301 in a column named TOTAL ENROLLED.

Query 12: For each course that has more than one section, display the course title, course ID, total capacity, and number of sections. Submission Instructions: For each of the queries above, submit the query and the result from running the query.

/*Please answer the Queries and display the code and output.*/

Here are the required SQL files:

/* ****************************************************************************/ /* */ /* */ /* The university database */ /* */ /* */ /* ****************************************************************************/

/* 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;

/* ****************************************************************************/ /* */ /* */ /* The university database */ /* */ /* */ /* ****************************************************************************/

/* 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;

/*schemes*/

SCHEMA

classroom (building, room_number, capacity) dept (dept_id_dept_name, building, budget) major(major_id, major_desc) course (course_id, title, dept_id, credits, prereq) faculty (faculty_id, name, dept_id, salary) semester (semester_id, semester_desc , start_date, end_date) section (course_id, sec_id, semester_id, year, building, room_number) teaches (faculty_id, course_id, sec_id, semester_id, year) student (student_id, last_name, first_name, street, city , state , birth_date, dept_id, major_id , phone) registration (student_id, course_id, sec_id, semester_id, year, grade) advisor (student_id, faculty_id) prereq (course_id, prereq_id)