-------------------------------------------------------------------------------
-- Create a function 
-- Function adds Mr./Ms. to last name depending on sex 
-------------------------------------------------------------------------------
CREATE or REPLACE FUNCTION salutation(p_lname varchar2, p_sex varchar2) 
                           RETURN varchar2 AS
    v_sal    varchar2(3);
    v_salute varchar2(50);

BEGIN
    IF p_sex = 'M' THEN
        v_sal := 'Mr.';
    ELSE 
        v_sal := 'Ms.';
    END IF;

    v_salute := v_sal || ' ' || p_lname;
    RETURN (v_salute);
END;
/

SELECT fname, salutation(lname, sex), student_id        -- call the function
  from student
/


-------------------------------------------------------------------------------
-- Create a function 
-- Function calculates a factorial 
-------------------------------------------------------------------------------
CREATE or REPLACE FUNCTION factorial(num int) RETURN int AS     
    fact int := 1;                                  -- the declaration section
    i    int := 1;

BEGIN 
    WHILE  i <= num  LOOP
        fact := fact * i;
        i    := i + 1;
    END LOOP;

    RETURN fact;
END;
/

SELECT factorial(10) from dual                      -- Call the above function
/


-------------------------------------------------------------------------------
-- Create a function 
-- Function to calculate a student total tuition 
-------------------------------------------------------------------------------
CREATE or REPLACE FUNCTION tuition(p_sid int) RETURN int AS     
    v_tuition int := 0;                     

BEGIN 
    SELECT SUM(price) into v_tuition 
      from student st join class  cl on st.ssn=cl.stu_ssn
                      join course co on co.course_id=cl.course_id
     where student_id = p_sid;

    IF v_tuition is null THEN             -- if student is not taking classes
        v_tuition := 0;
    END IF;

    RETURN v_tuition;
END;
/

SELECT fname, lname, tuition(student_id) from student     -- Call the function