------------------------------------------------------------------------
-- John David Ayers ----------------------------------------------------
-- October 29, 2009 ----------------------------------------------------
-- Homework - INNER JOINS ----------------------------------------------
------------------------------------------------------------------------

--1
/*
Join the Members and Entertainer_Members tables to create a query that will
display the following: The member's first name, last name, phone number, gender
and the EntertainerID.
*/

SELECT MbrFirstName, MbrLastName, MbrPhoneNumber, Gender, EntertainerID
FROM Members as m INNER JOIN Entertainer_Members as em ON m.MemberID = em.MemberID;

--2
/*
Join the Members, Entertainer_Members and Entertainers tables to create a query
that will display the following: the entertainer stage name, the date entered,
and the member's first name, last name, phone number and gender. Sort the
data by the stage name, within the stage name, sort by member last name, then
member first name. Display only the date, no time information for the date
entered field.
*/

SELECT EntStageName, CONVERT(CHAR(12),DateEntered,107), MbrFirstName, MbrLastName, MbrPhoneNumber, Gender
FROM ((Members as m INNER JOIN Entertainer_Members as em ON m.MemberID = em.MemberID)INNER JOIN Entertainers as e ON e.EntertainerID = em.EntertainerID);

--3
/*
Join the Entertainers, Entertainer_Styles and Musical_Styles tables to
create a query that will display the entertainer stage name, the entertainer
web page, and the style name.
*/

SELECT EntStageName, EntWebPage, StyleName
FROM ((Entertainers as e INNER JOIN Entertainer_Styles as es ON e.EntertainerID = es.EntertainerID)INNER JOIN Musical_Styles as ms ON ms.StyleID = es.StyleID);

--4
/*
Join the Customers, Musical_Preferences and Musical_Styles tables to create a
query that will display the customer's first name, customer's last name,
and the style name of his preferred music.
*/

SELECT CustFirstName, CustLastName, StyleName
FROM ((Customers as c INNER JOIN Musical_Preferences as mp ON c.CustomerID = mp.CustomerID)INNER JOIN Musical_Styles as ms ON ms.StyleID = mp.StyleID);

--5
/*
Redo number 4 and display only rows for those customers who prefer Country
or Country Rock.
*/

SELECT CustFirstName, CustLastName, StyleName
FROM ((Customers as c INNER JOIN Musical_Preferences as mp ON c.CustomerID = mp.CustomerID)INNER JOIN Musical_Styles as ms ON ms.StyleID = mp.StyleID)
WHERE StyleName IN ('Country','Country Rock');

--6
/*
Sometimes you may need to join tables together just to reference a column
name in a WHERE clause. The boss has received a request for an Elvis
impersonator. Create a query to display the member's first name and
last name concatenated together with a space, and the member's phone number,
for all members who are male and who are in a group (Entertainer_Members) that
performs Elvis music (Entertainer_Styles, Musical_Styles).
*/

SELECT MbrFirstName + ' ' + MbrLastName, MbrPhoneNumber, Gender
FROM (((Members as m INNER JOIN Entertainer_Members as em ON m.MemberID = em.MemberID)
INNER JOIN Entertainer_Styles as es ON es.EntertainerID = em.EntertainerID)
INNER JOIN Musical_Styles as ms ON ms.StyleID = es.StyleID)
WHERE Gender='M' AND Status=1 AND StyleName='Elvis';

--7
/*
Join the Customers, Entertainers, and Engagements to create a query that will
display the customers first and last name, the start date and end date
(do not display time), the contract price and the stage name of the
entertainer for all engagements.
*/

SELECT CustFirstName, CustLastName, CONVERT(CHAR(12),StartDate,107), CONVERT(CHAR(12),EndDate,107), ContractPrice, EntStageName
FROM ((Customers as c INNER JOIN Engagements as eg ON c.CustomerID = eg.CustomerID)INNER JOIN Entertainers as e ON e.EntertainerID = eg.EntertainerID)

--8
/*
Modify number 7 to display only engagements that started during 2008
and that had a contract price of over 1000.00. Sort the rows by the contract
price in descending order.
*/

SELECT CustFirstName, CustLastName, CONVERT(CHAR(12),StartDate,107), CONVERT(CHAR(12),EndDate,107), ContractPrice, EntStageName
FROM ((Customers as c INNER JOIN Engagements as eg ON c.CustomerID = eg.CustomerID)INNER JOIN Entertainers as e ON e.EntertainerID = eg.EntertainerID)
WHERE ContractPrice>1000 AND StartDate BETWEEN '1/1/2008' AND '12/31/2008';

--9
/*
Create a query that will display the entertainer stage name, the contract
price, the agents first and last name in one column, and a calculated
column that will display the agent's commission (multiply his commissionRate
by the contract price) for all engagements.
*/

SELECT EntStageName, ContractPrice, AgtFirstName + ' ' + AgtLastName, CommissionRate*ContractPrice
FROM ((Entertainers as e INNER JOIN Engagements as eg ON e.EntertainerID = eg.EntertainerID)INNER JOIN Agents as a ON a.AgentID = eg.AgentID)

--10
/*
The boss is thinking about assigning customers to an agent. Create a
query that will match agents to customers based on their zip code
(join Agents and Customers on their respective zip code fields.
Display the Agents last and first name in one column, the zip code in the
2nd column, and the customers last and first name in the third column.
*/

SELECT AgtLastName + ', ' + AgtFirstName as [Agent Name], AgtZipCode, CustLastName + ', ' + CustFirstName as [Customer Name]
FROM (Agents as a INNER JOIN Customers as c ON a.AgtZipCode = c.CustZipCode)



--What happens when two agents live in the same zip code?
The world will explode!
