CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
Lizzie
Lizzie

Reputation: 179

PL/SQL Enroll Student into Class

I need to enroll students into a class if the class capacity is not full.

Students:

SNUM  SNAME
****  ****
102   Bob
103   Lee
104   Ali

Courses:

DEPT  CNUM
****  ****
FIN   300
FIN   330
FIN   400

ClassSchedule:

ClassNum  Instructor  Capacity
********  **********  *******
10510     Larry        3
10210     Bob          5
10401     Sally        10

Enrollments:

SNUM  ClassNum
****  ********
102   10510
103   10510
104   10401

My pseudocode: 

CREATE OR REPLACE Procedure AddStudent(
  p_snum    students.SNUM%type
  p_ClassNum SchClasses.ClassNum%Type ) 
AS
  p_Capacity_SchClasses number;

BEGIN
  SELECT Count(*) into p_Capacity_ClassSchedue
  FROM SchClasses cs
  JOIN Enrollments e ON cs.ClassNum = e.ClassNum
  GROUP BY sc.CallNum

  IF count(p_ClassNum) <=  p_Capacity_SchClasses THEN
    insert into E values(p_SNUM, p_ClassNum, null);
    dbms_output.put.line('Congrats, you are now enrolled.');
  ELSE
    dbms_output.put.line('Sorry, this class is full.');
  END IF;
END;
/

How would I make p_snum and p_classnum count as 1 student?

Upvotes: 0

Views: 808

Answers (2)

Valli
Valli

Reputation: 1450

    CREATE OR REPLACE Procedure AddStudent(
      p_snum    students.SNUM%type
      p_ClassNum SchClasses.ClassNum%Type ) 
    AS
      p_Capacity_SchClasses number;
     v_capcity number;


    BEGIN
    --Get current capacity for class number passed into the procedure
      SELECT Count(*), cs.ClassNum 
        into p_Capacity_ClassSchedule 
      FROM SchClasses cs
      JOIN Enrollments e ON cs.ClassNum = e.ClassNum
     Where cs.ClassNum = p_ClassNum
    --get the total capacity of the class number passed in to procedure
     Select Capacity
       INTO v_capcity
       FROM SchClasses
     WHERE ClassNum = p_ClassNum

--Check If the total capacity is greater than the current capacity 
      IF v_capcity >  p_Capacity_SchClasses THEN
        insert into E values(p_SNUM, p_ClassNum, null);
        dbms_output.put.line('Congrats, you are now enrolled.');
      ELSE
        dbms_output.put.line('Sorry, this class is full.');
      END IF;
    END;
    /

Upvotes: 1

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

I believe, what you want to do is -

  1. Count the number of students enrolled in a particular class.

  2. If the number of students is less than the MAX which can be enrolled. Add a student to it. 

    CREATE OR REPLACE Procedure AddStudent(
  p_snum    students.SNUM%type
  p_ClassNum SchClasses.ClassNum%Type ) 
AS
  p_num_current_enrolled NUMBER := 0;
  p_num_max_capacity NUMBER := 0;
BEGIN
  -- the below will find the number of students enrolled in the class which you want to add a new student to
  SELECT Count(*) into p_num_current_enrolled
  FROM enrollments
  where ClassNum  = p_ClassNum;

  -- Get the max capacity of the class
  SELECT capacity into p_max_capacity
  from ClassSchedule
  where ClassNum = p_ClassNum;


  IF p_num_current_enrolled <  p_max_capacity THEN
     insert into Enrollments values(p_SNUM, p_ClassNum, null);
     dbms_output.put_line('Congrats, you are now enrolled.');
  ELSE
     dbms_output.put_line('Sorry, this class is full.');
  END IF;
END;
/

Upvotes: 1

Related Questions