For each of the following exercises, copy/paste both your SQL command and a screen shot of the results into a Word document.  You should obtain the following results. Code used to complete each problem should be contained in the same batch.

 

 

1. Set two variable values as follows:

 

@minEnrollment = 10

@maxEnrollment = 20

 

Determine the number of courses with enrollments between the values assigned to @minEnrollment and @maxEnrollment. If there are courses with enrollments between these two values, display a message in the form  

 

There is/are __class(es) with enrollments between __ and __..

 

If there are no classes within the defined range, display a message in the form 

"

There are no classes with an enrollment between __ and __ students."

 

2. Select the courses for which Bill Shakespeare has the most students and the least students. Assign these numbers and the difference between the numbers to variables. Display results in the form:

 

The largest class taught by Bill Shakespeare has an enrollment of _____.

The smallest class taught by Bill Shakespeare has an enrollment of ____.

There is a difference of _____ between his/her smallest and largest class.

 

 

3. Al Einstein has won the Nobel Prize in Physics and will be away next term to accept the award and guest-lecture around the world. The Dean has decided to remove all of his courses from the schedule.  Declare a cursor that selects all Faculty_ID's from the Course table. Execute the cursor, using it to delete all records for Al Einstein.

 

4. Make a dynamic SQL statement that, when executed, will display faculty first names, last names, course reference numbers, and course enrollments for each course they are teaching. The table names should be assigned as variables.

 

5. Make a TRY/CATCH statement that will validate an attempt to insert a record into the Course table.  Try to insert 3 records that contain the following errors:

 

1. An illegal course reference number.
2. A class with more than 40 students.
3. A record with no errors.

Each record with erroneous data should produce an error number and error message. Records with no errors should display a message indicating successful insertion into the table. Display results for all three attempts.

 

  

Results Messages Course_ID Ref Number 12345 54321 13524 24653 14862 96032 64321 90908 1 1 2 2 3 3 4 4 5 6 6 7 7 8 9 10 Faculty_ID Term Enrollment A 3 3 1 1 2 1 2 3 AB B B C B C 00 CA 28 24 18 7 29 14 8 23 38