Question

Create a stored procedure that can be used to add a student to the school and a section of a course. 1. If the student already exists, then just add him to the section (do not update information like address). 2. The procedure will require the following arguments (see table definition for types): A. Salutation B. First Name C. Last Name D. Street Address (including City) E. ZIP Code F. Phone Number G. Employer Name (if any) H. Course Number I. Section Number 3. The program should error if any constraints are violated (no last name, bad zip, bad course, bad section) 4. It should rollback changes if these or any other errors occur. 5. The dates in the STUDENT and ENROLLMENT tables should be set to the day the procedure is run. 6. Don't forget that each table has 4 extra required fields ( CREATED_BY, CREATED_DATE, MODIFIED_BY, and MODIFIED_DATE). The current user should be in both 'BY's and the day the procedure is run in both 'DATE's. 7. To test the procedure insert yourself into course 120, section 5. 8. If the insert fails (usually due to the ZIP code not being in the ZIPCODE table, then find a legal ZIP code in that table and use it instead of your own or insert you own ZIP into the table and rerun the procedure. 9. The following SQL can be used to verify that you have successfully placed yourself in the course: SELECT S.LAST_NAME [Last Name], s.FIRST_NAME [First Name], e.ENROLL_DATE [Enrolled On] FROM ENROLLMENT e INNER JOIN SECTION c ON c.SECTION_ID = e.SECTION_ID INNER JOIN STUDENT s ON s.STUDENT_ID = e.STUDENT_ID WHERE c.COURSE_NO =120 AND c.SECTION_NO = 5 structure of

[STUDENT]

STUDENT_ID(PK)                INT                        NOT NULL

SALUTATION                       VARCHAR(5)         NULL

FIRST_NAME                      VARCHAR(25)       NULL

LAST_NAME                        VARCHAR(25)       NOT NULL

STREET_ADDRESS              VARCHAR(50)       NULL

ZIP(FK)                                VARCHAR(5)         NOT NULL

PHONE                                VARCHAR(15)       NULL

EMPLOYER                          VARCHAR(60)       NULL

REGISTRATION_DATE            DATE       NOT NULL

[SECTION]

SECTION_ID(PK)                        INT                        NOT NULL

COURSE_NO(FK)                      INT                        NOT NULL

SECTION_NO                            TINYINT                 NOT NULL

START_DATE_TIME                  DATE                     NOT NULL

LOCATION                                 VARCHAR(30)       NULL

INSTRUCTOR_ID(FK)                INT                        NOT NULL

CAPACITY                                  TINYINT                 NULL

[ENROLLMENT]

STUDENT_ID(PK)(FK)                INT                        NOT NULL

SECTION_ID(PK)(FK)                 INT                        NOT NULL

ENROLL_DATE                          DATE                     NOT NULL

FINAL_GRADE                           TINYINT                 NULL

[ZIPCODE]

ZIP(PK)                                      CHAR(5)                NOT NULL

CITY                                           VARCHAR(25)       NULL

STATE                                        VARCHAR(2)         NULL

[COURSE]

COURSE_NO(PK)    INT                    NOT NULL

DESCRIPTION          VARCHAR(50)   NOT NULL

COST                       MONEY             NULL

PRERQUISITE(FK)   INT                    NULL

Answer 1

// the below statements are used for creating a simple insert stored procedure. You can run it through an explicit call from a host language program. .... similarly create other tables....

DROP PROCEDURE IF EXISTS `students`
GO

CREATE PROCEDURE students
     (
        IN p_student_id                    INT(11)       ,
        IN p_salutation                    VARCHAR(5)   ,
        IN p_lastname                      VARCHAR(25)   ,
        IN p_firstname                     VARCHAR(25)   ,
        IN p_streetaddress                 VARCHAR(50)    ,
        IN p_zip                           VARCHAR(5)    ,
        IN p_phone                         VARCHAR(15)   ,
   IN p_employer                      VARCHAR(60)   ,
   IN p_registrationdate            DATE    ,
   IN_createdby                VARCHAR(25) ,
   IN_createddate                DATE ,
   IN_modifyby                VARCHAR(25) ,
   IN_modifydate                DATE
     )
BEGIN

    INSERT INTO students
         (
           student_id ,
           salutation ,
           lastname    ,
           firstname   ,
           streetaddress ,
           zip         ,
           phone ,    
       employer ,
       registrationdate ,
       createdby ,
       createddate ,
       modifyby ,
       modifydate                
         )
    VALUES
         (
           p_student_id ,
           p_salutation ,
           p_lastname    ,
           p_firstname   ,
           p_streetaddress ,
           p_zip         ,
           p_phone ,    
       p_employer ,
       p_registrationdate                
       p_createdby ,
       p_createddate ,
       p_modifyby ,
       p_modifydate                
     ) ;
END

GO

// the below statements are used to create the table ..using mysql command.. similarly create other tables....

CREATE TABLE IF NOT EXISTS student
     (
         STUDENT_ID                INT               NOT NULL
   SALUTATION                 VARCHAR(5)        NULL
   FIRST_NAME                 VARCHAR(25)       NULL
   LAST_NAME                  VARCHAR(25)       NOT NULL
   STREET_ADDRESS             VARCHAR(50)       NULL
   ZIP(FK)                    VARCHAR(5)        NOT NULL
   PHONE                      VARCHAR(15)       NULL
   EMPLOYER                   VARCHAR(60)       NULL
   REGISTRATION_DATE           DATE             NOT NULL
       PRIMARY KEY (STUDENT_ID)
     ) ENGINE = MYISAM

GO

//query to check if student data exists if so add to section

CREATE DEFINER=`root`@`127.0.0.1`
PROCEDURE `GetUserName`(student STUDENT_ID INT,SECTION SECTION_ID INT)
BEGIN
DECLARE SECTION_ID INT;
SELECT STUDENT_ID SECTION SECTION_ID
FROM STUDENT,SECTION
WHERE STUDENT_ID = STUDENT_ID;
IF STUDENT_ID EXISTS
THEN
INSERT INTO SECTION (SECTION_ID, COURSE_NO, SECTION_NO, START_DATE_TIME, LOCATION, INSTRUCTOR_ID, CAPACITY);
END IF;
END

//query to select the required data

mysql> CREATE PROCEDURE my_proc_OUT (OUT highest_salary INT)
-> BEGIN
-> SELECT S.LAST_NAME [Last Name], s.FIRST_NAME [First Name], e.ENROLL_DATE [Enrolled On] FROM ENROLLMENT e INNER JOIN SECTION c ON c.SECTION_ID = e.SECTION_ID INNER JOIN STUDENT s ON s.STUDENT_ID = e.STUDENT_ID WHERE c.COURSE_NO =120 AND c.SECTION_NO = 5
-> END$$