Create a Schema, database and applications to support a GMC database system
The following database queries are required to be displayed on screen (i.e. produce the SQL code only, no form or report). All queries must be completed with each student from the pair completing 3 out of 6 queries. Please list the queries implemented by each group member in the Performa so that final marks can be adjusted if necessary. If you opt to work alone then you must complete all 6 queries alone.
A1. A list of all stored titles, showing type of music and the number of all CDs within each type (i.e. Classic 50, Jazz 126, etc.).
A2. For any given member number, identify his/her address, method of purchase, and all CDs they have bought since a given date. This application is to run on-line (i.e. produce the output on the screen). The input parameter (the member unique number) should be input by the user at run-time.
A3. List the member’s full name and address, whose memberships are due for renewal. The calculation of the due date should be based on last year renewal date and the date of running the query (i.e. today’s date).
A4. For any given member, give the total amount he/she spent on orders since he/she joined the club. The input parameter (the member unique number) should be input by the user at run-time.
A5. For any given artist (e.g. singer), list all albums produced by that artist which is available in GMC stock. The name of the artist should be input by the user at run-time. Assume Artist name is unique within the system.
A6. List the most popular Artist’s details in terms of number of sales. Group this output in terms of member gender.