Notes

--@E:18413707Week6week6.txt

--To check whether the sites table is already exit or not
SELECT * FROM sites;

--To check the structure of sites
DESC sites;

--using POOR method to query site_id and state_ref
SELECT site_id,state_ref
FROM sites;
WHERE site_id = 1;

SELECT site_id,state_ref
FROM sites;
WHERE site_id = 2;

column state_ref format A20
column site_id format A20

--using POOR method to query site_id and state_ref
SELECT site_id,state_ref
FROM sites;
WHERE site_id = 1;

SELECT site_id,state_ref
FROM sites;
WHERE site_id = 2;

--using BETTER method
SELECT DEREF(state_ref),site_id
FROM sites;

--using the BEST method
SELECT s.state_ref.state,s.state_ref.country,s.site_id
FROM sites s;


--Adding where clause for fast quering of required data
SELECT a.state_ref.state,a.state_ref.country,a.site_id
FROM sites a
WHERE a.state_ref.state ='BIHAR';

--using POOR method to query site_id and address
SELECT site_id,address
FROM sites;

--using BETTER method to query data where address street is '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';

--Query on instructor living in country UK
SELECT i.instructor_name, i.address.street STREET, i.address.city CITY, i.address.country COUNTRY
FROM instructors i
WHERE i.address.country = 'UK';


--SIMPLE QUERYING VARRAY COLUMN
SELECT site_id, classroom
FROM sites;

--to show the structure of classroom_type
desc classroom_type;

--For viewing id along with varray contents
SELECT s.site_id, c.room_number, c.capacity, c.description
FROM sites s, TABLE(s.classroom) c
WHERE s.site_id = 1;

--to view structure of invoices
desc invoices;

--simple query to view primary key and nested table column
SELECT invoice_number, invoice_item
FROM invoices;

--to view structure of invoice_item_type
desc invoice_item_type;

--QUERYING both primary key and nested table contents
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;

--QUERYING only nested table
SELECT VALUE(ii)
FROM THE(
SELECT invoice_item
FROM invoices
WHERE invoice_number = 10007) ii;