Using MySQL Workbench SELECT commands answer the questions listed below using th

Question

Using MySQL Workbench SELECT commands answer the questions listed below using the Tiny Video schema used in your previous assignment. Submit via the D2L drop box the final SQL script for each problem. All questions are worth 2 points each.

1. Write a query to display the columns listed below. The query should list each customer once, whether they have rented a movie or not. Where the customer has rented a movie it should show only the first rental date. The output should be sorted by membership number.

2. Write a query to display the columns listed below. The query should list each customer who has not rented a move. The output should be sorted by membership number.

3. Write a query to display the columns listed below. The query should list each customer where the total detail rental fee is greater than twice the overall average detail rental fee. The output should be sorted by membership number. (Hint: A HAVING clause will be needed)

4. Write a query to display the columns listed below. The query should list each customer in which the video rental is overdue. The Days_Overdue column should calculate the number of days overdue based upon the due date and return date. The output should be sorted by membership number and then by the detail due date.

5. Write a query to display the columns listed below. For each customer the query should show the current system date, the current day (when you do the problem the date and day will be different), the number of characters in the member last name, the last date the customer rented a video and how many total videos the person rented.

HERE IS THE TINY VIDEO SCHEMA FOR MYSQL WORKBENCH

membership id 102 103 104 105 106 107 108 109 110 first name last_name Tami Curt Jamal Iva Miranda First_Rental _Date Dason 2009-03-02 Knight Melendez 2009-03-02 Mccdain Parks Elliott 2009-03-01 2009-03-01 2009-03-02 Mattie Clint Lewis Stacy Luis Minnie Ochoa Rosales 2009-03-02 Mann Trujilo Gonzales 2009-03-02 112 113

Explanation / Answer

Please post the query seperately. Let me know if you have any doubt.

1:
SELECT membership_id, first_name, last_name, First_Rental_Date
FROM Table_name
ORDER BY membership_id;

2:
SELECT membership_id, first_name, last_name
FROM Table_name
WHERE First_Rental_Date IS NULL
ORDER BY membership_id;

3:
SELECT membership_id, first_name, last_name, AVG(rental_fee) as totalAverage
FROM Table_name
GROUP BY membership_id
HAVING Rental_fee > (2* totalAverage)
ORDER BY membership_id;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.