Notes

-- describing the structure of prospective_customer and social_media_type
-- DESC prospective_customer;
-- DESC social_media_type;

-- inserting into relational table 'prospective_customers' using varray type 'social_media_varray_type'
INSERT INTO prospective_customers(prospective_customer_id, company_name, contact_name, social_media_available)
VALUES (80000, 'IT REVOLUTION', 'DAVID',
social_media_varray_type(
social_media_type('FACEBOOK', '098 382 890'),
social_media_type('TWITTER', '984 342 234'),
social_media_type('LINKED IN', '423 677 345')));

INSERT INTO prospective_customers(prospective_customer_id, company_name, contact_name, social_media_available)
VALUES (80001, 'IT EVOLUTION', 'JOHN',
social_media_varray_type(
social_media_type('BBC', '4305 4385 4350'),
social_media_type('VOA', '2345 0438 1334'),
social_media_type('CNN', '0847 4502 4500')));


-- formatting 'prospective_customers' table
COLUMN prospective_customer_id FORMAT 999999;
COLUMN company_name FORMAT A15;
COLUMN contact_name FORMAT A15;
COLUMN social_media_available FORMAT A170;


-- to view 'prospective_customers' table to check whether data has been inserted correctly or not
-- SELECT * FROM prospective_customers;



-- describing structure of adverts and social_media_table_type
-- DESC adverts;
-- DESC social_media_table_type;

-- inserting into adverts table
INSERT INTO adverts(advert_id, advert_title, description, social_media_used)
VALUES(90000, 'NEWSPAPER', 'SOCIAL AWARENESS',
social_media_table_type(
social_media_type('THE HIMALAYAN TIMES', 014432928),
social_media_type('THE REPUBLIC', 014489283),
social_media_type('KANTIPUR', 014423879)));


-- formatting 'adverts' table
COLUMN advert_id FORMAT 999999;
COLUMN advert_title FORMAT A15;
COLUMN description FORMAT A20;
COLUMN social_media_used FORMAT A170;

-- to view 'adverts' table to check whether data has been inserted correctly or not
-- SELECT * FROM adverts;

-- describing sites, address_type, classroom_type, states
-- DESC sites;
-- DESC address_type;
-- DESC classroom_varray_type;
-- DESC states;

-- inserting 1 complete row into sites
INSERT INTO sites(site_id, address, classroom, location, state_ref)
SELECT 13, address_type('TEEN GHUMTI', 'POKHARA', 'NEPAL'),
classroom_varray_type(
classroom_type('901', 50, 'LUXURY'),
classroom_type('902', 100, 'PLAYGROUND'),
classroom_type('903', 200, 'MEETING HALL')),
'POKHARA', REF(s)
FROM states s
WHERE stat = 'VICTORIA';

-- to check whether data has been inserted correctly or not
-- SELECT * FROM sites;


-- inserting sites table without 'classroom' column
INSERT INTO sites(site_id, address, location, state_ref)
SELECT 14, address_type('BOUDHA', 'KATHMANDU', 'NEPAL'), 'KATHMANDU', REF(S)
FROM states s
WHERE stat = 'QUEENSLAND';

-- updating sites table with site_id = 6 to insert 'classroom' column
UPDATE sites s SET s.classroom = (
classroom_varray_type(
classroom_type('111', 5, 'IT'),
classroom_type('222', 15, 'DOCTOR'),
classroom_type('333', 20, 'BUSINESS')))
WHERE s.site_id = 14;

-- to check whether data has been inserted correctly or not
-- SELECT * FROM sites;

COMMIT;