/*
---------------------------------------------------------------------
Programmer:		John David Ayers
Course:			CSCI 2030 A70
Date:			December 3, 2009
---------------------------------------------------------------------
Purpose:		HOMEWORK
---------------------------------------------------------------------
*/


--1
/*
Display the name of each entertainer (the stage name) and the number of
engagements thay have had (count the number of rows). Only display entertainers
who have had an engagement. Label the second column Number of Engagements.
Sort the data in descending order by the number of engagements.
*/

SELECT EntStageName, COUNT(EngagementNumber) AS [Number of Engagements]
FROM Entertainers INNER JOIN Engagements ON Entertainers.EntertainerID = Engagements.EntertainerID
GROUP BY EntStageName
ORDER BY 2 DESC;


--2
/*
Modify Number #1 so that all entertainers are displayed, even those with no
engagements. Notice the number of engagements for Katherine Ehrlich. Now
switch from counting rows to counting the EngagementNumber.

2 extra points for a correct explanation of what is happening, and why.
*/

SELECT EntStageName, COUNT(EngagementNumber) AS [Number of Engagements]
FROM Entertainers LEFT OUTER JOIN Engagements ON Entertainers.EntertainerID = Engagements.EntertainerID
GROUP BY EntStageName
ORDER BY 2 DESC;

--3
/*
Display the entertainer stage name and the largest ContractPrice for each
entertainer. Display all entertainers, and in case there are entertainers with
no engagements, replace the null value with 0. Label the second column
Biggest Contract.
*/

SELECT EntStageName, ISNULL(MAX(ContractPrice), '0') AS [Biggest Contract]
FROM Entertainers LEFT OUTER JOIN Engagements ON Entertainers.EntertainerID = Engagements.EntertainerID
GROUP BY EntStageName

--4
/*
Display the stage name and the average contract price for each entertainer
who has had an engagement. Label the second column Average Contract Price and
display the average contract price with two decimal places and a dollar sign.
*/

SELECT EntStageName, '$ '+STR(AVG(ContractPrice), 8, 2) AS [Average Contract Price]
FROM Entertainers INNER JOIN Engagements ON Entertainers.EntertainerID = Engagements.EntertainerID
GROUP BY EntStageName;

--5
/*
Display the first and last name of every customer. For each customer who has a
musical preference, also display the number of musical styles.
*/

SELECT CustFirstName, CustLastName, COUNT(Musical_Preferences.CustomerID) AS [Prefrence]
FROM Customers INNER JOIN Musical_Preferences ON Customers.CustomerID = Musical_Preferences.CustomerID
GROUP BY CustFirstName, CustLastName;

--6
/*
Modify number 5 to display only those customers who have more than 2 preferred
styles.
*/

SELECT CustFirstName, CustLastName, COUNT(Musical_Preferences.CustomerID) AS [Prefrence]
FROM Customers INNER JOIN Musical_Preferences ON Customers.CustomerID = Musical_Preferences.CustomerID
GROUP BY CustFirstName, CustLastName
HAVING COUNT(Musical_Preferences.CustomerID) > 2;

--7
/*
For every agent with a commission rate greater than 4% (.04), compute the sum
of their commissions (contract price * commission rate). Display the agent's
first and last name and the sum of his commissions. Display the commissions
with a dollar sign and two decimal places.
*/

SELECT AgtFirstName, AgtLastName, '$ '+STR(SUM (ContractPrice*CommissionRate), 8, 2) AS [Commissions]
FROM Agents INNER JOIN Engagements ON Agents.AgentID = Engagements.AgentID
GROUP BY AgtFirstName, AgtLastName
HAVING SUM(CommissionRate) > .04;

--8
/*
Modify #7 to display only those agents whose sum exceeds $1200
*/

SELECT AgtFirstName, AgtLastName, '$ '+STR(SUM (ContractPrice*CommissionRate), 8, 2) AS [Commissions]
FROM Agents INNER JOIN Engagements ON Agents.AgentID = Engagements.AgentID
GROUP BY AgtFirstName, AgtLastName
HAVING SUM (ContractPrice*CommissionRate) > 1200;

--9
/*
For every agent, display the agent's first and last name and the number of
engagements he has booked. Display the AgentID, the agent's first name and
the agent's last name, as well as the number of engagements. label the last
column Engagements Booked.
*/

SELECT Agents.AgentID, AgtFirstName, AgtLastName, COUNT(EngagementNumber) AS [Engagements Booked]
FROM Agents INNER JOIN Engagements ON Agents.AgentID = Engagements.AgentID
GROUP BY Agents.AgentID, AgtFirstName, AgtLastName;

--10
/*
For every agent whose last name begins with a V, display the agent's first
and last name and the number of engagements he has booked. Display the AgentID,
the agent's first name and the agent's last name, as well as the number of
engagements. Label the last column Engagements Booked.
*/

SELECT Agents.AgentID, AgtFirstName, AgtLastName, COUNT(EngagementNumber) AS [Engagements Booked]
FROM Agents INNER JOIN Engagements ON Agents.AgentID = Engagements.AgentID
GROUP BY Agents.AgentID, AgtFirstName, AgtLastName
HAVING CHARINDEX('V',AgtFirstName)
