Problem:

Write one or more SQL statements that modify the STUDENT table and creates an object type,

phone_obj, with attributes COUNTRY_CODE, AREA_CODE and PHONE_NUMBER. Provide the SQL and logs or screenshots that supports your answer.

Solution:

CREATE OR REPLACE TYPE phone_obj AS OBJECT

(

AREA_CODE NUMBER(3),

PHONE_NUMBER VARCHAR2(8)

);

 

ALTER TABLE STUDENT

ADD (PHONENO phone_obj);

 

UPDATE TABLE STUDENT

SET PHONENO = phone_obj(SUBSTR(PHONE,1,3),SUBSTR(PHONE,5,8));

 

ALTER TABLE STUDENT

DROP COLUMN PHONE;