PL-SQL Oracle

Problem 1

Using a CURSOR, display all students that have a phone number in the 201 area code. You must use the following Select statement:

SELECT student_id, phone

FROM student.student;

Display the Total number of students with an area code of 201. The answer is 77.

Problem 2

Using a VARRAY collection, create a VARRAY with 5 entries (the first 5 prime numbers). Use a FOR statement to display these 5 entries.

Problem 3

Within one PL program, create code that will throw and catch the following exceptions. You may use commenting to test your code.

DUP_VAL_ON_INDEX

NO_DATA_FOUND

ZERO_DIVIDE

INVALID_NUMBER

VALUE_ERROR

STUDENT GRADE NUMBER(B) NUMBER (B) STUDENT.GRADE TYPE WEIGHT STUDENT ID SECTION_ID GRADE TYPE COCE ORADE_CODE_OCCURRENCE NUMBER(3) NUMERIC COMMENTS CREATED BY CRFATFD_DATF GRADE TYPE_CODE CHAR (2 BYTE CREATED BY MODIFIED_BY CHAR (2 BYTE GRADE TYPE_CODE PERCENT OF_FINAL GRADE NUMBER (3) CREATED BY ECTION VARCHAR2 (3D BYTE NUMBER (3) VARCHAR2 2000 BYTE) VARCHAR2 (30 BYTE) VARCHAR2 (3D BYTE VARCHAR2 (30 BYTE) GRTYP_PK (GRADE TYPE_CODE) DATE MODIFIED DATE GRTW_PK(SECTION ID GRADE_TYPE_CoDE) GRTW GRTYP FK (GRADE_TYPE_CODE) MODIFIED_DATE GR PK(STUDENT ID. SECTION ID. GRADE_TYPE_CODE. GRADE_CODE_OCCURRENCE) OR ENR_FK (STUDENT ID. SECTION D) GR GRTW FK(SECTION ID, GRADE TYPE CODE GATW SECT FK (SECTION ID) STUDENT.ENROLLMENT STUDENT STUDENT STUDENT.ZIPCODE NUMBER () NUMBER (8) PF" STUDENT ID STUDENT ID SALUTATION NUMBER(B) VARCHAR2 (5 BYTE) VARCHAR2 (25 BYTE) VARCHAR2 (25 BYTE) VARCHAR2 (60 BYTE) VARCHAR2 (5 BYTE) VARCHAR2 [16 BYTE) VARCHAR2 (50 BYTE) SECTION I ENROLL DATE VARCHAR2 (25 BYTE) VARCHAR2 (30 BYTE) VARCHAR2 (30 BYTE) GRADE NUMBER (3) VARCHAR2 (30 BYTE) CREATED_BY STREET ACDRESS a P PHONE CREATED DATE DATE MODIFIED_BY MODIFIED DATE DATE ENR PK (STUDENTD, SECTION ID) ENR SECT FK(SECTION ID) VARCHAR2 (30 BYTE) REGISTRATION DATE DATE CREATED CREATED_CATE MODIFIED_BY MODIFIED DATE VARCHAR2 (30 BYTE) STUDENT INSTRUCTOR STU PK (STUDENT ID INSTRUCTOR ID SALUTATION VARCHAR2 (5 BYTE) VARCHAR2 (25 BYTE) VARCHAR2 (5 B VARCHA2 (OD BYTE) STREET ADDRES ZIP SECTION ID COURSE NO SECTION NO START DATE TIME DATE LOCATION INSTRUCTOR ID CAPACITY CREATED BY CREATED_DATE MODIFIED BY MODIFIED_DATE CREATED BY CREATED DATE VARCHAR2 BYTE) DATE VARCHAR2 BYTE) STUDENT COURSE VARCHAR2 (50 BYTE) COURSE NO DESCRIPTION COST PREREQUISITE NUMBER(8) CREATEC BY CREATEC DATE DATE VARCHAR2 (EO BYTE MODIFIED DATE INST PK (INS TRUCTOR ID) VARCHAR2 (30 BYTE) VARCHAR2 (30 BYTE) INST-ZIP-FK (21P) VARCHAR2 (30 BYTE VARCHAR2 (30 BYTE) MODIFIED DATE DATE SECT_PK(SECTION ID) CRSE_PK COURSE_NO) SECT CRSE_FK (COJRSE NO) SECT INST_FK (INS RUCTOR ID) CRSE_CRSE_FK PREREQUISITE)