Notes

-- Week5 onwards

-- 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 REVOLUTION', 'DAVID',
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')));

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


INSERT INTO adverts(advert_id, social_media_used)
VALUES(90000,
social_media_table_type(
social_media_type('THE HIMALAYAN TIMES', 014432928),
social_media_type('THE REPUBLIC', 014489283),
social_media_type('KANTIPUR', 014423879)));

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

-- inserting 1 complete row into sites
INSERT INTO sites(site_id, address, classroom, location, state_ref)
SELECT 5, 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;