Take-Home Exercise #5 Use the GPVC.accdb database to develop the following queries. Make sure to view the SQL version of your QBE query to become familiar with SQL for your upcoming exam. Relationships Catalog Catalogi Title Rating Charge Deleted Supplier Supplier SupplierName ContactName ContactTitle Address City Region PostalCode Country Phone Fax HomePage Deleted BI 81 kan DVD VideoTapelD CatalogID Copy Supplier PurchaseDate PurchasePrice Deleted Query1 MemberID. MemberName - CatalogID. 12 Patricio Simpson 12 Patricio Simpson 12 Patricio Simpson 12 Patricio Simpson 18 Rents Rent ID Member D VideoTapelD DateOut Datein Fee Query1. List dvd's currently out with member with MemberID = 12 as shown below. Sort output by Date Out and then by Title. Your query's output should look the same to earn full credit. Title 205 Shall We Dance? [WS] (2004) 650 Harry Potter 5 666 August Rush 119 James Clavell's Shogun-Disc 1 (1980) Member Hint: If Datein Is Null, then the DVD copy is currently out with a member. Memberi MemberName Address City Region PostalCode Country Phone Fax MMonth MYear Active DateOut 3/5/2010 3/7/2010 3/10/2010 3/10/2010 1 Query2. List the number of rental transactions and the total of charges received for every month of every year. Make sure your query output's column names match to earn full credit. Hint: Year([DateOut]) will give the year part of the date. Month([DateOut]) will give the month part of the date. Query2 Year 2008 2008 2008 2008 2008 2008 2008 2008 2008 2009 2009 2009 2009 2009 2009 2009 2009 2009 2009 2009 2009 2010 2010 2010 Month 4 JOSA 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 2123 Number of Transactions. Total Revenue 4 $57.00 $30.00 $105.00 $141.00 3 10 5 6 7 14 11 10 14 19 13 17 16 15 22 25 23 32 44 40 61 211 $75.00 $108.00 $162.00 $261.00 $180.00 $153.00 $282.00 $315.00 $192.00 $282.00 $297.00 $180.00 $393.00 $366.00 $396.00 $444.00 $612.00 $549.00 $984.00 $357.00 Query 3. List Member#, Name, LatestRentalDate as shown below for members whose name has "an" in it. Hint: LIKE operator can be used to match the desired pattern using the wild card *: LIKE ***** Note: The grouping aggregate function MAX returns the maximum value while LAST simply returns the last value based on the current sort order. Query3 MemberID MemberName 1 Maria Anders 2 Ana Trujillo 3 Antonio Moreno 6 Hanna Moos 9 Laurence Lebihan 13 Francisco Chang 14 Yang Wang 19 Ann Devon 20 Roland Mendel 23 Martine Ranc 25 Peter Franken 31 Andr Fonseca 33 Manuel Pereira 40 Daniel Tonini 41 Annette Roulet 42 Yoshi Tannamuri 48 Fran Wilson 49 Giovanni Rovelli 51 Jean Fresnire 52 Alexander Feuer 67 Janete Limeira 69 Alejandra Camino 80 Miguel Angel Paolino 31 Anabela Domingues Latest Rental Date 3/9/2010 2/25/2010 3/11/2010 3/10/2010 3/10/2010 3/11/2010 3/11/2010 3/7/2010 3/7/2010 3/11/2010 3/11/2010 3/5/2010 3/11/2010 3/11/2010 3/6/2010 3/10/2010 3/10/2010 3/10/2010 3/8/2010 3/10/2010 3/11/2010 3/8/2010 3/9/2010 3/6/2010 Query4. For members in Spain, list MemberID, MemberName, CatalogID, Copy#, NumberOfDaysOut as shown below. That is, how many days (i.e., [DateIn] - [DateOut]) the dvd was out with the member? Sort the output by MemberID, CatalogID, and Copy#. Query MemberID. MemberName & Martin Sommer & Martin Sommer 8 Martin Sommer 22 Diego Roel 22 Diego Roel 22 Diego Roel 29 Eduardo Saavedra 29 Eduardo Saavedra 30 Jos Pedro Freyre 30 Jose Pedro Freyre 30 Jos Pedro Freyre 30 Jos Pedro Freyre 30 Jos Pedro Freyre 30 Jos Pedro Freyre 30 Jos Pedro Freyre 30 Jos Pedro Freyre 30 Jos Pedro Freyre 69 Alejandra Camino Now! CatalogID Querys CatalogID 58 Closer (WS) (2004) 104 Happenstance (2000) 678 Across the Universe 6 24: Season 3 - Disc 6 428 Our Mutual Friend 613 Dixie Chicks Shut up and Sing 82 Fahrenheit 9/11 (2004) 345 She's the Man 142 Longest Yard (2005) 216 Star Wars Trilogy: Return of the Jedi [WS] (2004) 323 A Good Woman 392 Jacket, The 400 Mirror, The 416 Start Wars II 508 Looking For Kitty 511 Zoom 650 Harry Potter 5 677 3:10 To Yuma [Now! Title 566 August Rush 561 The Good Shepherd 677 3:10 To Yuma 581 A Man in Our House 84 Fever Pitch [WS] (2005) Title Copy - Number of Days Out. 2 2 3 2 1 3 1 2 4 3 2 1 1 3 1 4 Query5. List the 5 most popular titles. That is, the movie Title most rented. Hint: If you sort in descending order of NumberOfTimesRented, then changing the SELECT in SQL view of the query from just SELECT to SELECT TOP 5 does the job! 1 5 Number of Times Rented. 10 8 13 11 10 8 8