------------------------------------------------------------------------
-- John David Ayers ----------------------------------------------------
-- November 13, 2009 ---------------------------------------------------
-- Homework 7 ----------------------------------------------------------
------------------------------------------------------------------------

--ALL COLUMNS MUST HAVE APPROPRIATE, USER FRIENDLY COLUMN NAMES


--1
/*
Display the first and last name (in one column) of all agents. Also display
a column that will contain the number of engagements the agent is credited with
(the number of rows in the Engagement table containing the corresponding
agentID). Label the second column Number of Engagements
*/

SELECT AgtFirstName + ' ' + AgtLastName AS [Agent Name],
(SELECT SUM(EngagementNumber) AS [Engagements]
FROM Engagements
WHERE Agents.AgentID = Engagements.AgentID)
FROM Agents;

--2
/*
Display the stage name of all entertainers and the number of styles the
entertainer can perform. Label the columns Enteratiner, and Number of Styles.
Sort them in descending order by the number of styles, within each number,
sort alphabetically by the stage name.
*/

SELECT 	EntStageName AS [Entertainers],
(SELECT Count(StyleID)
FROM Entertainer_Styles
WHERE Entertainer_Styles.EntertainerID = Entertainers.EntertainerID) AS [Styles Known]
FROM Entertainers
ORDER BY [Styles Known] DESC;

--3
/*
List  the first and last name of each agent and the sum of all the commissions
received from his engagements. Label the second column Earnings. Sort the rows
to display the top earning agent first.

Hint: Use a SELECT statement to sum the ContractPrice, then multiply the answer
by the agent's commission rate.
*/

SELECT AgtFirstName + ' ' + AgtLastName AS [Agent  Name],
(SELECT SUM(ContractPrice)*CommissionRate
FROM Engagements
WHERE Agents.AgentID = Engagements.AgentID) AS [Earnings]
FROM Agents
ORDER BY Earnings DESC

--4
/*
Modify number 3 so that any agent with no earnings will display 0 for
earnings instead of NULL.
*/

SELECT AgtFirstName + ' ' + AgtLastName AS [Agent  Name],
(SELECT ISNULL(SUM(ContractPrice)*CommissionRate, '0')
FROM Engagements
WHERE Agents.AgentID = Engagements.AgentID) AS [Earnings]
FROM Agents
ORDER BY Earnings DESC

--5
/*
Display the stage name for each entertainer and the number of members in that
group. Label the columns Entertainer and Number of Members. Sort the data so
that the largest groups are listed first.
*/

SELECT EntStageName AS [Entertainers],
(Select Count(EntertainerID)
FROM Entertainer_Members
WHERE Entertainer_Members.EntertainerID = Entertainers.EntertainerID) AS [Members In Group]
FROM Entertainers
ORDER BY [Members In Group] DESC;

--6
/*
Create a view named EntertainerStatistics that will display the stage name of
the entertainer and the number of engagements they have had. Label the first
column Entertainer and the second column Performances.
*/

CREATE VIEW EntertainerStatistics
AS
SELECT EntStageName AS [Entertainer],
(Select Count(EngagementNumber)
FROM Engagements
WHERE Engagements.EntertainerID = Entertainers.EntertainerID) AS [Performances]
FROM Entertainers

--7
/*
The boss wants to know which entertainers are requested the most. Use the
EntertainerStatistics view to create a query that will list the stage name
(Entertainer) of all entertainers who have the same number of engagements
(Performances) as the largest number in the view.
*/

SELECT Entertainer
FROM EntertainerStatistics
WHERE Performances=(SELECT MAX(Performances)
FROM EntertainerStatistics );

--8
/*
List the agent first name, agent last name, and the commission rate for all
agents who have the lowest commission rate.
*/

SELECT AgtFirstName + ' ' + AgtLastName AS [Agent Name],
(SELECT MIN(CommissionRate)
FROM Agents) AS [Commission Rate]
FROM Agents;

--9
/*
Create a query that will display the stage name of the entertainers, the
number of musical styles they perform, and the number of engagements they
have had. Label the columns Entertainer, Number of Styles and Number of
Engagements.
*/

SELECT EntStageName AS [Entertainer],
(SELECT Count(StyleID)
FROM Entertainer_Styles
WHERE Entertainer_Styles.EntertainerID = Entertainers.EntertainerID) AS [Styles Known],
(Select Count(EngagementNumber)
FROM Engagements
WHERE Engagements.EntertainerID = Entertainers.EntertainerID) AS [Performances]
FROM Entertainers;


--10
/*
Display the first and last name of each customer(in one column) and the number
of musical_preferences they have. Label the first column Customers and the
second column Number of Preferred Styles. Sort the data by the customer's
last name.
*/

SELECT CustFirstName + ' ' + CustLastName AS [Customers],
(SELECT Count(StyleID)
FROM Musical_Preferences
WHERE Musical_Preferences.CustomerID = Customers.CustomerID) AS [Number of Preferred Styles]
FROM Customers
ORDER BY CustLastName;


--Extra credit (3 points)
/*
The boss wants to know which entertainers have been requested by the greatest
number of different customers. Display the entertainer stage name as
Entertainer and the number of different customers they have performed for as
Distinct Requests. This can be done by using two subqueries (one will be a
column subquery to count the number of requests - it will contain a subquery in
the FROM clause that will retrieve the number of distinct EntertainerID,
CustomerID combinations.  The SELECT statement in the FROM will require a table
alias to be used in the WHERE clause. Sort the data in descending order by the
number of distinct requests.

Hint: Start by creating the subquery that will retrieve the
number of distinct EntertainerID, CustomerID combinations. Seeing what you have
may help with the rest. Also - there may be another way to solve the problem!
*/

--Extra credit (2 points)
/*
Modify number 4 to use the STR function to display everything in the Earnings
column with two decimal places.
*/
