SQL> start a:a
SQL> set echo on
SQL> set serveroutput on
SQL> --
SQL> -- joseph speigle
SQL> -- lab 6
SQL> -- july 13, 2002
SQL> --
SQL> -- question one
SQL> -- create a block that will allow the user to enter a student ID
SQL> -- student first name, last name, student zip.
SQL> --
SQL> DECLARE
  2  	     v_student_id student.student_id%TYPE := &v_student_id;
  3  	     v_last_name student.last_name%TYPE := '&v_last_name';
  4  	     v_first_name student.first_name%TYPE := '&v_first_name';
  5  	     v_zip student.zip%TYPE := &v_zip;
  6  	     v_check student.student_id%TYPE;
  7  BEGIN
  8  --      select student_id INTO v_check from student where student_id = v_student_id;
  9  
 10  	     INSERT into student values (v_student_id,NULL,
 11  	      v_first_name,v_last_name, NULL, v_zip, NULL, NULL,
 12  	     SYSDATE, USER, SYSDATE, USER, SYSDATE);
 13  	     EXCEPTION
 14  	     when DUP_VAL_ON_INDEX then
 15  		     DBMS_OUTPUT.PUT_LINE('that student_id is already on file');
 16  --      when others then
 17  -- 	     DBMS_OUTPUT.PUT_LINE('other exception occurred');
 18  
 19  END;
 20  .
SQL> /
Enter value for v_student_id: 1
old   2: 	v_student_id student.student_id%TYPE := &v_student_id;
new   2: 	v_student_id student.student_id%TYPE := 1;
Enter value for v_last_name: a
old   3: 	v_last_name student.last_name%TYPE := '&v_last_name';
new   3: 	v_last_name student.last_name%TYPE := 'a';
Enter value for v_first_name: a
old   4: 	v_first_name student.first_name%TYPE := '&v_first_name';
new   4: 	v_first_name student.first_name%TYPE := 'a';
Enter value for v_zip: 11419
old   5: 	v_zip student.zip%TYPE := &v_zip;
new   5: 	v_zip student.zip%TYPE := 11419;

PL/SQL procedure successfully completed.

SQL> -- Question 2
SQL> -- verify the student record
SQL> --
SQL> select last_name, first_name from student where student_id = 1;

LAST_NAME                 FIRST_NAME                                            
------------------------- -------------------------                             
a                         a                                                     

SQL> -- question 3
SQL> -- drop the student
SQL> delete from student where student_id = 1;

1 row deleted.

SQL> --
SQL> -- question 4
SQL> --
SQL> declare
  2  	     v_iid instructor.instructor_id%TYPE := &v_iid;
  3  	     v_salutation instructor.salutation%TYPE;
  4  	     v_first_name instructor.first_name%TYPE;
  5  	     v_last_name instructor.last_name%TYPE;
  6  	     v_phone instructor.phone%TYPE;
  7  	     v_number NUMBER;
  8  BEGIN
  9  	     SELECT i.salutation, i.first_name, i.last_name, i.phone, count(*)
 10  	     INTO v_salutation, v_first_name, v_last_name, v_phone, v_number
 11  	     FROM instructor i, section s
 12  	     WHERE i.instructor_id = v_iid
 13  	     AND i.instructor_id = s.instructor_id
 14  	     GROUP BY i.salutation, i.first_name, i.last_name, i.phone;
 15  
 16  	     DBMS_OUTPUT.PUT_LINE('Instructor: ' || v_salutation ||' '|| v_first_name ||' '||
 17  	     v_last_name ||'   phone: '|| v_phone ||',	teaches '|| v_number
 18  	     ||' sections');
 19  	     EXCEPTION
 20  	     when NO_DATA_FOUND then
 21  		     DBMS_OUTPUT.PUT_LINE('that instructor_id is not on file');
 22  	     when others then
 23  		     DBMS_OUTPUT.PUT_LINE('other exception occurred');
 24  
 25  END;
 26  .
SQL> /
Enter value for v_iid: 101
old   2: 	v_iid instructor.instructor_id%TYPE := &v_iid;
new   2: 	v_iid instructor.instructor_id%TYPE := 101;
Instructor: Mr Fernand Hanks   phone: 2125551212,  teaches 9 sections           

PL/SQL procedure successfully completed.

SQL> spool off