For each of the PL/SQL exercises, you should submit your script files (named lab8-2def.sql and lab8-2demo.sql, etc.) to Myo, who will make comments, suggestions, and corrections.

1. (From lab 8:) Once you understand the the bin2dec function, you'll write a function dec2bin to compute the opposite direction. Uncomment-out the declaration of this function and write it in the space provided. You can access the digits of the input the same way as in bin2dec. Since the result is a bitstring, the function's return type should be a varchar.

   Now, you'll re-implement your function in a slightly easier way. Since the parameter is a decimal, it can be represented as an ordinary number var. Two functions that may be helpful are mod (e.g., mod(10,3) -> 1) and trunc (e.g., trunc(10.5) -> 10).

2. (From lab 8:) Write a PL/SQL function taking a number as a parameter and returning the name of the employee, from table emp whose salary is equal to or closest to the value given. Ties may be broken arbitrarily. (I.e., return any one of a set of tying employees.)
3. (From lab 8:) Write a procedure get_subords(), taking a string parameter representing the name of an employee, to print a listing of all the named employee's direct subordinates. If this employee does not manage anyone else, print a message saying so. If the manager name is not found, print an error message indicating this.

4. Remember table contraints? Create tables as describe in this schema. Notice that the schema incluedes primary keys, foreign keys, unique contraints (which prevent duplicates, but not nulls, from being written to the specified fields), NOT NULLs, and domain constraints. It's a good idea to give names to your constraints, in order to make the error messages more helpful. To review the constraint syntax, take a look at this page. Be sure to name the course id constraint!

   When your tables are created, the following inserts (or pairs of inserts) should fail:

   1. insert into student(first) values('bill');
   2. insert into student values('123', 'gates', 'bill', sysdate, 'I', 4.0);
   3. insert into course values('101-1', '101', 'Intro to CS');
      insert into course values('101-1', '222', 'Intro to Semiotics');
   4. insert into course values('222-1', '1', 'Intro to Semiotics');
      insert into course values('333-1', '1', 'Intro to Semiotics');
   5. insert into enrolled values('999', '999-9', 'fall'); where '999' is not a student SSN and '999-9' is not a real course section number

5. Given the constraints you implemented above, it's now impossible to delete a student from the student table when enrolled refers to him. Still, we may want to delete a student, and it would be tedious to go delete the student's enrollments manually. Write a trigger, therefore, that will delete all a student's enrollments when deleting the student. (Will this be a BEFORE or after trigger?)

6. Given the constraints you implemented above, it should also be impossible to delete a course that has enrollees. To confirm this, insert a new course into course and an enrollment record for it in enrolled and try to delete the course. You should get an error.

   To effect automatic deletions of the course's enrollments, you could create another trigger as above. There is, however, another way. First, recall the name you gave to this constraint. (To get a list of your constaints, execute: SELECT CONSTRAINT_NAME FROM USER_CONSTAINTS;.) Now, drop that constraint:

   alter table enrolled drop constraint ;

   And create a new one, this kind with the addition of ON DELETE CASCADE:

   alter table enrolled add CONSTRAINT FOREIGN KEY (secno) REFERENCES course(secno) ON DELETE CASCADE;

   The result is that the delete of a course "cascades" to a delete of that course's enrollments. Test this by deleting a course that has enrollments.

7. As you know, the sqlzoo website contains examples based on data from the CIA World Factbook. Of course, they didn't import this data manually--they used Oracle's bulk loader. Download a copy of the CIA data file. Based on the data you see in the file, create a cia table on your system. (Use varchar(255) for the string fields.) Once you have a table, create a control file, based on the examples in class, for this input. The one big difference is that this file is tab-separated. A tab, of course, can't readily be specified, but you can indicate it with 'chr(9)' (including the single-quotes). Once you have your control file, test it on the data. Do you get 261 records?
8. If you have extra time (!), you can work on these.