Question

I need to write a SQL procedure that will look for sections with an enrollment less than 6 with the following information:

course.course_no,

course.course_description,

section.section_no,

CountOfEnrollment (which is less than 6)

Given the following tables:

Course:

NM + ON OO COURSE_NO DESCRIPTION 10 Test Course 20 Intro to Information Systems 25 Intro to Programming 80 Programming Techniques 100 Hands-On Windows 120 Intro to Java Programming 122 Intermediate Java Programming 124 Advanced Java Programming 125 Java Developer I 130 Intro to Unix 132 Basics of Unix Admin 134 Advanced Unix Admin 135 Unix Tips and Techniques 140 Systems Analysis 142 Project Management COST PREREQUISITE CREATED_BY CREATED_DATE MODIFIED_BY MODIFIED_DATE 2000 (null) DSCHERER 29-MAR-17 PS4 11-APR-19 1195 (null) DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1195 140 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1595 204 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1195 20 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1195 80 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1195 120 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1195 122 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1195 122 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1195 310 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1195 130 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1195 132 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1095 134 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1195 20 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 1195 20 DSCHERER 29-MAR-17 ARISCHER 05-APR-17 10 11 12 13 14 15

Section:

Enrollment:

Answer 1

SQL PROCEDURE:

CREATE PROCEDURE enrollment_info

AS

SELECT course.course_no, course.course_description, section_no

FROM course, section, enrollment

WHERE course.course_no=section.course_no

AND section.section_id=enrollment.section_id

GROUP BY enrollment.section_id

HAVING COUNT(enrollment.section_id)<6;

To execute created procedure, the SQL syntax is:

EXEC procedure_name;

Hence:

EXEC enrollment_info;

On executing the above statement the required information is resulted as output.