What are the title and special features for films

Assignment Help Database Management System

Reference no: EM13953414

Assignment: SQL

Getting Data out of the Database

For this assignment, you will be working with the same movie rental database as the in-class exercise (MovieDB). The schema is also the same, so you should use the one provided for the in-class exercise as a guide.

You will construct a single SQL queryusing MySQL Workbench that provides the answer to each of the questions below. You should also provide the answer to the question. Some of the questions can be answered by querying one table; others will require joining multiple tables to get the answer.

Also pay attention to what it says to Display. This is how your results should appear. For example:

Display: name and special features

Means that your SQL query should return only two columns of data: name and special features

(Note that this always isn't a field name, sometimes it is the result of a SUM(), COUNT(), or AVG()!)

Guidelines

• You must submit your answers electronically in a single Word document. You can copy and paste the SQL query and the results from SQL Workbench.

• You must include your name at the top of the document.

• Your answers should be emailed, as an attachment, from your Temple email account to the following OWLbox folder:[email protected]

• Please include your last name in the file name.

• The email must be sent by the start of class the day the assignment is due.

If you do not follow these instructions, your assignment will be counted late.

Evaluation

Your submission will be graded using two factors:

• A correctly formed SQL query that answers the specific question asked (no extra rows or columns).

• Providing the correct answer to the question (the answer the query provides).

Questions

1. What are the title and special features for films rated PG-13 and longer than 180 minutes?

Display: name and special features

2. What are the three most popular firstnames among the actors in the database?

Display: first name and the how many times that name appears in the database

3. What is the average rental cost (rate) for eachmovie rating (i.e., G, PG, R, NC-17)?

Display: rating and average rental rate

4. How many R movies mention ‘drama' in their description?

(Hint: use WHERE...LIKE with a wildcard. Remember, %dog% will match any value containing "dog," %dog will any value ending in "dog," and dog% will match any value beginning in "dog.")

Display: number of movies

5. Which store (identified by store_id) has the most movies in their inventory?

(Hint: List all stores. The list is short!)

Display: store_id, how many movies

6. Who were the stars of the movie "Operation Operation"?

Display: first name and last name

7. Which actors with a last name beginning with the letter "J" have starred in movies in theFrench language?

(Two hints: (1) Be sure to reference the language name "French" in your query and (2) Don't forget previous hints!)

Display: first name and last name

8. Which are the languages of the movies that Patricia Johnson has rented and how many has she rented in each of those languages?

(Hint: Assume no ties, just limit your results to the first three rows.)

Display: language name and how many movie rentals

9. What is the shortestR-rated movie in French and how long is it?

(Two hints: (1) Use a subselect statement with the MIN() function to return only the shortest movie and (2) be sure to reference the language name "French" in your query.)

Display: movie title and movie length

10. What was the shortest movie starring PenelopeGuiness and how long is it?

(Hint: Use a subselect statement with the MIN() function to return only the shortestmovie.)

Display: movie title and movie length.

Reference no: EM13953414

Questions Cloud

Calculate a conservative sample size estimate : Suppose that we want to conduct a study to estimate and confirm this rate to identify nodal metastases among breast cancer patients because previous estimates were all based on rather small samples. How many patients are needed to confirm this 90%..

Dextra computing sells merchandise : Dextra Computing sells merchandise for $17,000 cash on September 30 cost of merchandise is $11,900.

Valuation of inventory and measurement of income : Explain the relationship between the valuation of inventory and the measurement of income. Inventory costs ultimately become the cost of goods sold reflected in the income statement.

Preparation of its cash budget : A company is formulating its plans for the coming year, including the preparation of its cash budget.

What are the title and special features for films : What are the title and special features for films rated PG-13 and longer than 180 minutes? What are the three most popular firstnames among the actors in the database?

Wholesalers and retailers account for cost of goods sold : Show that you understand how wholesalers and retailers account for cost of goods sold. The cost of goods sold represents goods sold, as opposed to the inventory purchased during the year. Cost of goods sold is matched with the sales of the period.

Retailers account for sales of merchandise : Explain how wholesalers and retailers account for sales of merchandise. Net sales represents sales less deductions for discounts and merchandise returned (returns and allowances) and is a key figure on the income statement.

Test for the independence between the disease and exposure : Find a 95% confidence interval for the odds ratio measuring the strength of the relationship between the disease and the exposure. Test for the independence between the disease and the exposure.

Determining the definition of depreciation : Ignoring the effect of taxes, would you recommend the purchase or the lease? Why or why not? Referring to the definition of depreciation, what appropriate useful life should be used for the equipment and software?

User Account

All Pages