Notes

--describe the table sites
DESC sites;

--testing for table sites
SELECT site_id,state_ref
FROM sites;

--Better test method for table sites
SELECT DEREF(state_ref),site_id
FROM sites s;

--Best test method for table sites
SELECT s.site_id,s.state_ref.state state,s.state_ref.country country
FROM sites s;

--Best test method for table sites and select only bihar
SELECT s.site_id,s.state_ref.state state,s.state_ref.country country
FROM sites s
WHERE s.state_ref.state = 'Bihar' ;

--to view the structure of table
DESC sites;

--Poor test for table sites
SELECT site_id,address
FROM sites;

--to view the structure of datatype address_type
DESC address_type;

--Best test for table sites
SELECT s.site_id,s.address.street street,s.address.city city,s.address.country country
FROM sites s;

-- column width
COLUMN street FORMAT A20;
COLUMN city FORMAT A20;
COLUMN country FORMAT A20;
COLUMN description FORMAT A20;
COLUMN course_title FORMAT A10;
COLUMN student_surname FORMAT A20;

--Best test for table sites only selecting street 343 elm street
SELECT s.site_id,s.address.street street,s.address.city city,s.address.country country
FROM sites s
WHERE s.address.street = '343 ELM STREET';

--Structure of table sites and varray classroom
DESC sites;
DESC classroom_type;

--to view primary and varray column
SELECT site_id,classroom FROM sites;

--to view only primary key and varray column
SELECT s.site_id,c.room_number,c.capacity,c.description
FROM sites s,
TABLE(s.classroom) c
WHERE site_id =1;

--structure of invoice table
DESC invoices;

--to view primary and nested column
SELECT invoice_number,invoice_item FROM invoices;

--structure of type which nested table s based on
DESC invoice_item_table_type;

--view primary and all the content of nested column
SELECT i.invoice_number,ii.student_surname,ii.course_title,ii.start_date,ii.end_date,ii.amount
FROM invoices i,
TABLE(i.invoice_item) ii;

--VIEW ONLY NESTED TABLE INFORMATION
SELECT VALUE(ii)
FROM THE(
SELECT invoice_item
FROM invoices
WHERE invoice_number = 10007) ii;