ORACLE LAB QUESTION PAPER (REVISED)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	Given below r 3 tables and 40 queries. Each question paper will have 10 of the 40 queries and requires u to create the 3 tables and manipulate data onto them.
	
NOTE 1 : Since I don't have ORACLE, I have not tried out all the queries that I have listed below. Some of them did'nt work in MS Access, but I'm sure abt their syntax. So pls try them out in Oracle and mail me the right ones.

NOTE 2 : U can also try out the queries in MS ACCESS OR VIS DATA MANAGER itself
Only the operators such as date functions, (_,%) did not work.

NOTE 3 : Pls chk out the following queries in ORACLE (Query Nos. : 3,4,28,32,33,34,38)

Tables Creation
~~~~~~ ~~~~~~~~
-------------------------------------------------------
create table customer (cust_id varchar(3), fname varchar(20), lname varchar(20), area varchar(2), phone number(8));

create table movie (mv_no number(2), title varchar(25), type varchar(10), star varchar(25), price number(8,2));

create table invoice (inv_no varchar(3), mv_no number(2), cust_id varchar(3), issue_date date, return_date date);

INSERTING DATA
~~~~~~~~~ ~~~~

insert into customer values('&cust_id','&fname','&lname','&area',&phone);
e.g.  A01, jagan, kumar, DA, 243234

insert into movie values(&mv_no,'&title','&type','&star',&price);
e.g 1, hands of steel, action, sylvester stalone, 90

insert into invoice values ('&inv_no', &mv_no, '&cust_id', '&issue_date', '&return_date');
e.g I01, 2, 2, 3/2/01, 3/4/01


QUERIES
~~~~~~~

1. Find out the names of all customers
SELECT fname, lname FROM customer;

2. List various movie types available from the movie table
SELECT distinct type FROM movie;

3. Find the names of all cutomers having 'A' as their 2nd letter in their fnames
SELECT fname, lname FROM customer WHERE fname like '_A%';

4. Find out the lnames of all customers that begin with 'S' or 'J'
SELECT lname from cutomer where lname like 'S%' or lname like 'J%';

5. Find out the customers who stay in the area 'DA' or 'MU' or 'GH'
SELECT * from customer where area like 'DA' or area like 'MU' or area like 'GH';

6. Display the invoice table information for cust_id A01 and A02
SELECT * from invoice where cust_id like 'A01' or cust_id like 'A02';

7. Find the movies of type action and comedy
SELECT title, type from movie where type like 'action' or type like 'comedy';

8. Find the movies whose price is greater than 150 and less than or equal to 200
SELECT * from movie where price>150 and price<=200;

9. Print the names of all movies except horror movies
SELECT title from movie where type not like 'horror';

10. List the movies in the sorted order of their titles
SELECT title from movie order by title;

11. Divide the cost of the movie Homealone by the difference between its price & 100
UPDATE movie set price=price/(price-100) where title='Homealone';

12. Count the total no. of customers
SELECT count(*) from cust;

13. Calculate the total price of all the movies
SELECT sum(price) from movie;

14. Determine the max & min price of all the movies
SELECT max(price), min(price) from movie;

15. Count the number of movies, having price greater than or equal to 150.
SELECT count(mv_no) from movie where price>=150;

GROUP BY
~~~~~~~~

16. Print the type & average price of each movie type
SELECT type, avg(price) from movie group by type;

17. Find the no. of movies in each type
SELECT count(*) from movie group by type; 

18. Count separetly the no. of movies in comedy & horror types
SELECT type, count(*) from movie group by type having type = 'comedy' or type = 'horror';

19. Calculate the avg price for each type, that has a max price of 150
SELECT avg(price) from movie group by type having max(price) = 150;

-------------------------------------
20. Calculate avg price of all movies where the type is comedy or horror & the price is greater than or equal to 150
SELECT avg(price) from movie where type = 'comedy' or type='horror' and  price >=150;
-------------------------------------

JOINS 
~~~~~ 

21. Find out the movie number which has been issued to IVAN
SELECT i.mv_no from invoice i,customer c where c.cust_id = i.cust_id and c.fname = 'IVAN'; 

22. Find the names and movie numbers of all customers who have been issued a movie
SELECT c.fname, i.mv_no from customer c, invoice i where c.cust_id = i.cust_id;

23.Select the title, cust_id, mv_no, for all movies that are issued
SELECT m.title, i.cust_id, i.mv_no from movie m, invoice i where m.mv_no = i.mv_no;

24. Find out the titles and types of all movies that has been issued to  IVAN
SELECT m.title, m.type from movie m, customer c, invoice i where i.cust_id = c.cust_id and c.fname = 'IVAN' and i.mv_no = m.mv_no;

25. Find the customer names who had been issued the movie of type action
SELECT c.fname from customer c, invoice i, movie m where c.cust_id = i.cust_id and i.mv_no = m.mv_no and m.type = 'action';

SUB QUERIES
~~~ ~~~~~~~
26. Find out which customer has been issued movie no. 1
SELECT fname from customer where cust_id = (select cust_id from invoice where mv_no = 1); 

27. Find out the customer name and area with invoice no I02
SELECT fname, area from customer where cust_id = (select cust_id from invoice where inv_no='I02'); 

------------------------------------

28. Find the customer names and their phone nos, who had been issued movies before the month of august
SELECT c.fname, c.phone from customer c, invoice i where c.cust_id = i.cust_id and i.issue_date < '01-aug-2001';

29. Find the movie nos and movie names issued to all customers
SELECT i.mv_no, m.title from invoice i, movie m where m.mv_no = i.mv_no;

30. Find out if the movie starring Tom Cruise is issued to any customer and print the cust_id to whom it is issued.
SELECT i.cust_id from invoice i, movie m where m.star = 'tom cruise' and m.mv_no = i.mv_no;

31. Display tha movie no. and the day on which the customer were issued a movie
SELECT mv_no, issue_date from invoice;

32. Display the month in alphabets in which the customer are supposed to return the movies
SELECT cust_id, to_char(return_date,'fmMonth') from invoice;

33. Find the date 15 days after the current date
SELECT sysdate + 15 from dual;

34. Find the no. of dates elapsed between the current date & the return date of the movies for all customers
SELECT cust_id, (return_date-sysdate) from invoice;

35. Change the telephone no. of IVAN to 466389
UPDATE customer set phone = 466389 where fname = 'IVAN';

36. Change the issue_date of customer with id A01 to 28-feb-2001
UPDATE invoice set issue_date = '28-feb-2001' where cust_id = 'A01';

37. Change the price of the film "mi2" to Rs. 250
UPDATE movie set price=250 where title ='mi2';

38. Delete all records having return date before 5-mar-2001
DELETE FROM invoice where return_date < '5-mar-2001';

39. Display the return_date in the following format DD"th" of the Month, Year
SELECT To_char(return_date, 'DDth "of the" fmMonth, YYYY') from invoice; 

40. Increase the price of the movie by Rs.10 for all movies with price >=150
UPDATE movie set price = price +10 where price>=150;


				~~~~~~~~~~~~~~