This section gives you some suggested answers to the questions in Lab 4.1, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.
Run the PL/SQL block from the pre-exercise example.
In the declaration section of the PL/SQL block, the variable v_average_cost is declared as a varchar2. In the executable section of the block, this variable is given the value of the average cost from the course table by means of the SELECT INTO syntax. The SQL function TO_CHAR is issued to format the number. The DBMS_OUTPUT is then used to show the result to the screen.
The variable v_average_cost will be set to NULL when it is first declared. Because the DBMS_OUTPUT is placed before the variable is given a value, the output for the variable will be NULL. After the SELECT INTO, the variable will be given the same value as in the original block described in question a, but it will not be displayed because there is not another DBMS_OUTPUT line in the PL/SQL block.
In order to generate a unique ID, the maximum student_id is selected into a variable and then it is incremented by one. It is important to remember in this example that there is foreign key on the zip item in the student table, which means that the zipcode you choose to enter must be in the ZIPCODE table.
In the declaration section of the PL/SQL block, two variables are declared. They are both set to be datatypes within the student table using the %TYPE method of declaration. This ensures the datatypes match the columns of the tables into which they will be inserted. The two variables v_user and v_date are given values from the system by means of SELECT INTO. The value of the student_id is generated by using the next value of the student_id_seq sequence.