SQL Oracle Help



I need this done ASAP





Task 2. SQL Queries [10 marks each]


Create a script file (Task2XX.sql, where XX are your initials in upper case letters) to combine the SQL queries for each problem below, in order. You will submit both your completed script file and its combined output file (Task2XX.out) to D2L.


  1. Write a single SQL query that computes, for each contact, the average and sum of the rental transactions for rentals that were made in June 2011 (they could have possibly been returned in July or later). Include the overall grand total and overall transaction average in the result. You may not use a UNION query expression for your answer. For each contact, output:

    1. Contact ID

    2. Contact last name

    3. Contact first name

    4. Sum of the contact’s transactions for June 2011, in the format $9999.99

    5. Average of the contact’s transactions for June 2011, in the format $99.99

    6. For the grand total, substitute the literal string ‘June 2011 totals’ for the contact’s last name.

    7. Output the list of contacts ordered by first name within last name.




          1. Use COALESCE() to substitute the ‘June 2011 totals’ string in the output

          2. Use GROUP BY ROLLUP

          3. Use a derived table in the FROM clause

          4. You will find the EXTRACT() and TO_CHAR() functions useful



  1. Using the Video Store schema, write a query to produce a list of items that have never been rented. (Note: there is currently only one, with the Item ID of 1000). Rewrite your query in at least three different ways – that are semantically equivalent – that will return the identical result. Your rewrites may not be trivial ones – for example, reordering tables in the FROM clause or reordering predicates in the WHERE clause, or reversing or negating comparison operations.



  1. Using the Video Store schema, write a query to produce a report that lists the number of times each of the Star Wars movies were rented in October, 2011. Include the total rental amount for each of the films. Consider only those movies in Wide Screen format on DVD. Output the ID of each film, its title, the number of times it was rented in October 2011 (use the column heading “Rentals”) and the total rental amount for each film, using proper money formatting in dollars and cents – use the column header “Total Amt” for this column. Order your result by film title.




  1. Using the HR schema, write a query that lists all of the departments located in Seattle, Washington, along with their managers and the start date for each manager. If a department does not have a manager, output the value “Unknown”. Output the manager’s start date using the format “YYYY-MM-DD”. If the start date for that manager is unknown, also use the literal “Unknown”. Use appropriate headings for each column in the result. Order the list by department name.




Task 3. Supplementary task. [5 marks]


You have come into possession of a vintage VHS tape (on two cassettes) of the film “Lawrence of Arabia”. Add the tape to the Item table in the Video Store database using an INSERT statement that contains the necessary SELECT subquery to determine the proper item type code for this VHS double-set. COMMIT the change.


Once the film has been added to the database, re-run your queries from Task 2, Item 2. Ensure that your queries find both the new “Lawrence of Arabia” film, and the “Pirates of the Caribbean” film, both of which have yet to be rented.


After your query executes, delete the “Lawrence of Arabia” film using a DELETE statement, and COMMIT the deletion as part of your SQL script.


Create a script file (Task3ExtraXX.sql) that creates the new item, answers the queries, and deletes the item. Submit to D2L both your completed script file and output file (Task3ExtraXX.lst).