Take-Home Exercise #5 Use the GPVC.accdb database to develop the following queries. Make sure to view...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 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
Expert Answer:
Answer rating: 100% (QA)
Solutions Step 1 The SQL query in the question involves the following concepts Tables The query joins three tables Rents Member and Catalog The Rents table contains information about which DVDs are cu... View the full answer
Related Book For 
Posted Date:
Students also viewed these programming questions
-
What two processes in meiosis I generate genetic variation among gametes? What other process produces genetic variation?
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
Allison is paid $1,520 per week. What is the amount of federal income tax withheld from Allisons paycheck under the following conditions? Use the percentage method table in the Appendix to this...
-
Suppose that instead of using 16 bits for the network part of a class B address originally, 20 bits had been used. How many class B networks would there have been?
-
Write a system of four equations whose solution gives estimates for the temperatures T1( ( ( ( (T4.
-
Derive Equation 3.47. 1 Um +. (3.47) G12 Gf12 Gm
-
Unlevered Beta Counts Accounting has a beta of 1.15. The tax rate is 40%, and Counts is financed with 20% debt. What is Counts unlevered beta?
-
Task 1: Create a JavaBean Coffee Bean with the following: - Data members: int numSugar; double price; String typeCoffee; Methods: Constructor Accessor Mutator Task 2: Create a HTML CoffeeOrder.html...
-
Using the Public MACRO BITCOIN scorecard spreadsheet (linked in its associated masterclass lesson - Long Term 32), create a COPY of it and perform a complete analysis for the date 22/2/2022....
-
Choosing to impose a $0.05 tax on plastic shopping bags insteadn of a $0.05 reward for bringing a shopping bag is an example of which type of policy? Question 45 options: classicalism ultimatum...
-
At the end of the current year, the company had total assets of $7,600. During the year, the company recorded sales of $3,900 and expenses of $2,200. Its board of directors declared $500 in dividends...
-
Analyze the strengths, weaknesses, opportunities, and threats relating to the issues of programmatic operations in homeland security. Provide references in APA format
-
In 20X2, Carey Hanlon provided tax advice in a letter to Beth Jackson, a tax-only client. For reasons other than the advice given, Jackson engages Riley Urban for tax work in 20X3. Hanlon had not...
-
Write the Strength, Weakness, Opportunities and Threats of an HR in any Corporate
-
What is the gross profit (GP) of the original bill? (Sale price less COGS = GP) Food: COGs is 30% therefore the remaining profit is 70% of the food cost (food cost X 0.7) Wine: COGs is 20% therefore...
-
A 22 KV, 3 phase star-connected turbo- alternator with a synchronous impedance of 1.4 2/phase is delivering 240 MW at unity p.f. to a 22 KV grid. If the excitation is increased by 25%, then the...
-
Determine by direct integration the values of x for the two volumes obtained by passing a vertical cutting plane through the given shape of Fig. 5.21. The cutting plane is parallel to the base of the...
-
Show how the tables you created for question 1.8 solve the problems you described in question 1.5.
-
What tool(s) can be used to create a script?
-
What are the three sources of data for BI systems?
-
Let \(A\) and \(B\) be disjoint events with \(P(A)=0.3\) and \(P(B)=0.45\). Determine the probabilities \(P(A \cup B), P(\overline{A \cup B}), P(\bar{A} \cup \bar{B})\), and \(P(\bar{A} \cap B)\).
-
Let \(P(A \cap \bar{B})=0.3\) and \(P(\bar{B})=0.6\). Determine \(P(A \cup B)\).
-
Check whether for 3 arbitrary random events \(A, B\), and \(C\) the following constellations of probabilities can be true: (1) \(P(A)=0.6, P(A \cap B)=0.2\), and \(P(A \cap \bar{B})=0.5\), (2)...
Study smarter with the SolutionInn App