Notes

--SELECTING TICKET OF GIVEN PRICE
SELECT ticket_number, ticket_type, price
FROM tickets t
WHERE price IN (500, 1000, 1500);

--selecting staff first name starting from S
SELECT staff_f_name
FROM staff s
WHERE s.instructor_name LIKE 'S%';

--Ticket price in between given price range
SELECT ticket_number, ticket_type, price
FROM tickets t
WHERE price BETWEEN 500 AND 1500;

--greater than 201
SELECT COUNT(nature_id), location_id
FROM festivals
GROUP BY location_id
HAVING location_id>201
ORDER BY 1;


--To display all the UK cities where there are locations of staff(all value of both)
SELECT l.address.city, l.address.country
FROM locations l
WHERE l.address.country='UK'
UNION
SELECT s.address.city, s.address.country
FROM staff s
WHERE s.address.country='UK';


--To display all adddresses that are not in intersection tablename
SELECT a.city, a.country
FROM addresses a
MINUS
SELECT s.address.city, s.address.country
FROM staff s;



--This is testing for table 'locations'.(poor test)
SELECT location_id,address
FROM locations l;

--Best test method for table 'locations'.
SELECT l.site_id,l.address.street street,l.address.city city,l.address.country country
FROM locations l;

--Best test for table locations by only selecting Derby Road street
SELECT l.location_id,l.address.street street,l.address.city city,l.address.country country
FROM locations l
WHERE l.address.street = 'DERBY ROAD';

--To view primary and varray column
SELECT staff_id,contact FROM staff;

--To view only primary key and varray column
SELECT s.staff_id,c.contact_via,c.contact_type,c.contact_details
FROM staff s,
TABLE(s.contact) c+
WHERE staff_id =400;

--To view primary as well as nested column
SELECT staff_id,social_media FROM staff;

--To view structure of type in which nested table s is based on
DESC social_media_table_type;



--To view primary as well as all the content of nested column
SELECT s.staff_id, ...... ayena malai

--querying using inner and outer join
SELECT f.nature_id,f.festival_name,l.location_id,l.location_name
FROM festivals f
JOIN locations l
ON f.location_id=l.location_id;

--viewing the table structure
DESC tickets;
DESC locations;
DESC staff;

--querying to view all the students
SELECT s.staff_id s.staff_fname, s.staff_lname,fs.festival_staff_id
FROM staff s LEFT JOIN festival_name_staff fs
ON s.staff_id=fs.staff_id;