I NEED HELP WITH MY SQL ASSIGNMENT . MY INITIALS ARE MA
I need this done ASAP
I HAVE ALREADY CREATED THE TABLES WITH PRIMARY KEYS AND WHAT ELSE. here is what i need done.
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.
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:
Contact last name
Contact first name
Sum of the contactâ€™s transactions for June 2011, in the format $9999.99
Average of the contactâ€™s transactions for June 2011, in the format $99.99
For the grand total, substitute the literal string â€˜June 2011 totalsâ€™ for the contactâ€™s last name.
Output the list of contacts ordered by first name within last name.
Use COALESCE() to substitute the â€˜June 2011 totalsâ€™ string in the output
Use GROUP BY ROLLUP
Use a derived table in the FROM clause
You will find the EXTRACT() and TO_CHAR() functions useful
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.
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.
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).
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!Use Discount Code “Newclient” for a 15% Discount!NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.