SUMMARY QUERIES USING THE GOLF CLUB DATABASE

You will again be using the Golf Club Database to create more advanced queries.

You will be working with the Golf Club Database. This database keeps track of the activities and membership information for an Exclusive Golf Club located here in Ohio. The database consists of the following tables:

The Membership table is a list of all members personal information and their membership status. Each member is identified by a unique Member ID.

The Status table lists the different possible membership status types available (Junior, Senior, or Regular) and the yearly dues associated with that status. Each status is identified by a unique Status ID.

The Payments table lists payments made by the members. Each payment is uniquely identified by a PaymentID. The Charges table lists charges incurred by the members including a field that identifies the description of the charge. Each charge is uniquely identified by a ChargeID.

The Prospects table is a listing of all prospects that current members have been trying to recruit, the date the prospect was contacted, and the Member ID of the current member who tried to recruit them. Each prospect is uniquely identified by a ProspectID.

The Charge Description table is a listing of the different types of charges a member can post to their account. Each description is uniquely identified by a DescriptionID.

The database has already been populated.

Set the primary keys, relationships, enforce referential integrity, cascade update, and cascade delete.

Query 1 Write a query to summarize by MemberID, the number of prospects for Lee Trevino or the number of prospects for Robert Johnson. List the MemberID, Member Last name, and their number of prospects.

Query 2 Write a query to summarize by MemberID, their average payment between May 9, 2017 and June 1, 2017. List the MemberID and their average payment.

Query 3 All members will receive a 10% discount on their total charges. Write a query to summarize by MemberID the Discounted charge (name the field Discounted Charge). List the MemberID, StatusID, members first name, last name, the total charges and the discounted charge. Format the new Discounted Charge field to Currency.

Query 4 Write a query to display the number of members who live in Ohio. List only the number of members.