You are working for a business that owns two DVD rental stores. Both stores use software to manage the DVD rentals, but the software has no reporting capabilities. The owner wants to better understand various aspects of his business, and has asked you a few questions. In order to answer his questions, you will design and execute a few queries on the database of the DVD rental software that is used by the stores (there is one database for both stores). You can view the database schema at the following link: http://www.postgresqltutorial.com/postgresql-sample-database/ This page also contains instructions to download the full database and restore it in a PostgreSQL instance that you will set up.
Questions
- Which customer has made the most rentals at store 2?
- A customer tried to rent “Image Princess” from store 1 on 29/07/2005 at 3pm but it was soldout. Would he be able to rent it from store 2 if he had tried?
- How many customers are active at any given month per year (e.g. …, Jun 2005, Jul 2005,...., Jun 2006 etc)? We define active as performing at least one rental during that month.
- Which film category is the most popular among our customers?
- Which film is the most popular in category “Sports”?