Task 1: SQL Queries
(i) Display the full details for all members - the name details (given and family) should be shown in one column called 'Member Name' and the address details (street, city, state and postcode) in one column called 'Member Address'
(ii) Display the full details for all members who live in a particular postcode selected by the user at query execution time (this may be achieved using a substitution variable for the postcode). You should display the data in member number order. A substitution variable may be used in SQL Developer to prompt the user for a value at query execution time by using a place holder such as &myvalue.
select * from product where prod_no=&myvalue
or
select * from product where prod_name = '&myvalue'
(in the first example prod_no is numeric, in the second prod_name is character). The user entered value is then substituted for the placeholder and the query executed with this value.
Using a value of 3161
(iii) Display the full details for all movies in the movie table, which have the word 'Ring' in their title (the word may be present in any case).
(iv) Display the movie title, release date, running time and rating code for all movies which are currently available in stock and which have a running time less than 90 minutes, order the list such that the longest movie is first.
(v) Display all movie copies which are currently available for rent - display the movie title, the copy id, the movie release date and the rating code. This display should be in copy id order within title order.

(vi) Report the average number of rentals per member to two decimal places.

(vii) Provide a list of all movie copies which have been returned in the last 30 days. Display the movie title, copy id, member number of the member who returned the DVD and the return date.

(viii) For all movies currently in the system display details about the current stock and the disposed of stock:
• for each movie which has copies currently in stock: display the string 'Currently in Stock', the movie id, movie title, the total purchase cost of the in stock copies, the number of in stock copies and the number of times these in stock copies of this movie have been borrowed, and
• for each movie which has copies which have been disposed of: display the string 'Disposed of Stock', the movie id, movie title, the total cost of the disposed of copies, the number of disposed of copies and the number of times these disposed of copies of this movie have been borrowed.
The results from this listing should be displayed in a single result output.

(ix) For all genres in the genre table, list the number of movie titles in each genre currently in the Movie table, show the genre id, genre description and the count of titles in this classification.

(x) A list of all movies which are overdue showing the member name details, the members phone number, the movie title, copy id and the number of days late the movie currently is. This display should be ordered with the most overdue movie at the top of the list.

(xi) For a given member, identified by a member number and selected by the user, display the full details of all rentals that this member has made (rental date, return date, days late, copy id and movie title). If a movie has not been returned at this point in time it should be listed in the days late column as 'Not Returned'.
Using a value of 102:
(xii) The management would like to reward their most active customers by asking them to special limited screenings. To assist the company in being able to identify these active customers create a view of the member details (member number, member name as one attribute, phone contact and total number of rentals) of those members with a total number of rentals within 20% (inclusive) of the members(s) with the maximum number of rentals. The members should be listed in descending order of the number of rentals they have made. For example if the maximum number of rentals made by any customer is 300, this report will show those customers who have a total number of rentals between 240 and 300 inclusive.
After generating the view, show the contents of the view.
Task 2: PL/SQL
This task should only be carried out after you have completed task 1. Testing of your code for this task will make changes to the data in the tables.
(i) Write a trigger, which will implement an update cascade referential integrity rule between MOVIE and COPY
(ii) Every 4 rentals of a particular DVD the store clean and polish the DVD before a customer takes the copy (ie. rental number 4, 8, 12 etc). Write a trigger, which will ensure that a message (using
DBMS_OUTPUT.PUT_LINE) is displayed if a COPY about to be borrowed (ie. rented) requires cleaning.