Q$2.(20$ points$).$ Refer to page $3$ in note$6$b$\_$Records.

Define a user$-$defined record type called DEPT$\_$INFO that has four fields as:

dept$\_$id $($correspondent to department$\_$id in departments table$),$ dept$\_$name $($department$\_$name in departments table$),$ Max$\_$sal as number $(8,2),$ the maximal salary in this department: function MAX $($salary$),$

Average$\_$sal as number $(8,2),$ the average salary in this department: function AVG $($salary$).$

After having defined this record type, your program will declare a variable of this type.

The program will retrieve the needed info for department$\_$id $60,$ populate each field of this record type variable, and print out the result.

Hint, as we are going to re$-$use the codes again in question $3,$ better to define variable, such as dept$\_$no number $(4)$ :$=60.$

Note, sometimes, it is easier to use two simple SQL statements to get the data needed.

Q$3.(20$ points$)$

In question $2,$ we build a record type, and declare a variable of that type, it is good to store one record. In this question #$3,$ we will create a collection using this record type as its elements, thus it can store a set of records.

Write a PL$/$SQL block, define a user$-$defined record type called Dept$\_$Info as in question $2$ that has four fields.

Define a nested table type named as Dept$\_$Info$\_$NT$,$ that uses record type defined in Q$2$ Dept$\_$Info as its element; then declare a nested table variable called LIST.

Retrieve the data from Departments and Employees table for all the departments that have a manager assigned for that department $($departments$.$manager$\_$id is not null$).$ Populate this LIST with the retrieved data, print out the contents of the LIST.

Note, we use NT$\_$variable$\_$name $($index$)$ to reference the NT element; in this question, we need to use NT$\_$variable$\_$name $($index$).$record$\_$field to reference the element's field. Such as

LIST $($i$).$dept$\_$id for department$\_$id column in the departments table.

In this question $3,$ it will be convenient to declare two separate cursors, one to retrieve the department id and department name, the second cursor to retrieve maximal and average salary from employees table via group by$.$ In the executable section, you may run three loops, two for each cursor, and one for print out. If you have difficulties, please refer to the samples in the class notes.

Please try to solve both question $2$ and $3.$