/Please answer the Queries and display the code and output./ This assignment f

ID: 3861362 • Letter: #

Question

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

/* Insert data into the tables */

/* insert sample data into the semester table */

/* insert sample data into the section table */

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

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

Explanation / Answer

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

select count(*) as No_of_students_enrolled
From semester inner join registration
on semester.semester_id=registration.semester_id
where Semester_Desc like 'Fall 2009' and Sec_ID=1

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

select Sec_ID
From semester inner join section
on semester.semester_id=section.semester_id
where Semester_Desc like 'Spring 2009' and Course_ID='CS-190'

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.

Select count(*) as Cnt
From
(
Select course_ID,SEc_ID
From teaches inner join faculty
On teaches.faculty_ID=faculty.faculty_ID
Where Faculty.Name='Katz'
)Tbl1
Inner Join
registration
on Tbl1.Course_ID=registration.Course_ID and Tbl1.Sec_ID=registration.Sec_ID
where registration.Semester_ID='Fa09'

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

select round(avg(TRUNC(MONTHS_BETWEEN(SYSDATE, Birth_Date))/12),2) as average_age
,round(max(TRUNC(MONTHS_BETWEEN(SYSDATE, Birth_Date))/12),2) as highest_age
,round(min(TRUNC(MONTHS_BETWEEN(SYSDATE, Birth_Date))/12),2) as lowest_age
from Student;

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

Select Dept_ID, Count(*) as Cnt
From Faculty
Group by Dept_ID

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

Select count(*) as Cnt
From teaches
where Semester_ID='Sp10'

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

Select Distinct Faculty_ID as "Number of Instructors"
From Advisor

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.

Select Dept_Id,Count(Faculty.Faculty_ID) as Cnt
From Faculty
Inner Join
Teaches
On Faculty.Faculty_ID=Teaches.Faculty_ID
Where Semester_ID='Sp10'
Group by Dept_ID

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.

Select Max(Dept.Dept_Name),Count(Faculty.Faculty_ID) as Cnt
From Faculty
Inner Join
Dept
On Faculty.Dept_Id=Dept.Dept_Id
Group by Dept.Dept_ID

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.

select Sec_ID,Course_ID,Count(Student_ID) as Cnt
From Registration
where Course_ID='EN-1000' and Sec_ID='1'
Group by Sec_ID,Course_ID

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

select Count(Student_ID) as Cnt
From Registration
where Course_ID='BIO-301'

Query 12: For each course that has more than one section, display the course title, course ID, total capacity, and number of sections.

Select Max(Title),Course.Course_ID,count(*)
From Section Inner Join Course
On Section.Course_ID=Course.Course_ID
Group by Course.Course_ID
Having count(Sec_ID)>1

Navigate

/*My instructor says: My driver does not test all the class functions. My test d

/*Please post command +output*/ Assignment 3: Text File Utilities The purpose of

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

/*Please answer the Queries and display the code and output.*/ This assignment f

Question

Explanation / Answer

Related Questions

Navigate

/Please answer the Queries and display the code and output./ This assignment f