Notes

--@E:Anoop101Week2definition.txt
--creating object type
CREATE OR REPLACE TYPE invoice_address_type AS OBJECT(
street VARCHAR2(25),
city VARCHAR2(25),
country VARCHAR2(25));
/
show error

--creating table named customers
CREATE TABLE customers(
customer_id NUMBER(6),
customer_name VARCHAR2(25),
invoice_address invoice_address_type);
/
show error

--Defining object table name bill addresses
CREATE TABLE bill_addressed OF invoice_address_type;

--Dropping table customers
DROP TABLE customers;

CREATE TABLE customers(
customer_id NUMBER(6),
customer_name VARCHAR2(25),
invoice_address REF invoice_address_type SCOPE IS bill_addressed);

CREATE OR REPLACE TYPE state_type AS OBJECT(
stat VARCHAR2(20),
country VARCHAR2(20));
/
show error

--creating object table named states
CREATE TABLE states OF state_type;

ALTER TABLE sites ADD
(state_ref REF state_type SCOPE IS states);


/*
DROP TABLE prospective_customers;
DROP TABLE adverts;
DROP TYPE social_media_table_type;
DROP TYPE social_media_varray_type;
DROP TYPE social_media_type;
PURGE RECYCLEBIN;
*/

--creating object type
CREATE OR REPLACE TYPE social_media_type AS OBJECT(
media_name VARCHAR2(25),
contact VARCHAR2(20));
/
SHOW ERRORS

--creating varray

CREATE OR REPLACE TYPE social_media_varray_type AS
VARRAY(50) OF social_media_type;
/
SHOW ERRORS

--creating table
CREATE TABLE prospective_customers(
prospective_customer_id NUMBER(6),
company_name VARCHAR2(50),
contact_name VARCHAR2(25),
social_media_available social_media_varray_type);

--creating object table type
CREATE OR REPLACE TYPE social_media_table_type AS TABLE OF social_media_type;
/
SHOW ERRORS

--creating table
CREATE TABLE adverts(
advert_id NUMBER(6),
advert_title VARCHAR2(50),
description VARCHAR2(25),
social_media_used social_media_table_type)
NESTED TABLE social_media_used STORE AS social_media_table;