Other Suggestions

For SQL statements embedded in PL/SQL, use the same formatting guidelines to determine how the statements should appear in a block.
Provide a comment header that explains the intent of the block, lists the creation date and author's name, and have a line for each revision with the author's name , date, and the description of the revision.

FOR EXAMPLE

The following example shows the aforementioned suggestions. Notice that it also uses a monospaced font (Courier) that makes the formatting easier. Proportional spaced fonts can hide spaces and make lining up clauses difficult. Most text and programming editors by default use a monospace font.

REM ********************************************************
REM * filename: coursediscount01.sql              version: 1
REM * purpose:  To give discounts to courses that have at
REM *           least one section with an enrollment of more
REM *           than 10 students.
REM * args:     none
REM *
REM * created by:  s.tashi            date: January 1, 2000
REM * modified by: y.sonam            date: February 1, 2000
REM * description: Fixed cursor, added indentation and
REM *              comments.
REM ********************************************************
DECLARE
   -- C_DISCOUNT_COURSE finds a list of courses that have
   -- at least one section with an enrollment of at least 10
   -- students.
   CURSOR c_discount_course IS
      SELECT DISTINCT course_no
        FROM section sect
       WHERE 10 <= (SELECT COUNT(*)
                      FROM enrollment enr
                     WHERE enr.section_id = sect.section_id
                    );

   -- discount rate for courses that cost more than 00.00
   con_discount_2000 CONSTANT NUMBER := .90;

   -- discount rate for courses that cost between 01.00
   -- and 00.00
   con_discount_other CONSTANT NUMBER := .95;

   v_current_course_cost course.cost%TYPE;
   v_discount_all NUMBER;
   e_update_is_problematic EXCEPTION;
BEGIN
   -- For courses to be discounted, determine the current
   -- and new cost values
   FOR r_discount_course in c_discount_course LOOP
      SELECT cost
        INTO v_current_course_cost
        FROM course
       WHERE course_no = r_discount_course.course_no;

      IF v_current_course_cost > 2000 THEN
         v_discount_all := con_discount_2000;
      ELSE
         IF v_current_course_cost > 1000 THEN
            v_discount_all :=  con_discount_other;
         ELSE
            v_discount_all := 1;
         END IF;
      END IF;

      BEGIN
         UPDATE course
            SET cost = cost * v_discount_all
          WHERE course_no = r_discount_course.course_no;
      EXCEPTION
         WHEN OTHERS THEN
            RAISE e_update_is_problematic;
      END;   -- end of sub-block to update record
   END LOOP; -- end of main LOOP

   COMMIT;

EXCEPTION
   WHEN e_update_is_problematic THEN
      -- Undo all transactions in this run of the program
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE
         ('There was a problem updating a course cost.');
   WHEN OTHERS THEN
      NULL;
END;
/

Appendix C. Student Database Schema

Table and Column Descriptions

COURSE: Information for a course

Column Name	Null	Type	Comments
COURSE_NO	NOT NULL	NUMBER(8, 0)	The unique course number
DESCRIPTION	NULL	VARCHAR2(50)	The full name for this course
COST	NULL	NUMBER(9,2)	The dollar amount charged for enrollment in this course
PREREQUISITE	NULL	NUMBER(8, 0)	The ID number of the course that must be taken as a prerequisite to this course
CREATED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates user who inserted data
CREATED_DATE	NOT NULL	DATE	Audit column ” indicates date of insert
MODIFIED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates who made last update
MODIFIED_DATE	NOT NULL	DATE	Audit column ” date of last update

SECTION: Information for an individual section (class) of a particular course

Column Name	Null	Type	Comments
SECTION_ID	NOT NULL	NUMBER(8,0)	The unique ID for a section
COURSE_NO	NOT NULL	NUMBER(8,0)	The course number for which this is a section
SECTION_NO	NOT NULL	NUMBER(3)	The individual section number within this course
START_DATE_TIME	NULL	DATE	The date and time on which this section meets
LOCATION	NULL	VARCHAR2(50)	The meeting room for the section
INSTRUCTOR_ID	NOT NULL	NUMBER(8,0)	The ID number of the instructor who teaches this section
CAPACITY	NULL	NUMBER(3,0)	The maximum number of students allowed in this section
CREATED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates user who inserted data
CREATED_DATE	NOT NULL	DATE	Audit column ” indicates date of insert
MODIFIED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates who made last update
MODIFIED_DATE	NOT NULL	DATE	Audit column ” date of last update

STUDENT: Profile information for a student

Column Name	Null	Type	Comments
STUDENT_ID	NOT NULL	NUMBER(8,0)	The unique ID for a student
SALUTATION	NULL	VARCHAR2(5)	This student's title (Ms., Mr., Dr., etc.)
FIRST_NAME	NULL	VARCHAR2(25)	This student's first name
LAST_NAME	NOT NULL	VARCHAR2(25)	This student's last name
STREET_ADDRESS	NULL	VARCHAR2(50)	This student's street address
ZIP	NOT NULL	VARCHAR2(5)	The postal zipcode for this student
PHONE	NULL	VARCHAR2(15)	The phone number for this student, including area code
EMPLOYER	NULL	VARCHAR2(50)	The name of the company where this student is employed
REGISTRATION_DATE	NOT NULL	DATE	The date this student registered in the program
CREATED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates user who inserted data
CREATED_DATE	NOT NULL	DATE	Audit column ” indicates date of insert
MODIFIED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates who made last update
MODIFIED_DATE	NOT NULL	DATE	Audit column ” date of last update

ENROLLMENT: Information for a student registered for a particular section of a particular course (class)

Column Name	Null	Type	Comments
STUDENT_ID	NOT NULL	NUMBER(8,0)	The ID for a student
SECTION_ID	NOT NULL	NUMBER(8,0)	The ID for a section
ENROLL_DATE	NOT NULL	DATE	The date this student registered for this section
FINAL_GRADE	NULL	NUMBER(3,0)	The final grade given to this student for all work in this section (class)
CREATED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates user who inserted data
CREATED_DATE	NOT NULL	DATE	Audit column ” indicates date of insert
MODIFIED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates who made last update
MODIFIED_DATE	NOT NULL	DATE	Audit column ” date of last update

INSTRUCTOR: Profile information for an instructor

Column Name	Null	Type	Comments
INSTRUCTOR_ID	NOT NULL	NUMBER(8)	The unique ID for an instructor
SALUTATION	NULL	VARCHAR2(5)	This instructor's title (Mr., Ms., Dr., Rev., etc.)
FIRST_NAME	NULL	VARCHAR2(25)	This instructor's first name
LAST_NAME	NULL	VARCHAR2(25)	This instructor's last name
STREET_ADDRESS	NULL	VARCHAR2(50)	This instructor's street address
ZIP	NULL	VARCHAR2(5)	The postal zipcode for this instructor
PHONE	NULL	VARCHAR2(15)	The phone number for this instructor, including area code
CREATED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates user who inserted data
CREATED_DATE	NOT NULL	DATE	Audit column ” indicates date of insert
MODIFIED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates who made last update
MODIFIED_DATE	NOT NULL	DATE	Audit column ” date of last update

ZIPCODE: City, state, and zipcode information

Column Name	Null	Type	Comments
ZIP	NOT NULL	VARCHAR2(5)	The zipcode number, unique for a city and state
CITY	NULL	VARCHAR2(25)	The city name for this zipcode
STATE	NULL	VARCHAR2(2)	The postal abbreviation for the U.S. state
CREATED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates user who inserted data
CREATED_DATE	NOT NULL	DATE	Audit column ” indicates date of insert
MODIFIED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates who made last update
MODIFIED_DATE	NOT NULL	DATE	Audit column ” date of last update

GRADE_TYPE: Lookup table of a grade type (code) and its description

Column Name	Null	Type	Comments
GRADE_TYPE_CODE	NOT NULL	CHAR(2)	The unique code that identifies a category of grade (e.g., MT, HW)
DESCRIPTION	NOT NULL	VARCHAR2(50)	The description for this code (e.g., Midterm, Homework)
CREATED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates user who inserted data
CREATED_DATE	NOT NULL	DATE	Audit column ” indicates date of insert
MODIFIED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates who made last update
MODIFIED_DATE	NOT NULL	DATE	Audit column ” date of last update

GRADE_TYPE_WEIGHT: Information on how the final grade for a particular section is computed; for example, the midterm constitutes 50%, the quiz 10%, and the final examination 40% of the final grade

Column Name	Null	Type	Comments
SECTION_ID	NOT NULL	NUMBER(8)	The ID for a section
GRADE_TYPE_CODE	NOT NULL	CHAR(2)	The code which identifies a category of grade
NUMBER_PER_SECTION	NOT NULL	NUMBER(3)	How many of these grade types can be used in this section (i.e., there may be three quizzes)
PERCENT_OF_FINAL_GRADE	NOT NULL	NUMBER(3)	The percentage this category of grade contributes to the final grade
DROP_LOWEST	NOT NULL	CHAR(1)	Is the lowest grade in this type removed when determining the final grade? (Y/N)
CREATED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates user who inserted data
CREATED_DATE	NOT NULL	DATE	Audit column ” indicates date of insert
MODIFIED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates who made last update
MODIFIED_DATE	NOT NULL	DATE	Audit column ” date of last update

GRADE: The individual grades a student received for a particular section (class)

Column Name	Null	Type	Comments
STUDENT_ID	NOT NULL	NUMBER(8)	The ID for a student
SECTION_ID	NOT NULL	NUMBER(8)	The ID for a section
GRADE_TYPE_CODE	NOT NULL	CHAR(2)	The code that identifies a category of grade
GRADE_CODE_OCCURRENCE	NOT NULL	NUMBER(38)	The sequence number of one grade type for one section. For example, there could be multiple assignments numbered 1, 2, 3, etc.
NUMERIC_GRADE	NOT NULL	NUMBER(3)	Numeric grade value (e.g., 70, 75)
COMMENTS	NULL	VARCHAR2(2000)	Instructor's comments on this grade
CREATED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates user who inserted data
CREATED_DATE	NOT NULL	DATE	Audit column ” indicates date of insert
MODIFIED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates who made last update
MODIFIED_DATE	NOT NULL	DATE	Audit column ” date of last update

GRADE_CONVERSION: Converts a number grade to a letter grade

Column Name	Null	Type	Comments
LETTER_GRADE	NOT NULL	VARCHAR(2)	The unique grade as a letter (A, A-, B, B+, etc.)
GRADE_POINT	NOT NULL	NUMBER(3,2)	The number grade on a scale from 0 (F) to 4 (A)
MAX_GRADE	NOT NULL	NUMBER(3)	The highest grade number that corresponds to this letter grade
MIN_GRADE	NOT NULL	NUMBER(3)	The lowest grade number that corresponds to this letter grade
CREATED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates user who inserted data
CREATED_DATE	NOT NULL	DATE	Audit column ” indicates date of insert
MODIFIED_BY	NOT NULL	VARCHAR2(30)	Audit column ” indicates who last made update
MODIFIED_DATE	NOT NULL	DATE	Audit column ” date of last update