Notes

/*
UN ID = 18413710
WEEK - 1
ACTIVITY - 1
DATE- 10/2/2018
*/

--@D:18413710week4.txt

--inserting into object_table named bill_address
INSERT INTO bill_address (street,city,country)
VALUES ('54 FESTIVE ROAD','NORTHAMPTON','UK');

INSERT INTO bill_address (street,city,country)
VALUES ('30 ENGLISH STREET','BEDFORD','UK');

-- inserting into the relational table with a ref to an object_table named bill_address

INSERT INTO customers (customer_id,customer_name,invoice_address)
SELECT 1,'MANDY MORRELL', REF(a)
FROM bill_address a
WHERE street = '54 FESTIVE ROAD';

SELECT * from customers;

-- inserting into states table first
INSERT INTO states (state,country)
VALUES ('BIHAR','INDIA');

-- updating sites table using states table
UPDATE sites s
SET s.state_ref = (
SELECT REF (a)
FROM states a
WHERE a.state = 'BIHAR')
WHERE s.site_id =1;

-- dropping customer table
DROP TABLE customers;

-- recreating customers table using object column
CREATE TABLE customers(
customer_id NUMBER(6),
customer_name VARCHAR2(25),
invoice_address invoice_address_type);

-- inserting into customers table

INSERT INTO customers
VALUES (2,'CAROLE MORRELL',invoice_address_type('1 MY WAY','LUTON','UK'));

-- inserting more value into states table
INSERT INTO states
VALUES ('ALBERTA','CANADA');

INSERT INTO states
VALUES ('VICTORIA','AUSTRALIA');

INSERT INTO states
VALUES ('QUEENSLAND','AUSTRALIA');

INSERT INTO states
VALUES ('DEHLI','INDIA');

-- inserting into sites
INSERT INTO sites(site_id,address,state_ref)
SELECT 9,address_type('1 MY WAY','QUEENSLAND','AUSTRALIA'), REF (a)
FROM states a
WHERE state = 'QUEENSLAND';